[Snort-users] Purge Snort from MySQL

Ian snort_list at fishnet.co.uk
Thu May 9 06:20:05 EDT 2019


On 01/05/2019 19:01, Motter, Aaron wrote:
> Our Snort setup uses MySQL.
> 
> Is there a good query to use to delete data that’s older than say 90 days?
> 
> Might be better to post this on a MySQL forum.

Hi,

I use this:

DELETE FROM event WHERE `timestamp` < DATE_SUB( NOW(), INTERVAL 90 DAY);

DELETE FROM data USING data LEFT OUTER JOIN event USING (sid,cid) WHERE 
event.sid IS NULL;
DELETE FROM iphdr USING iphdr LEFT OUTER JOIN event USING (sid,cid) 
WHERE event.sid IS NULL;
DELETE FROM icmphdr USING icmphdr LEFT OUTER JOIN event USING (sid,cid) 
WHERE event.sid IS NULL;
DELETE FROM tcphdr USING tcphdr LEFT OUTER JOIN event USING (sid,cid) 
WHERE event.sid IS NULL;
DELETE FROM udphdr USING udphdr LEFT OUTER JOIN event USING (sid,cid) 
WHERE event.sid IS NULL;
DELETE FROM opt USING opt LEFT OUTER JOIN event USING (sid,cid) WHERE 
event.sid IS NULL;
DELETE FROM acid_event USING acid_event LEFT OUTER JOIN event USING 
(sid,cid) WHERE event.sid IS NULL;
DELETE FROM ag USING acid_ag_alert AS ag LEFT OUTER JOIN event AS e ON 
ag.ag_sid=e.sid AND ag.ag_cid=e.cid WHERE e.sid IS NULL;

OPTIMIZE TABLE event, data, iphdr, icmphdr, tcphdr, udphdr, opt, 
acid_event, acid_ag_alert;


The first query deletes the events older than 90 days.  The following 
queries delete other data that no longer has an event to go with it. 
The last query recovers the deleted space (this can depends on your 
table engine and settings - see the MySQL documentation for more info).



Regards

Ian
-- 




More information about the Snort-users mailing list