How to Create Custom ViciDial Reports with SQL Queries

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 asterisk database
Text editor or IDE with SQL syntax highlighting (optional but recommended)
Read access to /var/log/asterisk/messages for 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 number
caller_id_name — Calling party name
phone_number — Called party number
call_date — Call start timestamp
call_length — Duration in seconds
status — Final call status (SALE, NO ANSWER, BAD NUMBER, etc.)
campaign_id — Associated campaign
user — Agent username
list_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_log
user — Agent who answered
closer_called_number — Number dialed by agent
status — Agent-assigned disposition
talk_time — Seconds agent spent talking
call_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="manager@company.com"
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: range or ref (not ALL)
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.

How to Create Custom ViciDial Reports with SQL Queries

How to Create Custom ViciDial Reports with SQL Queries

Prerequisites

Understanding ViciDial Database Structure

Critical Tables for Reporting

Database Connection String

Setting Up Your Reporting Environment

Create a Dedicated Reporting Database User

Organize Your Queries

Common Report Queries

Daily Call Volume by Campaign

Agent Performance Report

Disposition Breakdown

Calls by Hour of Day

DNC Compliance Report

Advanced Query Patterns

Using Window Functions for Ranking

Multi-Campaign Comparison

Contact List Activity Report

Creating Automated Reports

Bash Script for Scheduled Reports

Schedule Reports via Cron

Email Report Delivery

Performance Optimization

Index Strategy for Reporting Queries

Query Performance Testing

Archive Old Data

Integration with ViciDial Web Interface

Custom Report Module Integration

Troubleshooting

Database Connection Issues

Slow Query Performance

Inconsistent Report Data

High Memory Usage During Report Generation

Missing Data in Reports

Summary

Tags

Author

Stats

Published

You Might Also Like

Reviving Open Source Giants: How I Brought Weave Scope Back with Multi-Platform Docker Support in One Afternoon Using Antigravity

GitHub doesn't retry webhooks. So I gave my tunnel a database.

I Automated My Entire GitOps Security Stack. The First Thing It Blocked Was My Own Salary.

The Backup Awakens: A Star Wars Story

Everything Was Working. AWS Wanted $1,665/Month More.

I Gave My Agent One Signed Permission It Couldn’t Mint Itself