Keeping OSSIM DB tables in check


In every OSSIM install I have done I’ve found the built in system for keeping the database size to manageable level doesn’t work very well. Eventually several tables gobble up all the disk space and fill the mysql partition. To make matters worse the default install uses a single ibdata file which doesn’t get released when you delete entries from the table.

So, I wrote this script to periodically check several problem tables and remove the oldest entries when they get to a certain size. What this size is depends on your environment. The problem tables I found to be were:

  • alienvault.extra_data
  • alienvault_siem.extra_data
  • alienvault.host_qualification
  • alienvault_siem.acid_event

I’ve just copied and pasted the same code 4 times as I was feeling lazy. You can find the mysql root password in /etc/ossim/ossim_setup.conf. Input that into the MYSQL_PASS variable. By default to script looks for when the number of rows in the table is greated than the THRESHOLD_ROWS value of 5000000. It will remove the number of rows that are more than this up to 5000000 in one go, so you should probably set this to run frequently, say hourly in a busy system.


#!/bin/bash
MYSQL_USER="root"
MYSQL_PASS="123456789"
THRESHOLD_ROWS=5000000
TARGET_DB="alienvault"
TARGET_TABLE="extra_data"
TARGET_COL_ORDER="event_id"
ROWS_PER_RUN=5000000

echo "Getting row count from table $TARGET_DB.$TARGET_TABLE"
numrows=`mysql -u $MYSQL_USER --password=$MYSQL_PASS -e "select count($TARGET_COL_ORDER) from $TARGET_DB.$TARGET_TABLE" | grep -v count | grep -v "-"`

echo "Got $numrows rows from table $TARGET_DB.$TARGET_TABLE"

if [ $numrows -gt $THRESHOLD_ROWS ]
then
        let "rowstodelete = $numrows - $THRESHOLD_ROWS"
        if [ $rowstodelete -gt $ROWS_PER_RUN ]
        then
                echo "Deleting $ROWS_PER_RUN from table $TARGET_DB.$TARGET_TABLE"
                result=`mysql -u $MYSQL_USER --password=$MYSQL_PASS -e "delete from $TARGET_DB.$TARGET_TABLE order by $TARGET_COL_ORDER ASC LIMIT $ROWS_PER_RUN"`
        else
                echo "Deleting  $rowstodelete from table $TARGET_DB.$TARGET_TABLE"
                result=`mysql -u $MYSQL_USER --password=$MYSQL_PASS -e "delete from $TARGET_DB.$TARGET_TABLE order by $TARGET_COL_ORDER ASC LIMIT $rowstodelete"`
        fi
        echo "Result=$result (blank is good)"
else
        echo "Number of rows = $numrows which not more than $THRESHOLD_ROWS so nothing to do."
fi

TARGET_DB="alienvault_siem"
TARGET_TABLE="extra_data"
TARGET_COL_ORDER="event_id"

echo "Getting row count from table $TARGET_DB.$TARGET_TABLE"
numrows=`mysql -u $MYSQL_USER --password=$MYSQL_PASS -e "select count($TARGET_COL_ORDER) from $TARGET_DB.$TARGET_TABLE" | grep -v count | grep -v "-"`

echo "Got $numrows rows from table $TARGET_DB.$TARGET_TABLE"

if [ $numrows -gt $THRESHOLD_ROWS ]
then
        let "rowstodelete = $numrows - $THRESHOLD_ROWS"
        if [ $rowstodelete -gt $ROWS_PER_RUN ]
        then
                echo "Deleting $ROWS_PER_RUN from table $TARGET_DB.$TARGET_TABLE"
                result=`mysql -u $MYSQL_USER --password=$MYSQL_PASS -e "delete from $TARGET_DB.$TARGET_TABLE order by $TARGET_COL_ORDER ASC LIMIT $ROWS_PER_RUN"`
        else
                echo "Deleting  $rowstodelete from table $TARGET_DB.$TARGET_TABLE"
                result=`mysql -u $MYSQL_USER --password=$MYSQL_PASS -e "delete from $TARGET_DB.$TARGET_TABLE order by $TARGET_COL_ORDER ASC LIMIT $rowstodelete"`
        fi
        echo "Result=$result (blank is good)"
else
        echo "Number of rows = $numrows which not more than $THRESHOLD_ROWS so nothing to do."
fi

TARGET_DB="alienvault"
TARGET_TABLE="host_qualification"
TARGET_COL_ORDER="hex(host_id)"

echo "Getting row count from table $TARGET_DB.$TARGET_TABLE"
numrows=`mysql -u $MYSQL_USER --password=$MYSQL_PASS -e "select count(host_id) from $TARGET_DB.$TARGET_TABLE" | grep -v count | grep -v "-"`

echo "Got $numrows rows from table $TARGET_DB.$TARGET_TABLE"

if [ $numrows -gt $THRESHOLD_ROWS ]
then
        let "rowstodelete = $numrows - $THRESHOLD_ROWS"
        if [ $rowstodelete -gt $ROWS_PER_RUN ]
        then
                echo "Deleting $ROWS_PER_RUN from table $TARGET_DB.$TARGET_TABLE"
                result=`mysql -u $MYSQL_USER --password=$MYSQL_PASS -e "delete from $TARGET_DB.$TARGET_TABLE order by $TARGET_COL_ORDER ASC LIMIT $ROWS_PER_RUN"`
        else
                echo "Deleting  $rowstodelete from table $TARGET_DB.$TARGET_TABLE"
                result=`mysql -u $MYSQL_USER --password=$MYSQL_PASS -e "delete from $TARGET_DB.$TARGET_TABLE order by $TARGET_COL_ORDER ASC LIMIT $rowstodelete"`
        fi
        echo "Result=$result (blank is good)"
else
        echo "Number of rows = $numrows which not more than $THRESHOLD_ROWS so nothing to do."
fi

TARGET_DB="alienvault_siem"
TARGET_TABLE="acid_event"
TARGET_COL_ORDER="timestamp"

echo "Getting row count from table $TARGET_DB.$TARGET_TABLE"
numrows=`mysql -u $MYSQL_USER --password=$MYSQL_PASS -e "select count($TARGET_COL_ORDER) from $TARGET_DB.$TARGET_TABLE" | grep -v count | grep -v "-"`

echo "Got $numrows rows from table $TARGET_DB.$TARGET_TABLE"

if [ $numrows -gt $THRESHOLD_ROWS ]
then
        let "rowstodelete = $numrows - $THRESHOLD_ROWS"
        if [ $rowstodelete -gt $ROWS_PER_RUN ]
        then
                echo "Deleting $ROWS_PER_RUN from table $TARGET_DB.$TARGET_TABLE"
                result=`mysql -u $MYSQL_USER --password=$MYSQL_PASS -e "delete from $TARGET_DB.$TARGET_TABLE order by $TARGET_COL_ORDER ASC LIMIT $ROWS_PER_RUN"`
        else
                echo "Deleting  $rowstodelete from table $TARGET_DB.$TARGET_TABLE"
                result=`mysql -u $MYSQL_USER --password=$MYSQL_PASS -e "delete from $TARGET_DB.$TARGET_TABLE order by $TARGET_COL_ORDER ASC LIMIT $rowstodelete"`
        fi
        echo "Result=$result (blank is good)"
else
        echo "Number of rows = $numrows which not more than $THRESHOLD_ROWS so nothing to do."
fi

You can also download the script here.

Advertisements
Tagged ,

One thought on “Keeping OSSIM DB tables in check

  1. Yurii says:

    Did you analyze what are mentioned tables are stored?
    – alienvault.extra_data
    – alienvault_siem.extra_data
    – alienvault.host_qualification
    – alienvault_siem.acid_event

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: