ViciDial Server Hardening & MariaDB Optimization Guide

A battle-tested playbook for securing and tuning production ViciDial servers

Table of Contents

1. Introduction: Why ViciDial Servers Are Prime Targets
2. Pre-Hardening Checklist
3. SSH Hardening
4. Firewall Configuration (UFW / iptables)
5. Fail2ban: Multi-Jail Brute-Force Protection
6. MySQL/MariaDB User Lockdown
7. Apache & Recording Access Control
8. Disabling Unnecessary Services
9. MariaDB Performance Optimization
10. Strategic Index Creation
11. Daily Table Archiving
12. Automated Backup System
13. Recording Cleanup Policies
14. Log Management & Rotation
15. Binary Log Management
16. Post-Hardening Verification Checklist
17. Ongoing Maintenance Schedule
18. Appendix: Quick Reference Commands

1. Introduction

ViciDial servers are attractive targets for attackers for several reasons:

• SIP exposure: Port 5060 is constantly scanned by botnets seeking to make fraudulent calls through compromised PBXes. A single weekend of toll fraud can rack up thousands of dollars in charges.
• Web interfaces: The agent (/agc/) and admin (/vicidial/) panels are publicly accessible over HTTP, making them susceptible to credential brute-forcing.
• MySQL exposure: ViciDial often runs with MySQL listening on all interfaces (port 3306) for multi-server clusters, which invites unauthorized access attempts.
• Long uptimes: ViciDial servers tend to run for months or years without rebooting, accumulating unpatched vulnerabilities.
• Valuable data: Call recordings, customer phone numbers, and agent credentials are all worth money on the dark web.

This guide documents the exact hardening and optimization procedures applied across multiple production ViciDial servers handling thousands of calls daily. Every command was tested in production. Every recommendation comes from real incidents: toll fraud attempts, runaway queries that locked the database, 9.5 GB log files consuming disk space, and a SIP capture daemon silently inserting 6.5 rows per second into a table that grew to 3.5 GB.

What this guide covers:

Prerequisites:

• Root SSH access to your ViciDial server
• ViciBox (openSUSE) or CentOS-based ViciDial installation
• MariaDB 10.5+ recommended (10.6+ for all features)
• Basic familiarity with Linux, MySQL, and Asterisk

Testing recommendation: If you have a replica or staging server, apply all changes there first and monitor for 24 hours before touching production.

2. Pre-Hardening Checklist

Before making any changes, document the current state of your server. You will thank yourself later.

Capture Current State

# Record the date and hostname
echo "=== Hardening started $(date) on $(hostname) ==="

# System info
uname -a
cat /etc/os-release
uptime

# Current firewall rules (save a copy)
iptables -L -n --line-numbers > /root/iptables-before-hardening.txt
iptables-save > /root/iptables-save-before-hardening.txt

# Current fail2ban status (if installed)
fail2ban-client status 2>/dev/null || echo "fail2ban not installed"

# Current SSH config
grep -E "^(Port|PermitRootLogin|PasswordAuthentication|MaxAuthTries)" /etc/ssh/sshd_config

# MySQL users and privileges
mysql -e "SELECT user, host, max_statement_time FROM mysql.user;"

# Current MariaDB variables (save for comparison)
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -e "SHOW VARIABLES LIKE 'max_statement_time';"
mysql -e "SHOW VARIABLES LIKE 'query_cache%';"
mysql -e "SHOW VARIABLES LIKE 'slow_query_log%';"
mysql -e "SHOW VARIABLES LIKE 'expire_logs_days';"

# Disk usage
df -h
du -sh /var/spool/asterisk/monitorDONE/ 2>/dev/null
du -sh /var/log/asterisk/ 2>/dev/null

# Active Asterisk channels (confirm no live calls before risky changes)
asterisk -rx "core show channels count"

# List running services
systemctl list-units --type=service --state=running

Create a Rollback Backup

# Backup critical configs before any changes
BACKUP_DIR="/root/pre-hardening-backup-$(date +%Y%m%d)"
mkdir -p "$BACKUP_DIR"

cp /etc/ssh/sshd_config "$BACKUP_DIR/"
cp -r /etc/fail2ban/ "$BACKUP_DIR/" 2>/dev/null
cp -r /etc/asterisk/ "$BACKUP_DIR/"
cp /etc/my.cnf "$BACKUP_DIR/" 2>/dev/null
cp /etc/my.cnf.d/*.cnf "$BACKUP_DIR/" 2>/dev/null
cp /etc/mysql/mariadb.conf.d/*.cnf "$BACKUP_DIR/" 2>/dev/null

mysqldump --no-data asterisk > "$BACKUP_DIR/schema-only.sql"
echo "Rollback backup saved to $BACKUP_DIR"

3. SSH Hardening

SSH is the most commonly attacked service on any internet-facing server. Moving it off port 22 eliminates 99% of automated scans.

Change SSH Port

# Edit SSH config
cp /etc/ssh/sshd_config /etc/ssh/sshd_config.bak.$(date +%Y%m%d)

# Change port (pick a non-standard port above 1024)
sed -i 's/^#Port 22/Port 9322/' /etc/ssh/sshd_config
# If Port 22 is already uncommented:
sed -i 's/^Port 22/Port 9322/' /etc/ssh/sshd_config

Harden SSH Settings

Add or modify these settings in /etc/ssh/sshd_config:

Port 9322
PermitRootLogin prohibit-password    # Key-only root login
PasswordAuthentication no            # Disable password auth entirely
MaxAuthTries 3                       # Lock out after 3 failed attempts
LoginGraceTime 30                    # 30 seconds to authenticate
ClientAliveInterval 300              # Disconnect idle sessions after 5 min
ClientAliveCountMax 2                # 2 missed keepalives = disconnect
X11Forwarding no                     # Disable X11 forwarding
AllowTcpForwarding no                # Disable TCP forwarding

Open the New Port Before Restarting SSH

This is critical. If you restart SSH without opening the new port in the firewall, you will lock yourself out.

# For iptables (ViciBox/openSUSE):
iptables -I INPUT 1 -p tcp --dport 9322 -j ACCEPT
iptables-save > /etc/sysconfig/iptables  # Persist on openSUSE
# or
iptables-save > /etc/iptables.rules      # Persist on Debian/Ubuntu

# For UFW (Debian/Ubuntu):
ufw allow 9322/tcp

# Now restart SSH
systemctl restart sshd

# TEST: Open a NEW terminal and connect on port 9322 BEFORE closing this session
# ssh -p 9322 root@YOUR_SERVER_IP

Warning: Never close your current SSH session until you have confirmed you can connect on the new port from a separate terminal.

4. Firewall Configuration

ViciDial servers need specific ports open. Everything else should be blocked.

Required Ports for ViciDial

UFW Configuration (Debian/Ubuntu)

# Reset to defaults
ufw default deny incoming
ufw default allow outgoing

# SSH (non-standard port)
ufw allow 9322/tcp comment 'SSH'

# Web (agent + admin)
ufw allow 80/tcp comment 'Apache HTTP'
ufw allow 443/tcp comment 'Apache HTTPS'

# SIP - restrict to your providers only
ufw allow from <SIP_PROVIDER_1_IP> to any port 5060 proto udp comment 'SIP Provider 1'
ufw allow from <SIP_PROVIDER_2_IP> to any port 5060 proto udp comment 'SIP Provider 2'
ufw allow from <SIP_PROVIDER_3_IP> to any port 5060 proto udp comment 'SIP Provider 3'
# Add all your SIP provider IPs

# RTP media (must be open for audio)
ufw allow 10000:20000/udp comment 'RTP Media'

# MySQL - replica and cluster servers ONLY
ufw allow from <REPLICA_SERVER_IP> to any port 3306 proto tcp comment 'MySQL Replica'
# ufw allow from <CLUSTER_NODE_IP> to any port 3306 proto tcp comment 'MySQL Cluster'

# Monitoring agents (if using Prometheus/Grafana)
ufw allow from <MONITORING_SERVER_IP> to any port 9100 proto tcp comment 'node_exporter'
ufw allow from <MONITORING_SERVER_IP> to any port 9101 proto tcp comment 'asterisk_exporter'

# Enable the firewall
ufw enable
ufw status verbose

iptables Configuration (ViciBox/openSUSE)

For ViciBox servers that use iptables natively:

# Flush existing rules (CAREFUL - have console access or do this via script)
# Better approach: insert rules at the top of the chain

# Allow established connections (must be first)
iptables -I INPUT 1 -m state --state ESTABLISHED,RELATED -j ACCEPT

# Allow loopback
iptables -I INPUT 2 -i lo -j ACCEPT

# SSH
iptables -A INPUT -p tcp --dport 9322 -j ACCEPT

# HTTP/HTTPS
iptables -A INPUT -p tcp --dport 80 -j ACCEPT
iptables -A INPUT -p tcp --dport 443 -j ACCEPT

# SIP - provider IPs only
iptables -A INPUT -p udp --dport 5060 -s <SIP_PROVIDER_1_IP> -j ACCEPT
iptables -A INPUT -p udp --dport 5060 -s <SIP_PROVIDER_2_IP> -j ACCEPT
iptables -A INPUT -p udp --dport 5060 -s <SIP_PROVIDER_3_IP> -j ACCEPT

# RTP
iptables -A INPUT -p udp --dport 10000:20000 -j ACCEPT

# MySQL - restricted
iptables -A INPUT -p tcp --dport 3306 -s <REPLICA_SERVER_IP> -j ACCEPT

# Monitoring
iptables -A INPUT -p tcp --dport 9100 -s <MONITORING_SERVER_IP> -j ACCEPT
iptables -A INPUT -p tcp --dport 9101 -s <MONITORING_SERVER_IP> -j ACCEPT

# Default deny at the end
iptables -A INPUT -j DROP

# Persist (openSUSE)
iptables-save > /etc/sysconfig/iptables

Auditing Existing Firewall Rules

One critical lesson learned: check for overly permissive rules that negate everything below them. An ACCEPT ALL rule early in the chain makes every subsequent rule meaningless.

# Look for dangerous rules
iptables -L INPUT -n --line-numbers | grep "ACCEPT.*0.0.0.0/0.*0.0.0.0/0"

# If you find one (e.g., at line 44), remove it:
# iptables -D INPUT 44
# WARNING: Verify this won't break anything first. List what's after it.

5. Fail2ban: Multi-Jail Brute-Force Protection

Fail2ban monitors log files and bans IPs that show malicious behavior. For ViciDial servers, you need five jails covering SSH, Asterisk/SIP, Apache, and repeat offenders.

Installation

# openSUSE (ViciBox)
zypper install fail2ban python3-pyinotify

# Debian/Ubuntu
apt install fail2ban

# CentOS/RHEL
yum install fail2ban fail2ban-systemd

Jail Configuration

Create /etc/fail2ban/jail.local (this overrides jail.conf and survives upgrades):

[DEFAULT]
# Whitelist your management IPs and office IPs (NEVER ban yourself)
ignoreip = 127.0.0.1/8 ::1 <YOUR_OFFICE_IP_1> <YOUR_OFFICE_IP_2> <YOUR_VPN_SUBNET>/24
banaction = iptables-multiport
backend = pyinotify
findtime = 600
maxretry = 5

# ============================================================
# JAIL 1: SSH (Standard)
# Bans IPs with 3 failed password attempts in 10 minutes
# ============================================================
[sshd]
enabled  = true
port     = 9322
filter   = sshd
logpath  = /var/log/auth.log
# On openSUSE/ViciBox use: logpath = /var/log/messages
maxretry = 3
bantime  = 3600
findtime = 600

# ============================================================
# JAIL 2: SSH Aggressive (Invalid Users)
# Catches scanners trying non-existent usernames
# Longer ban because these are always malicious
# ============================================================
[sshd-aggressive]
enabled  = true
port     = 9322
filter   = sshd[mode=aggressive]
logpath  = /var/log/auth.log
# On openSUSE/ViciBox use: logpath = /var/log/messages
maxretry = 1
bantime  = 86400
findtime = 3600

# ============================================================
# JAIL 3: Asterisk (SIP Brute Force)
# Catches SIP registration attacks and toll fraud attempts
# 24-hour ban because SIP scanners are persistent
# ============================================================
[asterisk]
enabled  = true
port     = 5060,5061
protocol = udp
filter   = asterisk
logpath  = /var/log/asterisk/messages
maxretry = 3
bantime  = 86400
findtime = 600

# ============================================================
# JAIL 4: Apache Auth
# Catches brute-force attempts against web interfaces
# ============================================================
[apache-auth]
enabled  = true
port     = http,https
filter   = apache-auth
logpath  = /var/log/apache2/error.log
# On openSUSE/ViciBox: logpath = /var/log/apache2/error_log
# On CentOS: logpath = /var/log/httpd/error_log
maxretry = 5
bantime  = 3600
findtime = 600

# ============================================================
# JAIL 5: Recidive (Repeat Offenders)
# If an IP gets banned 3 times across ANY jail, ban for 7 days
# This catches persistent attackers who rotate techniques
# ============================================================
[recidive]
enabled  = true
filter   = recidive
logpath  = /var/log/fail2ban.log
banaction = iptables-allports
maxretry = 3
bantime  = 604800
findtime = 86400

Custom Asterisk Filter (if not present)

Check if your fail2ban has an Asterisk filter. If /etc/fail2ban/filter.d/asterisk.conf does not exist or is outdated, create it:

# /etc/fail2ban/filter.d/asterisk.conf
[INCLUDES]
before = common.conf

[Definition]
_daemon = asterisk

failregex = NOTICE.* .*: Registration from '.*' failed for '<HOST>(:\d+)?' - Wrong password
            NOTICE.* .*: Registration from '.*' failed for '<HOST>(:\d+)?' - No matching peer found
            NOTICE.* .*: Registration from '.*' failed for '<HOST>(:\d+)?' - Username/auth name mismatch
            NOTICE.* .*: Registration from '.*' failed for '<HOST>(:\d+)?' - Device does not match ACL
            NOTICE.* <HOST> failed to authenticate as '.*'
            NOTICE.* .*: No registration for peer '.*' \(from <HOST>\)
            NOTICE.* .*: Host <HOST> failed MD5 authentication for '.*'
            SECURITY.* .*: SecurityEvent="InvalidAccountID".*RemoteAddress="IPV4/UDP/<HOST>/.*"
            SECURITY.* .*: SecurityEvent="InvalidPassword".*RemoteAddress="IPV4/UDP/<HOST>/.*"
            SECURITY.* .*: SecurityEvent="ChallengeResponseFailed".*RemoteAddress="IPV4/UDP/<HOST>/.*"

ignoreregex =

Start and Verify

# Enable and start
systemctl enable fail2ban
systemctl start fail2ban

# Verify all 5 jails are active
fail2ban-client status

# Expected output:
# Status
# |- Number of jail:    5
# `- Jail list:  apache-auth, asterisk, recidive, sshd, sshd-aggressive

# Check a specific jail
fail2ban-client status sshd

# Check that pyinotify backend is working (faster than polling)
fail2ban-client get sshd backend

# Test the Asterisk filter against your logs
fail2ban-regex /var/log/asterisk/messages /etc/fail2ban/filter.d/asterisk.conf

Managing Bans

# View all banned IPs across all jails
fail2ban-client status sshd
fail2ban-client status asterisk
fail2ban-client status recidive

# Manually unban an IP (if you accidentally banned yourself or a legitimate user)
fail2ban-client set sshd unbanip <IP_ADDRESS>
fail2ban-client set asterisk unbanip <IP_ADDRESS>

# Manually ban an IP
fail2ban-client set asterisk banip <ATTACKER_IP>

# Check fail2ban logs for ban activity
tail -50 /var/log/fail2ban.log

6. MySQL/MariaDB User Lockdown

ViciDial often ships with overly permissive MySQL users. Lock them down.

Principle of Least Privilege

Create separate users for different purposes:

-- Connect to MySQL as root
mysql

-- 1. REPORTING USER: Read-only, with query timeout
--    For dashboards, CRM integrations, external reporting tools
CREATE USER 'report_user'@'<REPORTING_SERVER_IP>'
  IDENTIFIED BY '<STRONG_RANDOM_PASSWORD>';

GRANT SELECT ON asterisk.vicidial_closer_log TO 'report_user'@'<REPORTING_SERVER_IP>';
GRANT SELECT ON asterisk.vicidial_log TO 'report_user'@'<REPORTING_SERVER_IP>';
GRANT SELECT ON asterisk.vicidial_agent_log TO 'report_user'@'<REPORTING_SERVER_IP>';
GRANT SELECT ON asterisk.vicidial_users TO 'report_user'@'<REPORTING_SERVER_IP>';
GRANT SELECT ON asterisk.recording_log TO 'report_user'@'<REPORTING_SERVER_IP>';
GRANT SELECT ON asterisk.call_log TO 'report_user'@'<REPORTING_SERVER_IP>';

-- Set a 5-second query timeout to prevent long queries from impacting production
ALTER USER 'report_user'@'<REPORTING_SERVER_IP>' SET max_statement_time = 5;

FLUSH PRIVILEGES;

-- 2. GRAFANA / MONITORING USER: Read-only, specific tables
CREATE USER 'grafana_ro'@'<MONITORING_SERVER_IP>'
  IDENTIFIED BY '<STRONG_RANDOM_PASSWORD>';

GRANT SELECT ON asterisk.* TO 'grafana_ro'@'<MONITORING_SERVER_IP>';
ALTER USER 'grafana_ro'@'<MONITORING_SERVER_IP>' SET max_statement_time = 10;

FLUSH PRIVILEGES;

-- 3. REPLICATION USER: Only replication privileges
CREATE USER 'repl_user'@'<REPLICA_SERVER_IP>'
  IDENTIFIED BY '<STRONG_RANDOM_PASSWORD>';

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'<REPLICA_SERVER_IP>';

FLUSH PRIVILEGES;

-- 4. AUDIT existing users and remove unnecessary privileges
SELECT user, host, max_statement_time FROM mysql.user;
SHOW GRANTS FOR 'cron'@'localhost';
-- Revoke ALL PRIVILEGES if a user only needs SELECT
-- REVOKE ALL PRIVILEGES ON *.* FROM 'some_user'@'some_host';
-- GRANT SELECT ON asterisk.* TO 'some_user'@'some_host';

Restrict the ViciDial Application User

ViciDial's cron user typically has full privileges. While you cannot restrict it without breaking ViciDial, you can add a query timeout:

-- Prevent runaway ViciDial queries from locking the database
-- This does NOT affect the cron user, only ad-hoc queries.
-- The global max_statement_time (Section 9) handles this.

Remove Test/Default Users

-- Check for default or test accounts
SELECT user, host FROM mysql.user WHERE user IN ('', 'test', 'admin');

-- Remove any found
DROP USER ''@'localhost';
DROP USER ''@'%';
DROP USER 'test'@'%';

FLUSH PRIVILEGES;

7. Apache & Recording Access Control

ViciDial stores call recordings as audio files served by Apache. Without access control, anyone who guesses a recording URL can download it.

IP-Based Recording Access Control

Create or edit the Apache configuration for the recordings directory:

# /etc/apache2/conf.d/recording-access.conf
# (On CentOS: /etc/httpd/conf.d/recording-access.conf)

<Directory "/var/spool/asterisk/monitorDONE">
    Options -Indexes
    AllowOverride None

    # Allow access only from whitelisted IPs
    <RequireAny>
        # Your office/management IPs
        Require ip <YOUR_OFFICE_IP>
        Require ip <YOUR_VPN_SUBNET>/24
        Require ip <YOUR_MONITORING_IP>

        # Localhost (for ViciDial's own recording playback)
        Require ip 127.0.0.1
        Require ip ::1
    </RequireAny>
</Directory>

# Alternative: Redirect non-whitelisted IPs to an HTML5 player
# that requires authentication
<Directory "/var/spool/asterisk/monitorDONE">
    RewriteEngine On
    RewriteCond %{REMOTE_ADDR} !^(127\.0\.0\.1|<ESCAPED_OFFICE_IP>|<ESCAPED_VPN_IP>)
    RewriteRule ^(.*)$ /recording-player.php?file=$1 [L,R=302]
</Directory>

# Test config and reload
apachectl configtest
systemctl reload apache2
# On CentOS: systemctl reload httpd

Disable Directory Listing Globally

# In your main Apache config or virtualhost
<Directory "/srv/www/htdocs">
    Options -Indexes
</Directory>

8. Disabling Unnecessary Services

SIP Capture Daemon

ViciDial includes a SIP capture daemon that records every SIP packet into the sip_packets table. On a busy server, this can insert 5-10 rows per second, growing the table by hundreds of megabytes per day.

# Check if sip_capture_daemon is running
systemctl status sip_capture_daemon 2>/dev/null
ps aux | grep sip_capture

# Check how large the sip_packets table has grown
mysql -e "SELECT COUNT(*) AS rows, ROUND(data_length/1024/1024, 1) AS data_mb
  FROM information_schema.tables
  WHERE table_name = 'sip_packets' AND table_schema = 'asterisk';"

If the table is large and you do not need SIP packet capture (you have Homer or another tool for that):

# Stop and disable the service
systemctl stop sip_capture_daemon
systemctl disable sip_capture_daemon

# Truncate the table to reclaim space
mysql -e "TRUNCATE TABLE asterisk.sip_packets;"

# Verify space was freed
mysql -e "SELECT ROUND(data_length/1024/1024, 1) AS data_mb
  FROM information_schema.tables
  WHERE table_name = 'sip_packets' AND table_schema = 'asterisk';"

Disable SIP Capture in ViciDial Settings

In the ViciDial admin panel: Admin > System Settings > SIP Capture -- set to DISABLED.

Or via SQL:

UPDATE system_settings SET sip_event_logging = '0' WHERE db_schema_version > 0;

Other Services to Review

# List all enabled services
systemctl list-unit-files --state=enabled

# Services you might want to disable on a ViciDial server:
# - cups (print server) -- not needed
# - avahi-daemon (mDNS) -- not needed
# - bluetooth -- not needed on a server
# - postfix -- only if you don't need email notifications

# Example: disable cups
systemctl stop cups
systemctl disable cups

9. MariaDB Performance Optimization

These settings are specifically tuned for ViciDial's write-heavy workload (continuous INSERT/UPDATE from live calls).

Global Query Timeout

This is the single most important setting. Without it, a single bad query (from a report, a CRM integration, or a broken PHP page) can lock tables and crash your entire phone system.

-- Set immediately (takes effect for new connections)
SET GLOBAL max_statement_time = 300;

-- Verify
SHOW GLOBAL VARIABLES LIKE 'max_statement_time';

Make it permanent in the MariaDB config:

# /etc/my.cnf.d/vicidial-tuning.cnf
# (On Debian: /etc/mysql/mariadb.conf.d/60-vicidial-tuning.cnf)

[mysqld]
# Kill any query running longer than 5 minutes
# Prevents runaway JOINs from report pages from locking tables
max_statement_time = 300

InnoDB Buffer Pool

The InnoDB buffer pool is where MariaDB caches table data and indexes in RAM. Larger = faster reads. Rule of thumb: set it to 50-70% of available RAM on a dedicated database server.

-- Check current size
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Check how much is currently used
SHOW STATUS LIKE 'Innodb_buffer_pool_pages%';

Calculate the optimal size:

# Check total RAM
free -g

# Example: 62 GB RAM server
# ViciDial + Asterisk use ~2-4 GB, OS needs ~2 GB
# Safe buffer pool: 50% = 31 GB, Conservative: 4-8 GB

Set in config:

# /etc/my.cnf.d/vicidial-tuning.cnf

[mysqld]
# Adjust based on your server's RAM
# 62 GB RAM server: 4-8 GB is safe with Asterisk running alongside
# Dedicated DB server: up to 50-70% of RAM
innodb_buffer_pool_size = 4G

# Buffer pool instances (1 per GB, max 8)
innodb_buffer_pool_instances = 4

Disable Query Cache

The query cache sounds helpful but actually hurts write-heavy workloads. Every INSERT/UPDATE/DELETE invalidates cached queries for that table. ViciDial constantly writes to vicidial_live_agents, vicidial_auto_calls, and other hot tables, so the cache spends more time invalidating than serving.

-- Check current state
SHOW VARIABLES LIKE 'query_cache%';
SHOW STATUS LIKE 'Qcache%';

-- Disable if enabled
SET GLOBAL query_cache_type = OFF;
SET GLOBAL query_cache_size = 0;

Make permanent:

# /etc/my.cnf.d/vicidial-tuning.cnf

[mysqld]
query_cache_type = OFF
query_cache_size = 0

Enable Slow Query Log

Find the queries that are hurting performance:

# /etc/my.cnf.d/vicidial-tuning.cnf

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = ON

# On openSUSE/ViciBox, the path might be:
# slow_query_log_file = /var/lib/mysql/slow-query.log

# Create the log directory if needed
mkdir -p /var/log/mysql
chown mysql:mysql /var/log/mysql

# Restart MariaDB
systemctl restart mariadb
# or: systemctl restart mysql

# After a day, analyze slow queries
mysqldumpslow -t 20 /var/log/mysql/slow-query.log

Additional InnoDB Tuning

# /etc/my.cnf.d/vicidial-tuning.cnf

[mysqld]
# === Query Timeout ===
max_statement_time = 300

# === InnoDB Engine ===
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
# ^ Value 2: flush to OS cache every commit, sync to disk every second
#   Faster than 1, minimal data loss risk (up to 1 second on crash)
#   Acceptable for VoIP call data

innodb_flush_method = O_DIRECT
# ^ Bypass OS file cache (InnoDB has its own buffer pool)

innodb_file_per_table = ON
# ^ Each table gets its own .ibd file. Allows reclaiming space with OPTIMIZE TABLE

# === Query Cache (disabled for write-heavy workload) ===
query_cache_type = OFF
query_cache_size = 0

# === Connection Handling ===
max_connections = 300
# ^ ViciDial uses many connections (each screen process, each cron, agents)
wait_timeout = 600
interactive_timeout = 600

# === Slow Query Log ===
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = ON

# === Temporary Tables ===
tmp_table_size = 64M
max_heap_table_size = 64M

Apply Changes

# Test the config file for syntax errors
mariadbd --verbose --help 2>&1 | head -5

# Restart MariaDB (will briefly disconnect agents -- plan for low-traffic window)
systemctl restart mariadb

# Verify settings took effect
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -e "SHOW VARIABLES LIKE 'max_statement_time';"
mysql -e "SHOW VARIABLES LIKE 'query_cache_type';"

10. Strategic Index Creation

ViciDial's default schema is missing indexes on several columns that are frequently queried by reports and the admin interface. Adding the right indexes can turn a 30-second report query into a sub-second one.

The Three Essential Missing Indexes

These indexes were identified by analyzing slow query logs and EXPLAIN plans on production servers:

Index 1: vicidial_agent_log.uniqueid

ViciDial joins vicidial_agent_log with other tables on uniqueid, but this column has no index by default.

-- Check if index already exists
SHOW INDEX FROM vicidial_agent_log WHERE Column_name = 'uniqueid';

-- BEFORE: See how queries perform without the index
EXPLAIN SELECT * FROM vicidial_agent_log
  WHERE uniqueid = '1707500000.12345'\G
-- You'll see: type=ALL, rows=<millions> (full table scan)

-- Create the index
ALTER TABLE vicidial_agent_log ADD INDEX idx_uniqueid (uniqueid);

-- AFTER: Same query with the index
EXPLAIN SELECT * FROM vicidial_agent_log
  WHERE uniqueid = '1707500000.12345'\G
-- You'll see: type=ref, rows=1 (index lookup)

Index 2: call_log composite index

The call_log table is queried by channel group and start time for inbound call reports. A composite index on both columns dramatically speeds up date-range queries filtered by call type.

-- Check current indexes
SHOW INDEX FROM call_log WHERE Column_name IN ('channel_group', 'start_time');

-- BEFORE
EXPLAIN SELECT * FROM call_log
  WHERE channel_group = 'DID_INBOUND'
  AND start_time >= '2026-03-01'
  AND start_time < '2026-03-02'\G
-- type=ALL or type=index, rows=<hundreds of thousands>

-- Create composite index
ALTER TABLE call_log ADD INDEX idx_changrp_starttime (channel_group, start_time);

-- AFTER
EXPLAIN SELECT * FROM call_log
  WHERE channel_group = 'DID_INBOUND'
  AND start_time >= '2026-03-01'
  AND start_time < '2026-03-02'\G
-- type=range, rows=<hundreds> -- orders of magnitude fewer rows scanned

Index 3: vicidial_closer_log composite index

The closer log is ViciDial's inbound call record table. Reports constantly query it by campaign, date, and status.

-- Check current indexes
SHOW INDEX FROM vicidial_closer_log WHERE Column_name IN ('campaign_id', 'call_date', 'status');

-- BEFORE
EXPLAIN SELECT status, COUNT(*) FROM vicidial_closer_log
  WHERE campaign_id = 'ukcapm'
  AND call_date >= '2026-03-01'
  AND call_date < '2026-03-02'
  GROUP BY status\G
-- type=ALL, rows=<millions>

-- Create composite index (campaign first, then date, then status for covering)
ALTER TABLE vicidial_closer_log
  ADD INDEX idx_campaign_calldate_status (campaign_id, call_date, status);

-- AFTER
EXPLAIN SELECT status, COUNT(*) FROM vicidial_closer_log
  WHERE campaign_id = 'ukcapm'
  AND call_date >= '2026-03-01'
  AND call_date < '2026-03-02'
  GROUP BY status\G
-- type=range, rows=<few thousand>, Extra: Using index (covering index!)

Understanding EXPLAIN Output

Key columns to look at in EXPLAIN output:

Index Maintenance

-- Check index sizes (large indexes slow down writes)
SELECT
  table_name,
  index_name,
  ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 1) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'asterisk'
  AND stat_name = 'size'
  AND index_name != 'PRIMARY'
ORDER BY stat_value DESC
LIMIT 20;

-- If an index is fragmented or bloated after heavy deletes:
OPTIMIZE TABLE vicidial_closer_log;
-- WARNING: This locks the table. Run during maintenance windows only.

11. Daily Table Archiving

ViciDial includes a built-in archiving script that moves old records from active tables to _archive tables. This keeps the live tables small and queries fast.

How It Works

ADMIN_archive_log_tables.pl moves records older than a configurable number of days from tables like vicidial_log, vicidial_closer_log, vicidial_agent_log, call_log, and others into corresponding _archive tables (e.g., vicidial_log_archive).

Enable Daily Archiving

# Find the script location
find / -name "ADMIN_archive_log_tables.pl" -type f 2>/dev/null
# Typically: /usr/share/astguiclient/ADMIN_archive_log_tables.pl

# Test it first (dry run -- check what it would do)
/usr/share/astguiclient/ADMIN_archive_log_tables.pl --help

Add to crontab:

crontab -e

# Archive ViciDial log tables daily at 01:20 AM
# --daily flag archives records older than the configured retention period
# Run during low-traffic hours to minimize impact
20 1 * * * /usr/share/astguiclient/ADMIN_archive_log_tables.pl --daily 2>&1 | tee -a /var/log/vicidial-archive.log

Configure Retention in ViciDial Admin

In the ViciDial admin panel: Admin > System Settings > Detail Log Archiving.

Key settings:

Or set via SQL:

-- Check current archiving settings
SELECT
  close_log_archive_days,
  log_archive_days,
  log_purge_days,
  call_log_archive_days
FROM system_settings\G

-- Set 90-day retention for active tables
UPDATE system_settings SET
  close_log_archive_days = 90,
  log_archive_days = 90,
  call_log_archive_days = 90
WHERE db_schema_version > 0;

Verify Archiving Is Working

# Check the archive log after the first run
tail -50 /var/log/vicidial-archive.log

# Compare sizes of live vs archive tables
mysql -e "
SELECT
  table_name,
  table_rows,
  ROUND(data_length/1024/1024, 1) AS data_mb
FROM information_schema.tables
WHERE table_schema = 'asterisk'
  AND (table_name LIKE 'vicidial_log%'
    OR table_name LIKE 'vicidial_closer_log%'
    OR table_name LIKE 'vicidial_agent_log%'
    OR table_name LIKE 'call_log%')
ORDER BY table_name;
"

Manual Archiving (First Time)

If your tables have years of accumulated data, the first archiving run might take a long time. Consider running it manually during a maintenance window:

# Run interactively and monitor progress
/usr/share/astguiclient/ADMIN_archive_log_tables.pl --daily

# For very large tables, you might want to archive in chunks
# by temporarily setting a shorter retention period
mysql -e "UPDATE system_settings SET close_log_archive_days = 365;"
/usr/share/astguiclient/ADMIN_archive_log_tables.pl --daily
# Then set it back to 90
mysql -e "UPDATE system_settings SET close_log_archive_days = 90;"

12. Automated Backup System

A complete backup script that dumps the ViciDial database, copies Asterisk configuration files, saves firewall rules, and uploads everything to a remote storage server.

Backup Script

Create /usr/local/sbin/vici-backup.sh:

#!/bin/bash
# =============================================================================
# ViciDial Daily Backup Script
# Dumps: MariaDB database, Asterisk configs, firewall rules, custom scripts
# Uploads to remote storage via SFTP
# Retains 14 days locally
# =============================================================================

set -euo pipefail

# --- Configuration -----------------------------------------------------------
BACKUP_DIR="/var/backups/vicidial"
RETENTION_DAYS=14
DATE=$(date +%Y%m%d-%H%M)
HOSTNAME=$(hostname -s)
BACKUP_NAME="${HOSTNAME}-backup-${DATE}"
WORK_DIR="${BACKUP_DIR}/${BACKUP_NAME}"

# Remote storage (SFTP)
REMOTE_HOST="<YOUR_STORAGE_HOST>"
REMOTE_PORT=23
REMOTE_USER="<YOUR_STORAGE_USER>"
REMOTE_PASS="<YOUR_STORAGE_PASSWORD>"

# Logging
LOG_FILE="/var/log/vici-backup.log"

# --- Functions ---------------------------------------------------------------
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

cleanup_on_error() {
    log "ERROR: Backup failed at line $1"
    rm -rf "$WORK_DIR"
    exit 1
}

trap 'cleanup_on_error $LINENO' ERR

# --- Start -------------------------------------------------------------------
log "=== Starting backup: ${BACKUP_NAME} ==="
mkdir -p "$WORK_DIR"

# 1. Database dump
log "Dumping MariaDB database..."
mysqldump \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --quick \
    --databases asterisk \
    | gzip > "${WORK_DIR}/asterisk-db.sql.gz"

DB_SIZE=$(du -sh "${WORK_DIR}/asterisk-db.sql.gz" | cut -f1)
log "Database dump: ${DB_SIZE}"

# 2. Asterisk configuration
log "Backing up Asterisk configs..."
tar czf "${WORK_DIR}/asterisk-config.tar.gz" \
    /etc/asterisk/ \
    /etc/astguiclient.conf \
    2>/dev/null || true

# 3. ViciDial custom files
log "Backing up ViciDial custom files..."
tar czf "${WORK_DIR}/vicidial-custom.tar.gz" \
    /var/lib/asterisk/agi-bin/*.agi \
    /var/lib/asterisk/agi-bin/*.php \
    /var/lib/asterisk/agi-bin/*.py \
    2>/dev/null || true

# 4. Web customizations (if any)
if [ -d "/srv/www/htdocs/zoiper_manual_calls" ]; then
    tar czf "${WORK_DIR}/web-custom.tar.gz" \
        /srv/www/htdocs/zoiper_manual_calls/ \
        2>/dev/null || true
fi

# 5. Firewall rules
log "Backing up firewall rules..."
iptables-save > "${WORK_DIR}/iptables-rules.txt" 2>/dev/null || true
ufw status verbose > "${WORK_DIR}/ufw-status.txt" 2>/dev/null || true

# 6. System configs
log "Backing up system configs..."
tar czf "${WORK_DIR}/system-config.tar.gz" \
    /etc/ssh/sshd_config \
    /etc/fail2ban/jail.local \
    /etc/my.cnf \
    /etc/my.cnf.d/ \
    /etc/crontab \
    /var/spool/cron/ \
    /usr/local/sbin/vici-backup.sh \
    2>/dev/null || true

# 7. Create a manifest
log "Creating manifest..."
{
    echo "Backup: ${BACKUP_NAME}"
    echo "Date: $(date)"
    echo "Hostname: $(hostname)"
    echo "Uptime: $(uptime)"
    echo ""
    echo "Files:"
    ls -lh "${WORK_DIR}/"
    echo ""
    echo "Disk usage:"
    df -h / | tail -1
    echo ""
    echo "MariaDB version:"
    mysql -V
    echo ""
    echo "Asterisk version:"
    asterisk -V 2>/dev/null || echo "N/A"
} > "${WORK_DIR}/manifest.txt"

# 8. Create single archive
ARCHIVE="${BACKUP_DIR}/${BACKUP_NAME}.tar.gz"
tar czf "$ARCHIVE" -C "$BACKUP_DIR" "$BACKUP_NAME"
rm -rf "$WORK_DIR"
TOTAL_SIZE=$(du -sh "$ARCHIVE" | cut -f1)
log "Archive created: ${ARCHIVE} (${TOTAL_SIZE})"

# 9. Upload to remote storage via SFTP
log "Uploading to remote storage..."
if command -v sshpass &>/dev/null; then
    sshpass -p "$REMOTE_PASS" sftp -oPort="$REMOTE_PORT" \
        -oBatchMode=no \
        -oStrictHostKeyChecking=no \
        "${REMOTE_USER}@${REMOTE_HOST}" <<SFTP_EOF
put ${ARCHIVE} $(basename "$ARCHIVE")
bye
SFTP_EOF
    UPLOAD_STATUS=$?
    if [ $UPLOAD_STATUS -eq 0 ]; then
        log "Upload successful"
    else
        log "WARNING: Upload failed with exit code ${UPLOAD_STATUS}"
    fi
else
    log "WARNING: sshpass not installed, skipping remote upload"
    log "Install with: zypper install sshpass (or apt install sshpass)"
fi

# 10. Clean up old local backups
log "Cleaning up backups older than ${RETENTION_DAYS} days..."
DELETED=$(find "$BACKUP_DIR" -name "*.tar.gz" -mtime +${RETENTION_DAYS} -delete -print | wc -l)
log "Deleted ${DELETED} old backup(s)"

# --- Done --------------------------------------------------------------------
REMAINING=$(ls -1 "$BACKUP_DIR"/*.tar.gz 2>/dev/null | wc -l)
log "=== Backup complete. ${REMAINING} backup(s) on disk ==="

Install and Schedule

# Make it executable
chmod 700 /usr/local/sbin/vici-backup.sh

# Install sshpass if not present
# openSUSE: zypper install sshpass
# Debian/Ubuntu: apt install sshpass
# CentOS: yum install sshpass

# Create backup directory
mkdir -p /var/backups/vicidial

# Test a manual run
/usr/local/sbin/vici-backup.sh

# Check the log
cat /var/log/vici-backup.log

# Schedule daily at 3:00 AM
crontab -e

# Daily ViciDial backup at 03:00
0 3 * * * /usr/local/sbin/vici-backup.sh >> /var/log/vici-backup.log 2>&1

Verify Backups

# List local backups
ls -lh /var/backups/vicidial/

# Test a restore (to a temporary database -- never to production directly)
mkdir /tmp/backup-test
cd /tmp/backup-test
tar xzf /var/backups/vicidial/latest-backup.tar.gz
zcat asterisk-db.sql.gz | head -50  # Verify SQL looks correct

# Full restore test (to a temporary database):
# mysql -e "CREATE DATABASE asterisk_test;"
# zcat asterisk-db.sql.gz | sed 's/asterisk/asterisk_test/g' | mysql asterisk_test
# mysql -e "SHOW TABLES FROM asterisk_test;" | wc -l
# mysql -e "DROP DATABASE asterisk_test;"

13. Recording Cleanup Policies

Call recordings are the single largest consumer of disk space on ViciDial servers. Without cleanup, they will eventually fill the disk and crash the server.

Check Current Recording Usage

# Total recording size
du -sh /var/spool/asterisk/monitorDONE/

# Breakdown by month
du -sh /var/spool/asterisk/monitorDONE/MP3/20260* 2>/dev/null
# or if recordings are in dated subdirectories:
for d in /var/spool/asterisk/monitorDONE/*/; do
    echo "$(du -sh "$d" 2>/dev/null)"
done

# Count recordings
find /var/spool/asterisk/monitorDONE/ -name "*.mp3" -o -name "*.wav" -o -name "*.gsm" | wc -l

# Oldest recording
find /var/spool/asterisk/monitorDONE/ -type f -name "*.mp3" | head -1 | xargs ls -la

Recording Cleanup Script

Create /usr/local/sbin/recording-cleanup.sh:

#!/bin/bash
# =============================================================================
# ViciDial Recording Cleanup
# Removes recordings older than RETENTION_DAYS
# Removes original (pre-mix) recordings after ORIG_RETENTION_DAYS
# =============================================================================

# Retention periods
RETENTION_DAYS=240        # Keep final recordings for 240 days (8 months)
ORIG_RETENTION_DAYS=1     # Keep original (pre-mix) recordings for 1 day only

# Directories
RECORDING_DIR="/var/spool/asterisk/monitorDONE"
ORIG_DIR="/var/spool/asterisk/monitor"

LOG_FILE="/var/log/recording-cleanup.log"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> "$LOG_FILE"
}

log "=== Recording cleanup started ==="

# Clean up finished recordings (older than retention period)
if [ -d "$RECORDING_DIR" ]; then
    BEFORE=$(du -sm "$RECORDING_DIR" 2>/dev/null | cut -f1)
    DELETED=$(find "$RECORDING_DIR" -type f \
        \( -name "*.mp3" -o -name "*.wav" -o -name "*.gsm" \) \
        -mtime +${RETENTION_DAYS} -delete -print 2>/dev/null | wc -l)
    AFTER=$(du -sm "$RECORDING_DIR" 2>/dev/null | cut -f1)
    FREED=$((BEFORE - AFTER))
    log "Finished recordings: deleted ${DELETED} files, freed ${FREED} MB"
fi

# Clean up original (pre-mix) recordings (keep only 1 day)
if [ -d "$ORIG_DIR" ]; then
    BEFORE=$(du -sm "$ORIG_DIR" 2>/dev/null | cut -f1)
    DELETED=$(find "$ORIG_DIR" -type f \
        \( -name "*.mp3" -o -name "*.wav" -o -name "*.gsm" \) \
        -mtime +${ORIG_RETENTION_DAYS} -delete -print 2>/dev/null | wc -l)
    AFTER=$(du -sm "$ORIG_DIR" 2>/dev/null | cut -f1)
    FREED=$((BEFORE - AFTER))
    log "Original recordings: deleted ${DELETED} files, freed ${FREED} MB"
fi

# Remove empty directories left behind
find "$RECORDING_DIR" -type d -empty -delete 2>/dev/null
find "$ORIG_DIR" -type d -empty -delete 2>/dev/null

log "=== Recording cleanup finished ==="

Schedule

chmod 700 /usr/local/sbin/recording-cleanup.sh

crontab -e

# Recording cleanup daily at 04:00 AM (after backup completes)
0 4 * * * /usr/local/sbin/recording-cleanup.sh

Choosing Retention Periods

Adjust RETENTION_DAYS in the script accordingly. If you have compliance requirements, consult your legal team.

14. Log Management & Rotation

Asterisk logs can grow to enormous sizes on busy servers. One production server had its /var/log/asterisk/messages file reach 9.5 GB.

Check Current Log Sizes

du -sh /var/log/asterisk/*
ls -lh /var/log/asterisk/messages

Configure Logrotate for Asterisk

Create /etc/logrotate.d/asterisk:

/var/log/asterisk/messages
/var/log/asterisk/error
/var/log/asterisk/queue_log
/var/log/asterisk/full {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    maxsize 500M
    postrotate
        /usr/sbin/asterisk -rx 'logger reload' > /dev/null 2>&1 || true
    endscript
}

Key settings explained:

Test Logrotate

# Dry run (shows what would happen without doing it)
logrotate -d /etc/logrotate.d/asterisk

# Force rotation now (useful for first-time setup or after creating the config)
logrotate -f /etc/logrotate.d/asterisk

# Verify
ls -lh /var/log/asterisk/

ViciDial Logs

ViciDial's own logs are in /var/log/astguiclient/. Add rotation for those too:

/var/log/astguiclient/*.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    maxsize 200M
}

Clean Up Mail Spools

ViciDial cron jobs generate output that accumulates in the mail spool:

# Check mail spool size
du -sh /var/spool/mail/

# Truncate (not delete) mail files for system users
truncate -s 0 /var/spool/mail/root 2>/dev/null
truncate -s 0 /var/spool/mail/asterisk 2>/dev/null

# Prevent future accumulation: redirect cron output
# In crontab, append >/dev/null 2>&1 to noisy jobs

Clean Up Screen Logs

ViciDial runs under screen, which can accumulate massive screenlog files:

# Check for large screenlog files
find / -name "screenlog.*" -size +100M 2>/dev/null

# Truncate them (don't delete -- screen may be actively writing)
find / -name "screenlog.*" -size +100M -exec truncate -s 0 {} \; 2>/dev/null

15. Binary Log Management

If you use MySQL replication, binary logs are essential. But without management, they grow without bound.

Configure Binary Log Retention

# /etc/my.cnf.d/vicidial-tuning.cnf

[mysqld]
# Keep binary logs for 7 days (enough time to recover from replication breaks)
expire_logs_days = 7

# Compress binary logs to save disk space (MariaDB 10.6+)
log_bin_compress = ON

Apply Without Restart

-- Set immediately
SET GLOBAL expire_logs_days = 7;

-- Check current binary log usage
SHOW BINARY LOGS;

-- Manual cleanup (if you have many old logs taking space)
-- Purge logs older than 7 days
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

Monitor Binary Log Disk Usage

# Check binary log directory size
du -sh /var/lib/mysql/mysql-bin.*
ls -lh /var/lib/mysql/mysql-bin.* | tail -10

# Total size of all binary logs
mysql -e "SHOW BINARY LOGS;" | awk '{sum += $2} END {printf "Total: %.1f MB\n", sum/1024/1024}'

16. Post-Hardening Verification Checklist

After applying all changes, verify everything is working correctly. Run through this checklist:

Security Verification

# 1. SSH: Can you connect on the new port?
ssh -p 9322 root@<YOUR_SERVER_IP>

# 2. Fail2ban: All 5 jails running?
fail2ban-client status
# Expected: 5 jails listed

# 3. Firewall: Default policy is DROP?
iptables -L INPUT | head -2
# or: ufw status

# 4. MySQL: No users with full privileges from '%'?
mysql -e "SELECT user, host FROM mysql.user WHERE host = '%';"
# Should return empty or only known users

# 5. SIP: Only provider IPs can reach port 5060?
# From an unauthorized IP, try:
# nmap -sU -p 5060 <YOUR_SERVER_IP>  -- should show filtered/closed

# 6. Apache: Recording directory not browsable?
curl -s http://<YOUR_SERVER_IP>/RECORDINGS/MP3/ | head -5
# Should return 403 Forbidden or redirect, not a directory listing

Performance Verification

-- 1. max_statement_time is set?
SHOW GLOBAL VARIABLES LIKE 'max_statement_time';
-- Expected: 300

-- 2. InnoDB buffer pool sized correctly?
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- Expected: 4G or your configured value

-- 3. Query cache disabled?
SHOW VARIABLES LIKE 'query_cache_type';
-- Expected: OFF

-- 4. All 3 indexes exist?
SHOW INDEX FROM vicidial_agent_log WHERE Key_name = 'idx_uniqueid';
SHOW INDEX FROM call_log WHERE Key_name = 'idx_changrp_starttime';
SHOW INDEX FROM vicidial_closer_log WHERE Key_name = 'idx_campaign_calldate_status';
-- Each should return 1+ rows

-- 5. Slow query log enabled?
SHOW VARIABLES LIKE 'slow_query_log';
-- Expected: ON

-- 6. Binary log retention set?
SHOW VARIABLES LIKE 'expire_logs_days';
-- Expected: 7

Operational Verification

# 1. Calls are still flowing?
asterisk -rx "core show channels count"
# Should show active calls during business hours

# 2. Agents can still log in?
# Have an agent log in to the ViciDial web phone and make a test call

# 3. Archiving cron is scheduled?
crontab -l | grep archive
# Expected: ADMIN_archive_log_tables.pl

# 4. Backup cron is scheduled?
crontab -l | grep backup
# Expected: vici-backup.sh at 03:00

# 5. Recording cleanup cron is scheduled?
crontab -l | grep recording
# Expected: recording-cleanup.sh at 04:00

# 6. Logrotate is configured?
logrotate -d /etc/logrotate.d/asterisk
# Should show it would rotate without errors

# 7. SIP capture daemon is disabled?
systemctl is-enabled sip_capture_daemon 2>/dev/null
# Expected: disabled (or not found)

# 8. ViciDial screens are running?
screen -ls
# Should show the standard ViciDial screen sessions

17. Ongoing Maintenance Schedule

After hardening, set up a regular maintenance routine:

Daily (Automated)

Weekly (Manual Review)

# Monday morning checks (5 minutes)

# 1. Check fail2ban -- how many IPs were banned last week?
grep "Ban " /var/log/fail2ban.log | tail -50

# 2. Check disk usage
df -h

# 3. Review slow query log
mysqldumpslow -t 10 /var/log/mysql/slow-query.log

# 4. Check backup log for failures
grep -i "error\|fail\|warning" /var/log/vici-backup.log | tail -20

# 5. Check replication health (if applicable)
mysql -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind"

# 6. Check recording cleanup log
tail -20 /var/log/recording-cleanup.log

Monthly (Deeper Review)

# 1. Check for OS security updates
# openSUSE: zypper list-patches --category security
# Debian/Ubuntu: apt list --upgradable
# CentOS: yum check-update --security

# 2. Review MySQL user accounts
mysql -e "SELECT user, host, max_statement_time FROM mysql.user ORDER BY user;"

# 3. Check table sizes -- are active tables growing despite archiving?
mysql -e "
SELECT table_name, table_rows, ROUND(data_length/1024/1024, 1) AS data_mb
FROM information_schema.tables
WHERE table_schema = 'asterisk'
  AND table_rows > 100000
ORDER BY data_length DESC
LIMIT 20;
"

# 4. Check InnoDB buffer pool hit rate
mysql -e "
SELECT
  ROUND(100 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests * 100), 2)
    AS hit_rate_pct
FROM (
  SELECT
    VARIABLE_VALUE AS Innodb_buffer_pool_reads
  FROM information_schema.GLOBAL_STATUS
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) a,
(
  SELECT
    VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
  FROM information_schema.GLOBAL_STATUS
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) b;
"
# Target: > 99%. If below 95%, increase innodb_buffer_pool_size.

# 5. Test backup restore (quarterly)
# Pick a random backup, extract it, load into a test database, verify data

Quarterly

• Test backup restore: Actually restore a backup to a test database and verify the data is complete.
• Review firewall rules: Remove any temporary rules that were added.
• Check fail2ban filter effectiveness: Run fail2ban-regex against current logs to ensure patterns still match.
• Review SIP provider IP list: Update firewall rules if providers have changed IPs.

18. Appendix: Quick Reference Commands

Fail2ban

fail2ban-client status                     # List all jails
fail2ban-client status sshd                # Details for sshd jail
fail2ban-client status asterisk            # Details for asterisk jail
fail2ban-client set sshd unbanip <IP>      # Unban an IP
fail2ban-client set asterisk banip <IP>    # Manually ban an IP
fail2ban-client reload                     # Reload config
tail -f /var/log/fail2ban.log              # Watch ban activity live

MariaDB

SHOW GLOBAL VARIABLES LIKE 'max_statement_time';         -- Query timeout
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';    -- Buffer pool
SHOW GLOBAL STATUS LIKE 'Threads_connected';             -- Active connections
SHOW PROCESSLIST;                                         -- Running queries
KILL <process_id>;                                        -- Kill a runaway query
SHOW ENGINE INNODB STATUS\G                               -- InnoDB internals
SHOW BINARY LOGS;                                         -- Binary log inventory

Asterisk

asterisk -rx "core show channels count"         # Active call count
asterisk -rx "sip show peers"                    # SIP trunk status
asterisk -rx "sip show channelstats"             # RTP quality metrics
asterisk -rx "core show uptime"                  # Server uptime
asterisk -rx "module show like res_security"     # Security modules

Disk and Logs

du -sh /var/spool/asterisk/monitorDONE/          # Recording space
du -sh /var/log/asterisk/                        # Log space
du -sh /var/lib/mysql/                           # Database space
df -h                                            # Overall disk usage
find / -type f -size +500M 2>/dev/null           # Find large files

Firewall

iptables -L INPUT -n --line-numbers              # List rules with numbers
iptables -D INPUT <rule_number>                  # Delete a specific rule
iptables-save > /etc/sysconfig/iptables          # Persist (openSUSE)
ufw status verbose                               # UFW status (Debian/Ubuntu)

Summary of Changes Applied

For reference, here is a consolidated view of every change this guide covers:

This guide was developed from production hardening work across multiple ViciDial servers handling thousands of daily calls. Every command was executed in production and every recommendation comes from real-world incidents and performance data.