Firewall logging to MySQL - the quick and easy way

By Anonymous

Security is a journey, not a destination. One good step along the way is to review and analyze your firewall logs and syslog messages on a regular basis. Unfortunately, the plain text logs produced by syslog are not in a form that is easily analyzed. Also, unless you are using syslog-ng, your firewall logs are probably scattered all over the various system message log files.

This article will show you how to move your firewall logs from syslog text files to a MySQL database in 10 minutes or so. The following examples were carried out on a SuSE 10.0 system but you can easily adapt them for other distributions.

1. Verify kernel settings

You can skip this step if you are using the default SuSE 10.0 kernel. The stock kernels that come with most distributions should be fine, but you will need to make sure you have your kernel compiled with the CONFIG_NETFILTER, CONFIG_IP_NF_IPTABLES, CONFIG_IP_NF_FILTER, and CONFIG_IP_NF_TARGET_ULOG options. Most firewalls will also need CONFIG_IP_NF_CONNTRACK, CONFIG_IP_NF_FTP, and CONFIG_IP_NF_IRC.

If you have a file called /proc/config.gz, it means your kernel was compiled with the IKCONFIG option. /proc/config.gz is the compressed version of the .config file that was used to generate that kernel, so you can check if you have the necessary options for netfilter and ulog with this command:

gunzip -c /proc/config.gz | grep -E 'CONFIG_(NETFILTER|(IP_NF_(IPTABLE|FILTER|TARGET_ULOG)))'

If they are not set as modules or compiled into the kernel you will need to change them and recompile the kernel. In menuconfig the following options need to be set:

Networking options > Network packet filtering
Networking options > Netfilter Configuration > IP tables support
Networking options > Netfilter Configuration > Packet filtering
Networking options > Netfilter Configuration > ULOG target support

You might also want to verify that iptables is compiled with ulog support.

2.1. Install MySQL

You can go directly to 2.2. if you have MySQL already setup. Otherwise:

apt install mysql
/etc/init.d/mysql restart
chkconfig mysql on

If you are using SuSE and do not have apt4rpm installed on your system, I highly recommend that you do so, as it will greatly simplify your package management issues.

You also need to set a password for the MySQL root user:

mysqladmin -u root password 'yourpassword'

2.2. Initialize the database

Type in:

mysql -p -u root

then enter your password at the prompt. Once you have logged into your MySQL database, enter the following commands to prepare the database to receive firewall logs from ulog.

create database ulogdb;
use ulogdb;
source /path/to/nulog/scripts/ulogd.mysqldump;
grant select,insert,update,drop,delete,create temporary tables, on ulogdb.* to ulog@localhost identified by 'ulogpass';
flush privileges;
quit;

So what happened here?

• We created a database ulogdb to host our logs
• We executed the SQL script ulogd.mysqldump, preparing the database for nulog-php, it allows to store more information than the MySQL table provided with ulogd, and you can find it in the scripts directory of nulog-php or right here.
• We created an user “ulog” (with pass “ulogpass”) to have read/write access to that database. I highly recommend that you enter a different password than this example uses.

3.1. Install ulogd

You will need to install the logging daemon ulogd:

apt install ulogd-mysql

3.2. Configure ulogd.conf

Edit /etc/ulogd.conf to match what we set up previously:

[MYSQL]
table="ulog"
pass="ulogpass"
user="ulog"
db="ulogdb"
host="localhost"

You should change the password “ulogpass” to the password you set in the GRANT command in your MySQL database. Now uncomment the following line to send the data to MySQL:

plugin /usr/lib/ulogd/ulogd_MYSQL.so

and comment out the following two lines to prevent logging to a text file:

#syslogfile /var/log/ulogd.syslogmenu
#plugin /usr/lib/ulogd/ulogd_LOGEMU.so

Now restart the ulogd daemon and set it to be automatically started at boot time with chkconfig:

/etc/init.d/ulogd restart
chkconfig ulogd on

4. Redirect iptables Logging

The following sed command switches all your iptables rules to log through ULOG, we will assume that you store your iptables ruleset in a file called “iptables” (usually in /etc/sysconfig/ or /var/lib/)

sed 's/LOG/ULOG/'; /etc/sysconfig/iptables > /etc/sysconfig/uiptables
iptables-restore < /etc/sysconfig/uiptables

You are now all set up! All the logs from your firewall are now being logged in your MySQL database. Don't forget to update your firewall startup script so the new iptables are taken into account.

5. Import Your Old Logs

So far, so good, but you probably would like to have your old logs in MySQL also. Here is a little perl script to allow you to import your old text logs to MySQL. Some of the regexps are reused from adcfw-log. You can usually find your netfilter logs in /var/log/firewall-XXXXXX.gz or /var/log/messages-XXXXXX.gz. To import:

gunzip -c /var/log/firewall-XXXXXX.gz | nf2sql.pl

Repeat for each of your other log files. To process a current log file (or other uncompressed log file) such as /var/log/messages or /var/log/firewall:

nf2sql.pl < /var/log/messages

That's it!

6. Analyze the Results

To analyze your logs in MySQL you can use nulog or webfwlog

Links

This article was partly inspired by this article (only available in Spanish).

The original ulog page can be found here.

If you want to push it further and log all system messages to MySQL, you can take a look at this HOWTO setup PHP syslog-ng wiki entry.

Here is a reason to move away from the usual text file logging.

If you do not have your iptables set already, you can easily build a good ruleset with shorewall, firehol or firestarter.

 

---

A. N. Onymous has been writing for LG since the early days - generally by sneaking in at night and leaving a variety of articles on the Editor's desk. A man (woman?) of mystery, claiming no credit and hiding in darkness... probably something to do with large amounts of treasure in an ancient Mayan temple and a beautiful dark-eyed woman with a snake tattoo winding down from her left hip. Or maybe he just treasures his privacy. In any case, we're grateful for his contribution.
-- Editor, Linux Gazette