#!/usr/bin/perl
#
# Title: 	Augment DB with flow-information
#
# File: 	snortalert.pl
#
# Version: 	1.0
#
# Written by:	raffael.marty@arcsight.com (ram)
#
# Comments:	This takes a snort rules file and assumes a database with all the 
# 		Snort binary logs in it. Then it will go through the rules file and 
# 		update the service-flag in the DB if it found the packet in the 
# 		alert
#
# Usage:	cat alert | /home/ram/afterglow/src/perl/snortalert.pl
#			
# URL:		http://afterglow.sourceforge.net
#
# Changes:	
# 
# 09/22/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{update sans set snort_alert=? where timestamp=? and sourcemac=? and destmac=? and sourceip=? and destip=? and sourceport=? and destport=? and ttl=?};
#my $query = qq{select * from sans where timestamp=? and sourcemac=? and destmac=? and sourceip=? and destip=? and sourceport=? and destport=? and ttl=?};
my $sth = $dbh->prepare($query) or die ("SQL error: ").$dbh->errstr;

#[**] [1:540:11] CHAT MSN message [**]
#[Classification: Potential Corporate Privacy Violation] [Priority: 1] 
#08/23-22:22:53.884488 0:0:C:4:B2:33 -> 0:3:E3:D9:26:C0 type:0x800 len:0xC1
#138.97.18.88:61924 -> 64.4.12.158:1863 TCP TTL:122 TOS:0x0 ID:127 IpLen:20 DgmLen:179 DF
#***AP*** Seq: 0x46F128B3  Ack: 0x359A173D  Win: 0x4062  TcpLen: 20

my $sig_message = 0;
my ($timestamp, $sourcemac, $destmac, $sip, $dip, $sport, $dport, $ttl);

while (<STDIN>) {
	chomp;
	my $input = $_;

	# print $input."\n";

	if ($input =~ /^\[\*\*\] (.*) \[\*\*\]$/) {

		$sig_message = $1;

	} elsif ($input =~ /^(\d{2})\/(\d{2})-(\d+\:\d+:\d+)\..* (.*) -> (.*) type:.* len:.*$/) {

		$timestamp = "2002-".$1."-".$2." ".$3;

		$sourcemac = mac($4);
		
		$destmac = mac($5);

		#print "TS: $timestamp $sourcemac $destmac \n";

	} elsif ($input =~ /^(\d+\.\d+\.\d+\.\d+):(\d+) -> (\d+\.\d+\.\d+\.\d+):(\d+) .* TTL:(\d+) TOS:.* ID:.* IpLen:.* DgmLen:.*/) {

		$sip = $1;
		$sport = $2;
		$dip = $3;
		$dport = $4;
		$ttl = $5;
		
		# that's all we need, so update
	
		# print "$sig_message : $timestamp $sourcemac $destmac $sip $dip $sport $dport $ttl\n";
	
		my $res;
		
		$res = $sth->execute($sig_message,$timestamp,$sourcemac,$destmac,$sip,$dip,$sport,$dport,$ttl) or 
			print "not found; $sig_message : $timestamp $sourcemac $destmac $sip $dip $sport $dport $ttl\n";
		if ($res > 1) {
			print "two entries updated: $sig_message : $timestamp $sourcemac $destmac $sip $dip $sport $dport $ttl\n";
		}


		$sth->finish();

	}


}

$dbh->disconnect();

exit;

sub mac() {
	my ($input) = @_; 
	my $output;
	my @splitter = split(":",$input);
	foreach my $sm (@splitter) {
		if (length($sm)==1) {
			$sm="0".$sm;
		}
		$output .= $sm.":";
	}
	$output =~ s/:$//;
	return lc($output);
}