Archiving and purging data

All installation and configuration problems and questions

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

Archiving and purging data

Postby TwistedFister » Fri Jul 28, 2017 7:57 am

Vicibox 7.0.3 from .iso | Vicidial 2.14-579a Build 161128-1746 | Asterisk 11.22.0-vici | CLuster setup: 1 web 1 DB 8 telephony | No Digium/Sangoma Hardware | No Extra Software After Installation

is there a script that we could run that would archive vicidial_log and vicidial_list? We have a years worth of data and we want to condense it to holding only 3 months but keep the call records intact. Also after the data is moved to archive is it safe to purge? We have millions of leads.


thanks in advance!
TwistedFister
 
Posts: 78
Joined: Tue Jul 26, 2016 11:50 am

Re: Archiving and purging data

Postby mflorell » Fri Jul 28, 2017 8:03 am

For the logs, there is a script you can set to do that automatically at regular intervals in the crontab:

# /usr/share/astguiclient/ADMIN_archive_log_tables.pl --help
allowed run time options:
[--daily] = only archives call_log, vicidial_log_extended, vicidial_dial_log and vicidial_drop_log tables, only last 24 hours kept
[--carrier-daily] = will also archive the vicidial_carrier_log table when --daily is run
[--vlog-daily] = will also archive the vicidial_log table when --daily is run
[--days=XX] = number of days to archive past, default is 732(2 years)
[--months=XX] = number of months to archive past, default is 24(2 years) If 'days' used then 'months' ignored
[--closer-log] = archive vicidial_closer_log records
[--queue-log] = archive QM queue_log records
[--only-trim-archive-level-one] = will not perform normal archive process, instead this will only delete records
that are older than XX months from least important log archive tables:
call_log_archive, vicidial_log_extended_archive, vicidial_dial_log_archive, vicidial_drop_log
[--only-trim-archive-level-two] = same as --only-trim-archive-level-one, except includes tables:
vicidial_carrier_log_archive, vicidial_api_log_archive, vicidial_rt_monitor_log_archive
[--only-trim-archive-level-three] = same as --only-trim-archive-level-two, except includes tables:
vicidial_log_archive, vicidial_agent_log_archive, vicidial_closer_log_archive
[--recording-log-days=XX] = OPTIONAL, number of days to archive recording_log table only past
[--cpd-log-purge-days=XX] = OPTIONAL, number of days to purge vicidial_cpd_log table only past
[--quiet] = quiet
[--calc-test] = date calculation test only
[--test] = test



For vicidial_list, you have to do it manually, here's an example,

CREATE TABLE vicidial_list_archive LIKE vicidial_list;
ALTER TABLE vicidial_list_archive MODIFY lead_id INT(9) UNSIGNED NOT NULL;

select count(*) from vicidial_list where modify_date < "2014-01-01 00:00:00";

INSERT INTO vicidial_list_archive SELECT * from vicidial_list where modify_date < "2014-01-01 00:00:00";
select count(*) from vicidial_list_archive where modify_date < "2014-01-01 00:00:00";
DELETE from vicidial_list where modify_date < "2014-01-01 00:00:00";
optimize table vicidial_list;
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida


Return to Support

Who is online

Users browsing this forum: Bing [Bot], Google [Bot], Majestic-12 [Bot] and 113 guests