#!/usr/bin/perl
#
# Title: 	Simulate a SQL subquery in Perl
#
# File: 	subquery.pl
#
# Version: 	1.0
#
# Written by:	raffael.marty@arcsight.com (ram)
#
# Description:	MySQL in older versions does not support subqueries:
# 		SELECT xyz from snort where id in (select foo from bar)
#	
# Usage:	./subquery.pl
#			
# URL:		http://afterglow.sourceforge.net
#
# Changes:	
# 
# 10/23/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;

#my $query1 = qq{select distinct(destip) from sans where (snort_alert like '%P2P%' or snort_alert like '%GNUTella%') order by destip};
#my $query1 = qq{select snort_alert, if (service=2,sourceip,destip) as p, timestamp, id from sans where sourcemac="00:03:e3:d9:26:c0" and destmac="00:00:0c:04:b2:33" group by p, snort_alert order by p};
my $query1 = qq{select snort_alert, sourceip, destip, timestamp, id from sans where sourcemac="00:03:e3:d9:26:c0" and destmac="00:00:0c:04:b2:33" order by destip};
my $sth1 = $dbh->prepare($query1) or die ("SQL error: ").$dbh->errstr;

#my $query2 = qq{select snort_alert from sans where destip=?};
#my $query2 = qq{select snort_alert,service from sans where if (service=2,sourceip=?,destip=?) and timestamp > ? };
#my $query2 = qq{select snort_alert from sans where timestamp > ? and ((destip=? and service!=2) or (sourceip=? and service=2))};
my $query2 = qq{select snort_alert,destip, timestamp from sans where destmac="00:03:e3:d9:26:c0" and sourcemac="00:00:0c:04:b2:33" and timestamp > ? and sourceip=?};
my $sth2 = $dbh->prepare($query2) or die ("SQL error: ").$dbh->errstr;

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

my %alerts;

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

	my $res2 = $sth2->execute($data[3], $data[2]);

	while (my @out = $sth2->fetchrow_array()) {
	
		#$alerts{$out[0]} = $alerts{$out[0]}+1;
		#$alerts{$data[1]} = $alerts{$data[1]}+1;
		if (($out[0]) && ($data[0])){
			$alerts{$out[0]} = $data[1]."->".$data[2]."->".$out[1]." Alerts: ".$data[0]."/".$out[0]." @ ".$data[3]."->".$out[2];
			# print $alerts{$out[0]}."\n";
		}

	}	
}

for my $entry (values %alerts) {
	print "$entry\n";
}

$sth2->finish();
$dbh->disconnect();