asterisk -rx /var/log/asterisk/messages mysql -u root -p asterisk -e "SELECT COUNT(*) FROM vicidial_log LIMIT 1;" mysql -u root -p asterisk -e "SELECT COUNT(*) FROM vicidial_log LIMIT 1;" mysql -u root -p asterisk -e "SELECT COUNT(*) FROM vicidial_log LIMIT 1;" /etc/asterisk/asterisk.conf /var/log/asterisk/cdr-csv/ vicidial_log vicidial_closer_log vicidial_carrier_log DESCRIBE vicidial_log; DESCRIBE vicidial_log; DESCRIBE vicidial_log; campaign_id length_in_sec phone_number called_number DESCRIBE vicidial_closer_log; DESCRIBE vicidial_closer_log; DESCRIBE vicidial_closer_log; CREATE USER 'cdr_reporter'@'localhost' IDENTIFIED BY 'SecurePassword123!'; GRANT SELECT ON asterisk.* TO 'cdr_reporter'@'localhost'; GRANT SELECT ON asterisk.vicidial_log TO 'cdr_reporter'@'localhost'; GRANT SELECT ON asterisk.vicidial_closer_log TO 'cdr_reporter'@'localhost'; FLUSH PRIVILEGES; CREATE USER 'cdr_reporter'@'localhost' IDENTIFIED BY 'SecurePassword123!'; GRANT SELECT ON asterisk.* TO 'cdr_reporter'@'localhost'; GRANT SELECT ON asterisk.vicidial_log TO 'cdr_reporter'@'localhost'; GRANT SELECT ON asterisk.vicidial_closer_log TO 'cdr_reporter'@'localhost'; FLUSH PRIVILEGES; CREATE USER 'cdr_reporter'@'localhost' IDENTIFIED BY 'SecurePassword123!'; GRANT SELECT ON asterisk.* TO 'cdr_reporter'@'localhost'; GRANT SELECT ON asterisk.vicidial_log TO 'cdr_reporter'@'localhost'; GRANT SELECT ON asterisk.vicidial_closer_log TO 'cdr_reporter'@'localhost'; FLUSH PRIVILEGES; CREATE VIEW cdr_daily_summary AS SELECT DATE(call_date) AS call_day, campaign_id, COUNT(*) AS total_calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate, SUM(talk_sec) AS total_talk_seconds, ROUND(AVG(talk_sec), 2) AS avg_talk_seconds, ROUND(AVG(length_in_sec), 2) AS avg_call_duration FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY DATE(call_date), campaign_id; CREATE VIEW cdr_daily_summary AS SELECT DATE(call_date) AS call_day, campaign_id, COUNT(*) AS total_calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate, SUM(talk_sec) AS total_talk_seconds, ROUND(AVG(talk_sec), 2) AS avg_talk_seconds, ROUND(AVG(length_in_sec), 2) AS avg_call_duration FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY DATE(call_date), campaign_id; CREATE VIEW cdr_daily_summary AS SELECT DATE(call_date) AS call_day, campaign_id, COUNT(*) AS total_calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate, SUM(talk_sec) AS total_talk_seconds, ROUND(AVG(talk_sec), 2) AS avg_talk_seconds, ROUND(AVG(length_in_sec), 2) AS avg_call_duration FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY DATE(call_date), campaign_id; mysql -u cdr_reporter -p asterisk -e "SELECT * FROM cdr_daily_summary LIMIT 10;" mysql -u cdr_reporter -p asterisk -e "SELECT * FROM cdr_daily_summary LIMIT 10;" mysql -u cdr_reporter -p asterisk -e "SELECT * FROM cdr_daily_summary LIMIT 10;" SELECT call_date, user, campaign_id, phone_number, status, length_in_sec, talk_sec FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 24 HOUR) AND status IN ('NO ANS', 'BUSY', 'NOT CALLABLE', 'DNC') AND campaign_id = 'YOUR_CAMPAIGN_ID' ORDER BY call_date DESC LIMIT 100; SELECT call_date, user, campaign_id, phone_number, status, length_in_sec, talk_sec FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 24 HOUR) AND status IN ('NO ANS', 'BUSY', 'NOT CALLABLE', 'DNC') AND campaign_id = 'YOUR_CAMPAIGN_ID' ORDER BY call_date DESC LIMIT 100; SELECT call_date, user, campaign_id, phone_number, status, length_in_sec, talk_sec FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 24 HOUR) AND status IN ('NO ANS', 'BUSY', 'NOT CALLABLE', 'DNC') AND campaign_id = 'YOUR_CAMPAIGN_ID' ORDER BY call_date DESC LIMIT 100; SELECT user, COUNT(*) AS calls_handled, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate, ROUND(AVG(talk_sec), 2) AS avg_talk_sec, SUM(talk_sec) AS total_talk_sec, ROUND(AVG(length_in_sec), 2) AS avg_call_length FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND campaign_id = 'YOUR_CAMPAIGN_ID' AND user NOT IN ('SYSTEM', 'TRANSFER') GROUP BY user ORDER BY conversion_rate DESC; SELECT user, COUNT(*) AS calls_handled, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate, ROUND(AVG(talk_sec), 2) AS avg_talk_sec, SUM(talk_sec) AS total_talk_sec, ROUND(AVG(length_in_sec), 2) AS avg_call_length FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND campaign_id = 'YOUR_CAMPAIGN_ID' AND user NOT IN ('SYSTEM', 'TRANSFER') GROUP BY user ORDER BY conversion_rate DESC; SELECT user, COUNT(*) AS calls_handled, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate, ROUND(AVG(talk_sec), 2) AS avg_talk_sec, SUM(talk_sec) AS total_talk_sec, ROUND(AVG(length_in_sec), 2) AS avg_call_length FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND campaign_id = 'YOUR_CAMPAIGN_ID' AND user NOT IN ('SYSTEM', 'TRANSFER') GROUP BY user ORDER BY conversion_rate DESC; SELECT CASE WHEN direction = 'INBOUND' THEN 'Inbound' ELSE 'Outbound' END AS call_type, COUNT(*) AS total_calls, COUNT(CASE WHEN status IN ('SALE', 'XFER') THEN 1 END) AS conversions, ROUND(COUNT(CASE WHEN status IN ('SALE', 'XFER') THEN 1 END) / COUNT(*) * 100, 2) AS conversion_rate, ROUND(AVG(talk_sec), 2) AS avg_talk_sec, ROUND(AVG(length_in_sec), 2) AS avg_call_duration FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY direction; SELECT CASE WHEN direction = 'INBOUND' THEN 'Inbound' ELSE 'Outbound' END AS call_type, COUNT(*) AS total_calls, COUNT(CASE WHEN status IN ('SALE', 'XFER') THEN 1 END) AS conversions, ROUND(COUNT(CASE WHEN status IN ('SALE', 'XFER') THEN 1 END) / COUNT(*) * 100, 2) AS conversion_rate, ROUND(AVG(talk_sec), 2) AS avg_talk_sec, ROUND(AVG(length_in_sec), 2) AS avg_call_duration FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY direction; SELECT CASE WHEN direction = 'INBOUND' THEN 'Inbound' ELSE 'Outbound' END AS call_type, COUNT(*) AS total_calls, COUNT(CASE WHEN status IN ('SALE', 'XFER') THEN 1 END) AS conversions, ROUND(COUNT(CASE WHEN status IN ('SALE', 'XFER') THEN 1 END) / COUNT(*) * 100, 2) AS conversion_rate, ROUND(AVG(talk_sec), 2) AS avg_talk_sec, ROUND(AVG(length_in_sec), 2) AS avg_call_duration FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY direction; SELECT campaign_id, COUNT(*) AS calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate, ROUND(AVG(talk_sec), 2) AS avg_talk_sec, SUM(talk_sec) / 3600 AS hours_talk_time FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND user NOT IN ('SYSTEM', 'TRANSFER') GROUP BY campaign_id ORDER BY conversion_rate DESC; SELECT campaign_id, COUNT(*) AS calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate, ROUND(AVG(talk_sec), 2) AS avg_talk_sec, SUM(talk_sec) / 3600 AS hours_talk_time FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND user NOT IN ('SYSTEM', 'TRANSFER') GROUP BY campaign_id ORDER BY conversion_rate DESC; SELECT campaign_id, COUNT(*) AS calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate, ROUND(AVG(talk_sec), 2) AS avg_talk_sec, SUM(talk_sec) / 3600 AS hours_talk_time FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND user NOT IN ('SYSTEM', 'TRANSFER') GROUP BY campaign_id ORDER BY conversion_rate DESC; SELECT DATE(call_date) AS call_date, COUNT(*) AS total_calls, COUNT(CASE WHEN length_in_sec < 5 THEN 1 END) AS dropped_early, ROUND(COUNT(CASE WHEN length_in_sec < 5 THEN 1 END) / COUNT(*) * 100, 2) AS drop_rate_pct, COUNT(CASE WHEN status IN ('NO ANS', 'BUSY') THEN 1 END) AS unreachable, ROUND(COUNT(CASE WHEN status IN ('NO ANS', 'BUSY') THEN 1 END) / COUNT(*) * 100, 2) AS unreachable_pct FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY DATE(call_date) ORDER BY call_date DESC; SELECT DATE(call_date) AS call_date, COUNT(*) AS total_calls, COUNT(CASE WHEN length_in_sec < 5 THEN 1 END) AS dropped_early, ROUND(COUNT(CASE WHEN length_in_sec < 5 THEN 1 END) / COUNT(*) * 100, 2) AS drop_rate_pct, COUNT(CASE WHEN status IN ('NO ANS', 'BUSY') THEN 1 END) AS unreachable, ROUND(COUNT(CASE WHEN status IN ('NO ANS', 'BUSY') THEN 1 END) / COUNT(*) * 100, 2) AS unreachable_pct FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY DATE(call_date) ORDER BY call_date DESC; SELECT DATE(call_date) AS call_date, COUNT(*) AS total_calls, COUNT(CASE WHEN length_in_sec < 5 THEN 1 END) AS dropped_early, ROUND(COUNT(CASE WHEN length_in_sec < 5 THEN 1 END) / COUNT(*) * 100, 2) AS drop_rate_pct, COUNT(CASE WHEN status IN ('NO ANS', 'BUSY') THEN 1 END) AS unreachable, ROUND(COUNT(CASE WHEN status IN ('NO ANS', 'BUSY') THEN 1 END) / COUNT(*) * 100, 2) AS unreachable_pct FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY DATE(call_date) ORDER BY call_date DESC; asterisk -rx "core show channels" asterisk -rx "core show channels" asterisk -rx "core show channels" asterisk -rx "sip show peers" asterisk -rx "sip show peers" asterisk -rx "sip show peers" asterisk -rx "cdr show status" asterisk -rx "cdr show status" asterisk -rx "cdr show status" /etc/asterisk/cdr.conf [general] enable=yes [general] enable=yes [general] enable=yes asterisk -rx "module reload cdr" asterisk -rx "module reload cdr" asterisk -rx "module reload cdr" #!/bin/bash # live_cdr_monitor.sh - Real-time call metrics while true; do clear echo "=== ViciDial CDR Live Monitor ===" echo "Time: $(date)" echo "" # Current active calls echo "--- Active Calls ---" asterisk -rx "core show channels" | grep -E "^SIP|^Agent" | head -5 # Calls in last hour echo "" echo "--- Last Hour Metrics ---" mysql -u cdr_reporter -p'SecurePassword123!' asterisk -e " SELECT COUNT(*) as calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) as conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as conv_rate FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 1 HOUR); " sleep 30 done #!/bin/bash # live_cdr_monitor.sh - Real-time call metrics while true; do clear echo "=== ViciDial CDR Live Monitor ===" echo "Time: $(date)" echo "" # Current active calls echo "--- Active Calls ---" asterisk -rx "core show channels" | grep -E "^SIP|^Agent" | head -5 # Calls in last hour echo "" echo "--- Last Hour Metrics ---" mysql -u cdr_reporter -p'SecurePassword123!' asterisk -e " SELECT COUNT(*) as calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) as conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as conv_rate FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 1 HOUR); " sleep 30 done #!/bin/bash # live_cdr_monitor.sh - Real-time call metrics while true; do clear echo "=== ViciDial CDR Live Monitor ===" echo "Time: $(date)" echo "" # Current active calls echo "--- Active Calls ---" asterisk -rx "core show channels" | grep -E "^SIP|^Agent" | head -5 # Calls in last hour echo "" echo "--- Last Hour Metrics ---" mysql -u cdr_reporter -p'SecurePassword123!' asterisk -e " SELECT COUNT(*) as calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) as conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as conv_rate FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 1 HOUR); " sleep 30 done chmod +x live_cdr_monitor.sh ./live_cdr_monitor.sh chmod +x live_cdr_monitor.sh ./live_cdr_monitor.sh chmod +x live_cdr_monitor.sh ./live_cdr_monitor.sh #!/bin/bash # export_cdr_csv.sh OUTPUT_FILE="/tmp/cdr_export_$(date +%Y%m%d_%H%M%S).csv" mysql -u cdr_reporter -p'SecurePassword123!' asterisk \ -e "SELECT call_date, user, campaign_id, phone_number, status, length_in_sec, talk_sec, hold_sec FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY call_date DESC;" \ --batch --skip-column-names | \ sed 's/\t/,/g' > "$OUTPUT_FILE" echo "CDR export saved to: $OUTPUT_FILE" ls -lh "$OUTPUT_FILE" #!/bin/bash # export_cdr_csv.sh OUTPUT_FILE="/tmp/cdr_export_$(date +%Y%m%d_%H%M%S).csv" mysql -u cdr_reporter -p'SecurePassword123!' asterisk \ -e "SELECT call_date, user, campaign_id, phone_number, status, length_in_sec, talk_sec, hold_sec FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY call_date DESC;" \ --batch --skip-column-names | \ sed 's/\t/,/g' > "$OUTPUT_FILE" echo "CDR export saved to: $OUTPUT_FILE" ls -lh "$OUTPUT_FILE" #!/bin/bash # export_cdr_csv.sh OUTPUT_FILE="/tmp/cdr_export_$(date +%Y%m%d_%H%M%S).csv" mysql -u cdr_reporter -p'SecurePassword123!' asterisk \ -e "SELECT call_date, user, campaign_id, phone_number, status, length_in_sec, talk_sec, hold_sec FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY call_date DESC;" \ --batch --skip-column-names | \ sed 's/\t/,/g' > "$OUTPUT_FILE" echo "CDR export saved to: $OUTPUT_FILE" ls -lh "$OUTPUT_FILE" chmod +x export_cdr_csv.sh ./export_cdr_csv.sh chmod +x export_cdr_csv.sh ./export_cdr_csv.sh chmod +x export_cdr_csv.sh ./export_cdr_csv.sh #!/bin/bash # analyze_csv_cdr.sh CDR_DIR="/var/log/asterisk/cdr-csv" # Show recent CDR file structure head -1 "$CDR_DIR"/*.csv | tail -1 # Count calls by status in last day find "$CDR_DIR" -name "*.csv" -mtime -1 -exec cat {} \; | \ awk -F',' '{print $NF}' | sort | uniq -c | sort -rn #!/bin/bash # analyze_csv_cdr.sh CDR_DIR="/var/log/asterisk/cdr-csv" # Show recent CDR file structure head -1 "$CDR_DIR"/*.csv | tail -1 # Count calls by status in last day find "$CDR_DIR" -name "*.csv" -mtime -1 -exec cat {} \; | \ awk -F',' '{print $NF}' | sort | uniq -c | sort -rn #!/bin/bash # analyze_csv_cdr.sh CDR_DIR="/var/log/asterisk/cdr-csv" # Show recent CDR file structure head -1 "$CDR_DIR"/*.csv | tail -1 # Count calls by status in last day find "$CDR_DIR" -name "*.csv" -mtime -1 -exec cat {} \; | \ awk -F',' '{print $NF}' | sort | uniq -c | sort -rn SELECT HOUR(call_date) AS hour_of_day, COUNT(*) AS calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate, ROUND(AVG(talk_sec), 2) AS avg_talk_sec FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND DAYOFWEEK(call_date) NOT IN (1, 7) GROUP BY HOUR(call_date) ORDER BY conversion_rate DESC; SELECT HOUR(call_date) AS hour_of_day, COUNT(*) AS calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate, ROUND(AVG(talk_sec), 2) AS avg_talk_sec FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND DAYOFWEEK(call_date) NOT IN (1, 7) GROUP BY HOUR(call_date) ORDER BY conversion_rate DESC; SELECT HOUR(call_date) AS hour_of_day, COUNT(*) AS calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate, ROUND(AVG(talk_sec), 2) AS avg_talk_sec FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND DAYOFWEEK(call_date) NOT IN (1, 7) GROUP BY HOUR(call_date) ORDER BY conversion_rate DESC; SELECT phone_number, COUNT(*) AS call_count, COUNT(DISTINCT user) AS unique_agents, COUNT(DISTINCT campaign_id) AS unique_campaigns, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY phone_number HAVING call_count > 100 AND conversion_rate < 5 AND unique_agents > 5 ORDER BY call_count DESC LIMIT 20; SELECT phone_number, COUNT(*) AS call_count, COUNT(DISTINCT user) AS unique_agents, COUNT(DISTINCT campaign_id) AS unique_campaigns, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY phone_number HAVING call_count > 100 AND conversion_rate < 5 AND unique_agents > 5 ORDER BY call_count DESC LIMIT 20; SELECT phone_number, COUNT(*) AS call_count, COUNT(DISTINCT user) AS unique_agents, COUNT(DISTINCT campaign_id) AS unique_campaigns, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conversion_rate FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY phone_number HAVING call_count > 100 AND conversion_rate < 5 AND unique_agents > 5 ORDER BY call_count DESC LIMIT 20; SELECT vl1.lead_id, COUNT(*) AS call_attempts, vl1.status AS final_status, MIN(vl1.call_date) AS first_call, MAX(vl1.call_date) AS last_call, ROUND(TIMESTAMPDIFF(HOUR, MIN(vl1.call_date), MAX(vl1.call_date)), 1) AS hours_to_resolution FROM vicidial_log vl1 WHERE vl1.call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND vl1.status IN ('SALE', 'XFER') GROUP BY vl1.lead_id HAVING call_attempts > 1 ORDER BY call_attempts DESC LIMIT 50; SELECT vl1.lead_id, COUNT(*) AS call_attempts, vl1.status AS final_status, MIN(vl1.call_date) AS first_call, MAX(vl1.call_date) AS last_call, ROUND(TIMESTAMPDIFF(HOUR, MIN(vl1.call_date), MAX(vl1.call_date)), 1) AS hours_to_resolution FROM vicidial_log vl1 WHERE vl1.call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND vl1.status IN ('SALE', 'XFER') GROUP BY vl1.lead_id HAVING call_attempts > 1 ORDER BY call_attempts DESC LIMIT 50; SELECT vl1.lead_id, COUNT(*) AS call_attempts, vl1.status AS final_status, MIN(vl1.call_date) AS first_call, MAX(vl1.call_date) AS last_call, ROUND(TIMESTAMPDIFF(HOUR, MIN(vl1.call_date), MAX(vl1.call_date)), 1) AS hours_to_resolution FROM vicidial_log vl1 WHERE vl1.call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND vl1.status IN ('SALE', 'XFER') GROUP BY vl1.lead_id HAVING call_attempts > 1 ORDER BY call_attempts DESC LIMIT 50; SELECT call_date, user, campaign_id, phone_number, status, recording_id, talk_sec, CASE WHEN recording_id IS NULL THEN 'NO RECORDING' WHEN talk_sec > 0 THEN 'RECORDED' ELSE 'NO CONVERSATION' END AS compliance_status FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 1 DAY) AND status IN ('SALE', 'XFER') AND recording_id IS NULL ORDER BY call_date DESC; SELECT call_date, user, campaign_id, phone_number, status, recording_id, talk_sec, CASE WHEN recording_id IS NULL THEN 'NO RECORDING' WHEN talk_sec > 0 THEN 'RECORDED' ELSE 'NO CONVERSATION' END AS compliance_status FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 1 DAY) AND status IN ('SALE', 'XFER') AND recording_id IS NULL ORDER BY call_date DESC; SELECT call_date, user, campaign_id, phone_number, status, recording_id, talk_sec, CASE WHEN recording_id IS NULL THEN 'NO RECORDING' WHEN talk_sec > 0 THEN 'RECORDED' ELSE 'NO CONVERSATION' END AS compliance_status FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 1 DAY) AND status IN ('SALE', 'XFER') AND recording_id IS NULL ORDER BY call_date DESC; #!/bin/bash # daily_cdr_report.sh REPORT_DATE=$(date +"%Y-%m-%d") RECIPIENT="[email protected]" mysql -u cdr_reporter -p'SecurePassword123!' asterisk > /tmp/daily_report.txt << EOF SELECT '=== DAILY CDR SUMMARY ===' as report; SELECT CONCAT('Report Date: ', DATE(NOW())); SELECT ''; SELECT 'CAMPAIGN PERFORMANCE' as section; SELECT campaign_id, COUNT(*) AS calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conv_rate FROM vicidial_log WHERE DATE(call_date) = DATE(NOW()) - INTERVAL 1 DAY GROUP BY campaign_id ORDER BY conv_rate DESC; SELECT ''; SELECT 'TOP AGENTS' as section; SELECT user, COUNT(*) AS calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(AVG(talk_sec), 2) AS avg_talk_sec FROM vicidial_log WHERE DATE(call_date) = DATE(NOW()) - INTERVAL 1 DAY AND user NOT IN ('SYSTEM', 'TRANSFER') GROUP BY user ORDER BY conversions DESC LIMIT 10; SELECT ''; SELECT 'CALL QUALITY METRICS' as section; SELECT COUNT(*) AS total_calls, COUNT(CASE WHEN length_in_sec < 5 THEN 1 END) AS dropped_calls, ROUND(COUNT(CASE WHEN length_in_sec < 5 THEN 1 END) / COUNT(*) * 100, 2) AS drop_rate FROM vicidial_log WHERE DATE(call_date) = DATE(NOW()) - INTERVAL 1 DAY; EOF mail -s "ViciDial CDR Report - $REPORT_DATE" "$RECIPIENT" < /tmp/daily_report.txt #!/bin/bash # daily_cdr_report.sh REPORT_DATE=$(date +"%Y-%m-%d") RECIPIENT="[email protected]" mysql -u cdr_reporter -p'SecurePassword123!' asterisk > /tmp/daily_report.txt << EOF SELECT '=== DAILY CDR SUMMARY ===' as report; SELECT CONCAT('Report Date: ', DATE(NOW())); SELECT ''; SELECT 'CAMPAIGN PERFORMANCE' as section; SELECT campaign_id, COUNT(*) AS calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conv_rate FROM vicidial_log WHERE DATE(call_date) = DATE(NOW()) - INTERVAL 1 DAY GROUP BY campaign_id ORDER BY conv_rate DESC; SELECT ''; SELECT 'TOP AGENTS' as section; SELECT user, COUNT(*) AS calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(AVG(talk_sec), 2) AS avg_talk_sec FROM vicidial_log WHERE DATE(call_date) = DATE(NOW()) - INTERVAL 1 DAY AND user NOT IN ('SYSTEM', 'TRANSFER') GROUP BY user ORDER BY conversions DESC LIMIT 10; SELECT ''; SELECT 'CALL QUALITY METRICS' as section; SELECT COUNT(*) AS total_calls, COUNT(CASE WHEN length_in_sec < 5 THEN 1 END) AS dropped_calls, ROUND(COUNT(CASE WHEN length_in_sec < 5 THEN 1 END) / COUNT(*) * 100, 2) AS drop_rate FROM vicidial_log WHERE DATE(call_date) = DATE(NOW()) - INTERVAL 1 DAY; EOF mail -s "ViciDial CDR Report - $REPORT_DATE" "$RECIPIENT" < /tmp/daily_report.txt #!/bin/bash # daily_cdr_report.sh REPORT_DATE=$(date +"%Y-%m-%d") RECIPIENT="[email protected]" mysql -u cdr_reporter -p'SecurePassword123!' asterisk > /tmp/daily_report.txt << EOF SELECT '=== DAILY CDR SUMMARY ===' as report; SELECT CONCAT('Report Date: ', DATE(NOW())); SELECT ''; SELECT 'CAMPAIGN PERFORMANCE' as section; SELECT campaign_id, COUNT(*) AS calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS conv_rate FROM vicidial_log WHERE DATE(call_date) = DATE(NOW()) - INTERVAL 1 DAY GROUP BY campaign_id ORDER BY conv_rate DESC; SELECT ''; SELECT 'TOP AGENTS' as section; SELECT user, COUNT(*) AS calls, SUM(CASE WHEN status IN ('SALE', 'XFER') THEN 1 ELSE 0 END) AS conversions, ROUND(AVG(talk_sec), 2) AS avg_talk_sec FROM vicidial_log WHERE DATE(call_date) = DATE(NOW()) - INTERVAL 1 DAY AND user NOT IN ('SYSTEM', 'TRANSFER') GROUP BY user ORDER BY conversions DESC LIMIT 10; SELECT ''; SELECT 'CALL QUALITY METRICS' as section; SELECT COUNT(*) AS total_calls, COUNT(CASE WHEN length_in_sec < 5 THEN 1 END) AS dropped_calls, ROUND(COUNT(CASE WHEN length_in_sec < 5 THEN 1 END) / COUNT(*) * 100, 2) AS drop_rate FROM vicidial_log WHERE DATE(call_date) = DATE(NOW()) - INTERVAL 1 DAY; EOF mail -s "ViciDial CDR Report - $REPORT_DATE" "$RECIPIENT" < /tmp/daily_report.txt crontab -e 0 8 * * * /root/daily_cdr_report.sh 0 8 * * * /root/daily_cdr_report.sh 0 8 * * * /root/daily_cdr_report.sh #!/bin/bash # cdr_anomaly_alert.sh THRESHOLD_DROP_RATE=15 # Alert if drop rate exceeds 15% ALERT_EMAIL="[email protected]" DROP_RATE=$(mysql -u cdr_reporter -p'SecurePassword123!' asterisk -se " SELECT ROUND( COUNT(CASE WHEN length_in_sec < 5 THEN 1 END) / COUNT(*) * 100, 2 ) FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 1 HOUR); ") if (( $(echo "$DROP_RATE > $THRESHOLD_DROP_RATE" | bc -l) )); then echo "ALERT: Call drop rate is $DROP_RATE% (threshold: $THRESHOLD_DROP_RATE%)" | \ mail -s "CRITICAL: High Call Drop Rate Detected" "$ALERT_EMAIL" fi #!/bin/bash # cdr_anomaly_alert.sh THRESHOLD_DROP_RATE=15 # Alert if drop rate exceeds 15% ALERT_EMAIL="[email protected]" DROP_RATE=$(mysql -u cdr_reporter -p'SecurePassword123!' asterisk -se " SELECT ROUND( COUNT(CASE WHEN length_in_sec < 5 THEN 1 END) / COUNT(*) * 100, 2 ) FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 1 HOUR); ") if (( $(echo "$DROP_RATE > $THRESHOLD_DROP_RATE" | bc -l) )); then echo "ALERT: Call drop rate is $DROP_RATE% (threshold: $THRESHOLD_DROP_RATE%)" | \ mail -s "CRITICAL: High Call Drop Rate Detected" "$ALERT_EMAIL" fi #!/bin/bash # cdr_anomaly_alert.sh THRESHOLD_DROP_RATE=15 # Alert if drop rate exceeds 15% ALERT_EMAIL="[email protected]" DROP_RATE=$(mysql -u cdr_reporter -p'SecurePassword123!' asterisk -se " SELECT ROUND( COUNT(CASE WHEN length_in_sec < 5 THEN 1 END) / COUNT(*) * 100, 2 ) FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 1 HOUR); ") if (( $(echo "$DROP_RATE > $THRESHOLD_DROP_RATE" | bc -l) )); then echo "ALERT: Call drop rate is $DROP_RATE% (threshold: $THRESHOLD_DROP_RATE%)" | \ mail -s "CRITICAL: High Call Drop Rate Detected" "$ALERT_EMAIL" fi */15 * * * * /root/cdr_anomaly_alert.sh */15 * * * * /root/cdr_anomaly_alert.sh */15 * * * * /root/cdr_anomaly_alert.sh asterisk -rx "cdr show status" /etc/asterisk/cdr.conf [general] enable=yes [general] enable=yes [general] enable=yes asterisk -rx "module reload cdr" asterisk -rx "module reload cdr" asterisk -rx "module reload cdr" asterisk -rx "cel show status" asterisk -rx "cel show status" asterisk -rx "cel show status" /etc/asterisk/extensions-vicidial.conf exten => s,n,Set(CDR(userfield)=${CAMPAIGN_ID}) exten => s,n,Set(CHANNEL(language)=en) exten => s,n,Set(CDR(userfield)=${CAMPAIGN_ID}) exten => s,n,Set(CHANNEL(language)=en) exten => s,n,Set(CDR(userfield)=${CAMPAIGN_ID}) exten => s,n,Set(CHANNEL(language)=en) ALTER TABLE vicidial_log ADD INDEX idx_call_date (call_date); ALTER TABLE vicidial_log ADD INDEX idx_campaign_user (campaign_id, user); ALTER TABLE vicidial_log ADD INDEX idx_status (status); ALTER TABLE vicidial_log ADD INDEX idx_call_date (call_date); ALTER TABLE vicidial_log ADD INDEX idx_campaign_user (campaign_id, user); ALTER TABLE vicidial_log ADD INDEX idx_status (status); ALTER TABLE vicidial_log ADD INDEX idx_call_date (call_date); ALTER TABLE vicidial_log ADD INDEX idx_campaign_user (campaign_id, user); ALTER TABLE vicidial_log ADD INDEX idx_status (status); mysql -u root -p asterisk -e "SHOW INDEXES FROM vicidial_log;" mysql -u root -p asterisk -e "SHOW INDEXES FROM vicidial_log;" mysql -u root -p asterisk -e "SHOW INDEXES FROM vicidial_log;" asterisk -rx usermod -a -G asterisk $USER newgrp asterisk asterisk -rx "core show channels" usermod -a -G asterisk $USER newgrp asterisk asterisk -rx "core show channels" usermod -a -G asterisk $USER newgrp asterisk asterisk -rx "core show channels" sudo asterisk -rx "core show channels" sudo asterisk -rx "core show channels" sudo asterisk -rx "core show channels" cat > ~/.my.cnf << EOF [client] user=cdr_reporter password=SecurePassword123! host=localhost database=asterisk EOF chmod 600 ~/.my.cnf cat > ~/.my.cnf << EOF [client] user=cdr_reporter password=SecurePassword123! host=localhost database=asterisk EOF chmod 600 ~/.my.cnf cat > ~/.my.cnf << EOF [client] user=cdr_reporter password=SecurePassword123! host=localhost database=asterisk EOF chmod 600 ~/.my.cnf mysql -e "SELECT COUNT(*) FROM vicidial_log;" mysql -e "SELECT COUNT(*) FROM vicidial_log;" mysql -e "SELECT COUNT(*) FROM vicidial_log;" recording_id grep -r "monitor_format" /etc/asterisk/*.conf | grep -v "^#" grep -r "recording" /etc/asterisk/*.conf | grep -v "^#" grep -r "monitor_format" /etc/asterisk/*.conf | grep -v "^#" grep -r "recording" /etc/asterisk/*.conf | grep -v "^#" grep -r "monitor_format" /etc/asterisk/*.conf | grep -v "^#" grep -r "recording" /etc/asterisk/*.conf | grep -v "^#" ls -lah /var/spool/asterisk/monitor/ ls -lah /var/spool/asterisk/vicidial/recordings/ ls -lah /var/spool/asterisk/monitor/ ls -lah /var/spool/asterisk/vicidial/recordings/ ls -lah /var/spool/asterisk/monitor/ ls -lah /var/spool/asterisk/vicidial/recordings/ mysql asterisk -e "SELECT variable_value FROM system_settings WHERE variable_name = 'recording_archive_days';" mysql asterisk -e "SELECT variable_value FROM system_settings WHERE variable_name = 'recording_archive_days';" mysql asterisk -e "SELECT variable_value FROM system_settings WHERE variable_name = 'recording_archive_days';" vicidial_log vicidial_closer_log - Access to the Asterisk server with root or sudo privileges - MySQL/MariaDB client installed and credentials for the asterisk database - Asterisk CLI access (typically via asterisk -rx command) - ViciDial installation (version 2.14 or later, though techniques apply to 2.12+) - Basic SQL knowledge (SELECT, WHERE, GROUP BY, JOIN) - Understanding of Asterisk dialplan basics (extensions, applications, variables) - Log file access at /var/log/asterisk/messages - Cron or systemd timer for scheduled tasks (optional but recommended) - Stored in /var/log/asterisk/cdr-csv/ (CSV format) - Fields: accountcode, src, dst, dcontext, channel, dstchannel, lastapp, lastdata, start, answer, end, duration, billsec, disposition, amaflags, uniqueid, userfield - Stored in MySQL tables: vicidial_log, vicidial_closer_log, vicidial_carrier_log - Includes agent activity, campaign info, lead scoring, inbound routing - Real-time accessible for live dashboards and compliance - uniqueid — Unique call identifier (matches Asterisk) - lead_id — ViciDial lead identifier - user — Agent username - campaign_id — Campaign code - call_date — Call timestamp - length_in_sec — Total call duration - talk_sec — Actual conversation time - hold_sec — Time on hold - status — Final disposition (SALE, XFER, NO ANS, etc.) - phone_number — Dialed number - called_number — Actual number called - Stale lead lists - Calling outside business hours - Network/codec issues on carrier side - Asterisk routing misconfiguration - Detecting hung calls - Verifying call routing during testing - Identifying resource exhaustion - Check /etc/asterisk/cdr.conf: - Reload CDR module: - If using CEL (Channel Event Logging), verify it's enabled: - Create .my.cnf in home directory: - Now scripts work without -u and -p flags: - Check where recordings are stored: - Common locations: - Check ViciDial settings for recording retention: - Identify and fix call quality issues within hours, not weeks - Optimize agent scheduling around peak conversion windows - Detect fraud and spam sources automatically - Verify compliance with recording and retention regulations - Benchmark agent and campaign performance objectively - Correlate talk time, hold time, and conversions for each agent - Track FCR (First Call Resolution) to identify training gaps - Monitor real-time metrics with custom dashboards - Eliminate unprofitable campaigns and time windows - Reduce waste on unreachable numbers and DNC violations - Optimize trunk routing and carrier selection based on call success rates - Start with the raw data: vicidial_log and vicidial_closer_log contain everything you need - Use database views: Create reusable views to simplify repeated analysis - Establish baselines: Know your historical performance before chasing improvements - Automate reporting: Scheduled queries and emails keep stakeholders aligned - Monitor in real-time: Catch problems while they're happening, not in historical reports - Index aggressively: Database performance degrades quickly without proper indexes - Document your queries: Future you will thank present you