Archive for November, 2012

Cleaning OSSEC MySQL database

November 21, 2012 Leave a comment

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"

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:

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

   /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;

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:


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

Categories: Databases, Security Tags: ,

Ulogd 2.x on CentOS 6

November 8, 2012 11 comments

Windcolour – second sight
A touch of silence and the violence of dark
Illusion span – the aroma of time
Shadowlife and the scent of nothingness

(Dark Tranquillity – Insanity’s Crescendo)

Ulogd is a small deamon capable of logging iptables output from ULOG (or other targets) to various different backends. One can log into MySQL, PostgreSQL, sqlite, or plain old textual log file. I used ulogd massively on servers on CentOS 5, so I really missed the CentOS 6 version. Now, I’ve noticed ulogd 2.0.0-beta4 being available in Fedora 17, so opportunity came for me to backport it. RedHat Enterprise Linux 6 is based on Fedora 12, and luckily things haven’t gone out of reach quite yet, so backporting from latest Fedora to RHEL/CentOS 6 are still quite easy.

Binary and source packages are available in Srce RPM repository for Enterprise Linux. You can add SRCE to your yum repositories list simply by running following set of commands:

# /usr/bin/wget
# /bin/rpm --import RPM-GPG-KEY-SRCE
# /bin/rm -f RPM-GPG-KEY-SRCE
# /bin/rpm -Uvh

After this things are quite easy, just use yum and install the software:

# yum install ulogd


%d bloggers like this: