ViciDial Campaign DNC (Do Not Call) List Management

Master the complete workflow for importing, maintaining, and enforcing Do Not Call lists in ViciDial to ensure compliance and protect your campaign reputation

Prerequisites

Before implementing ViciDial DNC list management, ensure you have:

ViciDial installation (v2.13 or later recommended) with working Asterisk backend
Database access to the asterisk MySQL database with appropriate user permissions
Command-line access to your ViciDial server with sudo privileges
Basic understanding of ViciDial architecture, campaigns, and the dialing process
Backup strategy in place before modifying DNC tables
FTP/SFTP access or direct file upload capability to the ViciDial server
PHP CLI installed for running bulk import scripts
At least 500MB free disk space for DNC list files during import operations

Verify your installation:

mysql -u root -p -e "USE asterisk; SHOW TABLES LIKE '%dnc%';"
ls -la /var/www/html/vicidial/admin.php
asterisk -rx "core show version"

Understanding ViciDial DNC Architecture

DNC Tables and Structure

ViciDial maintains DNC lists across several interconnected MySQL tables. The primary table is vicidial_dnc:

DESCRIBE vicidial_dnc;

Expected output shows these critical columns:

Field                  | Type             | Description
phone_number          | varchar(20)      | Normalized phone number (primary key)
status                | enum             | ACTIVE, INACTIVE, PENDING
list_id               | int(11)          | References vicidial_dnc_list
date_added            | datetime         | When number was added to DNC
user_id               | varchar(20)      | Admin who added it (optional)
comments              | text             | Reason for DNC status

The vicidial_dnc_list table defines DNC list categories:

SELECT * FROM vicidial_dnc_list;

Common list types:

FEDERAL_DNC: National Do Not Call Registry
INTERNAL_DNC: Your company's internal list
CAMPAIGN_DNC: Campaign-specific exclusions
MANUAL_DNC: Manually added numbers from customer requests

How ViciDial Checks DNC During Dialing

The dialing engine checks DNC status before attempting calls. This occurs in the vicidial dialer process and is controlled by campaign settings:

tail -f /var/log/asterisk/messages | grep "DNC"

When a lead is loaded, ViciDial's PHP process queries:

SELECT COUNT(*) FROM vicidial_dnc 
WHERE phone_number = '5551234567' 
AND status = 'ACTIVE' 
LIMIT 1;

If a match exists, the lead status changes to DNC and skips dialing.

Importing DNC Lists

Preparing Your DNC Data File

DNC lists come from various sources: government databases, third-party vendors, or internal customer request lists. Standardize the format before import.

Accepted input formats:

Simple CSV (one phone per line):

5551234567
5559876543
5553334444

CSV with metadata (recommended):

phone_number,status,list_id,comments
5551234567,ACTIVE,1,Federal Registry 2024-01
5559876543,ACTIVE,1,Federal Registry 2024-01
5553334444,ACTIVE,2,Internal - Customer Request

Tab-separated with area code normalization:

555	1234567	ACTIVE	1	Federal
555	9876543	ACTIVE	1	Federal

Data cleaning script - run before import to normalize:

<?php
// /home/vicidial/cleanup_dnc.php
$input_file = $argv[1];
$output_file = $argv[2];

$handle = fopen($input_file, 'r');
$output = fopen($output_file, 'w');

while (($line = fgets($handle)) !== false) {
    $phone = preg_replace('/[^0-9]/', '', trim($line));
    
    // Reject non-US/invalid lengths
    if (strlen($phone) != 10 && strlen($phone) != 11) {
        continue;
    }
    
    // Remove leading 1 if present (standardize to 10-digit)
    if (strlen($phone) == 11 && $phone[0] == '1') {
        $phone = substr($phone, 1);
    }
    
    fwrite($output, $phone . "\n");
}

fclose($handle);
fclose($output);
echo "Cleaned file: $output_file\n";
?>

Run the cleanup:

php /home/vicidial/cleanup_dnc.php raw_dnc_list.txt cleaned_dnc_list.txt
wc -l cleaned_dnc_list.txt

Method 1: ViciDial Admin Panel Import

Navigate to Admin → DNC Manager:

Click Import DNC List
Select List Type (FEDERAL_DNC, INTERNAL_DNC, etc.)
Upload your cleaned CSV file
Choose Append or Replace mode
Click Import

ViciDial will display import progress and any duplicate warnings.

Check import status via database:

SELECT list_id, COUNT(*) as count, DATE(date_added) as import_date 
FROM vicidial_dnc 
GROUP BY list_id, DATE(date_added);

Method 2: Direct MySQL Bulk Import

For large lists (1M+ records), use MySQL's LOAD DATA INFILE for speed:

LOAD DATA LOCAL INFILE '/tmp/cleaned_dnc_list.txt'
INTO TABLE vicidial_dnc
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(phone_number, status, list_id)
SET date_added = NOW(), user_id = 'ADMIN';

Important: Enable local file loading in MySQL:

mysql -u root -p -e "SET GLOBAL local_infile = 1;"

Add to /etc/mysql/mysql.cnf to persist:

[mysqld]
local_infile = 1

Verify the import:

SELECT COUNT(*) FROM vicidial_dnc WHERE date_added >= DATE_SUB(NOW(), INTERVAL 1 DAY);

Method 3: Automated Script with PHP CLI

Create a production-grade import script that handles duplicates and provides detailed logging:

<?php
// /usr/share/astguiclient/dnc_import.php
error_reporting(E_ALL);
ini_set('display_errors', 0);

$db_server = 'localhost';
$db_user = 'cron';
$db_pass = 'cron1234';
$db_name = 'asterisk';

$mysqli = new mysqli($db_server, $db_user, $db_pass, $db_name);
if ($mysqli->connect_error) {
    die("Database connection failed: " . $mysqli->connect_error);
}

if (empty($argv[1])) {
    die("Usage: php dnc_import.php <phone_list.txt> [list_id] [status]\n");
}

$file = $argv[1];
$list_id = isset($argv[2]) ? (int)$argv[2] : 1;  // Default to FEDERAL_DNC
$status = isset($argv[3]) ? $argv[3] : 'ACTIVE';

if (!file_exists($file)) {
    die("File not found: $file\n");
}

$handle = fopen($file, 'r');
$imported = 0;
$duplicates = 0;
$invalid = 0;
$start_time = time();

echo "[" . date('Y-m-d H:i:s') . "] Starting DNC import from: $file\n";
echo "List ID: $list_id | Status: $status\n";

while (($line = fgets($handle)) !== false) {
    $phone = preg_replace('/[^0-9]/', '', trim($line));
    
    // Validation
    if (strlen($phone) != 10 && strlen($phone) != 11) {
        $invalid++;
        continue;
    }
    
    if (strlen($phone) == 11 && $phone[0] != '1') {
        $invalid++;
        continue;
    }
    
    // Normalize to 10 digits
    $phone = strlen($phone) == 11 ? substr($phone, 1) : $phone;
    
    // Check for existing entry
    $check_query = "SELECT id FROM vicidial_dnc WHERE phone_number = ?";
    $stmt = $mysqli->prepare($check_query);
    $stmt->bind_param('s', $phone);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result->num_rows > 0) {
        $duplicates++;
        $stmt->close();
        continue;
    }
    $stmt->close();
    
    // Insert new DNC entry
    $insert_query = "INSERT INTO vicidial_dnc (phone_number, list_id, status, date_added) 
                     VALUES (?, ?, ?, NOW())";
    $stmt = $mysqli->prepare($insert_query);
    $stmt->bind_param('sis', $phone, $list_id, $status);
    
    if ($stmt->execute()) {
        $imported++;
    }
    $stmt->close();
    
    // Progress output every 10,000 records
    if (($imported + $duplicates + $invalid) % 10000 == 0) {
        echo "[" . date('Y-m-d H:i:s') . "] Processed: " . 
             ($imported + $duplicates + $invalid) . 
             " (Imported: $imported | Duplicates: $duplicates | Invalid: $invalid)\n";
    }
}

fclose($handle);
$elapsed = time() - $start_time;

echo "\n=== IMPORT COMPLETE ===\n";
echo "Imported: $imported\n";
echo "Duplicates (skipped): $duplicates\n";
echo "Invalid format: $invalid\n";
echo "Time elapsed: $elapsed seconds\n";
echo "Rate: " . round(($imported / $elapsed), 0) . " records/second\n";

$mysqli->close();
?>

Run the import:

php /usr/share/astguiclient/dnc_import.php /tmp/federal_dnc_2024.txt 1 ACTIVE

For federal DNC lists, check against the list_id in vicidial_dnc_list:

SELECT * FROM vicidial_dnc_list;

Enforcing DNC at Campaign Level

Campaign-Specific DNC Settings

Access the campaign configuration in the Admin Panel → Campaigns → [Your Campaign]:

Look for DNC Options:

Enforce DNC List: YES/NO
DNC List(s): Select which lists to check
Skip DNC Leads: Whether to skip or flag them
DNC Log: Track all DNC hits

Via database, check campaign DNC settings:

SELECT campaign_id, campaign_name, dnc_check_flag, dnc_action 
FROM vicidial_campaigns 
WHERE campaign_id = 'TESTCAMP';

Column meanings:

dnc_check_flag = 'Y' enables DNC checking
dnc_action = 'SKIP' or 'FLAG'

Update to enforce:

UPDATE vicidial_campaigns 
SET dnc_check_flag = 'Y', dnc_action = 'SKIP' 
WHERE campaign_id = 'TESTCAMP';

Verification in Real-Time

When a lead enters the dialer, ViciDial checks DNC before calling. Monitor this with:

tail -f /var/log/asterisk/messages | grep -i dnc

Or check the vicidial logs table:

SELECT lead_id, phone_number, status, call_date 
FROM vicidial_log 
WHERE status = 'DNC' 
AND call_date >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
LIMIT 20;

Manual DNC Management

Adding Individual Numbers

Via Admin Panel:

Admin → DNC Manager
Click Add Single Number
Enter 10-digit phone number
Select reason/list type
Click Add to DNC

Via MySQL:

INSERT INTO vicidial_dnc (phone_number, list_id, status, date_added, comments) 
VALUES ('5551234567', 2, 'ACTIVE', NOW(), 'Customer request - unsubscribe');

Via CLI script:

mysql -u cron -pcron1234 -e \
  "INSERT INTO asterisk.vicidial_dnc 
   (phone_number, list_id, status, date_added, comments) 
   VALUES ('5551234567', 2, 'ACTIVE', NOW(), 'Manual addition');"

Removing Numbers from DNC

To move a number back to active dialing (compliance-verified):

UPDATE vicidial_dnc 
SET status = 'INACTIVE', date_modified = NOW() 
WHERE phone_number = '5551234567';

Or delete entirely:

DELETE FROM vicidial_dnc 
WHERE phone_number = '5551234567' 
AND list_id = 2;

Important: Log all manual removals for compliance:

echo "[$(date)] Removed 5551234567 from DNC - Verified opt-in received" >> /var/log/vicidial_dnc_changes.log

Monitoring and Reporting

DNC Hit Rate Analysis

Check how many leads are being filtered:

SELECT 
    DATE(call_date) as date,
    campaign_id,
    COUNT(*) as total_calls,
    SUM(CASE WHEN status = 'DNC' THEN 1 ELSE 0 END) as dnc_hits,
    ROUND(100 * SUM(CASE WHEN status = 'DNC' THEN 1 ELSE 0 END) / COUNT(*), 2) as dnc_rate
FROM vicidial_log
WHERE call_date >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY DATE(call_date), campaign_id
ORDER BY date DESC;

Exporting DNC Lists for Compliance Audits

Generate a complete DNC list dump:

mysql -u cron -pcron1234 asterisk -e \
  "SELECT phone_number, status, list_id, date_added, comments 
   FROM vicidial_dnc 
   WHERE status = 'ACTIVE' 
   ORDER BY phone_number" \
  > /tmp/dnc_audit_$(date +%Y%m%d).csv

With headers:

(echo "phone_number,status,list_id,date_added,comments"; \
  mysql -u cron -pcron1234 -sN asterisk -e \
  "SELECT phone_number, status, list_id, date_added, comments 
   FROM vicidial_dnc WHERE status = 'ACTIVE' ORDER BY phone_number") \
  > /tmp/dnc_audit_$(date +%Y%m%d).csv

Verify file integrity:

wc -l /tmp/dnc_audit_*.csv
md5sum /tmp/dnc_audit_*.csv

Real-Time DNC Dashboard Query

Get a snapshot of current DNC statistics:

SELECT 
    dl.list_id,
    dl.list_name,
    COUNT(dn.phone_number) as active_dnc_numbers,
    MAX(dn.date_added) as last_added,
    DATEDIFF(NOW(), MIN(dn.date_added)) as days_since_oldest
FROM vicidial_dnc_list dl
LEFT JOIN vicidial_dnc dn ON dl.list_id = dn.list_id AND dn.status = 'ACTIVE'
GROUP BY dl.list_id, dl.list_name;

Automating DNC List Updates

Scheduled Federal DNC Sync

Major platforms (TCPA-compliant operations) should sync with the National Do Not Call Registry monthly. Create an automated script:

#!/bin/bash
# /usr/local/bin/federal_dnc_sync.sh

LOG_FILE="/var/log/vicidial_dnc_sync.log"
DNC_LIST_DIR="/home/vicidial/dnc_lists"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

{
    echo "[$(date)] ========== FEDERAL DNC SYNC START =========="
    
    # Download latest DNC list (example using vendor API)
    curl -s -u "API_KEY:API_SECRET" \
        https://dnc-vendor.com/api/export.csv \
        -o "$DNC_LIST_DIR/federal_dnc_$TIMESTAMP.csv" 2>&1
    
    if [ ! -f "$DNC_LIST_DIR/federal_dnc_$TIMESTAMP.csv" ]; then
        echo "ERROR: Failed to download DNC list"
        exit 1
    fi
    
    echo "[$(date)] Downloaded file: federal_dnc_$TIMESTAMP.csv"
    
    # Clean and import
    php /usr/share/astguiclient/dnc_import.php \
        "$DNC_LIST_DIR/federal_dnc_$TIMESTAMP.csv" 1 ACTIVE 2>&1
    
    # Archive old files
    find "$DNC_LIST_DIR" -name "federal_dnc_*.csv" -mtime +30 -delete
    
    echo "[$(date)] ========== SYNC COMPLETE =========="
} >> $LOG_FILE 2>&1

Make executable and add to crontab:

chmod +x /usr/local/bin/federal_dnc_sync.sh

# Add to root crontab (first of each month at 2 AM)
crontab -e
# 0 2 1 * * /usr/local/bin/federal_dnc_sync.sh

Hourly DNC Health Check

Monitor DNC table integrity:

#!/bin/bash
# /usr/local/bin/dnc_health_check.sh

mysql -u cron -pcron1234 asterisk << 'EOF'
-- Check for data consistency
SELECT 
    'Total DNC Records' as check_type,
    COUNT(*) as count
FROM vicidial_dnc
WHERE status = 'ACTIVE'

UNION ALL

SELECT 
    'Invalid Phone Numbers (not 10 digits)',
    COUNT(*)
FROM vicidial_dnc
WHERE status = 'ACTIVE'
AND (LENGTH(phone_number) != 10 OR phone_number NOT REGEXP '^[0-9]{10}$')

UNION ALL

SELECT 
    'Orphaned Records (invalid list_id)',
    COUNT(*)
FROM vicidial_dnc dn
LEFT JOIN vicidial_dnc_list dl ON dn.list_id = dl.list_id
WHERE dn.status = 'ACTIVE'
AND dl.list_id IS NULL;
EOF

Run hourly:

0 * * * * /usr/local/bin/dnc_health_check.sh | mail -s "ViciDial DNC Health Check" admin@company.com

Troubleshooting

Issue: Calls Still Dialing to DNC Numbers

Symptom: Leads marked DNC still receiving calls.

Diagnosis:

Verify DNC enforcement is enabled:

SELECT dnc_check_flag, dnc_action FROM vicidial_campaigns 
WHERE campaign_id = 'TESTCAMP';

Check if number exists in DNC table:

SELECT phone_number, status FROM vicidial_dnc 
WHERE phone_number = '5551234567';

Confirm lead status in vicidial_list:

SELECT lead_id, phone_number, status FROM vicidial_list 
WHERE phone_number = '5551234567';

Check dialer logs:

tail -100 /var/log/asterisk/messages | grep -A5 -B5 "5551234567"

Solution:

Manually set lead status to DNC:

UPDATE vicidial_list SET status = 'DNC' 
WHERE phone_number = '5551234567' 
AND campaign_id = 'TESTCAMP';

Restart the dialer:

systemctl restart vicidial

Issue: Import Fails with Large Files

Symptom: "Memory limit exceeded" or import hangs.

Solution: Process in chunks:

# Split large file into 100k-line chunks
split -l 100000 large_dnc_list.txt dnc_chunk_

# Process each chunk
for file in dnc_chunk_*; do
    php /usr/share/astguiclient/dnc_import.php "$file" 1 ACTIVE
    rm "$file"
done

Increase PHP memory for CLI:

php -d memory_limit=1024M /usr/share/astguiclient/dnc_import.php file.txt 1 ACTIVE

Issue: DNC Table Performance Degradation

Symptom: Slow lead loading; dialer constantly checking DNC.

Solution: Add index to DNC table:

ANALYZE TABLE vicidial_dnc;
SHOW INDEX FROM vicidial_dnc;

-- If missing, add:
ALTER TABLE vicidial_dnc ADD INDEX idx_phone_number (phone_number);
ALTER TABLE vicidial_dnc ADD INDEX idx_status (status);
ALTER TABLE vicidial_dnc ADD INDEX idx_list_id (list_id);

Check query performance:

EXPLAIN SELECT COUNT(*) FROM vicidial_dnc 
WHERE phone_number = '5551234567' AND status = 'ACTIVE';

Should show "Using index" in the Extra column.

Issue: Duplicate Phone Numbers in DNC

Symptom: Same number appears multiple times with different list_ids.

Check:

SELECT phone_number, COUNT(*) 
FROM vicidial_dnc 
GROUP BY phone_number 
HAVING COUNT(*) > 1;

Fix: Consolidate to primary list:

INSERT IGNORE INTO vicidial_dnc 
SELECT phone_number, 1 as list_id, status, MIN(date_added), user_id, comments 
FROM vicidial_dnc 
WHERE status = 'ACTIVE'
GROUP BY phone_number;

DELETE FROM vicidial_dnc WHERE list_id != 1;

Issue: Web Admin Panel DNC Import Hangs

Diagnosis:

ps aux | grep php
tail -f /var/log/apache2/error.log

Common cause: Timeout. Edit /etc/php.ini:

max_execution_time = 300
max_input_time = 300
memory_limit = 512M
upload_max_filesize = 256M
post_max_size = 256M

Restart Apache:

systemctl restart apache2

Use CLI import instead for large files.

Summary

ViciDial's DNC list management is critical for TCPA compliance and call list quality. Key takeaways:

Architecture:

DNC data stored in vicidial_dnc table with list categorization via vicidial_dnc_list
Checks happen during dialing phase; enforcement configured per campaign
Phone numbers normalized to 10-digit format for consistency

Import Methods:

Admin Panel — GUI, suitable for small lists
LOAD DATA INFILE — Fastest for 1M+ records
PHP CLI Script — Best for production automation with detailed logging

Best Practices:

Always clean and normalize phone data before import
Maintain separate list_ids for different DNC sources (federal, internal, campaign-specific)
Verify enforcement is enabled at campaign level
Monitor DNC hit rates to identify data quality issues
Export and audit lists monthly for compliance
Automate federal DNC syncs using APIs
Add database indexes to prevent performance degradation
Log all manual DNC modifications

Monitoring:

Query vicidial_log for DNC hits and rate analysis
Run periodic health checks on DNC table integrity
Set up alerts for import failures
Export audit reports for regulatory requirements

Automation:

Schedule monthly federal DNC updates
Automate customer opt-out sync from CRM
Health checks to catch orphaned or invalid records
Archive old import files

With proper DNC management, you maintain regulatory compliance, improve list quality, and protect your company's reputation. Use the provided scripts as templates and adapt to your specific compliance and operational requirements.