...making Linux just a little more fun!
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
syslog are not in a form that is easily
analyzed. Also, unless you are using
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.
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_IP_NF_TARGET_ULOG options. Most firewalls will
/proc/config.gz, it means your kernel was compiled with the IKCONFIG option.
/proc/config.gzis the compressed version of the
.configfile that was used to generate that kernel, so you can check if you have the necessary options for
ulogwith 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
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'
mysql -p -u rootthen 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?
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.
apt install ulogd-mysql
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:
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
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.
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.plRepeat 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/messagesThat's it!
This article was partly inspired by this article (only available in Spanish).
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.
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