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

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

# SSH into your ViciDial server ssh root@your-vicidial-server # Connect to MySQL mysql -u root -p # Select the asterisk database USE asterisk; 

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; 

+-----------------------+--------------+------------------------+-------+-----------------+ | 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| +-----------------------+--------------+------------------------+-------+-----------------+ 

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; 

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; 

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; 

#!/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 

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

<?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(); ?> 

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; 

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; 

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; 

#!/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="admin@company.com" # 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 

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

-- 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; 

-- 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' 

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