5 MySQL Queries Every VICIdial Manager Should Run Before 9 AM

VICIdial logs everything. Every dial attempt, every agent state change, every disposition — it all lands in MySQL tables that grow by thousands of rows per hour. The data is there. The problem is that nobody looks at it until something is already on fire.

I've managed VICIdial reporting for enough centers to know the pattern: manager walks in, checks the real-time screen, sees agents are logged in, assumes everything is fine. Meanwhile, the drop rate has been creeping toward 3%, three DIDs are flagged as spam, and the hopper ran dry 20 minutes ago on the best-performing campaign.

Here are the five queries I run every morning before agents even log in. They take two minutes and catch problems that would otherwise cost you a full shift of lost productivity. After the queries, I'll cover the real-time monitoring cadence that separates centers running blind from centers that catch problems before agents even notice.

Query 1: Yesterday's Campaign Health Check

This one gives you the overnight summary — or the previous day's final scorecard if you're arriving in the morning.

SELECT
    campaign_id,
    COUNT(*) as total_dials,
    COUNT(CASE WHEN status NOT IN ('NA','B','DC','N','AFTHRS') THEN 1 END) as answered,
    COUNT(CASE WHEN status = 'DROP' THEN 1 END) as drops,
    COUNT(CASE WHEN user != '' AND status NOT IN ('NA','B','DC','N','DROP','AFTHRS','XDROP') THEN 1 END) as agent_handled,
    ROUND(COUNT(CASE WHEN status NOT IN ('NA','B','DC','N','AFTHRS') THEN 1 END) /
          NULLIF(COUNT(*), 0) * 100, 2) as answer_rate_pct
FROM vicidial_log
WHERE DATE(call_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY campaign_id
ORDER BY campaign_id;

What to look for: answer rate dropping below your 7-day average means something changed — burned DIDs, list quality degradation, or a carrier issue. Drop count exceeding 2.5% of answered calls means you're flirting with TCPA trouble. A sudden spike in NA (No Answer) dispositions relative to total dials often signals DID reputation collapse — your calls are connecting at the carrier level but nobody picks up because their phone says "Spam Likely."

Query 2: The 30-Day Rolling Abandon Rate

This is the TCPA number. The FCC measures abandon rate over a 30-day rolling window per campaign. If this exceeds 3%, you're exposed to per-violation fines that stack fast. I set my alert threshold at 2.5%.

SELECT
    campaign_id,
    COUNT(CASE WHEN status = 'DROP' THEN 1 END) as drops,
    COUNT(CASE WHEN status NOT IN ('NA','B','DC','N','AFTHRS','XDROP') THEN 1 END) as answered,
    ROUND(COUNT(CASE WHEN status = 'DROP' THEN 1 END) /
          NULLIF(COUNT(CASE WHEN status NOT IN ('NA','B','DC','N','AFTHRS','XDROP') THEN 1 END), 0) * 100, 2)
          as drop_rate_30d
FROM vicidial_log
WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY campaign_id
HAVING drop_rate_30d > 2.0
ORDER BY drop_rate_30d DESC;

If this returns rows, you have work to do. Lower your adaptive dial level, check whether ADAPT_TAPERED is configured correctly, and verify your hopper isn't feeding leads that time out before an agent picks up. The HAVING drop_rate_30d > 2.0 clause filters to campaigns approaching the danger zone — automate this as a daily cron job that emails your compliance team when any campaign exceeds 2.5%.

One thing people miss: the TCPA measures the abandon rate as a percentage of answered calls, not total dials. So the denominator matters. If your answer rate drops (because DIDs are burned), your abandon rate percentage can actually increase even if the absolute number of drops stays the same.

Query 3: Agent Productivity Breakdown

Which agents are actually working and which are burning payroll in PAUSED status? This query breaks down every agent's shift into its component pieces.

SELECT
    user,
    campaign_id,
    COUNT(*) as total_calls,
    ROUND(SUM(talk_sec) / 60, 1) as total_talk_min,
    ROUND(AVG(talk_sec), 1) as avg_talk_sec,
    ROUND(SUM(wait_sec) / 60, 1) as total_wait_min,
    ROUND(SUM(pause_sec) / 60, 1) as total_pause_min,
    ROUND(SUM(dispo_sec) / 60, 1) as total_dispo_min,
    ROUND(SUM(talk_sec) / NULLIF(SUM(talk_sec + wait_sec + pause_sec + dispo_sec), 0) * 100, 1)
        as talk_pct
FROM vicidial_agent_log
WHERE event_time >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
  AND event_time < CURDATE()
GROUP BY user, campaign_id
ORDER BY talk_pct DESC;

The talk_pct column is the most telling metric in the whole database. Good outbound agents hit 45-55% talk time. Below 35% means either the dialer isn't feeding them enough calls (tuning problem) or they're ducking work (management problem). Either way, you want to know at 9 AM, not at 5 PM.

Also pay attention to avg_talk_sec. An agent with significantly shorter average talk time than their peers might be rushing through calls and not properly qualifying. An agent with unusually long average talk time might be having great conversations — or might be personal-calling on company time. Context matters.

The total_pause_min column sorted descending immediately shows you who's spending the most time paused. Cross-reference with your scheduled break times. If an agent has 90 minutes of pause time and they only get two 15-minute breaks, that's 60 minutes of unaccounted dead time.

Query 4: DID Health — Which Numbers Are Burned?

Your outbound DIDs degrade over time. Carriers flag high-volume numbers and consumer spam report services track them. This query surfaces the ones that need rotation:

SELECT
    phone_number as outbound_did,
    COUNT(*) as total_dials,
    COUNT(CASE WHEN status NOT IN ('NA','B','DC','N','AFTHRS') THEN 1 END) as answered,
    ROUND(COUNT(CASE WHEN status NOT IN ('NA','B','DC','N','AFTHRS') THEN 1 END) /
          NULLIF(COUNT(*), 0) * 100, 2) as answer_rate,
    COUNT(CASE WHEN status = 'NA' THEN 1 END) as no_answer,
    ROUND(COUNT(CASE WHEN status = 'NA' THEN 1 END) /
          NULLIF(COUNT(*), 0) * 100, 2) as no_answer_rate
FROM vicidial_log
WHERE call_date >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY phone_number
HAVING total_dials > 50
ORDER BY answer_rate ASC
LIMIT 10;

A clean DID should get 8-12% answer rates. If you see numbers below 4%, they're almost certainly flagged as "Spam Likely" on Hiya, TNS Call Guardian, or First Orion. Pull them from rotation immediately and replace with fresh numbers.

The thing that makes this tricky: DID degradation is gradual. A number that performed at 10% answer rate three months ago might be at 6% now and trending down. By the time it's obviously burned (below 3%), it's been costing you connects for weeks. Run this query weekly at minimum and track the trend. Any DID that drops 2+ percentage points week over week needs investigation even if it's still above your absolute threshold.

For a full walkthrough of DID management, rotation strategies, and reputation monitoring, see the ViciStack DID management guide.

Query 5: Hopper Status

The hopper is VICIdial's pre-loaded dialing queue. When it runs dry, your agents sit idle. I've seen centers lose entire hours because nobody noticed the hopper was empty.

SELECT
    campaign_id,
    COUNT(*) as leads_in_hopper,
    MIN(gmt_offset_now) as earliest_tz,
    MAX(gmt_offset_now) as latest_tz,
    COUNT(DISTINCT list_id) as lists_represented
FROM vicidial_hopper
GROUP BY campaign_id;

You want at least 2x your agent count in the hopper at all times. For a 50-agent campaign, keep 200-500 leads queued. If it's low, check your list availability, call time restrictions, timezone filtering, and whether the list is simply exhausted.

The timezone columns tell you something important: if earliest_tz and latest_tz are the same, you're only dialing into one timezone right now. That might be expected at certain times of day, but if it's 2 PM Eastern and you only have Eastern timezone leads in the hopper, you're missing the West Coast entirely. Either your timezone settings are wrong or those lists don't have accurate geographic data.

Also check lists_represented. If only one list is feeding the hopper and you have five lists loaded into the campaign, the others might be exhausted or filtered out. This catches the scenario where your best list ran out two days ago and nobody noticed because the B-list kept the hopper above zero.

The Bonus Query: Hourly Performance Heatmap

This one isn't a daily check — run it weekly or monthly. But it's the single most useful query for optimizing agent schedules:

SELECT
    HOUR(call_date) as call_hour,
    DAYNAME(call_date) as day_of_week,
    COUNT(*) as dials,
    COUNT(CASE WHEN status NOT IN ('NA','B','DC','N','AFTHRS') THEN 1 END) as answered,
    ROUND(COUNT(CASE WHEN status NOT IN ('NA','B','DC','N','AFTHRS') THEN 1 END) /
          NULLIF(COUNT(*), 0) * 100, 2) as answer_rate,
    COUNT(CASE WHEN status IN ('SALE','SET','XFER') THEN 1 END) as conversions
FROM vicidial_log
WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY HOUR(call_date), DAYNAME(call_date)
ORDER BY DAYOFWEEK(call_date), call_hour;

If Monday 5-6 PM has a 12% contact rate and 3.5% conversion rate while Tuesday 10-11 AM has 5% contact rate and 1.2% conversion rate, you know exactly where to concentrate your staffing. Most centers staff flat throughout the day. The data almost always shows that two or three hours are dramatically more productive than the rest. Stack agents into those windows.

Beyond Morning Queries: Real-Time Monitoring

These five queries are the minimum daily check. For real-time visibility, you have two options.

Option 1: VICIdial's built-in reports. The real-time campaign summary at /vicidial/realtime_report.php shows agents logged in by status (INCALL, READY, PAUSED, DEAD), calls ringing, calls waiting for agents, current dial level, drops today, and hopper count. Put it on a wallboard. The critical things to watch: calls waiting greater than zero for more than 5 seconds (you need more agents or lower dial level), drop percentage approaching 2.5% (reduce dial level immediately), hopper count dropping below 50 (list about to run dry), and agents in PAUSED state exceeding 20% of your floor.

Option 2: Grafana or Metabase dashboards. Connect a read-only MySQL user to Grafana, build panels with the queries above set to auto-refresh every 5-30 seconds, and you get a dashboard that looks like it was designed in this decade. Grafana's gauge panel is especially useful for the drop rate — set thresholds at green under 2%, yellow 2-2.5%, red over 2.5% and your compliance status is visible from across the room.

Create a dedicated read-only user for your dashboard tool:

CREATE USER 'grafana_readonly'@'%' IDENTIFIED BY 'your_strong_password';
GRANT SELECT ON asterisk.* TO 'grafana_readonly'@'%';
FLUSH PRIVILEGES;

Never use VICIdial's admin credentials for reporting. And if you're running more than 50,000 calls per day, point Grafana at a MySQL read replica so your reporting queries don't compete with the dialer for database resources.

The Four Tables You'll Live In

If you're writing your own queries, everything you need lives in four tables:

• vicidial_log — Every outbound dial attempt. Columns: uniqueid, lead_id, campaign_id, call_date, length_in_sec, status, user, term_reason. This is your primary reporting table.
• vicidial_closer_log — Every inbound call or in-group transfer. Same structure as vicidial_log but with queue_seconds for hold time tracking and service level calculations.
• vicidial_agent_log — Agent state transitions. Every time an agent moves between READY, INCALL, PAUSED, or DISPO, a row gets written with the duration in each state. The talk_sec, wait_sec, pause_sec, and dispo_sec columns give you the complete time breakdown.
• vicidial_live_agents — Current real-time agent status. Updated continuously. What the built-in real-time reports read from.

The key join between them is uniqueid for call-level data and user for agent-level data.

Automating Report Delivery

For operations that need daily reports emailed to stakeholders, set up a cron job that runs the campaign summary and top agents queries, formats the output as a text report, and emails it at 7 AM before the shift starts. A simple bash script with mysql -e piped to mail handles this in about 20 lines.

Set up a separate cron job running every 15 minutes that checks the 30-day rolling abandon rate and emails your compliance team if any campaign exceeds 2.5%. This is the one alert that can save you from a six-figure TCPA class action.

What to Review and When

Continuously (wallboard): Agent status distribution, calls waiting in queue, current drop rate, hopper count, active trunk count.

Daily (morning queries): Campaign contact rates and trends, agent utilization percentages, disposition distribution shifts, top and bottom agent performance.

Weekly: 30-day rolling abandon rate, DID performance comparison for flagged numbers, list penetration (how much is left), hourly performance heatmap for staffing optimization, AMD accuracy check.

Monthly: Cost per lead trends, agent turnover impact on performance, infrastructure capacity review, compliance audit (DNC scrub dates, consent records, state registrations).

For the complete reporting guide with every query template, Grafana panel configuration, and automated alert script, ViciStack publishes the full walkthrough and can set this up as part of their managed VICIdial service — all included at $150/agent/month.

Originally published at https://vicistack.com/blog/vicidial-reporting-monitoring/