#spambotchk.sh

#Used to rotate the MySQL firewall log
#for a full discussion on its use, see the paper http://www.altsec.info/fwlogproj/Stingley-GIAC-GOLD-2009.pdf

# Copyright (C) 2009 Mark Stingley
# mark AT altsec.info
# http://www.altsec.info

# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# See http://www.gnu.org/licenses/licenses.html#GPL or write to the
# Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston,
# MA  02111-1307  USA
#
# Changelog:
#Version 20090903.01

#fw1-rotate.sh
#This script is run from a cron job at 23:59 every night.  It not only rotates the current log to an archive table, 
#it compresses the table and restarts fw1loggrabber.

#The script is supplied as a possible template and definitely needs improvement
#the following must be changed to suit your environment:

#The arguments for isql32 (the 32-bit version, renamed for a 64-bit environment
#<ODBC handle> <database user> <user password> 

#verify the path to and name for the fw1-loggrabber executable

#verify the path to the mysql firewall log database

#Crontab for root (crontab –e):
# m h  dom mon dow   command
#59 23 * * * /usr/local/loggrabber/fw1-rotate.sh


#!/bin/sh

#fw1-rotate.sh will archive the current ‘fw1logs’ table to a date named archive table, such as ‘fw1logs.20090529’
#it stops fw1-loggrabber, renames the current table to archive, recreates the current table and restarts fw1-loggrabber, 
#then compresses the archived table

TNOW=`date "+%Y%m%d"`
TFILE=`tempfile`

cd /usr/local/loggrabber

echo "rename table fw1logs.fw1logs to fw1logs.$TNOW" > $TFILE
echo "drop table fw1logs.auditlogs" >> $TFILE
echo "drop table fw1logs.loggrabber" >> $TFILE
echo "" >> $TFILE

FPID=`/bin/pidof fw1-loggrabber`

/bin/kill -KILL $FPID

/usr/bin/isql32 <ODBC handle> <database user> <user password> < $TFILE

/usr/local/loggrabber/fw1-loggrabber --create-tables

/usr/local/loggrabber/fw1-loggrabber 1>/usr/local/loggrabber/grabber.log 2>/usr/local/loggrabber/grabber.err &

rm -f $TFILE

echo "Compressing $TNOW"

cd /var/lib/mysql/fw1logs

myisampack -v $TNOW
myisamchk -rq $TNOW