Vicidial® reporting essentials — built-in reports and custom queries

Vicidial® ships with dozens of built-in reports under Admin → Reports — agent performance, list penetration, hourly stats, drop rates, dispositions, etc. They cover most operational needs. When you need something specific that the GUI doesn't show, the database is well-structured for direct querying. This article covers both.

The built-in reports worth knowing

• Real-Time Reports — currently active state of the call center. Refreshes every few seconds. Supervisor's primary view.
• Realtime Calls in Progress — every active call, agent, duration, recipient number. Live monitoring.
• Agent Time Detail — per agent, time spent in each state (READY, INCALL, PAUSED, with sub-categories like which pause code).
• Inbound Report — incoming call metrics by ingroup, time-of-day patterns, abandon rates.
• Outbound Calling Report — by campaign: dialed, answered, dropped, dispositions breakdown.
• List Penetration Report — how far through each list the dialer has gone, contact rates, average dispositions per number.
• Server Performance — per-Asterisk-box load, call rates, registration health (relevant in multi-server clusters).

Key tables for custom queries

Useful custom queries

Per-agent productivity summary, today:

SELECT user, COUNT(*) AS calls,
       SUM(length_in_sec) AS total_talk_secs,
       AVG(length_in_sec) AS avg_talk_secs
  FROM vicidial_log
 WHERE call_date >= CURDATE() AND status = 'A'
 GROUP BY user
 ORDER BY calls DESC;

Disposition breakdown per campaign, this week:

SELECT campaign_id, status, COUNT(*) AS count
  FROM vicidial_log
 WHERE call_date > NOW() - INTERVAL 7 DAY
 GROUP BY campaign_id, status
 ORDER BY campaign_id, count DESC;

Drop rate per hour, today (TCPA monitoring):

SELECT HOUR(call_date) AS hr,
       SUM(CASE WHEN status='DROP' THEN 1 ELSE 0 END) AS drops,
       COUNT(*) AS total,
       ROUND(SUM(CASE WHEN status='DROP' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS drop_pct
  FROM vicidial_log
 WHERE call_date >= CURDATE() AND status IN ('A','PU','PR','DROP')
 GROUP BY hr
 ORDER BY hr;

List penetration:

SELECT list_id, COUNT(*) AS total_leads,
       SUM(called_since_last_reset) AS called,
       ROUND(SUM(called_since_last_reset) * 100.0 / COUNT(*), 1) AS pct_called
  FROM vicidial_list
 GROUP BY list_id
 ORDER BY pct_called DESC;

Recording lookup for a specific call:

SELECT r.recording_id, r.start_time, r.length_in_sec,
       r.location, l.phone_number, l.list_id
  FROM vicidial_recording_log r
  JOIN vicidial_log l USING (lead_id)
 WHERE l.phone_number = '5551234567'
 ORDER BY r.start_time DESC
 LIMIT 5;

Exporting data

For ad-hoc analysis in Excel / Sheets / Tableau, mysqldump or SELECT INTO OUTFILE works:

mysql -B -e "SELECT * FROM vicidial_log WHERE call_date >= '2026-06-01' AND call_date < '2026-07-01'" asterisk > june-2026-calls.tsv

The -B flag gives tab-separated output that imports cleanly into spreadsheets.

Read-only DB user for reporting

Don't run reporting queries as the Vicidial app user (potential to accidentally write or hold locks). Create a dedicated read-only user:

mysql -u root
CREATE USER 'reporting'@'localhost' IDENTIFIED BY 'STRONG_PW';
GRANT SELECT ON asterisk.* TO 'reporting'@'localhost';
FLUSH PRIVILEGES;

Use this user for Grafana, Excel external data connections, ad-hoc scripts.

Performance notes

• Queries against vicidial_log over long date ranges are slow. The table grows fast (millions of rows on a busy operation). Index on call_date is your friend; partition by date or archive old data if month-over-month queries get slow.
• JOINs across vicidial_log and vicidial_list can be expensive — both are large. Filter on call_date first to narrow the join.
• Reporting queries during peak dialing can contend with the dialer's own DB load. Schedule heavy reports for off-hours; run real-time queries against a read replica if you have one.