Master the art of building production-grade custom reports in ViciDial by writing optimized SQL queries against the asterisk database, complete with real examples, performance tuning, and troubleshooting strategies.
Prerequisites
Before diving into custom ViciDial reports, ensure you have:
- A running ViciDial installation (v2.14 or later recommended)
- SSH access to your ViciDial server with sudo privileges
- MySQL/MariaDB client installed locally or access to the database server
- Basic SQL knowledge (SELECT, WHERE, JOIN, GROUP BY)
- Understanding of ViciDial's call flow and database structure
- Database credentials for the
asteriskdatabase - Text editor or IDE with SQL syntax highlighting (optional but recommended)
- Read access to
/var/log/asterisk/messagesfor debugging
Verify your ViciDial installation:
mysql -u root -p -e "USE asterisk; SHOW TABLES LIKE 'vicidial%';" | head -20
Understanding ViciDial Database Structure
Critical Tables for Reporting
ViciDial stores call data across multiple interconnected tables. Understanding their relationships is fundamental to writing effective reports.
vicidial_log — The primary call record table. Every inbound/outbound call creates an entry here.
DESCRIBE asterisk.vicidial_log\G
Key columns:
uniqueid— Unique call identifier (varchar 32)caller_id_number— Calling party numbercaller_id_name— Calling party namephone_number— Called party numbercall_date— Call start timestampcall_length— Duration in secondsstatus— Final call status (SALE, NO ANSWER, BAD NUMBER, etc.)campaign_id— Associated campaignuser— Agent usernamelist_id— DNC/contact list
vicidial_closer_log — Bridge logs for calls transferred to agents.
DESCRIBE asterisk.vicidial_closer_log\G
Key columns:
uniqueid— Links to vicidial_loguser— Agent who answeredcloser_called_number— Number dialed by agentstatus— Agent-assigned dispositiontalk_time— Seconds agent spent talkingcall_date— When agent answered
vicidial_users — Agent/user information.
DESCRIBE asterisk.vicidial_users\G
vicidial_campaigns — Campaign definitions and settings.
DESCRIBE asterisk.vicidial_campaigns\G
vicidial_list — Contact records.
DESCRIBE asterisk.vicidial_list\G
Database Connection String
Create a MySQL credentials file for secure, repeatable access:
cat > ~/.my.cnf << 'EOF'
[client]
user=root
password=your_password
host=localhost
database=asterisk
EOF
chmod 600 ~/.my.cnf
Verify connection:
mysql -e "SELECT COUNT(*) as total_calls FROM vicidial_log WHERE call_date >= DATE_SUB(NOW(), INTERVAL 24 HOUR);"
Setting Up Your Reporting Environment
Create a Dedicated Reporting Database User
For production security, create a read-only user:
CREATE USER 'vicidial_reports'@'localhost' IDENTIFIED BY 'secure_password_here';
GRANT SELECT ON asterisk.* TO 'vicidial_reports'@'localhost';
FLUSH PRIVILEGES;
Test the new user:
mysql -u vicidial_reports -p -e "SELECT VERSION();"
Organize Your Queries
Create a directory structure for managing report queries:
mkdir -p /home/vicidial_admin/reports/{daily,weekly,monthly,custom}
mkdir -p /home/vicidial_admin/reports/outputs
chmod 755 /home/vicidial_admin/reports
Common Report Queries
Daily Call Volume by Campaign
This query aggregates all calls for the past 24 hours, grouped by campaign.
SELECT
c.campaign_name,
c.campaign_id,
COUNT(vl.uniqueid) as total_calls,
SUM(CASE WHEN vl.status = 'SALE' THEN 1 ELSE 0 END) as sales,
SUM(CASE WHEN vl.status = 'SALE' THEN 1 ELSE 0 END) / COUNT(vl.uniqueid) * 100 as conversion_rate,
ROUND(AVG(vl.call_length), 2) as avg_call_length,
SUM(vl.call_length) as total_talk_time,
MIN(vl.call_date) as first_call,
MAX(vl.call_date) as last_call
FROM
vicidial_log vl
LEFT JOIN vicidial_campaigns c ON vl.campaign_id = c.campaign_id
WHERE
vl.call_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
AND vl.call_date < CURDATE()
AND vl.status NOT IN ('', 'SKIP', 'QUEUE')
GROUP BY
vl.campaign_id, c.campaign_name
ORDER BY
total_calls DESC;
Save this to a file:
cat > /home/vicidial_admin/reports/daily/daily_volume.sql << 'EOF'
-- Daily Call Volume by Campaign
SELECT
c.campaign_name,
c.campaign_id,
COUNT(vl.uniqueid) as total_calls,
SUM(CASE WHEN vl.status = 'SALE' THEN 1 ELSE 0 END) as sales,
ROUND(SUM(CASE WHEN vl.status = 'SALE' THEN 1 ELSE 0 END) / COUNT(vl.uniqueid) * 100, 2) as conversion_rate,
ROUND(AVG(vl.call_length), 2) as avg_call_length,
SUM(vl.call_length) as total_talk_time,
MIN(vl.call_date) as first_call,
MAX(vl.call_date) as last_call
FROM
vicidial_log vl
LEFT JOIN vicidial_campaigns c ON vl.campaign_id = c.campaign_id
WHERE
vl.call_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
AND vl.call_date < CURDATE()
AND vl.status NOT IN ('', 'SKIP', 'QUEUE')
GROUP BY
vl.campaign_id, c.campaign_name
ORDER BY
total_calls DESC;
EOF
Execute the report:
mysql < /home/vicidial_admin/reports/daily/daily_volume.sql > /home/vicidial_admin/reports/outputs/daily_volume_$(date +%Y%m%d).txt
Agent Performance Report
Track individual agent metrics over a specific period.
SELECT
vu.user,
vu.full_name,
COUNT(DISTINCT vcl.uniqueid) as calls_handled,
SUM(vcl.talk_time) as total_talk_time,
ROUND(AVG(vcl.talk_time), 2) as avg_talk_time,
COUNT(DISTINCT CASE WHEN vcl.status = 'SALE' THEN vcl.uniqueid END) as sales,
ROUND(COUNT(DISTINCT CASE WHEN vcl.status = 'SALE' THEN vcl.uniqueid END) / COUNT(DISTINCT vcl.uniqueid) * 100, 2) as sales_percentage,
MIN(vcl.call_date) as first_call,
MAX(vcl.call_date) as last_call
FROM
vicidial_closer_log vcl
LEFT JOIN vicidial_users vu ON vcl.user = vu.user
WHERE
vcl.call_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND vcl.call_date < CURDATE()
AND vcl.status NOT IN ('', 'QUEUE')
AND vu.user != ''
GROUP BY
vcl.user, vu.full_name
ORDER BY
calls_handled DESC;
Disposition Breakdown
Understand how calls are being disposed across your system.
SELECT
c.campaign_name,
vl.status as disposition,
COUNT(vl.uniqueid) as count,
ROUND(COUNT(vl.uniqueid) / (SELECT COUNT(*) FROM vicidial_log WHERE call_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND campaign_id = c.campaign_id) * 100, 2) as percentage
FROM
vicidial_log vl
LEFT JOIN vicidial_campaigns c ON vl.campaign_id = c.campaign_id
WHERE
vl.call_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
AND vl.call_date < CURDATE()
AND vl.status NOT IN ('')
GROUP BY
vl.campaign_id, c.campaign_name, vl.status
ORDER BY
c.campaign_name, count DESC;
Calls by Hour of Day
Identify peak calling times and staffing patterns.
SELECT
HOUR(vl.call_date) as hour_of_day,
COUNT(vl.uniqueid) as total_calls,
COUNT(DISTINCT vl.user) as agents_active,
ROUND(AVG(vl.call_length), 2) as avg_call_length,
SUM(CASE WHEN vl.status = 'SALE' THEN 1 ELSE 0 END) as sales,
ROUND(SUM(CASE WHEN vl.status = 'SALE' THEN 1 ELSE 0 END) / COUNT(vl.uniqueid) * 100, 2) as sales_rate
FROM
vicidial_log vl
WHERE
vl.call_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
AND vl.call_date < CURDATE()
AND vl.status NOT IN ('', 'SKIP', 'QUEUE')
GROUP BY
HOUR(vl.call_date)
ORDER BY
hour_of_day ASC;
DNC Compliance Report
Monitor Do-Not-Call list compliance for regulatory requirements.
SELECT
COUNT(DISTINCT CASE WHEN vl.status = 'DNC' THEN vl.phone_number END) as dnc_calls,
COUNT(DISTINCT vl.phone_number) as unique_numbers_called,
ROUND(COUNT(DISTINCT CASE WHEN vl.status = 'DNC' THEN vl.phone_number END) / COUNT(DISTINCT vl.phone_number) * 100, 2) as dnc_percentage,
COUNT(DISTINCT vl.campaign_id) as campaigns_involved
FROM
vicidial_log vl
WHERE
vl.call_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
AND vl.status IN ('DNC', 'DNC PERM');
Advanced Query Patterns
Using Window Functions for Ranking
Identify top-performing agents with ranking:
SELECT
@rank := @rank + 1 as rank,
user,
calls_handled,
sales,
sales_percentage
FROM (
SELECT
vcl.user,
COUNT(DISTINCT vcl.uniqueid) as calls_handled,
COUNT(DISTINCT CASE WHEN vcl.status = 'SALE' THEN vcl.uniqueid END) as sales,
ROUND(COUNT(DISTINCT CASE WHEN vcl.status = 'SALE' THEN vcl.uniqueid END) / COUNT(DISTINCT vcl.uniqueid) * 100, 2) as sales_percentage
FROM
vicidial_closer_log vcl
WHERE
vcl.call_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND vcl.call_date < CURDATE()
GROUP BY
vcl.user
ORDER BY
sales_percentage DESC
) as ranked_agents, (SELECT @rank := 0) as init;
Multi-Campaign Comparison
Compare performance metrics across multiple campaigns simultaneously.
SELECT
c.campaign_name,
DATE(vl.call_date) as call_date,
COUNT(vl.uniqueid) as calls,
SUM(CASE WHEN vl.status = 'SALE' THEN 1 ELSE 0 END) as sales,
ROUND(SUM(CASE WHEN vl.status = 'SALE' THEN 1 ELSE 0 END) / COUNT(vl.uniqueid) * 100, 2) as conversion,
ROUND(AVG(vl.call_length), 2) as avg_length
FROM
vicidial_log vl
LEFT JOIN vicidial_campaigns c ON vl.campaign_id = c.campaign_id
WHERE
vl.call_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
AND c.campaign_id IN ('CAMPAIGN1', 'CAMPAIGN2', 'CAMPAIGN3')
GROUP BY
vl.campaign_id, DATE(vl.call_date)
ORDER BY
DATE(vl.call_date) DESC, c.campaign_name ASC;
Contact List Activity Report
Track activity on specific contact lists.
SELECT
vl.list_id,
COUNT(DISTINCT vl.phone_number) as unique_contacts,
COUNT(vl.uniqueid) as total_calls,
COUNT(DISTINCT CASE WHEN vl.status = 'SALE' THEN vl.phone_number END) as contacts_converted,
SUM(CASE WHEN vl.status = 'SALE' THEN 1 ELSE 0 END) as total_sales,
ROUND(SUM(CASE WHEN vl.status = 'SALE' THEN 1 ELSE 0 END) / COUNT(vl.uniqueid) * 100, 2) as sales_rate,
MIN(vl.call_date) as first_call_date,
MAX(vl.call_date) as last_call_date
FROM
vicidial_log vl
WHERE
vl.call_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND vl.list_id IN (SELECT list_id FROM vicidial_list GROUP BY list_id HAVING COUNT(*) > 0)
GROUP BY
vl.list_id
ORDER BY
total_calls DESC;
Creating Automated Reports
Bash Script for Scheduled Reports
Create a production-ready reporting script that runs via cron.
#!/bin/bash
# /home/vicidial_admin/reports/generate_reports.sh
REPORT_DIR="/home/vicidial_admin/reports"
OUTPUT_DIR="/home/vicidial_admin/reports/outputs"
BACKUP_DIR="/home/vicidial_admin/reports/archive"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/vicidial_reports.log"
# Ensure directories exist
mkdir -p "$OUTPUT_DIR" "$BACKUP_DIR"
# Function to log messages
log_message() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}
log_message "Starting ViciDial report generation"
# Generate daily volume report
log_message "Generating daily volume report..."
mysql --defaults-extra-file=~/.my.cnf < "$REPORT_DIR/daily/daily_volume.sql" > "$OUTPUT_DIR/daily_volume_$TIMESTAMP.csv" 2>> "$LOG_FILE"
if [ $? -eq 0 ]; then
log_message "Daily volume report completed successfully"
else
log_message "ERROR: Daily volume report failed"
fi
# Generate agent performance report
log_message "Generating agent performance report..."
mysql --defaults-extra-file=~/.my.cnf < "$REPORT_DIR/daily/agent_performance.sql" > "$OUTPUT_DIR/agent_performance_$TIMESTAMP.csv" 2>> "$LOG_FILE"
if [ $? -eq 0 ]; then
log_message "Agent performance report completed successfully"
else
log_message "ERROR: Agent performance report failed"
fi
# Clean up old reports (keep 30 days)
log_message "Cleaning up old reports..."
find "$OUTPUT_DIR" -name "*.csv" -mtime +30 -exec mv {} "$BACKUP_DIR" \; >> "$LOG_FILE" 2>&1
log_message "Report generation completed"
Make the script executable:
chmod +x /home/vicidial_admin/reports/generate_reports.sh
Schedule Reports via Cron
Add to root's crontab to run daily at 6 AM:
sudo crontab -e
# Generate ViciDial reports daily at 6 AM
0 6 * * * /home/vicidial_admin/reports/generate_reports.sh
Verify cron job is set:
sudo crontab -l | grep generate_reports
Email Report Delivery
Enhance the bash script to email reports:
#!/bin/bash
# Add this to your generate_reports.sh script
REPORT_DIR="/home/vicidial_admin/reports"
OUTPUT_DIR="/home/vicidial_admin/reports/outputs"
TIMESTAMP=$(date +%Y%m%d)
EMAIL_TO="[email protected]"
EMAIL_SUBJECT="ViciDial Daily Report - $TIMESTAMP"
HOSTNAME=$(hostname)
# Generate reports first
mysql --defaults-extra-file=~/.my.cnf < "$REPORT_DIR/daily/daily_volume.sql" > "$OUTPUT_DIR/daily_volume_$TIMESTAMP.csv"
# Create email body
cat > /tmp/email_body.txt << EOF
ViciDial Daily Report
Generated: $(date)
Hostname: $HOSTNAME
Daily Call Volume Summary:
$(head -20 "$OUTPUT_DIR/daily_volume_$TIMESTAMP.csv")
Full reports attached.
EOF
# Send email with attachment
mail -s "$EMAIL_SUBJECT" \
-a "From: vicidial@$HOSTNAME" \
-a "$OUTPUT_DIR/daily_volume_$TIMESTAMP.csv" \
"$EMAIL_TO" < /tmp/email_body.txt
rm /tmp/email_body.txt
Performance Optimization
Index Strategy for Reporting Queries
Check existing indexes:
SHOW INDEX FROM vicidial_log;
SHOW INDEX FROM vicidial_closer_log;
Add critical indexes for reporting if missing:
-- For call_date filtering (most common)
ALTER TABLE vicidial_log ADD INDEX idx_call_date (call_date);
-- For campaign filtering
ALTER TABLE vicidial_log ADD INDEX idx_campaign_id (campaign_id);
-- For user filtering
ALTER TABLE vicidial_closer_log ADD INDEX idx_closer_user (user, call_date);
-- Combined index for date range and campaign queries
ALTER TABLE vicidial_log ADD INDEX idx_campaign_date (campaign_id, call_date);
-- Verify indexes are created
SHOW INDEX FROM vicidial_log\G
Query Performance Testing
Use EXPLAIN to analyze query execution:
EXPLAIN
SELECT
COUNT(*) as calls,
SUM(call_length) as total_time
FROM
vicidial_log
WHERE
call_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
AND campaign_id = 'CAMPAIGN1';
Expected output should show:
- Type:
rangeorref(notALL) - Rows: Should be reasonably small relative to table size
- Extra: Should not say "Using temporary" or "Using filesort"
Archive Old Data
For very large databases, archive records older than 1 year:
#!/bin/bash
# Archive reports to separate database for historical analysis
ARCHIVE_DB="asterisk_archive"
RETENTION_DAYS=365
mysql << 'EOF'
-- Create archive table if not exists
CREATE TABLE IF NOT EXISTS ${ARCHIVE_DB}.vicidial_log_archive LIKE asterisk.vicidial_log;
-- Copy old records to archive
INSERT INTO ${ARCHIVE_DB}.vicidial_log_archive
SELECT * FROM asterisk.vicidial_log
WHERE call_date < DATE_SUB(NOW(), INTERVAL ${RETENTION_DAYS} DAY);
-- Delete archived records from main table
DELETE FROM asterisk.vicidial_log
WHERE call_date < DATE_SUB(NOW(), INTERVAL ${RETENTION_DAYS} DAY);
-- Optimize table to reclaim space
OPTIMIZE TABLE asterisk.vicidial_log;
EOF
Integration with ViciDial Web Interface
Custom Report Module Integration
ViciDial allows custom reports via the admin interface. Place your report script in the correct directory:
cat > /var/www/html/vicidial/admin/custom_reports.php << 'EOF'
<?php
// Simple custom report viewer for ViciDial
$db = mysqli_connect('localhost', 'vicidial_reports', 'password', 'asterisk');
if (!$db) {
die("Database connection failed: " . mysqli_connect_error());
}
// Sanitize input
$report_type = isset($_GET['type']) ? mysqli_real_escape_string($db, $_GET['type']) : 'daily_volume';
$days = isset($_GET['days']) ? intval($_GET['days']) : 1;
// Simple report queries
$queries = array(
'daily_volume' => "
SELECT
c.campaign_name,
COUNT(vl.uniqueid) as calls,
SUM(CASE WHEN vl.status = 'SALE' THEN 1 ELSE 0 END) as sales
FROM vicidial_log vl
LEFT JOIN vicidial_campaigns c ON vl.campaign_id = c.campaign_id
WHERE vl.call_date >= DATE_SUB(CURDATE(), INTERVAL $days DAY)
GROUP BY vl.campaign_id
ORDER BY calls DESC
",
'agent_performance' => "
SELECT
vcl.user,
COUNT(vcl.uniqueid) as calls,
SUM(vcl.talk_time) as talk_time,
COUNT(CASE WHEN vcl.status = 'SALE' THEN 1 END) as sales
FROM vicidial_closer_log vcl
WHERE vcl.call_date >= DATE_SUB(CURDATE(), INTERVAL $days DAY)
GROUP BY vcl.user
ORDER BY calls DESC
"
);
if (isset($queries[$report_type])) {
$result = mysqli_query($db, $queries[$report_type]);
echo "<table border='1'>";
// Print header
$row = mysqli_fetch_assoc($result);
echo "<tr>";
foreach ($row as $key => $value) {
echo "<th>$key</th>";
}
echo "</tr>";
// Print rows
echo "<tr>";
foreach ($row as $value) {
echo "<td>$value</td>";
}
echo "</tr>";
while ($row = mysqli_fetch_assoc($result)) {
echo "<tr>";
foreach ($row as $value) {
echo "<td>$value</td>";
}
echo "</tr>";
}
echo "</table>";
}
mysqli_close($db);
?>
EOF
chmod 644 /var/www/html/vicidial/admin/custom_reports.php
Access the report at: http://your-vicidial-server/vicidial/admin/custom_reports.php?type=daily_volume&days=7
Troubleshooting
Database Connection Issues
Problem: "Can't connect to MySQL server"
Solution:
# Check MySQL service status
systemctl status mysql
# Verify credentials
mysql -u vicidial_reports -p -e "SELECT 1;"
# Check user permissions
mysql -u root -p -e "SHOW GRANTS FOR 'vicidial_reports'@'localhost';"
# Review MySQL error log
tail -50 /var/log/mysql/error.log
Slow Query Performance
Problem: Report queries taking too long (>10 seconds)
Solution:
# Enable query logging
mysql -u root -p -e "SET GLOBAL slow_query_log = 'ON';"
mysql -u root -p -e "SET GLOBAL long_query_time = 5;"
# Check slow query log
tail -100 /var/log/mysql/slow.log
# Run EXPLAIN on slow queries
mysql << 'EOF'
SET SESSION sql_mode='';
EXPLAIN SELECT ... FROM ... WHERE ...;
EOF
# Create missing indexes based on EXPLAIN results
ALTER TABLE vicidial_log ADD INDEX idx_analysis (campaign_id, call_date);
Inconsistent Report Data
Problem: Different results when running the same report twice
Solution:
-- Check for locking issues
SHOW OPEN TABLES WHERE in_use > 0;
-- Check table integrity
CHECK TABLE vicidial_log;
REPAIR TABLE vicidial_log;
-- Verify data consistency
SELECT COUNT(*) FROM vicidial_log WHERE call_date IS NULL;
SELECT COUNT(*) FROM vicidial_log WHERE status = '';
High Memory Usage During Report Generation
Problem: Server runs out of memory when generating large reports
Solution:
# Break report into smaller date ranges
# Instead of 365 days at once, do monthly:
for month in {1..12}; do
START_DATE="2024-$(printf '%02d' $month)-01"
END_DATE="2024-$(printf '%02d' $((month+1)))-01"
mysql << EOF
SELECT * FROM vicidial_log
WHERE call_date >= '$START_DATE'
AND call_date < '$END_DATE'
INTO OUTFILE '/tmp/report_$month.csv';
EOF
done
Missing Data in Reports
Problem: Report shows fewer calls than expected
Solution:
-- Check for NULL campaigns
SELECT COUNT(*) FROM vicidial_log WHERE campaign_id IS NULL OR campaign_id = '';
-- Verify date range filter
SELECT MIN(call_date), MAX(call_date) FROM vicidial_log;
-- Check status filters in query
SELECT DISTINCT status FROM vicidial_log LIMIT 20;
-- Ensure no excluded statuses
SELECT status, COUNT(*) FROM vicidial_log
WHERE call_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY status;
Summary
Creating custom ViciDial reports with SQL requires understanding the database schema, mastering JOIN operations, and implementing performance optimizations. Key takeaways:
Essential Skills:
- Master the vicidial_log, vicidial_closer_log, and vicidial_users tables
- Use proper date filtering with call_date for accurate period-based reports
- Leverage GROUP BY and aggregate functions for summarization
- Implement indexes on frequently queried columns (campaign_id, call_date, user)
Production Best Practices:
- Create dedicated read-only database users for reporting
- Schedule reports via cron for consistent delivery
- Monitor query performance with EXPLAIN and slow query logs
- Archive old data to maintain query speed
- Validate data consistency regularly
Common Mistakes to Avoid:
- Forgetting to filter out empty or test statuses
- Not joining with vicidial_campaigns/vicidial_users tables
- Running reports without date range filters (inefficient)
- Ignoring NULL values in campaign_id or user fields
- Failing to account for time zone differences in call_date
Next Steps:
- Start with pre-built queries from this guide
- Test queries on non-peak hours first
- Create backup indexes for high-frequency report queries
- Document your custom queries in version control
- Monitor execution time and optimize based on EXPLAIN analysis
- Integrate with ViciDial's admin interface for user accessibility
- Set up automated email delivery for stakeholder reports
- Establish data retention and archival policies
With these fundamentals, you can build enterprise-grade reporting solutions that give real-time insights into your ViciDial call center operations.