Analyzing Asterisk CDR for ViciDial Performance Optimization

Asterisk CDR Analysis — Extract Insights from Call Detail Records

Asterisk systems in production ViciDial environments generate thousands of raw Call Detail Records daily. This data holds critical insights into call conversion, failure points, and agent efficiency that manual inspection cannot capture.

Why This Matters

In high-load telecom operations, the gap between raw Asterisk logs and actionable business intelligence is bridged by structured SQL analysis. Failing to automate this analysis leads to wasted carrier costs on dead-end numbers and compliance risks due to missing call recordings, which are often only discovered through systematic database auditing.

Key Insights

• ViciDial enhances native Asterisk logging through specialized MySQL tables like vicidial_log and vicidial_closer_log to capture agent-specific performance metrics.
• Call quality issues are identified by measuring ‘dropped early’ calls lasting under 5 seconds, which often signal codec mismatches or carrier network failures.
• SQL performance in production environments depends on indexing; adding indexes to call_date and campaign_id prevents SELECT query timeouts during heavy traffic.
• Compliance tracking requires verifying that every SALE or XFER status entry in the database has a corresponding recording_id to meet legal requirements.

Working Examples

SQL View to simplify repeated daily summary analysis.

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;

Bash script for real-time Asterisk and ViciDial monitoring.

#!/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
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

Practical Applications

• Campaign management systems use HOUR(call_date) analysis to identify peak conversion windows. Pitfall: Calling outside these windows leads to a 20-30% drop in efficiency.
• Fraud detection systems analyze phone_number frequency across multiple agents. Pitfall: High-volume numbers with low conversions are often spam sources that need immediate DNC listing.
• Performance benchmarking systems group vicidial_log by user to calculate conversion rates. Pitfall: Querying production using root instead of a read-only reporting user creates security vulnerabilities.

References:

• https://dev.to/moisi_trungu_31647b7ac300/asterisk-cdr-analysis-extract-insights-from-call-detail-records-1382