#!/usr/bin/perl
#
use DBI;
use DBD::mysql;
use Socket;
use English;

# Database communication parameters.  You can fill in $DBPASS if you want
# to run it in unattended mode.  Otherwise the script will prompt for 
# the password whenever it runs.
$DBNAME = "sguildb";
$DBHOST = "localhost";
$DBUSER = "sguil";
$DBPASS = "";

# This is the number of hosts which will be listed in each report
$TOP_N_HOSTS = 10;

# Set this to limit the timeframe of the report.  Typically this is something
# that selects the last 24 hours, though the reporting period can be adjust
# up or down (for debugging) as desired.
#
# Try this one for debugging the script.  It should return in a short time
#$TIME_INTERVAL = "start_time > DATE_SUB(NOW(), INTERVAL 1 HOUR)";
#
# Try this one for a weekly report.
$TIME_INTERVAL = "start_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) and CURDATE()";

# An SQL fragment that describes the src & dst networks that you're
# interested in.  This is usually set so both source IP and dest IP are
# on your local NET in order to detect internal scanning activity, but
# you could also tweak it for other purposes (incoming scan detection, for
# example).
# 
# Example: The easy way to select a class C network would be something like:
# '(src_ip BETWEEN INET_ATON("192.168.1.0") and INET_ATON("192.168.1.255"))'
#
$HOME_NET = "(src_ip between INET_ATON('192.168.1.0') and 
                             INET_ATON('192.168.1.255')) and
             (dst_ip between INET_ATON('192.168.1.0') and 
                             INET_ATON('192.168.1.255'))";


# You probably should not edit this.  It's the main query.  Concentrate 
# instead on tweaking the parameters listed above.  If you mess up
# $SQL_STATEMENT, the rest of the script won't work.
$SQL_STATEMENT = "select INET_NTOA(src_ip) as src,
                         count(distinct dst_ip) as dsts, 
                         count(distinct dst_port) as ports  
                  from sancp 
                  where 
                    ($TIME_INTERVAL) and 
                    dst_bytes = 0 and 
                    ip_proto in (6,17) and
                    ($HOME_NET)
                  group by src_ip 
                  having ports > 0";

# How many sessions we read
$NUM_SESSIONS = 0;

# Prompt for the db password if necessary
if($DBPASS eq "") {
    print "DB Password: ";
    `/bin/stty -echo`;
    $DBPASS = <STDIN>;
    `/bin/stty echo`;
    chomp($DBPASS);
    print "\n";
}

# On the local db machine, I might have to specify the exact location
# of my UNIX domain socket.  Otherwise I can probably skip this.
if(-e "/tmp/mysql.sock") {
    $dbh = DBI->connect("DBI:mysql:database=$DBNAME;host=$DBHOST;mysql_socket=/tmp/mysql.sock", $DBUSER, $DBPASS) or die $DBI::errstr;
} else {
    $dbh = DBI->connect("DBI:mysql:database=$DBNAME;host=$DBHOST", $DBUSER, $DBPASS) or die $DBI::errstr;
}

$sth = $dbh->prepare($SQL_STATEMENT);
$rv = $sth->execute();

$hash_ref = $sth->fetchrow_hashref;
while($hash_ref) {
    $RATIOS{$hash_ref->{src}} = ($hash_ref->{dsts} / $hash_ref->{ports});
    $PORTS{$hash_ref->{src}} = $hash_ref->{ports};
    $DSTS{$hash_ref->{src}} = $hash_ref->{dsts};
    $hash_ref = $sth->fetchrow_hashref;
} 

if($sth->err) {
    print "An error occurred during the query\n";
}

$NUM_SESSIONS = keys(%RATIOS);

# Make sure we have a reasonable number of sessions to make this report
# worthwhile.  If not, print a message and bail.
if($NUM_SESSIONS < 1000) {
    print "There were not enough sessions to compute meaningful statistics\n";
    exit 0;
}

# Set up the report formats we're going to use.
#
# This one is the line-by-line format for the data.
format STDOUT =
@<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<< @####### @####### @######.########
$key, $hostname, $DSTS{$key}, $PORTS{$key}, $RATIOS{$key}
.

# This is the format for the report header.  Note the use of the 
# $OPERATION variable.  The header is shared between different types
# of reports, so just update $OPERATION to tell what type of report 
# you're generating.
format STDOUT_TOP =

@|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
"Top $TOP_N_HOSTS $OPERATION"
@<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<< @<<<<<<< @<<<<<<< @<<<<<<<<<
"IP", "Hostname", "# Hosts", "# Ports", "Ratio"
----------------------------------------------------------------------------
.

$OPERATION = "Portsweepers (few ports, many hosts)";
$num_rows = 0;
foreach $key (sort {$RATIOS{$b} <=> $RATIOS{$a}} keys(%RATIOS)) {
    if($num_rows < $TOP_N_HOSTS) {
	$hostname = gethostbyaddr(inet_aton($key), AF_INET);
	$hostname = $hostname?$hostname:"UNKNOWN";
	write;
	$num_rows++;
    }
}

# Reset the top of the form so perl will print a new header for the next report
$FORMAT_LINES_LEFT = 0;
$OPERATION = "Portscanners (many ports, few hosts)";

$num_rows = 0;
foreach $key (sort {$RATIOS{$a} <=> $RATIOS{$b}} keys(%RATIOS)) {
    if($num_rows < $TOP_N_HOSTS) {
	$hostname = gethostbyaddr(inet_aton($key), AF_INET);
	$hostname = $hostname?$hostname:"UNKNOWN";
	write;
	$num_rows++;
    }
}



exit(0);