# 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

# 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"

# 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

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

# 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

# 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

# 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

# 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.

# openSUSE (ViciBox)
zypper install fail2ban python3-pyinotify

# Debian/Ubuntu
apt install fail2ban

# CentOS/RHEL
yum install fail2ban fail2ban-systemd

[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

# /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 =

# 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

# 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

-- 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';

-- 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.

-- 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;

# /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

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

# 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';"

# 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';"

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

# 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

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

-- Verify
SHOW GLOBAL VARIABLES LIKE 'max_statement_time';

# /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

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

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

# 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

# /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

-- 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;

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

[mysqld]
query_cache_type = OFF
query_cache_size = 0

# /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

# /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

# 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';"

-- 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)

-- 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

-- 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!)

-- 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.

# 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

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

-- 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;

# 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;
"

# 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;"

#!/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 ==="

# 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

# 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;"

# 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

#!/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 ==="

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

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

/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
}

# 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/

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

# 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

# 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

# /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

-- 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);

# 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}'

# 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

-- 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

# 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

# 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

# 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

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

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 -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

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

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)