## Perl Script 
## Sundara J (2013)  IP tables parse log script for further analysis.This is a script to parse output of iptables in kern.log or other locations 
## this script lets you analyze iptables logs more easily and writes the pertinent info to a mysql DB which can be used to analyse data further.
## A line in the log file looks like below 
## Feb 17 07:09:20 xxxx kernel: [41789.436510] IN= OUT=WAN SRC=xxx.xxx.xxx.xxx DST=199.45.32.38 LEN=80 TOS=0x00 PREC=0x00 TTL=64 ID=17671 DF PROTO=UDP SPT=60040 DPT=53 LEN=60
## MySQL Create Table SQL Run this in your db -> 
## create table log (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,logtime DATETIME, chain1 varchar(100),chain2 varchar(100), src_ip varchar(100), dest_ip varchar(100), protocol varchar(100), spt varchar(10), dpt varchar(10));

##############################################################
use DateTime;
use File::Temp;
use DBI;

## mysql user database name
$db ="somedb";
## mysql database user name
$user = "root";
## mysql database password
$pass = ""; 
## user hostname : This should be "localhost" but it can be diffrent too
$host="localhost";
## Log location , change to wherever your iptables file is located 
$log_loc="/home/someplace/kern.log";

## if your log file format changes you might want to adjust the index values to yours on $values[index] 

%mon2num = qw( jan 1 feb 2 mar 3 apr 4 may 5 jun 6 jul 7 aug 8 sep 9 oct 10 nov 11 dec 12 );
@logparse="";

open (FILE, $log_loc);
 while () {
 @current_line="";
	 chomp;
	 my @values = split(" ");
         my @time_split=split(":", $values[2]);
	 my $dt = DateTime->new(
		      year       => 2013,
		      month      => $mon2num{ lc substr($values[0], 0, 3) },
		      day        => $values[1],
		      hour       => $time_split[0],
		      minute     => $time_split[1],
		      second     => $time_split[2],
		      nanosecond => 500000000,
		      time_zone  => 'America/New_York',
		  );	
	# if not mac then SRC
	if ($values[8] =~ /MAC/)
	{
	 $src_ip = $values[9];  # SRC IP
	 $dest_ip = $values[10]; # DEST IP
	} else
	{
	 $src_ip=$values[8] ;  # SRC IP
	 $dest_ip = $values[9]; # DEST IP
	}
	
	if ($values[16] =~ /DF/)
	{
			if ($values[17]=~ /PROTO/) 
			{			 
			 $proto=$values[17];
			 $spt=$values[18];
			 $dpt=$values[19];
			} 
			else 
			{
			$proto=$spt=$values[16];;
			 $spt=$values[17];
			 $dpt=$values[18];
			} 
		}
		else {
			if ($values[16]=~ /PROTO/) 
			{
			$proto=$values[16];
			 $spt= $values[17];
			 $dpt= $values[18];
			} 
			else {
	 		 $proto=$values[15];  		
			 $spt= $values[16];
			 $dpt= $values[17];
			}
	}

	@current_line = 
	   ($dt,
	     $values[6], #IN
	     $values[7], #OUT
	     $src_ip, 
	     $dest_ip,
	     $proto,
	     $spt,
	     $dpt);
 	push @logparse, [ @current_line ] ;
	 }
 close (FILE);


## Write to the DB  
$dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass);
 
for $var (@logparse)
{
	for $var1 ([ @$var ])
	{
	$query = "insert into log values('','@$var1[0]','@$var1[1]','@$var1[2]','@$var1[3]','@$var1[4]','@$var1[5]','@$var1[6]','@$var1[7]')";
	$sqlQuery  = $dbh->prepare($query) or die "Can't prepare $query: $dbh->errstr\n";
	$rv = $sqlQuery->execute or die "can't execute the query: $sqlQuery->errstr";	

	}
}
$rc = $sqlQuery->finish;
 exit;