#!/usr/bin/perl
#
# Title: 	Delta Calculator II
#
# File: 	deltacalc2.pl
#
# Version: 	1.0
#
# Written by:	raffael.marty@arcsight.com (ram)
#
# Comments:	Go throught the DB and update it with delta information, i.e. it
# 		adds delta time to all the source/destiontion host pairs. 
# 		This will show automatic scans.
#
# Usage:	./deltacalc2.pl
#			
# URL:		http://afterglow.sourceforge.net
#
# Changes:	
# 
# 10/26/04	Initial Version by ram, based on deltacalc.pl
#
###############################################################################/

use strict;
use DBI; 
use Date::Manip qw(ParseDate UnixDate);
use Date::Calc qw(Delta_DHMS);

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{select id, timestamp, sourceip, destip from sans order by sourceip,destip,timestamp};
my $update = qq{update sans set delta2=? where id=?};
my $sth = $dbh->prepare($query) or die ("SQL error: ").$dbh->errstr;
my $sth1 = $dbh->prepare($update) or die ("SQL error: ").$dbh->errstr;

my (@old_date, $old_sip, $old_dip);

my $res = $sth->execute() or die ("SQL error: ").$dbh->errstr;

while (my @data = $sth->fetchrow_array()) {

	my $values = ParseDate($data[1]);
	my @date = UnixDate($values, "%Y", "%m", "%d", "%H", "%M", "%S");

	if ( ($data[2] == $old_sip) && ($data[3] == $old_dip) ) {
		
		# same connection, update delta

		my @diff = Delta_DHMS($old_date[0], $old_date[1], $old_date[2], 
			$old_date[3], $old_date[4], $old_date[5], 
			$date[0], $date[1], $date[2], 
			$date[3], $date[4], $date[5]);
	
		my $diff = $diff[1]*3600+$diff[2]*60+$diff[3];

		#print "$data[0]; delta: $diff\n";

		$sth1->execute($diff, $data[0]);
	} 

	# save old values
	@old_date = @date;
	$old_sip = $data[2];
	$old_dip = $data[3];
}

$dbh->disconnect();