Re: Purge Snort from MySQL

[Ian via Snort-users <snort-users () lists snort org>]

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
--


_______________________________________________
Snort-users mailing list
Snort-users () lists snort org
Go to this URL to change user options or unsubscribe:
https://lists.snort.org/mailman/listinfo/snort-users

        To unsubscribe, send an email to:
        snort-users-leave () lists snort org

Please visit http://blog.snort.org to stay current on all the latest Snort news!

Please follow these rules: https://snort.org/faq/what-is-the-mailing-list-etiquette