ViciDial Agent Performance Reports Explained
Master ViciDial agent performance metrics, learn how to generate actionable reports, and understand the data structures that power contact center analytics
Prerequisites
Before working with ViciDial agent performance reports, you'll need:
- A running ViciDial installation (v2.13 or later recommended)
- Root or sudo access to your ViciDial server
- Database access credentials for the asterisk database
- Understanding of MySQL/MariaDB basics
- Familiarity with ViciDial agent roles and campaigns
- SSH access to query logs and run CLI commands
- Basic PHP knowledge for custom report modifications
Verify your installation with:
asterisk -rx "core show version"
mysql -u root -p -e "USE asterisk; SHOW TABLES LIKE 'vicidial%';" | head -20
Understanding ViciDial Performance Metrics
Core Performance Indicators
ViciDial tracks agent performance across multiple dimensions. The primary metrics you'll encounter are:
- Talk Time (Handle Time): Duration of actual conversation
- Wrap-Up Time: Post-call work time before next call
- Calls Handled: Total number of calls worked
- Calls Connected: Calls that reached a live person
- Call Completion Rate: Successful completions vs. total attempts
- Average Handle Time (AHT): Combined talk time and wrap-up time
- Pause Time: Idle time between calls
- Not Ready Time: Time logged out or unavailable
These metrics exist across three primary tables in the ViciDial database:
vicidial_log - Individual call records
vicidial_closer_log - Detailed agent call details
vicidial_agent_log - Agent login/logout sessions
Database Schema Overview
The three core tables structure agent performance data:
-- vicidial_log: Master call records
SELECT
call_date,
caller_id_number,
called_party_number,
agent_only,
talk_sec,
wait_sec,
status
FROM vicidial_log
WHERE call_date >= DATE(NOW()) - INTERVAL 7 DAY
LIMIT 5;
-- vicidial_closer_log: Agent-specific call handling
SELECT
call_date,
user,
call_length,
pause_sec,
status,
lead_id,
list_id
FROM vicidial_closer_log
WHERE call_date >= DATE(NOW()) - INTERVAL 1 DAY
AND user = 'AGENT001'
ORDER BY call_date DESC;
-- vicidial_agent_log: Login sessions and availability
SELECT
agent_log_id,
user,
server_ip,
event_time,
event_type,
pause_type,
status
FROM vicidial_agent_log
WHERE user = 'AGENT001'
AND event_time >= DATE(NOW()) - INTERVAL 1 DAY
ORDER BY event_time DESC;
Accessing Built-in ViciDial Reports
Admin Dashboard Reports
The ViciDial admin interface provides quick-access reports. Navigate to:
http://your-vicidial-server/vicidial/admin.php?action=show_reports
From here, access:
- Agent Performance Report - Real-time agent metrics
- Daily Performance Report - Agent stats by day
- Call Center Performance - Campaign-level analytics
- Detailed Call Log - Individual call records
Agent Performance Report Navigation
Log into the admin interface and follow:
Reports > Agent Performance > Select Date Range > Select Agents
This displays:
- Login/logout times
- Total calls handled
- Average talk time
- Wrap-up time
- Call outcomes
- Customer satisfaction scores (if configured)
Building Custom Performance Reports
Basic Agent Daily Performance Query
Create a custom report for a specific date and agent:
SELECT
user AS agent_name,
DATE(call_date) AS call_date,
COUNT(*) AS total_calls,
SUM(CASE WHEN talk_sec > 0 THEN 1 ELSE 0 END) AS connected_calls,
ROUND(AVG(talk_sec), 2) AS avg_talk_sec,
SUM(talk_sec) AS total_talk_sec,
ROUND(AVG(wait_sec), 2) AS avg_wait_sec,
SUM(pause_sec) / 60 AS total_pause_min,
COUNT(DISTINCT(DATE_FORMAT(call_date, '%Y-%m-%d %H:00:00'))) AS hours_worked
FROM vicidial_closer_log
WHERE user = 'AGENT001'
AND call_date >= '2024-01-15 00:00:00'
AND call_date < '2024-01-16 00:00:00'
GROUP BY user, DATE(call_date);
Expected output:
agent_name | call_date | total_calls | connected_calls | avg_talk_sec | total_talk_sec | avg_wait_sec | total_pause_min | hours_worked
AGENT001 | 2024-01-15 | 87 | 67 | 312.45 | 27183 | 12.56 | 45.33 | 8
Advanced Performance Report with Status Breakdown
Generate a detailed report showing call outcome distribution:
SELECT
u.user,
u.full_name,
DATE(vcl.call_date) AS work_date,
COUNT(*) AS total_calls,
ROUND(COUNT(*) / (SELECT COUNT(*) FROM vicidial_closer_log
WHERE user = u.user AND DATE(call_date) = DATE(vcl.call_date)) * 100, 2) AS call_pct,
SUM(CASE WHEN vcl.talk_sec > 0 THEN 1 ELSE 0 END) AS connected_calls,
ROUND(AVG(vcl.talk_sec), 1) AS avg_talk_sec,
SUM(vcl.talk_sec) AS total_talk_sec,
ROUND(AVG(vcl.wait_sec), 1) AS avg_wait_sec,
GROUP_CONCAT(DISTINCT vcl.status ORDER BY vcl.status) AS call_statuses
FROM vicidial_closer_log vcl
INNER JOIN vicidial_users u ON vcl.user = u.user
WHERE DATE(vcl.call_date) = '2024-01-15'
AND vcl.user IN ('AGENT001', 'AGENT002', 'AGENT003')
GROUP BY u.user, u.full_name, DATE(vcl.call_date)
ORDER BY total_calls DESC;
Call Outcome Breakdown Report
Understand how agents handle different call statuses:
SELECT
user,
status,
COUNT(*) AS count,
ROUND(COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY user) * 100, 2) AS pct_of_total,
ROUND(AVG(talk_sec), 1) AS avg_talk_time,
SUM(talk_sec) AS total_talk_time
FROM vicidial_closer_log
WHERE DATE(call_date) = CURDATE()
AND user = 'AGENT001'
GROUP BY user, status
ORDER BY count DESC;
Common statuses include:
- SALE - Successful sale
- XFER - Transfer
- CALLACK - Callback scheduled
- BUSY - Line busy
- NOANSWER - No answer
- DROP - Dropped call
- ABANDOND - Abandoned by caller
- DNC - Do Not Call
Working with Agent Session Logs
Agent Activity Timeline
Track an agent's actual activities throughout their shift:
SELECT
aal.event_time,
aal.event_type,
aal.status,
aal.pause_type,
aal.data,
aal.server_ip
FROM vicidial_agent_log aal
WHERE aal.user = 'AGENT001'
AND aal.event_time >= '2024-01-15 08:00:00'
AND aal.event_time < '2024-01-15 17:00:00'
ORDER BY aal.event_time ASC;
Event types include:
- LOGIN - Agent logged in
- LOGOUT - Agent logged out
- PAUSE - Entered pause (with pause_type indicating reason)
- READY - Returned to ready state
- DISPO - Called disposition
- XFER - Transfer initiated
- 3WAYCONF - 3-way conference
Calculate Agent Availability Percentage
Determine what percentage of an agent's shift was spent ready for calls:
SELECT
user,
DATE(event_time) AS work_date,
TIMEDIFF(
MAX(CASE WHEN event_type = 'LOGOUT' THEN event_time END),
MIN(CASE WHEN event_type = 'LOGIN' THEN event_time END)
) AS total_session_time,
SEC_TO_TIME(
SUM(CASE
WHEN event_type = 'PAUSE' THEN
TIMESTAMPDIFF(SECOND, event_time,
LEAD(event_time) OVER (PARTITION BY user ORDER BY event_time))
ELSE 0
END)
) AS total_pause_time,
ROUND(
(1 - SUM(CASE
WHEN event_type = 'PAUSE' THEN
TIMESTAMPDIFF(SECOND, event_time,
LEAD(event_time) OVER (PARTITION BY user ORDER BY event_time))
ELSE 0
END) / TIMESTAMPDIFF(SECOND,
MIN(CASE WHEN event_type = 'LOGIN' THEN event_time END),
MAX(CASE WHEN event_type = 'LOGOUT' THEN event_time END))
) * 100, 2
) AS availability_pct
FROM vicidial_agent_log
WHERE DATE(event_time) = '2024-01-15'
AND user = 'AGENT001'
GROUP BY user, DATE(event_time);
Performance Report Dashboard in PHP
Creating a Custom Report Dashboard
Create a PHP script at /var/www/html/vicidial/reports/agent_performance.php:
<?php
// agent_performance.php - Custom Agent Performance Report
session_start();
require_once('/var/www/html/vicidial/admin/dbconnect.php');
// Verify admin login
if ($_SESSION['admin_user'] != 'Y') {
header('Location: /vicidial/index.php');
exit;
}
$agent_id = isset($_GET['agent_id']) ? sanitize($_GET['agent_id']) : '';
$report_date = isset($_GET['date']) ? sanitize($_GET['date']) : date('Y-m-d');
if (empty($agent_id)) {
die('Agent ID required');
}
// Connect to ViciDial database
$mysqli = new mysqli($db_host, $db_user, $db_pass, $db_name);
if ($mysqli->connect_error) {
die('Database connection failed: ' . $mysqli->connect_error);
}
// Fetch agent details
$agent_query = "SELECT user, full_name, user_level FROM vicidial_users WHERE user = ?";
$stmt = $mysqli->prepare($agent_query);
$stmt->bind_param('s', $agent_id);
$stmt->execute();
$agent_result = $stmt->get_result()->fetch_assoc();
if (!$agent_result) {
die('Agent not found');
}
// Main performance metrics
$perf_query = "
SELECT
user,
COUNT(*) AS total_calls,
SUM(CASE WHEN talk_sec > 0 THEN 1 ELSE 0 END) AS connected_calls,
ROUND(AVG(talk_sec), 2) AS avg_talk_sec,
SUM(talk_sec) AS total_talk_sec,
ROUND(AVG(wait_sec), 2) AS avg_wait_sec,
SUM(pause_sec) AS total_pause_sec,
MIN(call_date) AS first_call,
MAX(call_date) AS last_call
FROM vicidial_closer_log
WHERE user = ? AND DATE(call_date) = ?
GROUP BY user
";
$stmt = $mysqli->prepare($perf_query);
$stmt->bind_param('ss', $agent_id, $report_date);
$stmt->execute();
$perf_data = $stmt->get_result()->fetch_assoc();
// Status breakdown
$status_query = "
SELECT
status,
COUNT(*) AS count,
ROUND(COUNT(*) / (
SELECT COUNT(*) FROM vicidial_closer_log
WHERE user = ? AND DATE(call_date) = ?
) * 100, 2) AS percentage,
ROUND(AVG(talk_sec), 1) AS avg_talk_sec
FROM vicidial_closer_log
WHERE user = ? AND DATE(call_date) = ?
GROUP BY status
ORDER BY count DESC
";
$stmt = $mysqli->prepare($status_query);
$stmt->bind_param('ssss', $agent_id, $report_date, $agent_id, $report_date);
$stmt->execute();
$status_results = $stmt->get_result();
// Output HTML report
?>
<!DOCTYPE html>
<html>
<head>
<title>Agent Performance Report - <?php echo htmlspecialchars($agent_result['full_name']); ?></title>
<style>
body { font-family: Arial, sans-serif; margin: 20px; }
.report-header { background: #f0f0f0; padding: 15px; border-radius: 5px; margin-bottom: 20px; }
.metric { display: inline-block; margin: 10px 20px 10px 0; }
table { border-collapse: collapse; width: 100%; margin: 20px 0; }
th, td { border: 1px solid #ddd; padding: 10px; text-align: left; }
th { background-color: #4CAF50; color: white; }
tr:nth-child(even) { background-color: #f2f2f2; }
</style>
</head>
<body>
<div class="report-header">
<h1>Agent Performance Report</h1>
<p><strong>Agent:</strong> <?php echo htmlspecialchars($agent_result['full_name']) . ' (' . htmlspecialchars($agent_id) . ')'; ?></p>
<p><strong>Date:</strong> <?php echo htmlspecialchars($report_date); ?></p>
</div>
<div class="metrics">
<?php if ($perf_data): ?>
<div class="metric">
<strong>Total Calls:</strong> <?php echo $perf_data['total_calls']; ?>
</div>
<div class="metric">
<strong>Connected Calls:</strong> <?php echo $perf_data['connected_calls']; ?>
(<?php echo round(($perf_data['connected_calls']/$perf_data['total_calls'])*100, 1); ?>%)
</div>
<div class="metric">
<strong>Avg Talk Time:</strong> <?php echo gmdate('H:i:s', $perf_data['avg_talk_sec']); ?>
</div>
<div class="metric">
<strong>Total Talk Time:</strong> <?php echo gmdate('H:i:s', $perf_data['total_talk_sec']); ?>
</div>
<div class="metric">
<strong>Avg Wait Time:</strong> <?php echo round($perf_data['avg_wait_sec']); ?>s
</div>
<div class="metric">
<strong>Total Pause Time:</strong> <?php echo gmdate('H:i:s', $perf_data['total_pause_sec']); ?>
</div>
<?php else: ?>
<p>No data available for this agent on the selected date.</p>
<?php endif; ?>
</div>
<h2>Call Status Breakdown</h2>
<table>
<tr>
<th>Status</th>
<th>Count</th>
<th>Percentage</th>
<th>Avg Talk Time</th>
</tr>
<?php while ($row = $status_results->fetch_assoc()): ?>
<tr>
<td><?php echo htmlspecialchars($row['status']); ?></td>
<td><?php echo $row['count']; ?></td>
<td><?php echo $row['percentage']; ?>%</td>
<td><?php echo gmdate('H:i:s', $row['avg_talk_sec']); ?></td>
</tr>
<?php endwhile; ?>
</table>
</body>
</html>
<?php
$mysqli->close();
function sanitize($input) {
return htmlspecialchars(strip_tags($input));
}
?>
Access the report:
http://your-vicidial-server/vicidial/reports/agent_performance.php?agent_id=AGENT001&date=2024-01-15
Exporting Reports to CSV
Generate CSV Export via CLI
Create a bash script at /usr/local/bin/vicidial_export_performance.sh:
#!/bin/bash
# vicidial_export_performance.sh - Export agent performance to CSV
DB_HOST="localhost"
DB_USER="vicidial"
DB_PASS="your_password"
DB_NAME="asterisk"
REPORT_DATE="${1:-$(date +%Y-%m-%d)}"
OUTPUT_DIR="/tmp/vicidial_reports"
OUTPUT_FILE="${OUTPUT_DIR}/agent_performance_${REPORT_DATE}.csv"
mkdir -p "$OUTPUT_DIR"
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" \
-e "SELECT
u.user AS 'Agent ID',
u.full_name AS 'Agent Name',
COUNT(*) AS 'Total Calls',
SUM(CASE WHEN vcl.talk_sec > 0 THEN 1 ELSE 0 END) AS 'Connected',
ROUND(COUNT(*) / SUM(CASE WHEN vcl.talk_sec > 0 THEN 1 ELSE 0 END) * 100, 1) AS 'Connect %',
ROUND(AVG(vcl.talk_sec), 1) AS 'Avg Talk Sec',
SEC_TO_TIME(SUM(vcl.talk_sec)) AS 'Total Talk Time',
ROUND(AVG(vcl.wait_sec), 1) AS 'Avg Wait Sec',
SEC_TO_TIME(SUM(vcl.pause_sec)) AS 'Total Pause Time'
FROM vicidial_closer_log vcl
INNER JOIN vicidial_users u ON vcl.user = u.user
WHERE DATE(vcl.call_date) = '$REPORT_DATE'
GROUP BY vcl.user, u.full_name
ORDER BY COUNT(*) DESC" \
--batch --skip-column-names \
| sed 's/\t/","/g;s/^/"/;s/$/"/' > "$OUTPUT_FILE"
echo "Report exported to: $OUTPUT_FILE"
ls -lh "$OUTPUT_FILE"
Make it executable and run:
chmod +x /usr/local/bin/vicidial_export_performance.sh
/usr/local/bin/vicidial_export_performance.sh 2024-01-15
Export with Header Row
Modify the script to include headers:
#!/bin/bash
OUTPUT_FILE="/tmp/vicidial_reports/performance_$(date +%Y%m%d_%H%M%S).csv"
{
echo "Agent ID,Agent Name,Total Calls,Connected,Connect %,Avg Talk Sec,Total Talk Time,Avg Wait Sec,Total Pause Time"
mysql -h localhost -u vicidial -p"password" asterisk -N -e \
"SELECT
u.user,
u.full_name,
COUNT(*),
SUM(CASE WHEN vcl.talk_sec > 0 THEN 1 ELSE 0 END),
ROUND(SUM(CASE WHEN vcl.talk_sec > 0 THEN 1 ELSE 0 END) / COUNT(*) * 100, 1),
ROUND(AVG(vcl.talk_sec), 1),
SEC_TO_TIME(SUM(vcl.talk_sec)),
ROUND(AVG(vcl.wait_sec), 1),
SEC_TO_TIME(SUM(vcl.pause_sec))
FROM vicidial_closer_log vcl
INNER JOIN vicidial_users u ON vcl.user = u.user
WHERE DATE(vcl.call_date) = CURDATE()
GROUP BY vcl.user, u.full_name
ORDER BY COUNT(*) DESC" | sed 's/\t/,/g'
} > "$OUTPUT_FILE"
echo "Report saved: $OUTPUT_FILE"
Advanced Metrics and KPIs
Calculate Average Handle Time (AHT)
SELECT
user,
ROUND(AVG(talk_sec + pause_sec), 2) AS aht_seconds,
SEC_TO_TIME(ROUND(AVG(talk_sec + pause_sec))) AS aht_formatted,
COUNT(*) AS calls_handled
FROM vicidial_closer_log
WHERE DATE(call_date) = CURDATE()
AND talk_sec > 0
GROUP BY user
ORDER BY aht_seconds DESC;
First Call Resolution (FCR) Analysis
Track agents who solve issues on first contact:
SELECT
vcl1.user,
COUNT(DISTINCT vcl1.lead_id) AS total_unique_leads,
COUNT(DISTINCT CASE
WHEN NOT EXISTS (
SELECT 1 FROM vicidial_closer_log vcl2
WHERE vcl2.lead_id = vcl1.lead_id
AND vcl2.call_date > vcl1.call_date
AND DATE(vcl2.call_date) = DATE(vcl1.call_date)
)
THEN vcl1.lead_id
END) AS first_contact_resolutions,
ROUND(
COUNT(DISTINCT CASE
WHEN NOT EXISTS (
SELECT 1 FROM vicidial_closer_log vcl2
WHERE vcl2.lead_id = vcl1.lead_id
AND vcl2.call_date > vcl1.call_date
AND DATE(vcl2.call_date) = DATE(vcl1.call_date)
)
THEN vcl1.lead_id
END) / COUNT(DISTINCT vcl1.lead_id) * 100, 2
) AS fcr_percentage
FROM vicidial_closer_log vcl1
WHERE DATE(vcl1.call_date) = CURDATE()
AND vcl1.talk_sec > 0
GROUP BY vcl1.user
ORDER BY fcr_percentage DESC;
Customer Satisfaction Trend
If using surveys or quality scores:
SELECT
u.user,
u.full_name,
DATE(vcl.call_date) AS report_date,
COUNT(*) AS calls_reviewed,
ROUND(AVG(CAST(vcl.customer_satisfaction AS DECIMAL(3,2))), 2) AS avg_satisfaction,
COUNT(CASE WHEN CAST(vcl.customer_satisfaction AS DECIMAL(3,2)) >= 4 THEN 1 END) AS positive_surveys
FROM vicidial_closer_log vcl
INNER JOIN vicidial_users u ON vcl.user = u.user
WHERE vcl.customer_satisfaction IS NOT NULL
AND DATE(vcl.call_date) >= DATE(NOW()) - INTERVAL 7 DAY
GROUP BY u.user, u.full_name, DATE(vcl.call_date)
ORDER BY report_date DESC, avg_satisfaction DESC;
Monitoring and Real-Time Metrics
Real-Time Agent Dashboard Query
Pull current agent status without delay:
SELECT
u.user,
u.full_name,
ual.status,
ual.pause_type,
ual.event_time,
TIMESTAMPDIFF(MINUTE, ual.event_time, NOW()) AS minutes_in_status,
(SELECT COUNT(*) FROM vicidial_closer_log
WHERE user = u.user AND DATE(call_date) = CURDATE()) AS today_calls,
(SELECT SUM(talk_sec) FROM vicidial_closer_log
WHERE user = u.user AND DATE(call_date) = CURDATE()) AS today_talk_sec
FROM vicidial_agent_log ual
INNER JOIN vicidial_users u ON ual.user = u.user
WHERE ual.event_time = (
SELECT MAX(event_time) FROM vicidial_agent_log
WHERE user = u.user
)
ORDER BY u.full_name;
Set Up Automated Report Email
Create a cron job to email daily reports:
#!/bin/bash
# /usr/local/bin/email_performance_report.sh
RECIPIENT="supervisor@company.com"
SUBJECT="Daily Agent Performance Report - $(date +%Y-%m-%d)"
REPORT_DATE=$(date -d yesterday +%Y-%m-%d)
REPORT=$(mysql -h localhost -u vicidial -p"password" asterisk -N -e \
"SELECT CONCAT(
'Agent: ', u.full_name, ' | ',
'Calls: ', COUNT(*), ' | ',
'Avg Talk: ', SEC_TO_TIME(ROUND(AVG(talk_sec))), ' | ',
'Connect %: ', ROUND(SUM(CASE WHEN talk_sec > 0 THEN 1 ELSE 0 END)/COUNT(*)*100, 1)
)
FROM vicidial_closer_log vcl
INNER JOIN vicidial_users u ON vcl.user = u.user
WHERE DATE(vcl.call_date) = '$REPORT_DATE'
GROUP BY vcl.user
ORDER BY COUNT(*) DESC")
echo "$REPORT" | mail -s "$SUBJECT" "$RECIPIENT"
Add to crontab:
0 8 * * * /usr/local/bin/email_performance_report.sh
Troubleshooting
Report Shows No Data for Recent Calls
Problem: Queries return empty results for calls made in the last hour.
Solution: Check if calls are being written to vicidial_closer_log:
mysql -u vicidial -p asterisk -e \
"SELECT COUNT(*) FROM vicidial_closer_log WHERE call_date >= NOW() - INTERVAL 1 HOUR;"
If empty, verify the ViciDial daemon is running:
ps aux | grep Asterisk
ps aux | grep vicidial
systemctl status vicidial
Restart services if needed:
systemctl restart asterisk
systemctl restart vicidial
Large Query Timeout Issues
Problem: Complex reports timeout when querying large date ranges.
Solution: Add indexes to optimize queries:
ALTER TABLE vicidial_closer_log
ADD INDEX idx_user_date (user, call_date),
ADD INDEX idx_talk_sec (talk_sec),
ADD INDEX idx_status (status);
-- Verify indexes
SHOW INDEX FROM vicidial_closer_log;
For multi-month reports, use date partitioning or export to a data warehouse.
Agent Logs Show Incorrect Pause Time
Problem: total_pause_sec calculations don't match agent perception.
Solution: Cross-reference with vicidial_agent_log:
SELECT
u.user,
SUM(vcl.pause_sec) AS closer_log_pause,
SUM(CASE WHEN aal.event_type = 'PAUSE' THEN
TIMESTAMPDIFF(SECOND, aal.event_time,
LEAD(aal.event_time) OVER (PARTITION BY aal.user ORDER BY aal.event_time))
END) AS agent_log_pause
FROM vicidial_closer_log vcl
INNER JOIN vicidial_users u ON vcl.user = u.user
LEFT JOIN vicidial_agent_log aal ON u.user = aal.user
AND DATE(vcl.call_date) = DATE(aal.event_time)
WHERE DATE(vcl.call_date) = CURDATE()
GROUP BY u.user;
Discrepancies indicate calls missing entries in one table—check ViciDial logs at /var/log/asterisk/messages.
Performance Report Taking Too Long to Generate
Problem: Generating reports for teams/campaigns is slow.
Solution: Use STRAIGHT_JOIN to force join order and add date ranges:
SELECT STRAIGHT_JOIN
u.user,
COUNT(*) AS total_calls
FROM vicidial_closer_log vcl
STRAIGHT_JOIN vicidial_users u ON vcl.user = u.user
WHERE vcl.call_date >= '2024-01-15 00:00:00'
AND vcl.call_date < '2024-01-16 00:00:00'
AND u.user_level > 1
GROUP BY u.user;
For very large datasets, consider archiving old records:
# Archive calls older than 90 days
mysql -u vicidial -p asterisk -e \
"INSERT INTO vicidial_closer_log_archive
SELECT * FROM vicidial_closer_log
WHERE call_date < DATE_SUB(NOW(), INTERVAL 90 DAY);
DELETE FROM vicidial_closer_log
WHERE call_date < DATE_SUB(NOW(), INTERVAL 90 DAY);"
Custom Reports Showing Wrong User Names
Problem: Agent IDs display instead of full names.
Solution: Verify the vicidial_users table join and check if users were deleted:
SELECT COUNT(*) FROM vicidial_users WHERE user = 'AGENT001';
If the agent was deleted, their historical data remains in vicidial_closer_log but vicidial_users join will fail. Use this approach:
SELECT
vcl.user,
COALESCE(u.full_name, vcl.user) AS agent_name,
COUNT(*) AS calls
FROM vicidial_closer_log vcl
LEFT JOIN vicidial_users u ON vcl.user = u.user
WHERE DATE(vcl.call_date) = CURDATE()
GROUP BY vcl.user;
Reports Not Reflecting Real-Time Changes
Problem: Updated agent information or statuses not showing immediately.
Solution: ViciDial caches some data. Clear relevant caches:
# Clear PHP cache if using OPcache
php -r "opcache_reset();"
# Restart web server
systemctl restart apache2
# or
systemctl restart nginx
For agent status, query directly from memory:
asterisk -rx "show channels concise" | grep -i SIP
Top comments (0)