How to Use ViciDial Real-Time Report for Campaign Monitoring

Master real-time call monitoring, agent performance tracking, and live campaign metrics in ViciDial to optimize your contact center operations and identify bottlenecks instantly.

Prerequisites

Before implementing real-time report monitoring in ViciDial, ensure you have:

• A fully functional ViciDial installation (version 2.10 or later recommended)
• SSH/terminal access to your ViciDial server
• MySQL/MariaDB administrative access
• Understanding of basic ViciDial campaign structure (campaigns, user accounts, inbound/outbound queues)
• Web browser access to the ViciDial admin interface (http://your-server/vicidial/admin.php)
• Asterisk running with proper CDR logging enabled
• Write permissions to /var/log/asterisk/ directory
• At least one active campaign configured with agents

Understanding ViciDial Real-Time Reporting Architecture

How Real-Time Reports Work

ViciDial real-time reports pull data directly from the Asterisk database (asterisk schema) rather than relying on pre-calculated reports. The system queries active tables like vicidial_log, vicidial_closer_log, vicidial_list, and vicidial_users to display live metrics.

The key difference between real-time reports and historical reports:

• Real-time reports: Query current call activity, agent status, and campaign state every few seconds
• Historical reports: Aggregate completed data from vicidial_log after calls disconnect

Real-time reports use three primary data sources:

1. vicidial_log - Individual call records with timestamps, results, and agent assignments
2. vicidial_closer_log - Detailed call outcomes (sale, no-sale, callback, etc.)
3. vicidial_users - Agent status and live availability

Database Table Structure

Understanding the relevant tables ensures you query the right data:

-- Check core tables for real-time data
USE asterisk;
DESCRIBE vicidial_log;
DESCRIBE vicidial_closer_log;
DESCRIBE vicidial_users;
DESCRIBE vicidial_campaigns;

Key columns in vicidial_log:

• call_id - Unique call identifier
• campaign_id - Campaign this call belongs to
• user - Agent username
• call_date - Call timestamp
• call_type - INBOUND, OUTBOUND, etc.
• call_result - Final disposition
• call_length - Duration in seconds
• status - Current state (LIVE, COMPLETED, etc.)

Accessing the Real-Time Report Interface

Web-Based Real-Time Reports

The primary method to view real-time reports is through the ViciDial admin interface:

http://your-vicidial-server/vicidial/admin.php

Steps to access real-time reports:

1. Log in with your administrative account
2. Navigate to Reports → Real-Time Reports (or Live Calls depending on version)
3. Select your campaign from the dropdown
4. The dashboard automatically refreshes every 5-10 seconds

Command-Line Real-Time Queries

For server-side monitoring or custom integrations, query the database directly:

# SSH into your ViciDial server
ssh root@your-vicidial-server

# Connect to MySQL
mysql -u root -p

# Select the asterisk database
USE asterisk;

Practical Real-Time Monitoring Queries

Query 1: Active Calls by Campaign (Last 5 Minutes)

SELECT 
    c.campaign_name,
    COUNT(*) as active_calls,
    AVG(TIMESTAMPDIFF(SECOND, vl.call_date, NOW())) as avg_call_duration_sec,
    SUM(CASE WHEN vl.call_result = 'SALE' THEN 1 ELSE 0 END) as sales,
    ROUND(100.0 * SUM(CASE WHEN vl.call_result = 'SALE' THEN 1 ELSE 0 END) / COUNT(*), 2) as conversion_rate
FROM 
    vicidial_log vl
    INNER JOIN vicidial_campaigns c ON vl.campaign_id = c.campaign_id
WHERE 
    vl.call_date >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
    AND vl.status IN ('LIVE', 'COMPLETED')
GROUP BY 
    c.campaign_name
ORDER BY 
    active_calls DESC;

Output example:

+-----------------------+--------------+------------------------+-------+-----------------+
| campaign_name         | active_calls | avg_call_duration_sec  | sales | conversion_rate |
+-----------------------+--------------+------------------------+-------+-----------------+
| Sales Campaign Q4     |           23 |                    87  |     5 |            21.74|
| Lead Qualification    |           18 |                   124  |     2 |            11.11|
| Customer Retention    |            7 |                    45  |     1 |            14.29|
+-----------------------+--------------+------------------------+-------+-----------------+

Query 2: Agent Performance Real-Time Snapshot

SELECT 
    u.user,
    u.user_level,
    u.status,
    COUNT(vl.call_id) as calls_today,
    ROUND(AVG(vl.call_length), 1) as avg_call_length_sec,
    SUM(CASE WHEN vl.call_result = 'SALE' THEN 1 ELSE 0 END) as sales,
    ROUND(100.0 * SUM(CASE WHEN vl.call_result = 'SALE' THEN 1 ELSE 0 END) / COUNT(vl.call_id), 2) as sales_rate,
    MAX(vl.call_date) as last_call_time
FROM 
    vicidial_users u
    LEFT JOIN vicidial_log vl ON u.user = vl.user 
        AND vl.call_date >= DATE_SUB(NOW(), INTERVAL 1 DAY)
WHERE 
    u.user_level > 0
    AND u.active = 'Y'
GROUP BY 
    u.user, u.user_level, u.status
ORDER BY 
    sales DESC, calls_today DESC;

Query 3: Queue Depth and Wait Times

SELECT 
    vc.campaign_id,
    vc.campaign_name,
    vc.dial_method,
    COUNT(DISTINCT vl.call_id) as current_queue_depth,
    ROUND(AVG(TIMESTAMPDIFF(SECOND, vl.call_date, NOW())), 0) as avg_queue_wait_sec,
    COUNT(DISTINCT vl.user) as available_agents
FROM 
    vicidial_log vl
    INNER JOIN vicidial_campaigns vc ON vl.campaign_id = vc.campaign_id
WHERE 
    vl.status = 'LIVE'
    AND vl.call_date >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
GROUP BY 
    vc.campaign_id, vc.campaign_name, vc.dial_method;

Query 4: Real-Time Call Result Distribution

SELECT 
    vc.campaign_id,
    vc.campaign_name,
    vl.call_result,
    COUNT(*) as result_count,
    ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM vicidial_log WHERE campaign_id = vc.campaign_id AND call_date >= DATE_SUB(NOW(), INTERVAL 1 HOUR)), 2) as percentage
FROM 
    vicidial_log vl
    INNER JOIN vicidial_campaigns vc ON vl.campaign_id = vc.campaign_id
WHERE 
    vl.call_date >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY 
    vc.campaign_id, vc.campaign_name, vl.call_result
ORDER BY 
    result_count DESC;

Building Custom Real-Time Monitoring Dashboards

Creating a Shell Script for Continuous Monitoring

Create a bash script that polls real-time data and displays it in the terminal:

#!/bin/bash
# File: /usr/local/bin/vicidial-monitor.sh
# Real-time ViciDial monitoring script

MYSQL_USER="root"
MYSQL_PASS="your_mysql_password"
DB_NAME="asterisk"
REFRESH_INTERVAL=10

function get_active_calls() {
    mysql -u $MYSQL_USER -p$MYSQL_PASS $DB_NAME -e "
    SELECT 
        c.campaign_name,
        COUNT(*) as active_calls,
        SUM(CASE WHEN vl.call_result = 'SALE' THEN 1 ELSE 0 END) as sales
    FROM 
        vicidial_log vl
        INNER JOIN vicidial_campaigns c ON vl.campaign_id = c.campaign_id
    WHERE 
        vl.call_date >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
        AND vl.status IN ('LIVE', 'COMPLETED')
    GROUP BY 
        c.campaign_name
    ORDER BY 
        active_calls DESC;
    "
}

function get_agent_status() {
    mysql -u $MYSQL_USER -p$MYSQL_PASS $DB_NAME -e "
    SELECT 
        user,
        status,
        COUNT(*) as count
    FROM 
        vicidial_users
    WHERE 
        user_level > 0
    GROUP BY 
        user, status;
    "
}

# Main loop
while true; do
    clear
    echo "====== VICIDIAL REAL-TIME MONITOR ======"
    echo "Refresh Time: $(date '+%Y-%m-%d %H:%M:%S')"
    echo ""
    echo "--- ACTIVE CALLS BY CAMPAIGN ---"
    get_active_calls
    echo ""
    echo "--- AGENT STATUS SUMMARY ---"
    get_agent_status
    echo ""
    echo "Next refresh in $REFRESH_INTERVAL seconds..."
    sleep $REFRESH_INTERVAL
done

Make it executable:

chmod +x /usr/local/bin/vicidial-monitor.sh

# Run it
/usr/local/bin/vicidial-monitor.sh

PHP-Based Real-Time Dashboard

Create a lightweight PHP dashboard for web access:

<?php
// File: /var/www/html/vicidial/real-time-dashboard.php
// Simple real-time dashboard

header('Content-Type: text/html; charset=utf-8');

$db_host = 'localhost';
$db_user = 'root';
$db_pass = 'password';
$db_name = 'asterisk';

$conn = new mysqli($db_host, $db_user, $db_pass, $db_name);

if ($conn->connect_error) {
    die("Database connection failed: " . $conn->connect_error);
}

// Query active calls
$query = "
SELECT 
    c.campaign_name,
    COUNT(*) as active_calls,
    SUM(CASE WHEN vl.call_result = 'SALE' THEN 1 ELSE 0 END) as sales,
    AVG(vl.call_length) as avg_duration
FROM 
    vicidial_log vl
    INNER JOIN vicidial_campaigns c ON vl.campaign_id = c.campaign_id
WHERE 
    vl.call_date >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
    AND vl.status IN ('LIVE', 'COMPLETED')
GROUP BY 
    c.campaign_name
ORDER BY 
    active_calls DESC
";

$result = $conn->query($query);

?>
<!DOCTYPE html>
<html>
<head>
    <title>ViciDial Real-Time Monitor</title>
    <meta http-equiv="refresh" content="10">
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; background: #f5f5f5; }
        table { border-collapse: collapse; width: 100%; background: white; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
        th, td { padding: 12px; text-align: left; border-bottom: 1px solid #ddd; }
        th { background-color: #4CAF50; color: white; }
        tr:hover { background-color: #f5f5f5; }
        .metric { font-weight: bold; color: #2196F3; }
        .sales { color: #4CAF50; }
        .timestamp { color: #999; font-size: 0.9em; }
    </style>
</head>
<body>
    <h1>ViciDial Real-Time Report - Campaign Monitor</h1>
    <p class="timestamp">Last Updated: <?php echo date('Y-m-d H:i:s'); ?></p>
    
    <table>
        <tr>
            <th>Campaign Name</th>
            <th class="metric">Active Calls</th>
            <th class="sales">Sales</th>
            <th>Avg Duration (sec)</th>
        </tr>
        <?php
        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                echo "<tr>";
                echo "<td>" . htmlspecialchars($row['campaign_name']) . "</td>";
                echo "<td class='metric'>" . $row['active_calls'] . "</td>";
                echo "<td class='sales'>" . $row['sales'] . "</td>";
                echo "<td>" . round($row['avg_duration'], 1) . "</td>";
                echo "</tr>";
            }
        } else {
            echo "<tr><td colspan='4'>No active calls</td></tr>";
        }
        ?>
    </table>
</body>
</html>
<?php
$conn->close();
?>

Access it at: http://your-server/vicidial/real-time-dashboard.php

Monitoring Specific Metrics for Campaign Health

Call Abandonment Rate

SELECT 
    vc.campaign_name,
    COUNT(*) as total_calls,
    SUM(CASE WHEN vl.call_result = 'ABANDON' THEN 1 ELSE 0 END) as abandoned,
    ROUND(100.0 * SUM(CASE WHEN vl.call_result = 'ABANDON' THEN 1 ELSE 0 END) / COUNT(*), 2) as abandon_rate
FROM 
    vicidial_log vl
    INNER JOIN vicidial_campaigns vc ON vl.campaign_id = vc.campaign_id
WHERE 
    vl.call_date >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
    AND vl.call_type = 'INBOUND'
GROUP BY 
    vc.campaign_name
HAVING 
    abandon_rate > 10.0
ORDER BY 
    abandon_rate DESC;

Average Handle Time (AHT) by Agent

SELECT 
    u.user,
    COUNT(*) as calls,
    ROUND(AVG(vl.call_length), 0) as aht_seconds,
    ROUND(AVG(vl.call_length) / 60, 2) as aht_minutes,
    ROUND(SUM(vl.call_length) / 3600, 1) as total_talk_hours
FROM 
    vicidial_log vl
    INNER JOIN vicidial_users u ON vl.user = u.user
WHERE 
    vl.call_date >= DATE_SUB(NOW(), INTERVAL 8 HOUR)
    AND vl.call_type = 'OUTBOUND'
GROUP BY 
    u.user
ORDER BY 
    aht_seconds DESC;

Dial-Through Rate (Predictive/Progressive Dialing)

SELECT 
    vc.campaign_name,
    vc.dial_method,
    COUNT(*) as total_dials,
    SUM(CASE WHEN vl.call_result NOT IN ('NO ANSWER', 'BUSY') THEN 1 ELSE 0 END) as connections,
    ROUND(100.0 * SUM(CASE WHEN vl.call_result NOT IN ('NO ANSWER', 'BUSY') THEN 1 ELSE 0 END) / COUNT(*), 2) as connect_rate
FROM 
    vicidial_log vl
    INNER JOIN vicidial_campaigns vc ON vl.campaign_id = vc.campaign_id
WHERE 
    vl.call_date >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
    AND vl.call_type = 'OUTBOUND'
GROUP BY 
    vc.campaign_name, vc.dial_method;

Configuring Alerts for Campaign Issues

Setting Up Threshold-Based Alerts

Create a monitoring script that sends alerts when metrics exceed thresholds:

#!/bin/bash
# File: /usr/local/bin/vicidial-alert-monitor.sh
# Alert when metrics exceed thresholds

MYSQL_USER="root"
MYSQL_PASS="password"
DB_NAME="asterisk"
EMAIL_TO="[email protected]"

# Check abandon rate
ABANDON_RATE=$(mysql -u $MYSQL_USER -p$MYSQL_PASS $DB_NAME -N -e "
SELECT MAX(abandon_pct) FROM (
    SELECT 
        ROUND(100.0 * SUM(CASE WHEN call_result = 'ABANDON' THEN 1 ELSE 0 END) / COUNT(*), 2) as abandon_pct
    FROM 
        vicidial_log
    WHERE 
        call_date >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
        AND call_type = 'INBOUND'
    GROUP BY campaign_id
) subquery;
")

ABANDON_THRESHOLD=15

if (( $(echo "$ABANDON_RATE > $ABANDON_THRESHOLD" | bc -l) )); then
    echo "ALERT: Abandon rate is ${ABANDON_RATE}% (threshold: ${ABANDON_THRESHOLD}%)" | \
    mail -s "ViciDial Alert: High Abandon Rate" $EMAIL_TO
fi

# Check active agent availability
AVAILABLE_AGENTS=$(mysql -u $MYSQL_USER -p$MYSQL_PASS $DB_NAME -N -e "
SELECT COUNT(*) FROM vicidial_users 
WHERE user_level > 0 
AND status IN ('READY', 'PAUSED')
AND active = 'Y'
")

QUEUE_DEPTH=$(mysql -u $MYSQL_USER -p$MYSQL_PASS $DB_NAME -N -e "
SELECT COUNT(*) FROM vicidial_log 
WHERE status = 'LIVE' 
AND call_date >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
")

if [ "$AVAILABLE_AGENTS" -lt 5 ] && [ "$QUEUE_DEPTH" -gt 20 ]; then
    echo "ALERT: Low agent availability ($AVAILABLE_AGENTS agents) with queue depth at $QUEUE_DEPTH calls" | \
    mail -s "ViciDial Alert: Understaffing Risk" $EMAIL_TO
fi

Add to crontab to run every minute:

crontab -e

# Add this line:
* * * * * /usr/local/bin/vicidial-alert-monitor.sh

Performance Optimization for Real-Time Reports

Database Indexing for Faster Queries

Large ViciDial installations can experience slow real-time reports without proper indexing:

-- Add indexes if they don't exist
ALTER TABLE vicidial_log ADD INDEX idx_call_date (call_date);
ALTER TABLE vicidial_log ADD INDEX idx_campaign_status (campaign_id, status);
ALTER TABLE vicidial_log ADD INDEX idx_user_date (user, call_date);
ALTER TABLE vicidial_closer_log ADD INDEX idx_call_date (call_date);

-- Verify indexes exist
SHOW INDEX FROM vicidial_log;

Limiting Query Scope

Never query unbounded date ranges in production:

-- GOOD: Limited to last hour
WHERE vl.call_date >= DATE_SUB(NOW(), INTERVAL 1 HOUR)

-- BAD: No date filter - scans entire table
WHERE vl.user = 'john.smith'

Using READ REPLICAS for Reporting

For high-volume operations, query a read replica instead of the production database:

# Example: Redirect reporting queries to replica
REPLICA_HOST="vicidial-replica.internal"
REPLICA_USER="report_user"
REPLICA_PASS="replica_password"

mysql -h $REPLICA_HOST -u $REPLICA_USER -p$REPLICA_PASS asterisk -e "SELECT ..."

Integrating with Asterisk CLI for Live Monitoring

Real-Time Agent Status from Asterisk

# Check SIP channels
asterisk -rx "sip show channels"

# Check Asterisk queue status
asterisk -rx "queue show"

# Get specific queue info
asterisk -rx "queue show vicidial_queue"

Sample output:

vicidial_queue has 3 calls (max unlimited) in 'ringall' strategy (0s holdtime, 45s talktime), W:0, C:125, A:3, SL:98.4% within 45s
    SIP/2002 (Agent John) with 1 calls (dynamic) answered in 124s
    SIP/2003 (Agent Sarah) with 1 calls (dynamic) answered in 156s
    Local/3051@from-internal-00000001 (IVR Menu) with 1 calls (dynamic) answered in 89s

Pull CDR Data from Asterisk

# Check Asterisk call records
asterisk -rx "cdr show active"

# Get CDR SQL status
asterisk -rx "cdr show status"

Troubleshooting Common Real-Time Reporting Issues

Issue 1: Real-Time Reports Show Zero Calls

Symptom: Dashboard displays "No active calls" when calls are clearly being processed.

Solution Steps:

1. Verify vicidial_log table is being populated:

SELECT COUNT(*) as recent_records FROM vicidial_log 
WHERE call_date >= DATE_SUB(NOW(), INTERVAL 1 MINUTE);

2. Check if campaigns are properly configured:

SELECT campaign_id, campaign_name, active FROM vicidial_campaigns 
WHERE campaign_id IN (
    SELECT DISTINCT campaign_id FROM vicidial_log 
    WHERE call_date >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
);

3. Verify Asterisk is logging to MySQL:

# Check Asterisk CDR module
asterisk -rx "cdr show status"

# Expected output: CDR logging enabled, MySQL module loaded

4. Restart CDR logging if needed:

asterisk -rx "cdr reload"

Issue 2: Real-Time Reports Run Slowly

Symptom: Dashboard takes 30+ seconds to load; queries timeout.

Solution Steps:

1. Analyze slow query log:

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- View slow queries
tail -f /var/log/mysql/slow.log

2. Check query execution plan:

EXPLAIN SELECT ... [your query here];

-- Look for "Full table scan" entries - these need indexes

3. Clear old data to reduce table size:

-- Archive calls older than 90 days
CREATE TABLE vicidial_log_archive_2024 LIKE vicidial_log;

INSERT INTO vicidial_log_archive_2024 
SELECT * FROM vicidial_log 
WHERE call_date < DATE_SUB(NOW(), INTERVAL 90 DAY);

DELETE FROM vicidial_log 
WHERE call_date < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Optimize table
OPTIMIZE TABLE vicidial_log;

Issue 3: Refresh Lag or Stale Data

Symptom: Real-time dashboard shows outdated information; metrics don't refresh.

Solution Steps:

1. Check database replication status (if using replication):

SHOW SLAVE STATUS\G

-- Look for Seconds_Behind_Master value
-- Should be 0 or very small

2. Verify web server isn't caching the report:

// Add to real-time dashboard header
header("Cache-Control: no-store, no-cache, must-revalidate, max-age=0");
header("Pragma: no-cache");
header("Expires: 0");

3. Increase refresh frequency in dashboard:

<!-- Change refresh interval from 10s to 5s -->
<meta http-equiv="refresh" content="5">

Issue 4: Database Locks During Real-Time Queries

Symptom: Queries hang; "Lock wait timeout exceeded" errors in MySQL log.

Solution Steps:

1. Identify locked tables:

SHOW OPEN TABLES WHERE In_use > 0;

-- Check for long-running transactions
SHOW PROCESSLIST;

2. Kill blocking query if necessary:

KILL [PROCESS_ID];

3. Configure MySQL to use InnoDB (better for concurrent access):

# Edit /etc/mysql/my.cnf
[mysqld]
default-storage-engine=InnoDB
innodb_buffer_pool_size=4G
innodb_log_file_size=512M

Issue 5: PHP Dashboard Blank or Shows Database Errors

Symptom: Real-time dashboard page is blank or displays "Database connection failed."

Solution Steps:

1. Check PHP error logs:

tail -f /var/log/apache2/error.log
tail -f /var/log/php-fpm.log

2. Verify MySQL credentials in dashboard script:

$conn = new mysqli('localhost', 'root', 'password', 'asterisk');
if ($conn->connect_error) {
    die("Error: " . $conn->connect_error);
}

3. Test MySQL connectivity from command line:

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

4. Check database user permissions:

-- Verify user has SELECT permission
SHOW GRANTS FOR 'root'@'localhost';

-- Grant if needed
GRANT SELECT ON asterisk.* TO 'root'@'localhost';
FLUSH PRIVILEGES;

Summary

ViciDial real-time reporting provides critical insight into campaign performance, agent activity, and system health. Key takeaways:

Core Concepts:

• Real-time reports query live data from vicidial_log, vicidial_users, and vicidial_campaigns tables
• Data refreshes every 5-10 seconds in web interface; can be queried instantly via CLI
• Direct MySQL queries offer flexibility beyond built-in reports

Practical Implementation:

• Use web-based dashboards at /vicidial/admin.php for quick overview
• Build custom bash scripts for server-side monitoring
• Create PHP dashboards for team visibility
• Implement threshold-based alerts for critical metrics

Key Metrics to Monitor:

• Active calls by campaign
• Agent performance (AHT, sales rate, availability)
• Queue depth and abandon rates
• Dial-through rates and connection quality

Performance Best Practices:

• Add database indexes on call_date, campaign_id, status, and user
• Limit queries to recent time windows (last 1-24 hours)
• Archive old data to keep tables responsive
• Use read replicas for reporting in high-volume environments

Troubleshooting Foundation:

• Verify MySQL logging is active and connected to Asterisk
• Check slow query logs and optimize with indexes
• Clear caches in PHP headers to ensure fresh data
• Monitor database locks and process list during issues

With proper real-time monitoring in place, you'll detect abandoned calls, staffing shortages, and performance bottlenecks within minutes rather than hours—enabling proactive response that directly impacts customer satisfaction and campaign ROI.