How to delete old call logs leads Data’s in vicidial

VICIdial is an enterprise class, open source, contact center suite in use by many large call centers around the world. VICIdial has a full featured predictive dialer.  It is capable of inbound, outbound, and blended phone call handling. 

MySQL is an open-source relational database management system (RDBMS),
MariaDB is a fork of the MySQL database management system.
Vicidial use either mysql or mariadb as there default database software.

  Steps To delete or Flush Old data

   Step 1: Login to Mysql/mariadb

SSH to the server using the Putty
login to mysql by typing   

mysql -p

(if you dont know password try below command )

mysql -ucron -p1234

   Step 2:  Select the Database Asterisk

–copy paste in mysql cli–

  use asterisk;

   Step 3: Command to Check Space Used by Asterisk database

copy paste the below command in mysql cli

SELECT table_schema AS “asterisk”,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS “Size (MB)”
FROM information_schema.TABLES
GROUP BY table_schema;

You will get output similar to below picture

As you can see my database consumed 10gb of Disk space

   Step 4: Command to check disk space consumed by each tables

Copy paste the below command to check the space occupied by each table in vicidial

SELECT table_name AS “Table”,

ROUND(((data_length + index_length) / 1024 / 1024), 2) AS “Size (MB)”

FROM information_schema.TABLES

WHERE table_schema = “asterisk”

ORDER BY (data_length + index_length) DESC;

you will see output similar to below picture,which shows space consumed by each tables in vicidial 

   Step 5: Deleting the Data older than x no of days

           In this step we will be deleting the data’s of each table which is one year old, if you want to delete all data mention the date in which the vicidial is installed.

Below are the top 10 tables which consumed huge disk space,

vicidial_url_log
call_log
vicidial_list
vicidial_dial_log
vicidial_admin_log
vicidial_carrier_log
vicidial_log_extended
vicidial_log
recording_log
vicidial_agent_log

Deleting each table data for the date on or before 2023 dec 23:59
Run the below command in mysql console to delete old logs ie before DEC31-2023

Note: change the date as per your requirement

vicidial_url_log

DELETE FROM vicidial_url_log WHERE url_date < ‘2023-12-31 23:59:00’;

optimize table vicidial_url_log;

call_log

DELETE FROM call_log WHERE start_time < ‘2023-12-31 23:59:00’;

optimize table call_log;

vicidial_dial_log 

DELETE FROM vicidial_dial_log WHERE call_date < ‘2023-12-31 23:59:00’;

optimize table vicidial_dial_log;

vicidial_agent_log

DELETE FROM vicidial_agent_log WHERE event_time < ‘2023-12-31 23:59:00’;

optimize table vicidial_agent_log;