## 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;