#!/usr/bin/perl

################################################################################
#     Program: scansearcher.pl v.01
#     Description: Queries a SQLite database for port scans and creates a report.
#    Dependencies: Perl, DBD::SQLite and Template::Toolkit
#    Development Platform:  Windows 7 with Perl v5.10.1, Template::Toolkit v2.22
#        and DBD::Sqlite v1.29
#    Release Date: 2010-03-09
#
#     Copyright 2010 Adam Bray
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
################################################################################

# Modules
use warnings;
use strict;
use Getopt::Long;
use DBD::SQLite;
use Template;

# Declare variables
my $help;
my $db;
my $scanner;
my $target;
my $output;
my $limit = '10';
my $maintitle;
my $sql;
my $results;
my $title;
my $description;
my $fields;
my @data;

# Get options
GetOptions (
           "database|d=s" => \$db,
           "limit|l=i" => \$limit,
           "output|o=s" => \$output,
           "scanner|s=s" => \$scanner,
           "target|t=s" => \$target,
           "help|h" => \$help);

&Help if $help || !defined($db && $output);

# Connect to database
my $dbh = DBI->connect( "dbi:SQLite:dbname=$db", "", "" );

if ( !defined ($scanner || $target)  ) {

$maintitle = "Overview of Port Scans";

# Detects port scans (vertical scans)
$title = 'Port scan detection';
$description = 'Finds the IP addresses that sent SYNs to the most destination ports of another host.  Good way to find port scans (vertical scans).  Active mode FTP servers may show up as a false positive.';
$sql = "SELECT saddr, daddr, MIN(dport) ||':'|| MAX(dport) AS dport_range, COUNT(DISTINCT dport) AS uniq_dports
FROM ip, tcp
WHERE ip.id = tcp.id AND tcp.flags = 2
GROUP BY saddr, daddr
ORDER BY uniq_dports DESC LIMIT $limit;";
$fields = ['saddr', 'daddr', 'dport_range', 'uniq_dports'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# Detects port sweeps (horizontal scans)
$title = "Port sweep detection";
$description = "Top source IPs sending TCP connection requests to the most unique destination IPs per minute. Good for detecting port sweeps (horizontal scans).";
$sql = "SELECT strftime('%Y-%m-%d %H:%M', time) AS datetime, saddr, dport, COUNT(DISTINCT daddr) AS uniq_daddr
FROM ip, tcp
WHERE ip.id = tcp.id AND tcp.flags = 2
GROUP BY datetime, saddr, dport
ORDER BY uniq_daddr DESC LIMIT $limit;";
$fields = ['datetime', 'saddr', 'dport', 'uniq_daddr'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# Most common source ports for SYNs
$title = "Top SYN source ports";
$description = "Finds IP addresses that use the same source port the most when sending SYNs.  Some port scanners don't change the source port when scanning.";
$sql = "SELECT saddr, sport, COUNT(sport) AS sport_cnt
FROM ip, tcp
WHERE ip.id = tcp.id AND tcp.flags = 2
GROUP BY saddr, sport
ORDER BY sport_cnt DESC LIMIT $limit;";
$fields = ['saddr', 'sport', 'sport_cnt'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# Most common sequence numbers for SYNs
$title = "Top SYN sequence numbers.";
$description = "Finds IP addresses that use the same TCP sequence number the most when sending SYNs.  Some port scanners don't change the sequence number when scanning.";
$sql = "SELECT saddr, seq, COUNT(*) AS seq_count
FROM ip, tcp
WHERE ip.id = tcp.id AND tcp.flags = 2
GROUP BY saddr, seq
ORDER BY seq_count DESC LIMIT $limit;";
$fields = ['saddr', 'seqnum', 'seq_count'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# IP addresses sending the most SYNs per minute
$title = "Most SYNs per minute";
$description = "Can detect port scanning by looking for the IP addresses sending the most SYNs per minute.";
$sql = "SELECT strftime('%Y-%m-%d %H:%M', time) AS datetime, saddr, daddr, MIN(dport) ||':'|| MAX(dport) AS dport_range, COUNT(DISTINCT dport) AS uniq_dports, COUNT(*) AS syn_per_min
FROM ip, tcp
WHERE ip.id = tcp.id AND tcp.flags = 2
GROUP BY datetime, saddr, daddr
ORDER BY syn_per_min DESC LIMIT $limit;";
$fields = ['datetime', 'saddr', 'daddr', 'dport_range', 'uniq_dports', 'syn_per_min'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# Detects some illegal TCP flag combinations
$title = "Illegal TCP flag combinations";
$description = "Detects *some* illegal TCP flag combinations and sorts them by the most common.  Some port scanners send unusual stimuli in order to elicit a response.";
$sql = "SELECT saddr, COUNT(DISTINCT daddr) AS uniq_daddr, MIN(dport) ||':'|| MAX(dport) AS dport_range, tcp.flags, COUNT(tcp.flags) AS flag_count
   FROM ip, tcp
   WHERE ip.id = tcp.id AND tcp.flags IN (0,3,5,6,7,15,19,22,41,43,61,63)
   GROUP BY saddr, tcp.flags
   ORDER BY flag_count DESC LIMIT $limit;";
$fields = ['saddr', 'uniq_daddr', 'dport_range', 'flags', 'flag_count'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# SYNs with no TCP options
$title = "SYNs with no TCP options";
$description = "Finds SYN packets with no TCP options.  The Maximum Segment Size (MSS) is one of the things that
should be exchanged during session establishment.  Some port scanners don't set any TCP options when the SYN flag is set.";
$sql = "SELECT saddr, daddr, MIN(dport) ||':'|| MAX(dport) AS dport_range, COUNT(*) AS count
FROM ip, tcp
WHERE ip.id = tcp.id AND tcp.flags = 2 AND tcp.opts = 0
GROUP BY saddr, daddr
ORDER BY count DESC LIMIT $limit;";
$fields = ['saddr', 'daddr', 'dport_range', 'count'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# Top IPs sending TCP connection requests
$title = "Top SYN senders";
$description = "Top IP addresses making TCP connection requests.";
$sql = "SELECT saddr, COUNT(*) AS syn_count
FROM ip, tcp
WHERE ip.id = tcp.id AND tcp.flags = 2
GROUP BY saddr
ORDER BY syn_count DESC LIMIT $limit;";
$fields = ['saddr', 'syn_count'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# UDP port scan
$title = "UDP port scan";
$description = "Can detect UDP port scans by looking at session data.";
$sql = "SELECT saddr, daddr, MIN(sport) ||':'|| MAX(sport) AS sport_range, COUNT(DISTINCT sport) AS uniq_sports, MIN(dport) ||':'|| MAX(dport) AS dport_range, COUNT(DISTINCT dport) AS uniq_dports
FROM ip, udp
WHERE ip.id = udp.id
GROUP BY saddr, daddr
ORDER BY uniq_dports DESC LIMIT $limit;";
$fields = ['saddr', 'daddr', 'sport_range', 'uniq_sports', 'dport_range', 'uniq_dports'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# ACK scan
$title = "ACK scan";
$description = "Can detect ACK port scans by looking at IP and port information as well as ACK segments sent per minute.";
$sql = "SELECT strftime('%Y-%m-%d %H:%M', time) AS datetime, saddr, daddr, MIN(sport) ||':'|| MAX(sport) AS sport_range, COUNT(DISTINCT sport) AS uniq_sports, MIN(dport) ||':'|| MAX(dport) AS dport_range, COUNT(DISTINCT dport) AS uniq_dports, COUNT(*) AS ack_per_min
FROM ip, tcp
WHERE ip.id = tcp.id AND tcp.flags = 16
GROUP BY datetime, saddr, daddr
ORDER BY ack_per_min DESC LIMIT $limit;";
$fields = ['datetime', 'saddr', 'daddr', 'sport_range', 'uniq_sports', 'dport_range', 'uniq_dports', 'ack_per_min'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# RST scan
$title = "RST scan";
$description = "A large number of RST-ACKs sent per minute to many hosts can indicate a RST scan being used to determine which hosts are alive.";
$sql = "SELECT strftime('%Y-%m-%d %H:%M', time) AS datetime, saddr, MIN(dport) ||':'|| MAX(dport) AS dport_range, COUNT(DISTINCT daddr) AS uniq_daddr
FROM ip, tcp
WHERE ip.id = tcp.id AND tcp.flags = 20
GROUP BY datetime, saddr
ORDER BY uniq_daddr DESC LIMIT $limit;";
$fields = ['datetime', 'saddr', 'dport_range', 'uniq_daddr'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# XMAS scan
$title = "XMAS scan";
$description = "Can detect XMAS port scans by looking for segments with the out-of-spec FIN, PSH, and URG flags set.";
$sql = "SELECT saddr, daddr, MIN(dport) ||':'|| MAX(dport) AS dport_range, COUNT(DISTINCT dport) AS uniq_dports
FROM ip, tcp
WHERE ip.id = tcp.id AND tcp.flags = 41
GROUP BY saddr, daddr
ORDER BY uniq_dports DESC LIMIT $limit;";
$fields = ['saddr', 'daddr', 'dport_range', 'uniq_dports'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# Nmap ICMP echo probe
$title = "Nmap ICMP echo probe";
$description = "Detects Nmap's ICMP echo OS fingerprinting probes that have a bogus code of 9";
$sql = "SELECT MIN(time) as first_seen, saddr, daddr, type, code, COUNT(*) AS count
FROM ip, icmp
WHERE ip.id = icmp.id AND type = 8 AND code = 9
GROUP BY saddr, daddr
ORDER BY count DESC LIMIT $limit;";
$fields = ['first_seen', 'saddr', 'daddr', 'type', 'code', 'count'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# Nmap UDP probe
$title = "Nmap UDP probe";
$description = "Detects Nmap UDP probes with an IPID of 4162 and a total UDP length of 308.";
$sql = "SELECT saddr, sport, daddr, dport, ipid, udp.len, COUNT(*) as count
FROM ip, udp
WHERE ip.id = udp.id AND ipid = 4162 AND udp.len = 308
GROUP BY saddr, daddr
ORDER BY count DESC LIMIT $limit;";
$fields = ['saddr', 'sport', 'daddr', 'dport', 'ipid', 'udp.len', 'count'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# Detects spoofed source IPs
$title = "Possible spoofed IPs";
$description = "Can detect spoofed IPs from something like Nmap scans using decoys.  A router's MAC address may show up as a false positive.";
$sql = "SELECT smac, COUNT(DISTINCT saddr) AS uniq_saddr
FROM ip
GROUP BY smac
ORDER BY uniq_saddr DESC LIMIT $limit;";
$fields = ['smac', 'uniq_saddr'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];

}


if ($scanner && $target) {

$maintitle = "Details of Scanner ($scanner) and Target ($target)";

# First and last contact from scanner
$title = "First and last contact";
$description = "Shows the first and last time the scanner ($scanner) sent an IP packet to the target ($target).";
$sql = "SELECT MIN(time) AS first_seen, MAX(time) AS last_seen
FROM ip
WHERE saddr = \"$scanner\" AND daddr = \"$target\";";
$fields = ['first_seen', 'last_seen'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# Open ports found
$title = "Open ports found on the target";
$description = "Lists the open ports the scanner ($scanner) found on the target ($target).";
$sql = "SELECT saddr, sport, daddr
FROM ip, tcp
WHERE ip.id = tcp.id AND saddr = \"$target\" AND daddr = \"$scanner\" AND tcp.flags = 18
GROUP BY saddr, sport, daddr
ORDER BY sport ASC LIMIT $limit;";
$fields = ['saddr', 'sport', 'daddr'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# Scanner responses to SYN-ACKs
$title = "Scanner responses to SYN-ACK segments";
$description = "Finds each TCP flag that the scanner sent to the target immediately after receiving a SYN-ACK.";
$fields = ['time', 'saddr', 'sport', 'daddr', 'dport', 'tcp.flags'];
($sql, $results) = &synackResponse();
push @data, [$title, $description, $sql, $fields, $results];


# TCP flags sent by the scanner
$title = "TCP flags the scanner sent to the target";
$description = "Lists all the TCP flags the scanner ($scanner) sent to the target ($target).";
$sql = "SELECT saddr, tcp.flags, MIN(dport) ||':'|| MAX(dport) AS dport_range, COUNT(DISTINCT dport) AS uniq_dports, COUNT(*) as flag_cnt
FROM ip, tcp
WHERE ip.id = tcp.id AND saddr = \"$scanner\" AND daddr = \"$target\"
GROUP BY tcp.flags
ORDER BY flag_cnt DESC;";
$fields = ['saddr', 'tcp.flags', 'dport_range', 'uniq_dports', 'flag_cnt'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# TCP flags sent by the target
$title = "TCP flags the target sent to the scanner";
$description = "Lists all the TCP flags the target ($target) sent to the scanner ($scanner).";
$sql = "SELECT saddr, tcp.flags, MIN(dport) ||':'|| MAX(dport) AS dport_range, COUNT(DISTINCT dport) AS uniq_dports, COUNT(*) as flag_cnt
FROM ip, tcp
WHERE ip.id = tcp.id AND saddr = \"$target\" AND daddr = \"$scanner\"
GROUP BY tcp.flags
ORDER BY flag_cnt DESC;";
$fields = ['saddr', 'tcp.flags', 'dport_range', 'uniq_dports', 'flag_cnt'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# Application bytes sent/received
$title = "Application bytes sent/received";
$description = "Lists the application bytes involving the scanner ($scanner) and the target ($target).";
$sql = "SELECT time, saddr, sport, daddr, dport, SUM(appbytes * count) as app_bytes
FROM   
   (SELECT time, saddr, sport, daddr, dport, (ip.len - (ip.hlen + tcp.hlen)) AS appbytes, COUNT(*) as count
   FROM ip, tcp
   WHERE ip.id = tcp.id AND appbytes > 0 AND saddr IN (\"$scanner\",\"$target\") AND daddr IN (\"$scanner\",\"$target\")
   GROUP BY saddr, daddr, sport, dport, appbytes)
GROUP BY saddr, daddr, sport, dport
ORDER BY time ASC LIMIT $limit;";
$fields = ['time', 'saddr', 'sport', 'daddr', 'dport', 'app_bytes'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# Sessions initiated by target to scanner
$title = "Outbound TCP sessions from target to scanner";
$description = "Looks for outbound sessions initiated by the target to the port scanner.";
$sql = "SELECT time, saddr, sport, daddr, dport, tcp.flags
FROM ip, tcp
WHERE ip.id = tcp.id AND saddr = \"$target\" AND daddr = \"$scanner\" AND tcp.flags = 2
ORDER BY time ASC LIMIT $limit;";
$fields = ['time', 'saddr', 'sport', 'daddr', 'dport', 'tcp.flags'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# Nmap TCP ping
$title = "Nmap TCP ping";
$description = "Can detect Nmap TCP pings to port 80 on the target that happens right before the scan starts.";
$sql = "SELECT time, saddr, sport, daddr, dport, tcp.flags
FROM ip, tcp
WHERE ip.id = tcp.id AND saddr = \"$scanner\" AND daddr = \"$target\"
ORDER BY time ASC LIMIT 3;";
$fields = ['time', 'saddr', 'sport', 'daddr', 'dport', 'tcp.flags'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# Port scanner and target MAC to IP relationships
$title = "Scanner and target MAC addresses";
$description = "Shows the port scanner and target MAC and IP addresses.";
$sql = "SELECT smac, saddr, dmac, daddr
FROM ip
WHERE saddr = \"$scanner\" AND daddr = \"$target\"
LIMIT 1;";
$fields = ['smac', 'saddr', 'dmac', 'daddr'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];


# Hosts discovered by scanner
$title = "Other hosts discovered by the scanner";
$description = "Lists the hosts the scanner ($scanner) knows are alive.";
$sql = "SELECT saddr, daddr, COUNT(*) as count
FROM ip
WHERE daddr = \"$scanner\"
GROUP BY saddr, daddr
ORDER BY count DESC LIMIT $limit;";
$fields = ['saddr', 'daddr', 'count'];
$results = $dbh->selectall_arrayref($sql);

push @data, [$title, $description, $sql, $fields, $results];
}


# Subroutine to get response to SYN-ACKs
sub synackResponse {
   my $return;

   # First query finds all SYN-ACKs
   $sql = "SELECT time, sport, dport
   FROM ip, tcp
   WHERE ip.id = tcp.id AND saddr = \"$target\" AND daddr = \"$scanner\" AND tcp.flags = 18
   ORDER BY time ASC LIMIT $limit;";
   $results = $dbh->selectall_arrayref($sql);

   # Process $results to use in second query
   for (my $i = 0; $i <= $#$results; $i++) {

       my $time = $results->[$i][0];
       my $sport = $results->[$i][1];
       my $dport = $results->[$i][2];

       # Second query finds TCP flag the scanner responded with
       $sql = "SELECT time, saddr, sport, daddr, dport, tcp.flags
           FROM ip, tcp
           WHERE ip.id = tcp.id AND saddr = \"$scanner\" AND daddr = \"$target\" AND sport = $dport AND dport = $sport AND time > \"$time\"
           ORDER BY time ASC LIMIT 1;";
       my @response = $dbh->selectrow_array($sql);

       # Put @response elements into $return
       for (my $j = 0; $j <= $#response; $j++) {
           $return->[$i][$j] = $response[$j];
       }
   }

   return ($sql, $return);
}

# Template Toolkit
my $tt = Template->new() || die "$Template::ERROR\n";

# Create variables for the template
my $vars = {'data'  => [ @data ],
           'maintitle' => $maintitle};

# Process the template
$tt->process('scansearcher.tt', $vars, "$output") || die "$tt->error()\n";

# Help subroutine
sub Help {

print <<HELP;

ScanSearcher version .01

Queries a SQLite database created with pkts2db.pl for evidence of port scanners and creates an HTML report.

Usage: $0 [-d] [-l] [-o] [-s] [-t] [-h]

  -d\tSpecify database to use
  -l\tLimit or increase the number of rows to return (Default 10)
  -o\tOutput to HTML file
  -s\tSpecify the port scanner's IP address
  -t\tSpecify the target's IP address
  -h\tHelp

Example 1: $0 -l 15 -d evidence04.db -o evidence04.html

Example 2: $0 -d evidence04.db -s 10.42.42.253 -t 10.42.42.50 -o details.html
HELP

exit 1;
}
