Cleanup Snort Alerts from MySQL

From Notes

I did not create these queries, this was obtained from: re: http://www.terryburton.co.uk/blog/2007/09/deleting-old-snort-and-base-event-data.html

#!/bin/bash

#
# This is a simple script that will prune all but the last 28 days of
# data from the snort DB.
#
# TODO: - remove root password from script
#       - use configurable variables
#


/usr/bin/mysql --user=root --password=<pass> snort <<EOF
use snort;
DELETE FROM event WHERE timestamp < DATE_SUB(NOW(),INTERVAL 28 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;
EOF