#!/usr/bin/env perl
#
# /usr/sbin/myiptstate - A simple iptstate-to-mysql logger.
#
# This script is intended to run in a background process (or as a daemon),
# and collect iptables state data to help monitor and detect possible
# intrusions on the LAN.
#
# Execution Example:
# [root@mybox ]# myiptstate &
#
# Sean O'Donnell 
#
# $Id: myiptstate.pl,v 1.1 2011/03/14 11:29:25 seanodonnell Exp $
#
use strict;
use DBI;

# number of seconds between firewall-queries
my $interval=10;

# iptstate command
my $iptstate = '/usr/sbin/iptstate -1';

# mysql database configuration
my $dbconfig = {
        hostname        => 'localhost',
        username        => 'myiptstate',
        password        => 'myiptstate_pass',
        db              => 'myiptstate',
};

my $dsn = 'DBI:mysql:'. $dbconfig->{'db'} .':'.$dbconfig->{'hostname'};

my $db = DBI->connect($dsn, $dbconfig->{'username'}, $dbconfig->{'password'})
        or return 'Connection Error: $DBI::err($DBI::errstr)';

# begin infinite loop. this script should run in the background (or as a daemon)

while (1==1)
{
        # infinite loop... a scripted-daemon
        open(IPTS,$iptstate ."|") || die "Cannot execute ". $iptstate .":\n". $!;

        while (my $line = <IPTS>)
        {
                if ($line =~ /^[0-9]/)
                {
                        #       print $line;
                        my ($source,$destination,$protocol,$state,$duration) = split(" ",$line);

                        my ($source_ip,$source_port) = split(":",$source);
                        my ($destination_ip,$destination_port) = split(":",$destination);

                        if ($state =~ /^[0-9]/)
                        {
                                # no state-data. duration-data landed here (during parsing) instead.
                                $duration = $state;
                                $state = "";
                        }

                        my $sql = "INSERT INTO iptstate
                                (source_ip,source_port,destination_ip,destination_port,protocol,state,duration) VALUES
                                ('$source_ip','$source_port','$destination_ip','$destination_port','$protocol','$state','$duration')";

                        my $db_query = $db->prepare($sql)
                                or return 'SQL Error: $DBI::err($DBI::errstr)';

                        $db_query->execute()
                                or return 'Query Error: $DBI::err($DBI::errstr)';

                        # my $data_id = $db_query->last_insert_id();

                        $db_query->finish();

                }
        }
        # close the application
        close(IPTS);
        sleep($interval);
}
$db->disconnect();