Pruning CDR Reports - Delete CDR/CEL Records Print

⚠️ Disclaimer:
The steps below are provided as guidance to existing 20tele customers for PBXact 16 or FreePBX 16 on CentOS 7 only. Always ensure you have recent backups when performing any significant changes. This guide is based solely on what worked for us following substantial testing on the date of publication, and assumes you are securely connected via PuTTY on Windows and logged in as root over SSH. If in doubt, please email [email protected] before proceeding. 

Tested with PBXact UC 16.0.45 on 2nd July 2026.

Complete CDR/CEL Prune and Restore-Test Runbook

This guide deletes CDR/CEL records before 1 July 2023 00:00:00. Adjust the cutoff date to suit your own migration before running.

The restore test is split into two parts:

1. Safe restore test: import the backup into a temporary database and verify it. This does not touch the live CDR tables.
2. Optional live restore: copy the old rows back into the live database only if you intentionally want to restore the historic CDR/CEL.

Important: on some FreePBX systems, plain mysql uses freepbxuser from client defaults. That user may not be able to create or access the temporary restore database. For all commands involving asteriskcdrdb_restore, this runbook uses mysql --no-defaults -u root.

This procedure has been tested on a limited number of official FreePBX systems and is intended for use with the official FreePBX distributions using MariaDB. Although it is designed to be cautious and repeatable, no two deployments are identical. Ensure you have current backups and/or virtual machine snapshots available before proceeding, and review each command to confirm it is appropriate for your environment.

Confirm there is sufficient free disk space for the existing CDR/CEL data, the backup, any temporary files created during the dump, and the temporary restore database used during restore testing. The backup should complete successfully before any records are removed. Before continuing, confirm the dump file exists, is not empty and can be restored if required.

This procedure only targets the cdr and cel tables within asteriskcdrdb. It does not modify FreePBX configuration, call recordings, voicemail, queue logs or any other database content. During the pruning process, new CDR and CEL records may continue to be generated, so the backup and live database may not represent exactly the same point in time.

The restore test uses a temporary database to verify that the backup can be restored without affecting the live system. The optional live restore section should only be used if you intentionally want to restore historic CDR/CEL records into the live database. If your system has been customised, migrated from another platform or differs from the official FreePBX distributions, confirm that the commands are suitable before proceeding.

A. Pre-checks and Prune

Always test pruning on a non-production system first. Adjust table names, cutoff dates, and paths to match your own environment before running this procedure on a live system.

Step 1: Confirm the expected columns exist

mysql -e "DESCRIBE asteriskcdrdb.cdr;"
mysql -e "DESCRIBE asteriskcdrdb.cel;"

You are checking that these columns exist:

cdr -> calldate
cel -> eventtime

Step 2: Check indexes on the date columns

mysql -e "
SHOW INDEX FROM asteriskcdrdb.cdr WHERE Column_name = 'calldate';
SHOW INDEX FROM asteriskcdrdb.cel WHERE Column_name = 'eventtime';
"

If both return results, the batched delete should be able to find old rows efficiently. If either returns no rows, the prune may still work, but it may be much slower on a large system.

Step 3: Check table sizes

mysql -e "
SELECT table_schema, table_name,
       ROUND(data_length/1024/1024,2) AS data_mb,
       ROUND(index_length/1024/1024,2) AS index_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema='asteriskcdrdb'
  AND table_name IN ('cdr','cel');
"

Step 4: Check disk space

df -h

Do not underestimate the amount of free disk space required. The restore test temporarily creates a second copy of the CDR/CEL data. On large production systems, this can consume many gigabytes of additional storage. If the filesystem becomes full, the restore may fail part-way through, MySQL connections may be lost, and manual recovery may be required. Ensure sufficient free disk space is available before starting any backup, restore test or live restore. You need enough space for:

live CDR/CEL tables
• SQL dump file
• temporary restore database during restore testing

Step 5: Check oldest and newest records

mysql -e "
SELECT MIN(calldate) AS oldest_cdr, MAX(calldate) AS newest_cdr
FROM asteriskcdrdb.cdr;

SELECT MIN(eventtime) AS oldest_cel, MAX(eventtime) AS newest_cel
FROM asteriskcdrdb.cel;
"

Step 6: Count what will be deleted

mysql -e "
SELECT COUNT(*) AS cdr_to_delete
FROM asteriskcdrdb.cdr
WHERE calldate < '2023-07-01 00:00:00';

SELECT COUNT(*) AS cel_to_delete
FROM asteriskcdrdb.cel
WHERE eventtime < '2023-07-01 00:00:00';
"

Check the numbers look sensible before continuing.

Step 7: Back up CDR and CEL

mysqldump --single-transaction --quick asteriskcdrdb cdr cel \
  > /root/asteriskcdrdb-cdr-cel-before-prune-$(date +%F-%H%M%S).sql

This make take some time. Then, confirm the backup exists:

ls -lh /root/asteriskcdrdb-cdr-cel-before-prune-*.sql

Step 8: Create the prune script

cat > /root/prune-cdr-cel.sh <<'EOF'
#!/bin/bash
set -euo pipefail

CUTOFF="2023-07-01 00:00:00"
BATCH=5000

echo "This will DELETE CDR/CEL rows before: $CUTOFF"
read -r -p "Type YES to continue: " CONFIRM
[ "$CONFIRM" = "YES" ] || exit 1

prune_table() {
  local TABLE="$1"
  local DATECOL="$2"

  while true; do
    ROWS=$(mysql -N -B -e "
      DELETE FROM asteriskcdrdb.${TABLE}
      WHERE ${DATECOL} < '${CUTOFF}'
      LIMIT ${BATCH};
      SELECT ROW_COUNT();
    " | tail -n 1)

    if [ "$ROWS" -eq 0 ]; then
      echo "${TABLE}: complete"
      break
    fi

    echo "${TABLE}: deleted ${ROWS} rows"
    sleep 1
  done
}

prune_table cdr calldate
prune_table cel eventtime

echo "Done."
EOF

chmod +x /root/prune-cdr-cel.sh

Step 9: Start screen

screen keeps the prune running even if your SSH session drops. Check it is installed before you begin:

command -v screen

If nothing is printed, install it using the command for your system:

• Debian/Ubuntu, usually FreePBX 17: apt install -y screen
• CentOS/RHEL/Sangoma OS, usually FreePBX 16 and earlier: yum install -y screen

Once screen is confirmed installed, start a session:

screen -S cdr-prune

Step 10: Run the prune script inside screen

/root/prune-cdr-cel.sh

You will see:

This will DELETE CDR/CEL rows before: 2023-07-01 00:00:00
Type YES to continue:

Type exactly:

YES

Then press Enter. Anything other than YES exits without deleting.

Step 11: Watch progress

This may take a long time. You will see continuous output like:

cdr: deleted 5000 rows
cdr: deleted 5000 rows
cdr: deleted 5000 rows
cdr: deleted 185 rows
cdr: complete
cel: deleted 5000 rows
cdr: deleted 5000 rows
cdr: deleted 5000 rows
cel: deleted 318 rows
cel: complete
Done.

When you see Done., the prune has finished.

Step 12: Leave screen after the job has finished

Once the script says Done., type:

exit

That closes the screen session and returns you to your normal SSH shell.

Step 13: Confirm the prune worked

mysql -e "
SELECT COUNT(*) AS cdr_remaining
FROM asteriskcdrdb.cdr
WHERE calldate < '2023-07-01 00:00:00';

SELECT COUNT(*) AS cel_remaining
FROM asteriskcdrdb.cel
WHERE eventtime < '2023-07-01 00:00:00';
"

Both should return 0.

Step 14: Move the backup files into a tidy folder

mkdir -p /root/cdr-prune-backups
mv /root/asteriskcdrdb-cdr-cel-before-prune-*.sql /root/cdr-prune-backups/
ls -lh /root/cdr-prune-backups/

This moves all matching prune backups from /root. The .sql files are database dump files. Do not type the filename by itself.

B. Safe Restore Test into Temporary Database Only

This tests that the backup can be imported and read without touching the live asteriskcdrdb.cdr or asteriskcdrdb.cel tables.

Restore test Step 1: Choose the backup file

List the available backups:

ls -lh /root/cdr-prune-backups/

Set the backup variable using the real filename shown by ls. Example:

BACKUP="/root/cdr-prune-backups/asteriskcdrdb-cdr-cel-before-prune-YYYY-MM-DD-HHMMSS.sql"

Confirm it exists:

ls -lh "$BACKUP"

Restore test Step 2: Create a temporary restore database

mysql --no-defaults -u root -e "
DROP DATABASE IF EXISTS asteriskcdrdb_restore;
CREATE DATABASE asteriskcdrdb_restore;
"

Restore test Step 3: Import the backup into the temporary database

mysql --no-defaults -u root asteriskcdrdb_restore < "$BACKUP"

Please be patient. This loads the dump into asteriskcdrdb_restore. It does not touch the live asteriskcdrdb.

Restore test Step 4: Confirm the temporary restore worked

mysql --no-defaults -u root -e "
SELECT COUNT(*) AS restored_temp_cdr_rows
FROM asteriskcdrdb_restore.cdr;

SELECT COUNT(*) AS restored_temp_cel_rows
FROM asteriskcdrdb_restore.cel;
"

Restore test Step 5: Check the restored date range

mysql --no-defaults -u root -e "
SELECT MIN(calldate) AS restored_oldest_cdr,
       MAX(calldate) AS restored_newest_cdr
FROM asteriskcdrdb_restore.cdr;

SELECT MIN(eventtime) AS restored_oldest_cel,
       MAX(eventtime) AS restored_newest_cel
FROM asteriskcdrdb_restore.cel;
"

Restore test Step 6: Confirm the live database still has no old rows

mysql --no-defaults -u root -e "
SELECT COUNT(*) AS live_old_cdr_rows
FROM asteriskcdrdb.cdr
WHERE calldate < '2023-07-01 00:00:00';

SELECT COUNT(*) AS live_old_cel_rows
FROM asteriskcdrdb.cel
WHERE eventtime < '2023-07-01 00:00:00';
"

These should still be 0. At this point, the restore test has succeeded without writing anything back into live.

Restore test Step 7: Remove the temporary restore database

Only do this after you have finished checking the temporary restore performed as expected.

mysql --no-defaults -u root -e "DROP DATABASE asteriskcdrdb_restore;"

C. Optional Full Live Restore

Only use this section if you intentionally want to put the historic CDR/CEL rows back into the live database.

Live restore Step 1: Choose the backup file

List the available backups:

ls -lh /root/cdr-prune-backups/

Set the backup variable using the real filename shown by ls. Example:

BACKUP="/root/cdr-prune-backups/asteriskcdrdb-cdr-cel-before-prune-YYYY-MM-DD-HHMMSS.sql"

Confirm it exists:

ls -lh "$BACKUP"

Live restore Step 2: Create a temporary restore database

mysql --no-defaults -u root -e "
DROP DATABASE IF EXISTS asteriskcdrdb_restore;
CREATE DATABASE asteriskcdrdb_restore;
"

Live restore Step 3: Import the backup into the temporary database

mysql --no-defaults -u root asteriskcdrdb_restore < "$BACKUP"

Live restore Step 4: Confirm live old rows are absent before copying back

mysql --no-defaults -u root -e "
SELECT COUNT(*) AS live_old_cdr_rows
FROM asteriskcdrdb.cdr
WHERE calldate < '2023-07-01 00:00:00';

SELECT COUNT(*) AS live_old_cel_rows
FROM asteriskcdrdb.cel
WHERE eventtime < '2023-07-01 00:00:00';
"

Both should be 0.

If either is not 0, stop. Do not copy the rows back without deciding how to handle duplicates.

Live restore Step 5: Copy the old rows back into the live database

Only continue if Live restore Step 4 returned 0 for both tables.

mysql --no-defaults -u root -e "
INSERT INTO asteriskcdrdb.cdr
SELECT *
FROM asteriskcdrdb_restore.cdr
WHERE calldate < '2023-07-01 00:00:00';

INSERT INTO asteriskcdrdb.cel
SELECT *
FROM asteriskcdrdb_restore.cel
WHERE eventtime < '2023-07-01 00:00:00';
"

This restores only rows before the cutoff. It leaves newer live records alone.

Live restore Step 6: Confirm the old rows are back

mysql --no-defaults -u root -e "
SELECT COUNT(*) AS live_restored_cdr_rows
FROM asteriskcdrdb.cdr
WHERE calldate < '2023-07-01 00:00:00';

SELECT COUNT(*) AS live_restored_cel_rows
FROM asteriskcdrdb.cel
WHERE eventtime < '2023-07-01 00:00:00';
"

Live restore Step 7: Check the full live date range

mysql --no-defaults -u root -e "
SELECT MIN(calldate) AS oldest_cdr, MAX(calldate) AS newest_cdr
FROM asteriskcdrdb.cdr;

SELECT MIN(eventtime) AS oldest_cel, MAX(eventtime) AS newest_cel
FROM asteriskcdrdb.cel;
"

Live restore Step 8: Remove the temporary restore database

mysql --no-defaults -u root -e "DROP DATABASE asteriskcdrdb_restore;"

D. Backup Retention

List retained backups

ls -lh /root/cdr-prune-backups/

Remove backups later when no longer needed

rm /root/cdr-prune-backups/asteriskcdrdb-cdr-cel-before-prune-*.sql

Type y and then re-list retained backups.