#!/usr/bin/perl
#
# Title: 	TCPdump 2 SQL
#
# File: 	tcpdump2sql.pl
#
# Version: 	1.0
#
# Written by:	raffael.marty@arcsight.com (ram)
#
# Description:	Takes a snort binary log file and populates a MySQL database.
# 		Needs the schema described in createDB.sql. 
#
# Comments:	Make sure line 25 has the correct credentials to access your 
# 		database.
#
# Usage:	tcpdump -vttttnnelr /tmp/sans | tcpdump2sql.pl
#			
# URL:		http://afterglow.sourceforge.net
#
# Changes:	
# 
# 08/28/04	Initial Version by ram
#
###############################################################################/

use strict;
use DBI; 

my $dbh = DBI->connect('DBI:mysql:tcpdump:localhost', 'root', 'pass')
	or die "Couldn't connect to database: " . DBI->errstr;

# prepare some queries
my $query = qq{insert into sans set timestamp=?, sourcemac=?, destmac=?, sourceip=?, destip=?, sourceport=?, destport=?, tcpflags=?, length=?, proto=?, ttl=?, ipid=?, offset=?, iptos=?, ipflags=?};
my $sth = $dbh->prepare($query) or die ("SQL error: ").$dbh->errstr;

# 2002-08-24 05:34:18.634488 00:00:0c:04:b2:33 > 00:03:e3:d9:26:c0, ethertype IPv4 (0x0800), length 223: IP (tos 0x0, ttl 122, id 544, offset 0, flags [DF], length: 209, bad cksum ff6b (->14ac)!) 138.97.18.88.61924 > 64.4.12.158.1863: P [bad tcp cksum 6c66 (->aca6)!] 9384:9553(169) ack 9641 win 17390
# 2002-08-24 10:52:42.184488 00:03:e3:d9:26:c0 > 00:00:0c:04:b2:33, ethertype IPv4 (0x0800), length 60: IP (tos 0x0, ttl 232, id 0, offset 8896, flags [+, DF], length: 40, bad cksum ff99 (->b4d9)!) 192.9.100.88 > 138.97.10.219: tcp

while (<STDIN>) {
	chomp;
	my $input = $_;
	#print $input."\n";
	if ($input =~ /(.*) \(tos (\S+), ttl +(\d+), id (\d+), offset (\d+), flags ([\S\+]+)(?:, ([\S\+]+))?, length: (\d+)(?:, .*?)?\) (.*)/) {
		$input = "$1 $2 $3 $4 $5 $6$7 $8 $9 $10";
		#print $1." 22222: $4\n\n";
	} else {
		print "ERROR: $input\n";
	}

	my @fields = split (" ",$input);
	
	# some sanitization
	
	# concatenate two timestamp fields
	my $timestamp = $fields[0]." ".$fields[1];
	$timestamp =~ s/(.*?)\.\d+$/\1/;

	my $sourcemac = $fields[2];
	my $destmac = $fields[4];
	$destmac =~ s/,//g;

	$fields[4] =~ s/,$//;

	my $len = $fields[9];
	$len =~ s/:$//;

	my $tos = $fields[11];

	my $ttl = $fields[12];
	my $id = $fields[13];
	my $offset = $fields[14];
	my $ipflags = $fields[15];
	$ipflags =~ s/\[(.*)\]/\1/g;	# getting rid of square brackets

	my $sip = $fields[17]; $sip =~ s/([^\.]+\.[^\.]+\.[^\.]+\.[^\.]+).*/\1/; $sip =~ s/:$//;

	my $sport = $fields[17]; 
	if ($sport =~ /[^\.]+\.[^\.]+\.[^\.]+\.[^\.]+\.(.*)/) {
		$sport = $1;
		$sport =~ s/:$//;
	} else { $sport = "null"; }

	my $dip = $fields[19]; $dip =~ s/([^\.]+\.[^\.]+\.[^\.]+\.[^\.]+).*/\1/; $dip =~ s/:$//;

	my $dport = $fields[19]; 
	if ($dport =~ /[^\.]+\.[^\.]+\.[^\.]+\.[^\.]+\.(.*)/) {
		$dport = $1;
		$dport =~ s/:$//;
	} else { $dport = "null"; }

	my $proto = "null"; #$fields[20];
	my $flags = $fields[20] if ($fields[20] =~ /[SRPU.]+/);
	my $proto = "tcp" if ($fields[20] =~ /[SRPU.]+/);

	#print "$timestamp $sourcemac $destmac $sip $dip $sport $dport $flags $len $proto $ttl $id $offset $tos $ipflags\n";
	
	$sth->execute($timestamp,$sourcemac,$destmac,$sip,$dip,$sport,$dport,$flags,$len, $proto, $ttl, $id, $offset, $tos, $ipflags );
}

$dbh->disconnect();