Home > Databases, Security > Cleaning OSSEC MySQL database

Cleaning OSSEC MySQL database

If I had wings, would I be forgiving?
If I had horns
Would there be flames to shy my smile?

(Dark Tranquillity – Punish my heaven)

OSSEC is a good intrusion detection tool  that can help pinpoint not only security breaches but general software anomalies too. It can be installed as standalone and as server-client tool. Server can write data about alerts to database (usually it’s MySQL). But, on a large network one has to be really careful about using database as an alert datastore. Things can get out of control quite quickly.
Here is how current setup looks like on one of the installations that I help managing:

# /var/ossec/bin/agent_control -l | grep -c "^\s*ID"
149

So, this means that this server controls 149 agents. Average on this installation is about 300 alerts per day, but that is mainly due to lack of spare time available for optimization of custom rules. Nevertheless, all of the alerts go to the MySQL database too. So, by doing simple arithmetic we should be at around 55’000 database entries? Well, not quite, because OSSEC also logs events for which no alert will be sent… So this number is huge – take a look:

mysql> select count(*) from alert;
+-----------+
| count(*)  |
+-----------+
| 123008768 |
+-----------+

So this means that there is 675’000 rows inserted to MySQL per day! And all that goes to MyISAM by default OSSEC schema! Database is around 35GB, so it was time to do something about it. We decided to shrink the database to 32 days, and to purge the rest. Now, little shell magic comes to play. I’ve created a script that will be called with cron daily job. This is how the script looks like:

#!/bin/bash
DELETE2TIME=`/bin/date -d "32 days ago" "+%Y-%m-%d %H:%M:%S"`

getvaluefromossec()
{
   /bin/grep "<$1>" /var/ossec/etc/ossec.conf | /bin/sed "s|^.*<$1>\(.*\)</$1>|\1|g"
}

MYSQLHOST=`getvaluefromossec "hostname"`
MYSQLDB=`getvaluefromossec "database"`
MYSQLUSER=`getvaluefromossec "username"`
MYSQLPASS=`getvaluefromossec "password"`

echo "
SET @delete2time=\"$DELETE2TIME\";
DELETE FROM alert WHERE timestamp < UNIX_TIMESTAMP(@delete2time);
DELETE FROM data WHERE timestamp < @delete2time;
" | mysql -h$MYSQLHOST -u$MYSQLUSER -p$MYSQLPASS -D$MYSQLDB

You can simply put this script to /etc/cron.daily and it will run every day and purge only 1 day of old data. You can modify the number in the date command invocation by how far back do you want to keep your data. In this example data is kept 32 days.

Note: first run may take a long time if you have large database and want to purge lot of rows 😉 In my case – deleting of month of history entries (22.5 million rows in two tables) took 45 minutes!

Note2: I almost forgot… You should OPTIMIZE TABLE after big deletes to free disk space occupied by MyISAM tables. This can be done in cron.daily script, by adding these two lines:

echo "OPTIMIZE TABLE alert;" | mysql -h$MYSQLHOST -u$MYSQLUSER -p$MYSQLPASS -D$MYSQLDB
echo "OPTIMIZE TABLE data;" | mysql -h$MYSQLHOST -u$MYSQLUSER -p$MYSQLPASS -D$MYSQLDB

although I would strongly recommend to organize these kind of tasks in cooperation with DBA.

Advertisements
Categories: Databases, Security Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

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: