Asterisk CDR Analysis — Extract Insights from Call Detail Records

Master the complete workflow for analyzing Asterisk Call Detail Records in production ViciDial environments—from database queries to real-time metrics and automated reporting.

Introduction

Call Detail Records (CDRs) are the backbone of telecom operations. Every call passing through your Asterisk system generates raw data that contains critical insights: which numbers convert, where calls fail, which agents perform best, and where money is being wasted. In production ViciDial environments, CDR data accumulates rapidly—thousands of records daily—making manual inspection impossible.

This tutorial walks you through extracting, transforming, and analyzing CDR data using SQL queries, command-line tools, and custom scripts. You'll learn to identify trends, debug call failures, optimize routing, and generate compliance reports. These are skills you need right now if you're managing an Asterisk system under load.

Prerequisites

Before starting, verify you have:

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)

Verify database connectivity:

mysql -u root -p asterisk -e "SELECT COUNT(*) FROM vicidial_log LIMIT 1;"

If this fails, check /etc/asterisk/asterisk.conf for database credentials and verify the MySQL service is running.

Understanding Asterisk CDR Structure in ViciDial

Native Asterisk CDR vs. ViciDial Logging

Asterisk generates CDRs natively via the CDR module, but ViciDial overlays custom logging on top. Understanding both is essential:

Native Asterisk CDR:

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

ViciDial Enhanced Logging:

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

Critical ViciDial Tables for CDR Analysis

vicidial_log — The primary call record table:

DESCRIBE vicidial_log;

Key fields:

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

vicidial_closer_log — Detailed disposition and result data:

DESCRIBE vicidial_closer_log;

Includes agent notes, call outcomes, lead disposition changes.

vicidial_carrier_log — Inbound/outbound carrier metrics (if applicable).

Setting Up Your Analysis Environment

Create a Dedicated Reporting Database User

Don't query production using root. Create a read-only user:

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 Helper Views for Common Queries

Simplify repeated analysis with SQL views:

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;

Test the view:

mysql -u cdr_reporter -p asterisk -e "SELECT * FROM cdr_daily_summary LIMIT 10;"

Core CDR Analysis Queries

1. Identifying Failed Calls and Bottlenecks

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;

This reveals which numbers aren't working and why. High "NO ANS" rates might indicate:

Stale lead lists
Calling outside business hours
Network/codec issues on carrier side
Asterisk routing misconfiguration

2. Agent Performance Benchmarking

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;

3. Inbound vs. Outbound Call Analysis

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;

4. Campaign Performance Comparison

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;

5. Detecting Call Quality Issues

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;

Early call drops (< 5 seconds) indicate network issues, bad numbers, or hangup detection problems.

Real-Time Monitoring with Asterisk CLI

Monitor Active Calls

asterisk -rx "core show channels"

Output includes current calls, channel types, and duration. Useful for:

Detecting hung calls
Verifying call routing during testing
Identifying resource exhaustion

Check SIP Registration Status

asterisk -rx "sip show peers"

Ensure all configured trunks and agents are registered. Missing registrations explain inbound call failures.

Monitor CDR Recording

asterisk -rx "cdr show status"

Verify CDR module is active. If output shows "CDR logging disabled," check /etc/asterisk/cdr.conf:

[general]
enable=yes

Then reload:

asterisk -rx "module reload cdr"

Create a Live Dashboard Script

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

Make it executable:

chmod +x live_cdr_monitor.sh
./live_cdr_monitor.sh

Exporting and Processing CDR Data

Export to CSV for Excel/Sheets Analysis

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

Run:

chmod +x export_cdr_csv.sh
./export_cdr_csv.sh

Parse Raw Asterisk CDR Files

If you're using CSV CDR storage:

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

Advanced Analysis: Detecting Patterns and Anomalies

Finding Your Best Performing Time Windows

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;

This reveals peak efficiency hours. Most campaigns see 20-30% variation across day parts. Concentrate calls during peak windows.

Identifying Caller ID Spoofing / Fraud

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;

Numbers appearing across multiple agents/campaigns with poor conversion and high volume are often fraud or spam sources. Add to DNC list immediately.

Measuring First Call Resolution (FCR)

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;

Multiple calls to same lead = poor FCR. High numbers indicate ineffective agent training or bad lead qualification.

Compliance and Call Recording Verification

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;

Missing recordings on completed calls violate compliance in many jurisdictions. This query exposes gaps.

Creating Automated Reports and Alerts

Daily Email Report Script

#!/bin/bash
# daily_cdr_report.sh

REPORT_DATE=$(date +"%Y-%m-%d")
RECIPIENT="manager@yourcompany.com"

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

Schedule in crontab:

crontab -e

Add:

0 8 * * * /root/daily_cdr_report.sh

Alert on Anomalies

#!/bin/bash
# cdr_anomaly_alert.sh

THRESHOLD_DROP_RATE=15  # Alert if drop rate exceeds 15%
ALERT_EMAIL="ops@yourcompany.com"

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

Run every 15 minutes:

*/15 * * * * /root/cdr_anomaly_alert.sh

Troubleshooting CDR Issues

No CDR Records Being Generated

Symptom: asterisk -rx "cdr show status" shows "CDR logging disabled"

Solution:

Check /etc/asterisk/cdr.conf:

[general]
enable=yes

Reload CDR module:

asterisk -rx "module reload cdr"

If using CEL (Channel Event Logging), verify it's enabled:

asterisk -rx "cel show status"

CDR Records Exist But Have NULL Fields

Symptom: Database shows talk_sec, hold_sec as NULL

Reason: Variables not being set in dialplan. Asterisk requires explicit tracking via DIALPLAN() application.

Solution: In /etc/asterisk/extensions-vicidial.conf, ensure:

exten => s,n,Set(CDR(userfield)=${CAMPAIGN_ID})
exten => s,n,Set(CHANNEL(language)=en)

Database Queries Slow / Timeouts

Symptom: Large SELECT queries hang or timeout after 30 seconds

Solution: Add indexes:

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);

Verify indexes exist:

mysql -u root -p asterisk -e "SHOW INDEXES FROM vicidial_log;"

Asterisk CLI Commands Failing (Permission Denied)

Symptom: asterisk -rx returns "permission denied"

Solution: Your user isn't in the asterisk group:

usermod -a -G asterisk $USER
newgrp asterisk
asterisk -rx "core show channels"

Or run with sudo:

sudo asterisk -rx "core show channels"

MySQL Connection Issues from Scripts

Symptom: Bash scripts fail with "Access denied for user"

Solution:

Create .my.cnf in home directory:

cat > ~/.my.cnf << EOF
[client]
user=cdr_reporter
password=SecurePassword123!
host=localhost
database=asterisk
EOF

chmod 600 ~/.my.cnf

Now scripts work without -u and -p flags:

mysql -e "SELECT COUNT(*) FROM vicidial_log;"

Recording Files Not Found Despite recording_id in Database

Symptom: recording_id populated but actual audio files missing

Reason: Recordings stored in separate location, deleted by cleanup script, or disabled

Solution:

Check where recordings are stored:

grep -r "monitor_format" /etc/asterisk/*.conf | grep -v "^#"
grep -r "recording" /etc/asterisk/*.conf | grep -v "^#"

Common locations:

ls -lah /var/spool/asterisk/monitor/
ls -lah /var/spool/asterisk/vicidial/recordings/

Check ViciDial settings for recording retention:

mysql asterisk -e "SELECT variable_value FROM system_settings WHERE variable_name = 'recording_archive_days';"

Summary

Asterisk CDR analysis transforms raw call data into actionable business intelligence. The techniques in this tutorial enable you to:

Operational improvements:

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

Performance insights:

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

Cost optimization:

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

Key takeaways:

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

The Asterisk CDR system is powerful but requires intentional design and discipline. Start with the core queries provided here, customize them to your specific campaigns and metrics, and gradually build a comprehensive reporting infrastructure. Your operations team and finance department will demand this data sooner or later—better to build it on your schedule.

推荐订阅源

DEV Community