Fwlog2sql.pl

#!/usr/bin/perl
use strict;
use DBI qw(:sql_types);
use Digest::MD5 qw(md5 md5_hex md5_base64);

my $debug=0;
my @my_time = localtime(time());
my $year = $my_time[5]+1900;
my $dbname = "log";
my $trash;

my %months;
$months{'Jan'} = 0;
$months{'Feb'} = 2;
$months{'Mar'} = 3;
$months{'Apr'} = 4;
$months{'May'} = 5;
$months{'Jun'} = 6;
$months{'Jul'} = 7;
$months{'Aug'} = 8;
$months{'Sep'} = 9;
$months{'Oct'} = 10;
$months{'Noc'} = 11;
$months{'Dec'} = 12;

my $database = "database";
my $hostname = "localhost";
my $port = "3306";
my $user = "user";
my $password = "password";

my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
my $dbh = DBI->connect($dsn, $user, $password)
 or die "Couldn't connect to database: " . DBI->errstr;

#my $myinsert = $dbh->prepare("INSERT INTO hit(foo,bar,baz) VALUES (?,?,?)")
my $myinsert = $dbh->prepare("INSERT INTO hit ( hash, ID, TS, hostname, PROTO, TOS, CH, RULE, PREC, DST, SRC, SPT, DPT, INTERFACE, MACADDR) VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
 or die "Couldn't prepare myinsert, dbh\-\>errstr: $dbh->errstr\n";

my $myhashcheck = $dbh->prepare("SELECT count(hash) FROM hit WHERE hash = ? ")
 or die "Couldn't prepare myhashcheck, dbh\-\>errstr: $dbh->errstr\n";

my $mygethit = $dbh->prepare("SELECT hash, ID, TS, hostname, PROTO, TOS, CH, RULE, PREC, DST, SRC, SPT, DPT, INTERFACE, MACADDR FROM hit WHERE hash = ? ")
 or die "Couldn't prepare mygethit, dbh\-\>errstr: $dbh->errstr\n";

# if ( @ARGV < 1 ) { die "what to filter\?\n"; }

while(<STDIN>) {

	#print ".";

	my %record;

	# strip of meta data common to all log entries
	( $record{"month"}, $record{"date"}, $record{"time"} ,$record{"hostname"}, $record{"data"} )= split /[\s]+/,$_,5;

	chomp ( $record{data} );

	# code for Mysql's DATETIME
	# YYYY-MM-DD HH:MM:SS
	# problem : can't sort DATETIME nice
	# $record{"TS"} = sprintf( "%04d-%02d-%02d %s" , $year, $months{$record{"month"}}, $record{"date"}, $record{"time"} );

	# Mysql's timestamp
	# YYYY-MM-DD HH:MM:SS
	#my $temptime = substr($record{"time"}, 0, 1) . substr($record{"time"}, 3 ,4 ) . substr("abcdef", 6,7); 
	#$record{"time"} = $temptime;

	$record{"TS"} = sprintf( "%04d-%02d-%02d %s" , $year, $months{$record{"month"}}, $record{"date"}, $record{"time"} );

	if ( $debug ) {
		printf ( "Month: %s \n",	$record{"month"} );
		printf ( "Date: %s \n",		$record{"date"} );
		printf ( "Time: %s \n",		$record{"time"} );
		printf ( "FW name: %s \n",	$record{"hostname"} );
		printf ( "Data: %s \n",		$record{"data"} );
		printf ( "TS: %s \n",		$record{"TS"} );

	}

	my @tuples = split ( " " , $record{"data"} );
	my $variable_name ="";
	my $variable_value ="";

	foreach my $tuple (@tuples) {

		# print "Tuple: $tuple\n";
		my ( $variable_name , $variable_value ) = split ( /[=]/,$tuple );
		# print "NAME: $variable_name VALUE:$variable_value\n";

		if ( length ( $variable_value ) > 0 ) {
			$record{"$variable_name"} = $variable_value;
		}
	}

	$record{"hash"} = md5_hex($_);

	if ( $debug ) {
		print "VARDUMP\n";
		foreach my $variable_name ( keys %record ) {
			print "\t$variable_name: $record{$variable_name}\n";
		}
	}

	# check for hash
	my $rc = $myhashcheck->bind_param(1, $record{"hash"} ) or die $myhashcheck->errstr;
	if ($debug) {print "Return from myhashcheck->bind_param: $rc\n"; }
	my $count;
	$myhashcheck->execute() or die "myhashcheck->execute failed\n";
	$myhashcheck->bind_columns( undef, \$count );

	my ( $count ) = $myhashcheck->fetchrow_array();

	if ($debug) {print "Count: $count\n";}

	if ( $count < 1 ) {
		eval {
			$myinsert->bind_param( 1,  $record{"hash"}, SQL_VARCHAR);
			$myinsert->bind_param( 2,  $record{"ID"}, SQL_VARCHAR);
			$myinsert->bind_param( 3,  $record{"TS"}, SQL_VARCHAR);
			$myinsert->bind_param( 4,  $record{"hostname"}, SQL_VARCHAR);
			$myinsert->bind_param( 5,  $record{"PROTO"}, SQL_VARCHAR);
			$myinsert->bind_param( 6,  $record{"TOS"}, SQL_VARCHAR);
			$myinsert->bind_param( 7,  $record{"CH"}, SQL_VARCHAR);
			$myinsert->bind_param( 8,  $record{"RULE"}, SQL_VARCHAR);
			$myinsert->bind_param( 9,  $record{"PREC"}, SQL_VARCHAR);
			$myinsert->bind_param( 10, $record{"DST"}, SQL_VARCHAR);
			$myinsert->bind_param( 11, $record{"SRC"}, SQL_VARCHAR);
			$myinsert->bind_param( 12, $record{"SPT"}, SQL_VARCHAR);
			$myinsert->bind_param( 13, $record{"DPT"}, SQL_VARCHAR);
			$myinsert->bind_param( 14, $record{"IN"}, SQL_VARCHAR);
			$myinsert->bind_param( 15, $record{"MAC"}, SQL_VARCHAR);
			$myinsert->execute();
			# $dbh->commit();
		};

		if( $@ ) {
			warn "Database error: $DBI::errstr\n";
			$dbh->rollback(); #just die if rollback is failing
		}

	} else {
		print "----------------\n";
		print "Seen this before\n";
		print "----------------\n";
		my ($hash, $ID, $TS, $hostname, $PROTO, $TOS, $CH, $RULE, $PREC, $DST, $SRC, $SPT, $DPT, $INTERFACE, $MACADDR );
		my $rc = $mygethit->bind_param(1, $record{"hash"} ) or die $mygethit->errstr;
		if ($debug) {print "Return from mygethit->bind_param: $rc\n"; }
		$mygethit->execute();
		$mygethit->bind_columns( undef, \$hash, \$ID, \$TS, \$hostname, \$PROTO, \$TOS, \$CH, \$RULE, \$PREC, \$DST, \$SRC, \$SPT, \$DPT, \$INTERFACE, \$MACADDR );
		while ($mygethit->fetch) {
			print "seen: " . $record{"hash"}.", ".$record{"ID"}.", ".$record{"TS"}.", ".$record{"hostname"}.", ".$record{"PROTO"}.", ".$record{"TOS"}.", ".$record{"CH"}.", ".$record{"RULE"}.", ".$record{"PREC"}.", ".$record{"DST"}.", ".$record{"SRC"}.", ".$record{"SPT"}.", ".$record{"DPT"}.", ".$record{"IN"}.", ".$record{"MAC"}."\n";
			print "seen: $hash, $ID, $TS, $hostname, $PROTO, $TOS, $CH, $RULE, $PREC, $DST, $SRC, $SPT, $DPT, $INTERFACE, $MACADDR\n";
		}
	}

}