This was a quick mysql routine to pull all alertids corresponding to aged
logs and deleting the rows from tables "agent" and "data". Idea is to run
it daily to keep the database lean. Added optimization commands as well. Am
I missing any other tables, indexes etc?
DELIMITER $$
CREATE DEFINER=`ossec`@`%` PROCEDURE `purge_old_data`()
BEGIN
declare done int default 0;
declare cur1id tinytext;
declare cur1 cursor for select alertid from ossec.alert where
date(from_unixtime(timestamp)) < curdate() -30;
declare continue handler for sqlstate '02000' set done = 1;
open cur1;
repeat
fetch cur1 into cur1id;
if not done then
delete from ossec.alert where alert.alertid = cur1id;
delete from ossec.data where data.alertid = cur1id;
optimze table alert;
optimize table data;
end if;
until done end repeat;
close cur1;
END
Thanks for the advice.
Ash Kumar
On Friday, November 2, 2012 5:57:38 AM UTC-4, Andy wrote:
>
> I'm not familiar with this type of script, but would you also want to
> optimise the table?
>
> On Thursday, November 1, 2012 9:07:32 PM UTC, ash kumar wrote:
>>
>> Does the following procedure appear right to delete older than 30 days
>> data, or am I missing something?
>>
>> I am deleting from tables alert and data only based on alert ids.
>>
>> DELIMITER $$
>>
>> CREATE DEFINER=`ossec`@`%` PROCEDURE `purge_old_data`()
>> BEGIN
>>
>> declare done int default 0;
>> declare cur1id tinytext;
>> declare cur1 cursor for select alertid from ossec.alert where
>> date(from_unixtime(timestamp)) < curdate() -30;
>> declare continue handler for sqlstate '02000' set done = 1;
>>
>> open cur1;
>> repeat
>> fetch cur1 into cur1id;
>> if not done then
>> delete from ossec.alert where alert.alertid = cur1id;
>> delete from ossec.data where data.alertid = cur1id;
>> end if;
>> until done end repeat;
>> close cur1;
>>
>> END
>>
>> Thanks and regards
>>
>> Ash Kumar
>>
>
