Limit Call History to 3 Months?

[steve_pbuk (Steve Allen)]

Currently my call history goes back to when I first set the server up in January 2018.

Is it possible to enable something that limits how long call history stays in the database?

Thanks

[BlazeStudios (Tom Ray)]

You’re going to have to write something and either run it manually or setup a cronjob that will dip into the CDR and CEL databases and delete old entries.

[steve_pbuk (Steve Allen)]

Is anyone out there already doing this and willing to share their method?

[PitzKey (Itzik)]

Did you try searching the forum?
IIRC @dicko provided useful cron jobs how to do that. Or maybe was it to delete recorded calls only?

[steve_pbuk (Steve Allen)]

Good point @PitzKey

Just had a quick look and found the following from @cynjut in 2016

Create a script called (for example) “DeleteCDR8Mos.sql” in your “asterisk root directory” or in the “root” home directory. The example below assumes it’s in the “/root” directory.

use asteriskcdrdb;
repair table cdr;
repair table cel;
delete from cdr where calldate < DATE_SUB(NOW(), INTERVAL 6 MONTH);
delete from cel where eventtime < DATE_SUB(NOW(), INTERVAL 6 MONTH);
optimize table cdr;
optimize table cel;
exit;

In your crontab on the server (important part, since access to the database is limited to localhost), add something like:

X X X X X X mysql -u root asteriskcdrdb < /root/DeleteCDR8Mos.sql

The “X” entries are there for your time/date/day entries - salt to taste.

That should get you started.

Anyone using this script now on an up to date server?