-
- #!/bin/bash
-
- domain=mydomain.net
- spprouters=`dig _spp._udp.$domain srv +short | awk '{print $NF}' | sed 's/\.$//'`
-
- dir="$(mktemp -d)"
-
- production_dir=/var/www/widgets/htdocs/cdr
- mkdir -p $production_dir
- echo "Working folder set to: $dir"
-
- #grab data off remote servers
- for server in $spprouters
- do
- echo "*****Grab SQL Data from Remote Server: $server *****"
- # Dump data to csv. Need elevated priv to do copy command
- psql -U master testdb -h $server -t -c "copy (SELECT CASE WHEN DATE_TRUNC('MONTH', ABSTIME(start_ts)) = DATE_TRUNC('MONTH', CURRENT_DATE) THEN 'current' ELSE DATE_TRUNC('MONTH', ABSTIME(start_ts))::DATE::TEXT END,
- id,
- status,
- class,
- dir,
- volid,
- f_user,
- t_user,
- ABSTIME(start_ts),
- ABSTIME(end_ts),
- elapsed,
- callid, f_uri, t_uri, lcr_tag
- FROM widgets) to '/tmp/"$server".db' With CSV DELIMITER ',';"
- scp -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null $server:/tmp/"$server.db" "$dir/"
-
- first_time=1
- #process data
- for tmpfile in $dir/$server.db
- do
- echo "Evaluate $tmpfile"
- number_of_records=$(cat $tmpfile | wc -l)
- echo "number of records in $tmpfile.db file is: $number_of_records"
- if [ "$number_of_records" -gt 0 ]; then
- echo "==== Processing $tmpfile ===="
- while IFS=, read -r date_for_filename rec_id rec_id_onwards; do
- #any CDR that's current will stay in the db. Don't add to inventory for deletion.
- if ! [ "$date_for_filename" == "current" ]
- then
- echo "$rec_id" >> $dir/$server.records_to_delete.txt;
- else
- if [ $first_time == 1 ]
- then
- #delete the existing .current.csv file if this first time trying to write to it this cycle.
- echo "tested first time and its true"
- tmpname="${server#*.}" # take file name and strip off server id
- rm $production_dir/$tmpname.current.csv
- first_time=0
- fi
- fi
- echo "$rec_id,$rec_id_onwards" >> $dir/$server.$date_for_filename.csv;
- done < $tmpfile
-
- mv $tmpfile $tmpfile.done
-
- #clean up any trailing , in records_to_delete file
- sed '$s/,$//' $dir/$server.records_to_delete.txt > /dev/null
-
- #copy records to delete file to respective servers. faster than running from widgets.
- scp -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null $dir/$server.records_to_delete.txt $server:/tmp/
-
- db_count_pre_prune=$(psql -U master testdb -h $server -t -c "SELECT COUNT(*) FROM widgets")
- echo "$db_count_pre_prune records in db before pruning db"
-
- # trigger the deletion on remote machine.
- deletion_status=$(psql -U master testdb -h $server -c "CREATE TEMP TABLE tmp_cdr (id int); COPY tmp_cdr FROM '/tmp/$server.records_to_delete.txt'; DELETE FROM widgets USING tmp_cdr WHERE widgets.id = tmp_cdr.id;")
- echo "$deletion_status is results of deletion attempt"
-
- db_count_post_prune=$(psql -U master testdb -h $server -t -c "SELECT COUNT(*) FROM widgets")
-
- echo "$db_count_post_prune $server " >> "$dir/audit.txt"
- else
- echo "No records to process on $server"
- fi
- done
- done
-
- #validate remaining records in db.
- total_current_csv_records="$(wc -l $dir/*current.csv |awk '{print $1}' | tail -1)"
- echo "$total_current_csv_records = number of records is *current.csv files"
- total_in_db=$(cat $dir/audit.txt | awk '{print $1}' | awk '{ sum+=$1 } END { print sum }')
- echo "$total_in_db = number of records remaining in db"
- if ! [ "$total_in_db" == "$total_current_csv_records" ]
- then
- echo "$(date) - whoops! Expecting $total_current_csv_records records (combined) in servers. But actual count is $total_in_db." >> $production_dir/cdr-prune.exceptions
- fi
-
- cd $dir
- for csvfile in *.csv
- do
- echo "$csvfile is the csvfile"
- tmpname="${csvfile#*.}" # take file name and strip off server id
- cat $csvfile >> $production_dir/$tmpname
- done
-
- #Clean up
- rm -r $dir
-
-