#!/usr/bin/perl
#
#
# Script      : ipchainslogs2mysql
# Description : reads the syslog file ARGV[0], extracts the ipchains log
#               entries from it and dumps them into the table ipchainslogs
# Usage       : ipchainslogs2mysql <logfile>
#
# Note        : Read the comment about replaced the database connection
#               information with the correct values at the end of this file.
#
# Author      : Alexander Schreiber <als@thangorodrim.de>
#
# RCS         : $Id: ipchainslog2mysql,v 1.3 2000/02/29 19:29:25 als Exp $
#

use DBI;
use DBD::mysql;

$Records = 0;

sub InitDB {

  my $DbName = shift;
  my $DbServer = shift;
  my $DbUser = shift;
  my $DbPasswd = shift;

  $DataBase = DBI->connect("DBI:mysql:$DbName:$DbServer", $DbUser, $DbPasswd);

  unless ( $DataBase ) {
    die "Unable to connect to DataBase !\n";
  }

}


sub loadlogdata {
  
  my $workline;
  my @work;
  my $SLQ, $return;
  my $chain, $action, $interface, $protocol, $source_ip, $source_port,
     $dest_ip, $dest_port, $length, $tos, $fragment_offset, $flags, 
     $ttl, $rule, $syslog_time;

  open(LOG, $ARGV[0]) or die "Cannot open $ARGV[0]\n!";

  while ( $workline = <LOG> ) {
    if ( $workline =~ /kernel: Packet log/ ) {
      $workline =~ s/\n//;
      @work = split ' ', $workline;
      $syslog_time = shift(@work);
      $syslog_time .= " ";
      $syslog_time .= shift(@work);
      $syslog_time .= " ";
      $syslog_time .= shift(@work);
      shift(@work);
      shift(@work);
      shift(@work);
      shift(@work);
      $chain = shift(@work);
      $action = shift(@work);
      $interface = shift(@work);
      $work = shift(@work);
      $work =~ /PROTO=([0-9]+)/;
      $protocol = $1;
      $work = shift(@work);
      ($source_ip, $source_port) = split /:/, $work;
      $work = shift(@work);
      ($dest_ip, $dest_port) = split /:/, $work;
      $work = shift(@work);
      $work =~ /L=([0-9]+)/;
      $length = $1;
      $work = shift(@work);
      $work =~ /S=([0-9]+)/;
      $tos = $1;
      $work = shift(@work);
      $work =~ /I=([0-9]+)/;
      $fragment_offset = $1;
      $work = shift(@work);
      $work =~ /F=([0-9]+)/;
      $flags = $1;
      $work = shift(@work);
      $work =~ /T=([0-9]+)/;
      $ttl = $1;
      $work = pop(@work);
      $work =~ /\(#([0-9]+)/;
      $rule = $1;
      $rule_flags = shift(@work);

      $syslog_time = $DataBase->quote($syslog_time);
      $chain = $DataBase->quote($chain);
      $action = $DataBase->quote($action);
      $interface = $DataBase->quote($interface);
      $protocol = $DataBase->quote($protocol);
      $source_ip = $DataBase->quote($source_ip);
      $source_port = $DataBase->quote($source_port);
      $dest_ip = $DataBase->quote($dest_ip);
      $dest_port = $DataBase->quote($dest_port);
      $length = $DataBase->quote($length);
      $tos = $DataBase->quote($tos);
      $fragment_offset = $DataBase->quote($fragment_offset);
      $flags = $DataBase->quote($flags);
      $ttl = $DataBase->quote($ttl);
      $rule_flags = $DataBase->quote($rule_flags);
      $rule = $DataBase->quote($rule);

      $SQL = "INSERT INTO ipchainslogs (syslog_time,chain,action,interface,protocol,source_ip,source_port,dest_ip,dest_port,length,tos,fragment_offset,flags,ttl,rule) values ($syslog_time, $chain, $action, $interface, $protocol, $source_ip, $source_port, $dest_ip, $dest_port, $length, $tos, $fragment_offset, $flags, $ttl, $rule);";

      
      $return = $DataBase->do($SQL);
      unless ( $return = 1 ) {
        print "insert returned |$return|\n";
      }

    }

  }

}



##MAIN##

unless ( -f $ARGV[0] ) {
  die "expecting name of logfile as first commandline parameter  !\n";
}

# replace DB_NAME, DB_HOST, DB_USER and DB_PASSWD below with the values
# appropriate for your environment

&InitDB('DB_NAME', 'DB_HOST', 'DB_USER', 'DB_PASSWD');

&loadlogdata();