Debian: Building “IPTables log analyzer” from the sources (V0.4)

In the search for a clear log display for iptables I found the page http://www.gege.org/iptables/ from Gerald Garcia.

Essentially it consists of a perl script, which writes new entries in the iptables log into a sql database.

The database can run on the firewall, but I would like to install it on my management server on which the fwbuilder runs.

Thus the logging of the firewalls can be centralized.

In the following guide I have used the version 0.4.

There is a version 0.9 at this address: https://sourceforge.net/projects/iptablelog/files/

This version was developed by Daniel Tarbuck. I will look at this version later and make a comparison.

Management Server

Installation and preparation of mysql, apache and php

apt-get install mysql-server mysql-client
apt-get install apache2
apt-get install php5 libapache2-mod-php5
apt-get install php5-mysql
service apache2 restart

To test php a simple php.info script is sufficient

vim /var/www/html/info.php

Put the following lines into the file


Go to http:///info.php
If everything is installed correctly, the following page should be displayed:

phpinfo

You should delete the file afterwards.

Create a database

mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.5.54-0+deb8u1 (Debian)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database iptables;
Query OK, 1 row affected (0.00 sec)

Create users

I work with 2 users, one for the web server and one for the feed_db.pl.

Create user for webinterface

mysql> grant select,insert on iptables.* to iptables_www identified by '1q2w3e4r';
mysql> grant create temporary tables on iptables.* TO iptables_www identified by '1q2w3e4r';

Create user for feeder:

grant insert on iptables.logs to iptables_feeder identified by '1q2w3e4r';

Fill database with the necessary tables and data

In the tar archive is in the folder sql a script for the import.First, the keyword TYPE must be replaced by ENGINE in the file db.sql!

cat db.sql | mysql -u root -p iptables

Check whether everything has been created correctly

mysql -u root -p iptables
mysql> show tables;
+--------------------+
| Tables_in_iptables |
+--------------------+
| logs  |
| ports |
+--------------------+
2 rows in set (0.00 sec)

 select * from ports;

mysql> SHOW FIELDS FROM logs;
+--------------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------------------+-------+
| host | varchar(200) | NO | | | |
| date | datetime | NO | MUL | 0000-00-00 00:00:00 | |
| chain | varchar(50) | NO | MUL | DROP | |
| interface_in | varchar(50) | NO | | | |
| ip_src | varchar(50) | NO | MUL | | |
| name_src | varchar(200) | NO | | | |
| ip_dest | varchar(50) | NO | | | |
| name_dest | varchar(200) | NO | | | |
| proto | varchar(50) | NO | | | |
| port_src | int(11) | NO | | 0 | |
| port_dest | int(11) | NO | MUL | 0 | |
+--------------+--------------+------+-----+---------------------+-------+
11 rows in set (0.00 sec)

Allow remote access

Edit /etc/mysql/my.cnf and change the ip address

bind-address = 192.168.1.119

Restart service

service mysql restart

Install the web interface

Copy all files from web directory:

cp -R web/* /var/www/html

And configure the config.php file according to your database settings.

# Host of the MySQL database
$db_host="localhost";

# User of the MySQL database
$db_user="iptables_www";

# Password of the MySQL database
$db_password="1q2w3e4r";

# Name of the database
$db_name="iptables";

Firewall

Install database feeder.

Install prerequisites:

perl -MCPAN -e 'install DBD::Pg'
perl -MCPAN -e 'install DBD::mysql'

Alternatively:

apt-get install libdbd-mysql-perl

All necessary files are in the scripts directory. Copy them into the appropriate directories:

cp feed_db.pl /usr/local/bin/
cp iptablelog /etc/init.d/

Modify the config section in /usr/local/bin/feed_db.pl

my $dsn = 'DBI:mysql:iptables:192.168.1.119';
my $db_user_name = 'iptables_feeder';
my $db_password = '1q2w3e4r';
my $log_file = '/var/log/netfilter.log';
my $pid_file = "/var/run/iptablelog.pid";

Because of my German environment there was a problem in the feed_db.pl

To convert the monthly abbreviations in the iptables log into the correct numbers, a hashtable is used. The values are generated with strftime.

Jun-06
Jan-01
Sep-09
Feb-02
Nov-11
Jul-07
Aug-08
Mai-05
Mär-03
Okt-10
Dez-12
Apr-04

The problem is the March, in German actually with “Mär” abbreviated. In the logfile unfortunately with “Mar“.

Mar 26 21:35:18 debian-iptables1 kernel: [10470.701942] [NETFILTER ACCEPT] IN= OUT=eth0 SRC=192.168.1.112 DST=192.168.1.109 LEN=72 TOS=0x00 PREC=0x00 TTL=64 ID=38441 DF PROTO=UDP SPT=483

My quick solution to solve the problem:

# get the short name of months according to the locale
# thanks to Bill Garrett 
#my(%m);
#my($month_nb);
#for $month_nb (0..11) {
# $m{strftime("%b", 0, 0, 0, 1, $month_nb, 96)}=sprintf("%02d",$month_nb+1);
#}

#Joeds alternativ for german month

my(%m) = ("Jan" => "01","Feb" => "02","Mar" => "03","Apr" => "04","Mai" => "05","Jun" => "06","Jul" => "07","Aug" => "08","Sep" => "09","Okt" => "10","Nov" => "11","Dez" => "12");

Checks

To test the function of the script it should be started by hand first.

root@debian-iptables1 ~ # /usr/local/bin/feed_db.pl
2017-03-26 21:35:18
'debian-iptables1','2017-03-26 21:35:18','[NETFILTER','','192.168.1.112','unknown','192.168.1.109','synology.dehm.local','UDP','48337','53'
2017-03-26 21:35:18

Afterwards, the database should be checked to see if the table logs were filled.

root@debian-fwbuilder ~ # mysql -u root -p iptables
Enter password:
mysql> select count(*) from logs;
+----------+
| count(*) |
+----------+
| 3056 |
+----------+
1 row in set (0.00 sec)
mysql> select * from logs;


The entries should now also be displayed in the web interface.

iptablesloganalyzer

Advertisements

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