sguil-db-purge

[GoogleCodeExporter]

Need a cronjob that will purge old entries from MySQL and repair tables.

Original issue reported on code.google.com by doug.bu...@gmail.com on 14 Sep 2011 at 6:13

[GoogleCodeExporter]

Added the following files:
/usr/local/bin/sguil-db-purge 
/etc/cron.d/sguil-db-purge

Built package using the following command:
/var/lib/gems/1.8/bin/fpm -s dir -t deb -n sguil-db-purge -v 20110914 
/usr/local/bin/sguil-db-purge /etc/cron.d/sguil-db-purge

Updated security-onion-upgrade.sh with the following:
sed -i 's| |=|g' $CONF
source $CONF
if [ "$VERSION" = "20110913" ]; then
        NEW="20110914"
        echo "**********************************************"   | $LOGGER
        echo "* Upgrading from $VERSION to $NEW."               | $LOGGER
        echo "**********************************************"   | $LOGGER
        DIR="/nsm/backup/$NEW"
        mkdir -p $DIR                                           | $LOGGER
        cd $DIR

        echo "* Backing up /usr/local/bin/setup"                | $LOGGER
        cp /usr/local/bin/setup $DIR/                           | $LOGGER

        echo "* Updating /usr/local/bin/setup" | $LOGGER
        FILE='securityonion-setup_20110914_i386.deb'
        wget -q http://sourceforge.net/projects/security-onion/files/$NEW/$FILE -O $FILE | $LOGGER
        dpkg -i $FILE                                           | $LOGGER

        echo "* Installing sguil-db-purge" | $LOGGER
        FILE='securityonion-sguil-db-purge_20110914_i386.deb'
        wget -q http://sourceforge.net/projects/security-onion/files/$NEW/$FILE -O $FILE | $LOGGER
        dpkg -i $FILE                                           | $LOGGER

        echo "* Backing up $CONF"                               | $LOGGER
        cp $CONF $DIR/                                          | $LOGGER

        echo "* Adding DAYSTOKEEP variable to $CONF"            | $LOGGER
        echo "DAYSTOKEEP=365" >> $CONF

        echo "* PLEASE NOTE!"
        echo "* sguil-db-purge is now installed and is schedule to run"
        echo "* every day at 5:01 AM.  It will do the following:"
        echo "  - stop sguild"
        echo "  - purge old events from the database"
        echo "  - repair the remaining MySQL tables"
        echo "  - start sguild"
        echo "* The default retention policy for the purge is 365 days."
        echo "* If you would like to change this value, please change"
        echo "* the DAYSTOKEEP variable in /etc/nsm/securityonion.conf."

        sed -i "s|VERSION=$VERSION|VERSION=$NEW|g" $CONF        | $LOGGER
        echo "* Upgrade to $NEW complete."                      | $LOGGER
        echo 
fi

Original comment by doug.bu...@gmail.com on 14 Sep 2011 at 7:42

• Changed state: Verified
• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

Published:
http://securityonion.blogspot.com/2011/09/security-onion-20110914-now-available.
html

Original comment by doug.bu...@gmail.com on 15 Sep 2011 at 11:01

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

sguil-db-purge script should check if it is on a Sguil server and only then 
script should be run.
now in distributed server-sensor enviroinment on sensors every day you will get 
such errors:
ERROR 1049 (42000): Unknown database 'securityonion_db'
maybe sguil-db-purge script could be copied only to Sguil server

Original comment by karolis....@gmail.com on 15 Sep 2011 at 1:32

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

Good point!  It should be copied to all boxes (servers and sensors), but the 
script itself should check for an existing Sguil database before continuing.  
I'll work on adding that.

Original comment by doug.bu...@gmail.com on 15 Sep 2011 at 1:36

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

This is now fixed and should be published tomorrow morning.

Original comment by doug.bu...@gmail.com on 15 Sep 2011 at 8:48

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

Published:
http://securityonion.blogspot.com/2011/09/security-onion-20110915-now-available.
html

Original comment by doug.bu...@gmail.com on 16 Sep 2011 at 10:56

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

Changed sguil-db-purge sript a little bit. 
My securityonion database is 50gb and is growing rapidly. It takes more than 
10h the sguil-db-purge script to finish everyday. While script is working the 
Sguil server is unusable. To diminish the load of the script on mysqld service, 
I change the script so that it performs daily repairs only on todays tables and 
on Saturdays it repairs all tables of the DB. Due to this change cronjob in 
/etc/cron.d/sguil-db-purge should be changed too to run this script in the end 
of the day (e.g. 01 22   * * * root /usr/local/bin/sguil-db-purge >> 
/var/log/nsm/sguil-db-purge.log). Also for statistic purposes I would like to 
keep event table data longer than the other tables data so I moved variable 
DAYSTOKEEP to an array of days(comment out DAYSTO KEEP variable in 
/etc/nsm/securityonion.conf ). You can keep the data of different table types 
for different periods of time.
Hope someone will find it usefull too.

Karolis

Original comment by karolis....@gmail.com on 28 Oct 2011 at 8:17

• Added labels: ****
• Removed labels: ****

Attachments:

• sguil-db-purge