How to extract actionable insights from Asterisk CDR in ViciDial

Asterisk Call Detail Records (CDRs) are more than just logs—they are a goldmine of operational intelligence. Every call that flows through your telecom stack leaves behind a digital footprint containing critical performance indicators, from call success rates and agent productivity to carrier reliability and routing efficiency. In production ViciDial environments, these records accumulate rapidly, often reaching thousands per day, making manual review not just inefficient but practically impossible. The solution lies in systematic analysis using SQL queries, command-line tools, and automated reporting pipelines.

The dual nature of CDR data in ViciDial ecosystems

Asterisk’s native CDR system and ViciDial’s enhanced logging layer serve distinct but complementary purposes. Understanding their interaction is the first step toward meaningful analysis.

Native Asterisk CDRs are generated by the system’s CDR module and typically stored as CSV files in /var/log/asterisk/cdr-csv/. These records include essential fields such as accountcode, src, dst, channel, start, answer, end, duration, and disposition. While comprehensive, they lack contextual details like agent identity, campaign affiliation, or lead-level outcomes.

ViciDial extends this raw data by enriching it with structured entries in MySQL tables such as vicidial_log, vicidial_closer_log, and vicidial_carrier_log. These tables capture agent-specific activities, campaign performance metrics, lead scoring, inbound routing decisions, and real-time disposition tracking. The result is a unified view where technical call data meets business intelligence.

Key ViciDial tables for CDR analysis

• `vicidial_log` — The central repository for call-level events. Its schema includes:
• uniqueid: A globally unique identifier, matching Asterisk’s native CDR
• lead_id: Links calls to specific leads in the ViciDial database
• user: The agent or system user associated with the call
• campaign_id: Identifies the campaign under which the call was made or received
• call_date: Timestamp of the call initiation
• length_in_sec: Total duration of the call
• talk_sec: Time spent in actual conversation
• hold_sec: Duration on hold
• status: Final disposition (e.g., SALE, XFER, NO ANS)
• phone_number and called_number: Dialed and actual destination numbers

• `vicidial_closer_log` — Stores granular disposition outcomes, agent notes, and lead disposition changes, enabling deeper performance tracking.

• `vicidial_carrier_log` — Tracks carrier-related metrics such as inbound/outbound routing performance, latency, and termination quality.

Preparing your environment for CDR analysis

Before diving into queries, ensure your analysis environment is secure, accessible, and optimized for performance.

Step 1: Create a dedicated read-only user

Avoid querying production databases with root privileges. Create a restricted user with SELECT-only access to relevant tables:

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;

Test connectivity using:

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

If the command fails, verify database credentials in /etc/asterisk/asterisk.conf and confirm the MySQL service is running.

Step 2: Build reusable SQL views

To streamline frequent analyses, create views that aggregate common metrics. For example, a daily campaign performance summary:

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;

Validate the view with:

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

Essential CDR queries for telecom optimization

1. Diagnosing call failures and routing issues

Failed calls—whether due to no answer, busy signals, or unroutable numbers—directly impact revenue and customer experience. Use this query to identify patterns:

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;

Common causes include:

• Outdated lead lists with incorrect numbers
• Calling outside permitted time windows
• Carrier-side routing or codec mismatches
• Misconfigured Asterisk dialplan or trunk settings

2. Measuring agent performance over time

Agent productivity isn’t just about call volume—it’s about outcomes. This query benchmarks individual agents based on conversion rates and talk time:

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;

Use these insights to:

• Identify top performers for coaching opportunities
• Detect agents with low talk time who may need training
• Flag users with high failure rates for performance reviews

3. Comparing inbound vs. outbound call behavior

Understanding traffic patterns helps optimize staffing, routing rules, and campaign strategies. This analysis categorizes calls by direction and measures success rates:

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_seconds
FROM vicidial_log
WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY call_type;

Unexpected patterns may reveal:

• Inbound spikes during off-hours, suggesting misconfigured IVR or routing
• High outbound failure rates in specific area codes
• Discrepancies between expected and actual call volumes

Automation and compliance: turning data into action

Once core analysis is established, automate reporting to maintain visibility without manual intervention. Use cron jobs or systemd timers to refresh daily summaries and deliver results via email, dashboards, or API endpoints.

Schedule a nightly job to generate the cdr_daily_summary report:

0 2 * * * mysql -u cdr_reporter -p asterisk -e "SELECT * FROM cdr_daily_summary;" > /var/reports/cdr_daily_summary_$(date +%Y%m%d).csv

For compliance, export call records to secure archives for audits or regulatory reviews. Ensure logs are encrypted, access-controlled, and retained according to your organization’s data retention policy.

With these techniques, Asterisk CDRs evolve from static logs into a strategic asset—one that powers smarter routing, higher agent efficiency, and lower operational costs. Whether you're troubleshooting call failures, benchmarking agents, or validating carrier performance, structured CDR analysis delivers clarity in complex telecom environments.