package Iptparser;

use strict;
use lib '../lib'; # the path to DBhandler.pm
use DBhandler;
use Digest::MD5 qw /md5_hex/;
use Time::localtime;

{

    sub _execute {
        my $self = shift;
        my %args = @_;

        $self->_fetch_handle if (! $self->{dbo});

        $self->{_dbo}->add_sql( $args{statement} =>
                                $self->_get_statement(statement => $ar
+gs{statement})
                              );
        return $self->{_dbo}->execute(
                                      handle    => 'infomgr',
                                      statement => $args{statement},
                                      bindvar   => $args{bindvar},
                                     );


    }

    sub _get_statement {
        my $self = shift;
        my %args = @_;

        my %sql = (
                   exist => 'select count(*) from stats_iptables where
+ hash = ?',
                   add   => qq/insert into stats_iptables values (next
+val('stats_iptables_stat_id_seq'),
                               ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?
+, ?, ?, ?, ?, ?, ?, ?, ?, ?)/,
                  );

        return $sql{$args{statement}};
    }

    # Grab a db_handle on demand
    sub _fetch_handle {
        my $self = shift;
        $self->{_dbo} = DBhandler->new(
                                       handle =>'infomgr',
                                       sid    =><sid>,
                                       user   =><user>,
                                       pwd    =><passwd>,
                                      );

    }

    sub _check {
        my $self = shift;
        my %args = @_;
        return $self->_execute(handle    => 'infomgr',
                               statement => 'exist',
                               bindvar   => [$args{bindvar}],);
    }

    sub _add {
        my $self = shift;
        my %args = @_;

        return $self->_execute(handle    => 'infomgr',
                               statement => 'add',
                               bindvar   => $args{bindvar},);
    }
}

sub new {
    my $pkg = shift;


    my $self = bless { }, $pkg;
    return $self;
}

sub gather_data {
    my $self = shift;

    my $logfile = '/var/log/messages';
    local*LOGFILE;
    open (LOGFILE, $logfile) || die "Cannot open $logfile for processi
+ng: $!";

    while (<LOGFILE>) {
 
        next unless /ipt\:/;
        chomp;

        my $digest = md5_hex($_);
        my @log = split(/ /, $_);

        # Go grab the "header" information
        my ($mon, $day, $time, $host, $chain) = @log[0,2,3,4,7];

        # Go grab the rest of the log information
        my %hash;
        foreach (@log) {
             next unless ($_ =~ /=/);
             my @tmp = split(/=/, $_);
             $hash{$tmp[0]}=$tmp[1];
        }
        my $year = localtime->year() + 1900;

        my %month = (Jan=>1,Feb=>2,Mar=>3,Apr=>4,May=>5,Jun=>6,
                     Jul=>7,Aug=>8,Sep=>9,Oct=>10,Nov=>11,Dec=>12);

        my $check = $self->_check(bindvar => $digest);
        if ($check->[0] < 1) {
            my $retval = $self->_add(bindvar => ["$year-$month{$mon}-$
+day $time",$digest, $hash{IN}, $host, $chain,
                                     $hash{OUT},$hash{MAC},$hash{SRC},
+$hash{DST},$hash{LEN},$hash{TOS},
                                     $hash{PREC},$hash{TTL},$hash{ID},
+$hash{PROTO},$hash{SPT},$hash{DPT},
                                     $hash{SEQ},$hash{ACK},$hash{WINDO
+W},$hash{RES},$hash{RST},$hash{URGP}]);

        };

    }

}
1

The SQL

DROP TABLE stats_iptables;
DROP SEQUENCE stats_iptables_stat_id_seq;

CREATE TABLE stats_iptables (
                        stat_id         serial primary key,
                        date            timestamp, 
                        hash            varchar,
                        inbound         varchar,
                        host            varchar,
                        chain           varchar,
                        outbound        varchar,
                        mac             varchar,
                        src             varchar,
                        dst             varchar,
                        len             integer,
                        tos             varchar,
                        prec            varchar,
                        ttl             integer,
                        id              integer,
                        proto           varchar,
                        spt             integer,
                        dpt             integer,
                        seq             varchar,
                        ack             varchar,
                        window          varchar,
                        res             varchar,
                        rst             varchar,
                        urgp            varchar
                    );

create index hash_idx on stats_iptables (hash);
create index date_idx on stats_iptables (date);

grant select on stats_iptables to apache;
grant insert on stats_iptables to apache;
grant update on stats_iptables to apache;
grant delete on stats_iptables to apache;
grant update on stats_iptables_stat_id_seq to apache;