The 5 MySQL Views Every VICIdial Admin Should Build Today

VICIdial's built-in reports cover the basics. They stop being enough around 25 agents, when your clients start asking for metrics the GUI doesn't have and the export options are too limited to build them yourself.

The good news: VICIdial stores everything in MySQL. Every call, every disposition, every agent state change, every pause code. It's all sitting in well-structured tables in the asterisk database. Once you know which tables matter, you can build reports that are faster, more flexible, and more useful than anything the admin panel offers.

The Tables That Matter

VICIdial has over 200 tables. For reporting, you need about 15. Here are the ones I hit most often:

Table	What It Stores	What You Get From It
vicidial_log	Every outbound call attempt	Call outcomes, talk time, wait time, campaign stats
vicidial_closer_log	Inbound/transfer call records	Queue times, handle times, service level
vicidial_agent_log	Agent state changes	Login time, pause time, productivity, utilization
vicidial_list	Lead/contact records	Lead data, status, called count, source
vicidial_users	Agent profiles	Names, groups, permissions
vicidial_campaigns	Campaign configuration	Dial method, settings
vicidial_carrier_log	SIP trunk records	Carrier-level call data
recording_log	Call recordings	Recording file paths, durations
vicidial_pause_codes	Pause reason definitions	Break types

Start with a read-only MySQL user. Never point reporting tools at VICIdial with write access:

CREATE USER 'reports'@'localhost' IDENTIFIED BY 'your_secure_password';
GRANT SELECT ON asterisk.* TO 'reports'@'localhost';
FLUSH PRIVILEGES;

Deep Dive: The Big Three Tables

vicidial_log

This is the backbone of outbound reporting. Every outbound call attempt gets a row. Key columns: uniqueid (Asterisk call ID), lead_id (links to vicidial_list), campaign_id, call_date, length_in_sec, status (disposition code), user (agent), term_reason (how the call ended), alt_dial (which phone number was dialed — MAIN, ALT, ADDR3).

A basic query for today's call volume by campaign:

SELECT
    campaign_id,
    COUNT(*) AS total_calls,
    SUM(CASE WHEN status IN ('SALE','XFER','CALLBK') THEN 1 ELSE 0 END) AS connects,
    ROUND(AVG(length_in_sec), 1) AS avg_duration
FROM vicidial_log
WHERE call_date >= CURDATE()
GROUP BY campaign_id
ORDER BY total_calls DESC;

vicidial_closer_log

Mirrors vicidial_log but for inbound and transferred calls. The important extra column is queue_seconds — time the caller waited in queue. If you're running blended campaigns or have inbound queues, this table is essential for service level reporting.

vicidial_agent_log

Where agent productivity data lives. Every time an agent changes state (available, in-call, paused, wrapping up), a row is written. Columns for pause_sec, wait_sec, talk_sec, and dispo_sec give you the full picture of where agent time goes. The sub_status field contains the pause code when the agent is paused.

Five Views That Change How You Manage the Floor

MySQL views save complex queries as virtual tables. Query them from any tool that connects to MySQL — Grafana, Metabase, a bash script, whatever. Here are the five I build on every deployment.

1. Agent Daily Summary

Shows each agent's talk time, pause time, wait time, and conversion rate for the day. Team leads live in this view:

CREATE OR REPLACE VIEW v_agent_daily_summary AS
SELECT
    DATE(event_time) AS report_date,
    a.user AS agent_id,
    u.full_name AS agent_name,
    a.campaign_id,
    COUNT(*) AS total_calls,
    SUM(a.talk_sec) AS total_talk_sec,
    SUM(a.pause_sec) AS total_pause_sec,
    SUM(a.wait_sec) AS total_wait_sec,
    SUM(a.dispo_sec) AS total_dispo_sec,
    ROUND(SUM(a.talk_sec) / NULLIF(SUM(a.talk_sec + a.pause_sec + a.wait_sec + a.dispo_sec), 0) * 100, 1) AS talk_pct,
    SUM(CASE WHEN a.status IN ('SALE','XFER') THEN 1 ELSE 0 END) AS conversions,
    ROUND(SUM(CASE WHEN a.status IN ('SALE','XFER') THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) * 100, 2) AS conversion_rate
FROM vicidial_agent_log a
LEFT JOIN vicidial_users u ON a.user = u.user
WHERE a.talk_sec > 0
GROUP BY DATE(event_time), a.user, u.full_name, a.campaign_id;

Query it simply: SELECT * FROM v_agent_daily_summary WHERE report_date = CURDATE() ORDER BY conversion_rate DESC;

2. Campaign Hourly Metrics

Track campaign performance hour by hour to identify peak times and spot problems as they develop:

CREATE OR REPLACE VIEW v_campaign_hourly AS
SELECT
    DATE(call_date) AS report_date,
    HOUR(call_date) AS hour_of_day,
    campaign_id,
    COUNT(*) AS attempts,
    SUM(CASE WHEN user != 'VDAD' THEN 1 ELSE 0 END) AS agent_handled,
    SUM(CASE WHEN status = 'DROP' THEN 1 ELSE 0 END) AS drops,
    ROUND(SUM(CASE WHEN status = 'DROP' THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) * 100, 2) AS drop_rate,
    ROUND(AVG(CASE WHEN length_in_sec > 0 THEN length_in_sec ELSE NULL END), 1) AS avg_talk_sec
FROM vicidial_log
GROUP BY DATE(call_date), HOUR(call_date), campaign_id;

3. List Penetration Report

Know exactly how deep into each list you've dialed. If penetration is at 90% and conversions are drying up, it's time for fresh data — not more aggressive dialing:

CREATE OR REPLACE VIEW v_list_penetration AS
SELECT
    l.list_id,
    vl.list_name,
    vl.campaign_id,
    COUNT(*) AS total_leads,
    SUM(CASE WHEN l.called_count > 0 THEN 1 ELSE 0 END) AS dialed_leads,
    ROUND(SUM(CASE WHEN l.called_count > 0 THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) * 100, 1) AS penetration_pct,
    SUM(CASE WHEN l.status IN ('NEW','QUEUE') THEN 1 ELSE 0 END) AS dialable_remaining,
    AVG(l.called_count) AS avg_call_attempts,
    SUM(CASE WHEN l.status IN ('SALE','XFER') THEN 1 ELSE 0 END) AS conversions
FROM vicidial_list l
JOIN vicidial_lists vl ON l.list_id = vl.list_id
GROUP BY l.list_id, vl.list_name, vl.campaign_id;

4. Pause Code Breakdown

Find out where agent time actually goes. If your top agent is spending 40 minutes a day on "LUNCH" pause codes but lunch is 30 minutes, you've found the problem:

SELECT
    a.user AS agent_id,
    u.full_name AS agent,
    a.sub_status AS pause_code,
    COALESCE(p.pause_code_name, 'UNKNOWN') AS pause_name,
    COUNT(*) AS pause_count,
    SEC_TO_TIME(SUM(a.pause_sec)) AS total_pause_time,
    ROUND(AVG(a.pause_sec), 0) AS avg_pause_sec
FROM vicidial_agent_log a
JOIN vicidial_users u ON a.user = u.user
LEFT JOIN vicidial_pause_codes p
    ON a.sub_status = p.pause_code
    AND a.campaign_id = p.campaign_id
WHERE DATE(a.event_time) = CURDATE()
  AND a.pause_sec > 0
GROUP BY a.user, u.full_name, a.sub_status, p.pause_code_name
ORDER BY SUM(a.pause_sec) DESC;

5. AMD Performance Tracker

If you're relying on answering machine detection, you need to know how accurate it is. The possible_false_positives column catches calls that were 1-3 seconds long but weren't dispositioned as machines — a sign AMD is dropping live humans:

CREATE OR REPLACE VIEW v_amd_performance AS
SELECT
    DATE(call_date) AS report_date,
    campaign_id,
    COUNT(*) AS total_calls,
    SUM(CASE WHEN status = 'AA' THEN 1 ELSE 0 END) AS amd_detected,
    SUM(CASE WHEN status = 'AM' THEN 1 ELSE 0 END) AS amd_machine,
    ROUND(SUM(CASE WHEN status IN ('AA','AM','AL') THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) * 100, 1) AS amd_rate,
    SUM(CASE WHEN length_in_sec BETWEEN 1 AND 3 AND status NOT IN ('AA','AM','AL','B','NA','DC') THEN 1 ELSE 0 END) AS possible_false_positives
FROM vicidial_log
GROUP BY DATE(call_date), campaign_id;

Automate the Boring Parts

Manual queries are useful for digging into problems. Daily reports should run themselves. Save a shell script that dumps a view to CSV and emails it to management:

#!/bin/bash
REPORT_DATE=$(date -d "yesterday" +%Y-%m-%d)
REPORT_FILE="/opt/vicidial-reports/output/agent_report_${REPORT_DATE}.csv"

mysql -u reports -p'your_password' asterisk --batch --raw -e "
SELECT * FROM v_agent_daily_summary
WHERE report_date = '${REPORT_DATE}'
ORDER BY conversions DESC
" > "$REPORT_FILE"

if [ -s "$REPORT_FILE" ]; then
    echo "Agent performance report for ${REPORT_DATE}" | \
    mail -s "Daily Agent Report - ${REPORT_DATE}" \
         -a "$REPORT_FILE" [e-mail]
fi

Schedule it with cron: 0 6 * * * /opt/vicidial-reports/daily_agent_report.sh

For heavy reporting across months of data, build summary tables that aggregate daily stats. Querying 10 million rows of vicidial_log for a monthly report is slow. Querying 30 rows from a pre-computed daily summary table is instant.

Connecting to BI Tools

Grafana turns these views into live auto-refreshing dashboards. Set the MySQL data source to your read-only user, build panels with queries like SELECT $__timeGroupAlias(call_date, '1h') AS time, campaign_id AS metric, COUNT(*) AS value FROM vicidial_log WHERE $__timeFilter(call_date) GROUP BY time, campaign_id ORDER BY time, and you have a real-time campaign dashboard that auto-updates.

Metabase is easier to set up and better for non-technical users who want to explore data themselves. It auto-detects tables and lets managers build questions with point-and-click. The views you create become clean interfaces that hide the complexity of joins and give human-readable column names.

Security Considerations

When connecting external tools to your VICIdial database: always use a read-only MySQL user, restrict to localhost if the tool runs on the same server (use SSH tunneling if remote), create views that exclude sensitive data (mask phone numbers and names if BI tool access is shared broadly), and monitor query performance — heavy reporting queries can slow down live dialing. Run expensive queries during off-hours or use a read replica.

If you're pushing VICIdial's reporting beyond what the admin panel offers, these views give you a solid foundation. ViciStack builds custom reporting dashboards for VICIdial operations — pre-built views, automated reports, database optimization, and ongoing maintenance included in our flat per-agent pricing.

Originally published at https://vicistack.com/blog/vicidial-custom-mysql-reports/