Tuesday, September 11, 2007

Deleting old Snort and BASE event data from MySQL

The following SQL that I recently cooked up thoroughly clears out the data from events that are more than 28 days old, then reoptimises the tables. It takes some time to complete so it is probably best scheduled as a regular night time cron job.

The DELETE FROM ... USING syntax is quite appealing and powerful...

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;

3 comments:

mrbulli said...

# Delete all snort alerts older than n months
# based on http://www.terryburton.co.uk/blog/2007/09/deleting-old-snort-and-base-event-data.html
DBU=$(df -Ph /var/lib/mysql/snort | awk '/dev/ {sub(/%/,"",$5);print $5}')
if [ $DBU -gt 85 ]
then
CUTOFF=1
elif [ $DBU -gt 75 ]
then
CUTOFF=3
elif [ $DBU -gt 40 ]
then
CUTOFF=6
else
CUTOFF=12
fi

# Clean out month by month to lessen the load on the database
MONTH=12
MKBS=$(mysqladmin variables | awk '/key_buffer_size/ {print $4}')
while [ $MONTH -ge $CUTOFF ]
do
# Remove old event references
CUTOFF_DATE=$(date +'%F %T' -d "$MONTH months ago")
RC=1
while [ $RC -ne 0 ]
do
RC=$(mysql -As snort -e "DELETE LOW_PRIORITY FROM event WHERE timestamp < '$CUTOFF_DATE' LIMIT 500;SELECT ROW_COUNT()")
done

# Remove orphan data (caused by the deletes above)
mysql -As snort -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY FROM data USING data LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;'"SET GLOBAL key_buffer_size = $MKBS"
mysql -As snort -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY FROM iphdr USING iphdr LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;'"SET GLOBAL key_buffer_size = $MKBS"
mysql -As snort -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY FROM icmphdr USING icmphdr LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;'"SET GLOBAL key_buffer_size = $MKBS"
mysql -As snort -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY FROM tcphdr USING tcphdr LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;'"SET GLOBAL key_buffer_size = $MKBS"
mysql -As snort -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY FROM udphdr USING udphdr LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;'"SET GLOBAL key_buffer_size = $MKBS"
mysql -As snort -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY FROM opt USING opt LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;'"SET GLOBAL key_buffer_size = $MKBS"
mysql -As snort -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY FROM acid_event USING acid_event LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;'"SET GLOBAL key_buffer_size = $MKBS"
mysql -As snort -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY 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;'"SET GLOBAL key_buffer_size = $MKBS"

MONTH=$(($MONTH - 1))
done

# Finally optimize all tables to gain back disk space
mysql -As snort -e 'SET SESSION myisam_sort_buffer_size=268435456;SET GLOBAL key_buffer_size = 536870912;OPTIMIZE TABLE event,data,iphdr,icmphdr,tcphdr,udphdr,opt,acid_event,acid_ag_alert;'"SET GLOBAL key_buffer_size = $MKBS" > /dev/null

3:56 PM
amacks said...

To add to MrBulli I added a couple of variables at the top to handle authenticating to the DB

# Delete all snort alerts older than n months
# based on http://www.terryburton.co.uk/blog/2007/09/deleting-old-snort-and-base-event-data.html
## Constants
SNORTUSER='YOURDBUSERNAME'
SNORTPASSWD='YOURDBPASSWORD'

MYSQL="mysql -As --user=${SNORTUSER} --password=${SNORTPASSWD} --host=YOURDBHOSTNAME"


## Try to delete as little data as needed to keep the amount of free space reasonable
DBU=$(df -Ph /var/lib/mysql/snort | awk '/dev/ {sub(/%/,"",$5);print $5}')
if [ $DBU -gt 85 ]
then
CUTOFF=1
elif [ $DBU -gt 75 ]
then
CUTOFF=3
elif [ $DBU -gt 40 ]
then
CUTOFF=6
else
CUTOFF=12
fi

##We want to hard-code to keep 3 months
CUTOFF=3

# Clean out month by month to lessen the load on the database
MONTH=12
MKBS=$(mysqladmin variables | awk '/key_buffer_size/ {print $4}')
while [ $MONTH -ge $CUTOFF ]; do
# Remove old event references
CUTOFF_DATE=$(date +'%F %T' -d "$MONTH months ago")
RC=1
while [ $RC -ne 0 ]; do
RC=$(${MYSQL} snort -e "DELETE LOW_PRIORITY FROM event WHERE timestamp < '$CUTOFF_DATE' LIMIT 500;SELECT ROW_COUNT()")
done

# Remove orphan data (caused by the deletes above)
${MYSQL} -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY FROM data USING data LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;'"SET GLOBAL key_buffer_size = $MKBS"
${MYSQL} snort -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY FROM iphdr USING iphdr LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;'"SET GLOBAL key_buffer_size = $MKBS"
${MYSQL} snort -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY FROM icmphdr USING icmphdr LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;'"SET GLOBAL key_buffer_size = $MKBS"
${MYSQL} snort -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY FROM tcphdr USING tcphdr LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;'"SET GLOBAL key_buffer_size = $MKBS"
${MYSQL} snort -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY FROM udphdr USING udphdr LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;'"SET GLOBAL key_buffer_size = $MKBS"
${MYSQL} snort -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY FROM opt USING opt LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;'"SET GLOBAL key_buffer_size = $MKBS"
${MYSQL} snort -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY FROM acid_event USING acid_event LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;'"SET GLOBAL key_buffer_size = $MKBS"
${MYSQL} snort -e 'SET GLOBAL key_buffer_size = 536870912;DELETE LOW_PRIORITY 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;'"SET GLOBAL key_buffer_size = $MKBS"

MONTH=$(($MONTH - 1))
done

# Finally optimize all tables to gain back disk space
${MYSQL} snort -e 'SET SESSION myisam_sort_buffer_size=268435456;SET GLOBAL key_buffer_size = 536870912;OPTIMIZE TABLE event,data,iphdr,icmphdr,tcphdr,udphdr,opt,acid_event,acid_ag_alert;'"SET GLOBAL key_buffer_size = $MKBS" > /dev/null

3:03 PM
t-readyroc said...

Pastie'd: http://pastie.org/2167324

2:53 PM

Post a Comment

Subscribe to: Post Comments (Atom)