Snort ACID MySQL Database maintenance...
I've recently inherited a Snort installation that uses MySQL/ACID/BASE which had run itself out of room on the root partition where everything was stored. After some emergency resuscitation of the LVM, I set out to create a few MySQL statements that will remove data that are older than X days old. These specific commands can be run from the mysql command line (in order) replacing X with the number of days you want to keep:
DELETE FROM event WHERE timestamp < DATE_SUB(NOW(),INTERVAL X 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;
Of course if you want to automate this you can place these commands and any others you need to execute (such as archiving the database first) in a script and use the ".my.cnf" mysql configuration file to store your user/database information. Don't forget to secure the preferences file!
Have you got any suggestions to make this better? Leave a comment on how you maintain the Snort data - and play some golf with the commands.
