#!/usr/bin/perl 
#===============================================================================
#
#         FILE: log_crunch.pl
#
#        USAGE: ./log_crunch.pl
#
#  DESCRIPTION: parse logwatch output into database
#
#      OPTIONS: see usage: -h
# REQUIREMENTS: internet connection, perl modules, linux
#         BUGS: ---
#        NOTES: ---
#       AUTHOR: Michael Simoni (ms), mdsimoni@gmail.com
#      COMPANY: Zebulak Enterprises LLC
#      VERSION: $Revision: 1.3 $
#      CREATED: 04/21/2013 11:15:11 AM
#     REVISION: $Revision: 1.3 $
#    COPYRIGHT: Copyright (c) 2013, Michael Simoni
#===============================================================================
# @see The GNU Public License (GPL): http://www.opensource.org/licenses/gpl-license.php
#
# 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 2 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, write to the Free Software Foundation, Inc.,
# 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#===============================================================================
################################################################################
## CVS Information
## $Source: /var/cvs/logwatch/log_crunch.pl,v $
## $Author: msimoni $
## $Date: 2013/06/01 02:26:48 $
## $Id: log_crunch.pl,v 1.3 2013/06/01 02:26:48 msimoni Exp $
## $Revision: 1.3 $
################################################################################
## Notes
#
# the following will delete all data from both header and ports
#  -- delete from header;
#
# Examples of some handy sql statements
# -- select source_ip, city, state,cc,country from header order by country;
# -- select h.source_ip, h.city, h.country, p.process_date from header h, ports p where h.source_ip = p.source_ip order by source_ip;
# -- select h.source_ip, h.city, h.cc, h.country, p.process_date from header h, ports p where h.source_ip = p.source_ip  order by h.cc;
# -- SELECT l.source_ip, l.ip_name, p.protocol, p.port, p.process_date FROM header l, ports p WHERE l.source_ip = p.source_ip AND l.source_ip = '117.200.178.237';
# -- SELECT l.source_ip, l.ip_name, p.protocol, p.port, p.process_date, s.name, s.note FROM header l, ports p, services s  WHERE l.source_ip = p.source_ip AND p.port = s.port AND l.source_ip = '117.200.178.237';
# -- select source_ip, city,cc,country, count(*) count from header group by cc order by count;

# -- mysql> SELECT l.source_ip, l.ip_name, l.cc, l.country, p.protocol, p.port, p.process_date FROM header l, ports p WHERE l.source_ip = p.source_ip
# -- SELECT l.source_ip, count(*) count FROM header l, ports p WHERE l.source_ip = p.source_ip GROUP BY l.source_ip ORDER BY count;
# -- SELECT l.source_ip, l.ip_name, p.protocol, p.port, p.process_date, s.ip_name, s.note, l.address1 FROM header l, ports p, services s WHERE l.source_ip = p.source_ip AND p.port = s.port and p.protocol = s.protocol order by l.source_ip, process_date;
# --SELECT p.port, s.ip_name, p.protocol, count(*) count  FROM header h, ports p, services s WHERE h.source_ip = p.source_ip AND p.port = s.port  and p.protocol = s.protocol group by port order by count;
# -- SELECT p.port, s.name FROM header h, ports p, services s WHERE h.source_ip = p.source_ip AND p.port = s.port  and p.protocol = s.protocol and p.port = 1433;
# -- SELECT h.source_ip, p.protocol, p.port, s.name, count(*) count   FROM  header h, ports p, services s WHERE h.source_ip = p.source_ip and p.port = s.port and p.protocol = s.protocol and s.port in ( 1433 ) group by h.source_ip order by count;
# -- SELECT h.source_ip, h.cc, h.country, p.protocol, p.port, s.name, COUNT(*) count   FROM  header h, ports p, services s WHERE h.source_ip = p.source_ip AND p.port = s.port AND p.protocol = s.protocol AND s.port IN ( 1433 ) GROUP BY h.source_ip ORDER BY count;
#
#################################################################################

use strict;
use warnings;

$| = 1;

#
use English;

#use Term::Cap;
#use Benchmark;
#use Term::ReadKey;
use Getopt::Long;
use Data::Dumper;
use Net::Whois::IP qw(whoisip_query);
use Net::Whois::IANA;
use File::Temp qw/ tempfile tempdir /;

#use IP::Country::Fast;
use Geography::Countries;

use DBI;
use Text::Unaccent;

use HTTP::Status qw(:constants :is status_message);

#Local libaries with common perl libraries that I use
use lib "/home/msimoni/dev/lib";
use utillib;

## Globals
#
#my $hostname  = hostname();   # always the current host
my $USER = "root";
my $DBH;
my $PASSWORD;
my $VERBOSE  = 0;
my $DEBUG    = 0;
my %log_data = ();
#################################################################################
## print helpful information
#################################################################################
sub usage()
{
    print <<EOD;
Usage:
$0:   [options]
      where:
            -f datafile of logwatch data from an email - type = unformatted
            -b insert log data from file into database using bulk load. 
            -i insert log data from file into database using insert statements. Very slow 

            -w get the nmap-services file , requires wget
            -s datafile.  load services data into services table of database and exit
            -l load services data using insert statement. default is to bulk load. -l is very slow.

            -a load the http logs. Assumes logs written in combined format and will only load the log entries with POST to wp-login.php
            -m filename - load data from mod_evasive. data is in the form of following, one per line.
                    Thu, 09 May 2013 17:45:52 -0500 mod_evasive HTTP Blacklisted 76.73.235.105

            -t ip-address - lookup a single ip address and exit - like whois at the command line but uses perl modules
            -r print a report. Prints to standard out.
            -v verbose on some options
            -d debug
            -h help - this message
EOD
}
################################################################################
# open up a databse connection adn keep it open
################################################################################
sub get_mysql_connection()
{
    $DBH = DBI->connect( 'DBI:mysql:logwatch;host=localhost', $USER, $PASSWORD, { RaiseError => 1, PrintError => 1 } );
    return $DBH;
}
################################################################################
# prepare the sql statements
# lets try to keep all the sql statements in one place. hoepfully this will make
# the code easier to read and maintain
# expects nothing
# returns a reference to a hash
################################################################################
sub prepare_sqls()
{
    my %sqls = ();
    my $sql;

    # header
    $sqls{"insert_header"} = "INSERT INTO header (source_ip, output_level, host, note, ip_name, city, state, cc, country, address1, address2, mnt_by) 
                                  VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );";

    $sqls{"bulk_load_header"} = "LOAD DATA LOCAL INFILE ? INTO TABLE header
                                 FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
                                 LINES TERMINATED BY '\n'
                                 (source_ip,ip_name,note,host,output_level,city,state,cc,country,address1,address2,mnt_by)";

    $sqls{"select_header"} = "SELECT source_ip FROM header 
                                  WHERE source_ip = ?;";

    # ports
    $sqls{"insert_port"} = "INSERT INTO ports (source_ip, process_date, protocol, packet_count, port) 
                            VALUES( ?, ?, ?, ?, ? );";

    $sqls{"bulk_load_ports"} = "LOAD DATA LOCAL INFILE ? INTO TABLE ports
                                FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
                                LINES TERMINATED BY '\n'
                                (source_ip,protocol,packet_count,port,process_date);";

    # services
    $sqls{"insert_services"} = "INSERT INTO services ( port_protocol, ip_name, port, protocol, open_freq, note )
                                VALUES( ?, ?, ?, ?, ?, ? );";
    $sqls{"select_services"} = "SELECT port_protocol FROM services
                                WHERE port_protocol = ?;";

    $sqls{"bulk_load_services"} = "LOAD DATA LOCAL INFILE ? INTO TABLE services 
                                   FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
                                   LINES TERMINATED BY '\n'
                                   (port_protocol,name,port,protocol,open_freq,note)";

    # load data from the apache module mod_evasives
    $sqls{"bulk_load_evasives"} = "LOAD DATA LOCAL INFILE ? INTO TABLE mod_evasive 
                                   FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
                                   LINES TERMINATED BY '\n'
                                   (source_ip, ip_name, date, protocol, message)";

    # load data from the apache access logs
    $sqls{"bulk_load_httplogs"} = "LOAD DATA LOCAL INFILE ? INTO TABLE http_log
                                   FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
                                   LINES TERMINATED BY '\n'
                                   (agent,cmd,date,gmt,length,request,protocol,rcode,rmessage,source_ip,ip_name,url)";

    # use the following for creating reports:
    $sqls{date_activity}       = "select p.process_date, count(*) count from ports p group by process_date order by p.process_date DESC;";
    $sqls{country_activity}    = "select country, count(*) count from header group by country order by count DESC;";
    $sqls{ip_activity_count}   = "SELECT h.source_ip, h.ip_name, h.country, count(*) count FROM header h, ports p WHERE h.source_ip = p.source_ip GROUP BY h.source_ip ORDER BY count DESC;";
    $sqls{ip_activity_country} = "SELECT h.source_ip, h.ip_name, h.country, count(*) count FROM header h, ports p WHERE h.source_ip = p.source_ip GROUP BY h.source_ip ORDER BY country, count;";
    $sqls{port_activity}       = "SELECT p.protocol, p.port, s.name, COUNT(*) count  FROM  ports p, services s WHERE p.port = s.port and p.protocol = s.protocol group by p.port order by count DESC;";

    $sqls{http_login_activity} = "SELECT source_ip, ip_name, count(*) count FROM http_log GROUP BY source_ip ORDER BY count DESC;";
    return \%sqls;
}
################################################################################
# does a simple whois and dns lookup for an ip
# expects a string of ip address in x.x.x.x format
# returns nothing. aoutput is to STDOUT
################################################################################
sub my_whois_lookup( $ )
{
    my ($ip_address) = @_;

    my ( $name, $cc, $country );

    $name = look_up_reverse_address_NETDNS( $ip_address, 2 );

    print "DNS lookup for $ip_address: $name\n";
    my ( $response, $array_of_responses ) = whoisip_query( $ip_address, 0 );
    print Dumper($response) . "\n";

    if ( defined $$response{country} )
    {
        $cc = $$response{country};
        chomp($cc);
        $cc = cleanup_text($cc);
        print "1:$cc:\n";
    }
    elsif ( defined $$response{Country} )
    {
        $cc = $$response{Country};
        chomp($cc);
        $cc = cleanup_text($cc);
        print "2: $cc\n";
    }

    if ( defined $cc )
    {
        $country = country("$cc");
        if ( defined $country )
        {
            $country =~ s/^\s+|\s+$//;    #strip leading and trailing blanks
        }
        else
        {
            $country = "unknown";
        }
        print "Country for ip: $ip_address and cc: $cc is: $country\n";
    }
    else
    {
        $country = $cc = "";
        print "Country for ip: $ip_address and cc: $cc is: $country\n";
    }
    my $iana = new Net::Whois::IANA;
    $iana->whois_query( -ip => $ip_address );
    print "Country: ",  $iana->country(),  "\n";
    print "Netname: ",  $iana->netname(),  "\n";
    print "Descr: ",    $iana->descr(),    "\n";
    print "Status: ",   $iana->status(),   "\n";
    print "Source: ",   $iana->source(),   "\n";
    print "Server: ",   $iana->server(),   "\n";
    print "Inetnum: ",  $iana->inetnum(),  "\n";
    print "fullinfo: ", $iana->fullinfo(), "\n";
    my @cidr = $iana->cidr();    #returns an array of arrays
    print Dumper( \@cidr ) . "\n";
    $country = country("$iana->country()");

    if ( defined $country )
    {
        $country =~ s/^\s+|\s+$//;    #strip leading and trailing blanks
    }
    else
    {
        $country = "unknown";
    }
    print "Country for ip: $ip_address is: $country\n";
}
################################################################################
# download the services file
#  wget -nv --output-document=$blackdir/$uniquedt_sg.tar.gz \
#       https://svn.nmap.org/nmap/nmap-services
# expects: url, download_dir
#          global: owner, group, mode
# returns: nothing
#
#export http_proxy=http://your.proxy.server:port && wget -c http://whatever
################################################################################
sub download_services( $ $ )
{
    my ( $url, $dir ) = @_;

    my $wgopts;
    $DEBUG ? $wgopts = " -nv" : $wgopts = " -q";
    my $file = "nmap-services.txt";

    my $output_document = "--output-document=$file";

    if ( -e $dir )
    {

        my @cmd = ( "/usr/bin/wget", "$wgopts", " $output_document", " $url" );
        open WGET, "| @cmd" or die "Unable to open pipe to wget: $!\n";
        close(WGET);

        #chown( $owner, $group, "$output_document" );
        #chmod( oct($mode), $dir ) or die "couldn't chmod some of $dir: $!";
    }
    else
    {
        print "directory $dir does not exist. Exiting.\n";
        exit;
    }
}
################################################################################
# Load the data into an array of hash.
# expect:  files created by logwatch
# returns: ref to a hash
# format of file
# tcpmux  1/tcp   0.001995        # TCP Port Service Multiplexer [rfc-1078]
# this data is from
# https://svn.nmap.org/nmap/nmap-services
#################################################################################
sub read_services( $ )
{
    my ($data) = @_;
    my %services = ();

    #for ( my $i = 0 ; $i <= 65535 ; $i++ )
    #{
    #    my ( $name, $aliases, $port_number, $protocol_name ) = getservbyport( $i, "tcp" );
    #    print "Name = $name";
    #    print " Aliases = $aliases";
    #    print " Port Number = $port_number";
    #    print " Protocol Name = $protocol_name\n";
    #}

    open DATA, "$data" or die "Could not open $data: $!\n";

    my $count = 0;
    my ( $title, $processing_date );
    while ( my $data_line = <DATA> )
    {
        $count++;
        print STDERR "$count\r";
        chomp($data_line);
        $data_line =~ s/^\s+//;    #strip leading and
        $data_line =~ s/\s+$//;    #trailing blanks
                                   #skip comments and blank lines
        next if $data_line =~ /^#|^;/ or $data_line =~ /^\s*$/;

        if ( $data_line =~ /#/ )
        {
            my ( $first, $note ) = split( /#/, $data_line, );
            $first =~ s/^\s+|\s+$//;    #strip leading and training blanks
            my ( $service_name, $port_protocol, $open_freq ) = split( /\s+/, $first );
            my ( $port, $protocol ) = split( /\//, $port_protocol );
            $services{$port_protocol} = {
                service_name => $service_name,
                open_freq    => $open_freq,
                port         => $port,
                protocol     => $protocol,
                note         => $note,
            };
        }
        else
        {
            my ( $service_name, $port_protocol, $open_freq ) = split( /\s+/, $data_line );
            my ( $port, $protocol ) = split( /\//, $port_protocol );
            $services{$port_protocol} = {
                service_name => $service_name,
                open_freq    => $open_freq,
                port         => $port,
                protocol     => $protocol,
                note         => undef,
            };
        }
    }
    print Dumper( \%services ) . "\n" if $DEBUG;

    return ( \%services );
}
################################################################################
# insert services data from
# expects referenc to a hash
# returns nothing, excutes sql to load a table in a mysql database
# this is very slow and kept here only for learning purposes.
#     {
#       '1/tcp' => {
#         'note' => ' TCP Port Service Multiplexer [rfc-1078]',
#         'open_freq' => '0.001995',
#         'port' => '1',
#         'protocol' => 'tcp',
#         'service_name' => 'tcpmux'
#       },
################################################################################
sub insert_services_into_db( $ )
{
    my ($services) = @_;
    my $prepared_sqls = prepare_sqls();

    my $insert_services_sql = $$prepared_sqls{"insert_services"};
    my $i_services_h        = $DBH->prepare($insert_services_sql);

    my $select_services_sql = $$prepared_sqls{"select_services"};
    my $s_services_h        = $DBH->prepare($select_services_sql);

    my $count = 0;
    foreach my $port_protocol ( sort keys %$services )
    {

        my $service_name = $$services{$port_protocol}{service_name};
        my $port         = $$services{$port_protocol}{port};
        my $protocol     = $$services{$port_protocol}{protocol};
        my $open_freq    = $$services{$port_protocol}{open_freq};
        my $note         = $$services{$port_protocol}{note};

        $s_services_h->execute($port_protocol);
        my ($port_protocol) = $s_services_h->fetchrow_array();    # this assumes uniqness on this field
        next if ($port_protocol);

        $count++;
        if ($note)
        {
            print "$count: Inserting: $service_name, $port_protocol, $note\n";
        }
        else
        {
            print "$count: Inserting: $service_name, $port_protocol\n";
        }
        $i_services_h->execute( $port_protocol, $service_name, $port, $protocol, $open_freq, $note );
    }
}
################################################################################
# bulk load services data into DB
# expects referenc to a hash,
# returns nothing, cereates a temporary file then
# excutes sql to bulk load a table in a mysql database
# this is fast and is the recommended way
#     {
#       '1/tcp' => {
#         'note' => ' TCP Port Service Multiplexer [rfc-1078]',
#         'open_freq' => '0.001995',
#         'port' => '1',
#         'protocol' => 'tcp',
#         'service_name' => 'tcpmux'
#       },
################################################################################
sub bulk_load_services_into_db( $ )
{
    my ($services) = @_;
    my $prepared_sqls = prepare_sqls();

    my $bulk_load_services_sql = $$prepared_sqls{"bulk_load_services"};
    my $bl_services_h          = $DBH->prepare($bulk_load_services_sql);

    my $template = "services_XXXX";
    my $suffix   = ".csv";

    #my $dir = getcwd;
    my ( $fh, $filename ) = tempfile( $template, SUFFIX => $suffix, UNLINK => 1 );

    #open $fh, "> $tmpfile" or die "Could not open $tmpfile: $!\n";

    my $count = 0;
    foreach my $port_protocol ( sort keys %$services )
    {

        my $service_name = $$services{$port_protocol}{service_name};
        my $port         = $$services{$port_protocol}{port};
        my $protocol     = $$services{$port_protocol}{protocol};
        my $open_freq    = $$services{$port_protocol}{open_freq};
        my $note         = $$services{$port_protocol}{note};

        #$s_services_h->execute($port_protocol);
        #my ($port_protocol) = $s_services_h->fetchrow_array();    # this assumes uniqness on this field
        #next if ($port_protocol);

        $count++;
        print "$count   \r";
        if ($note)
        {
            $note =~ s/^\s+//;    #strip leading and
            $note =~ s/\s+$//;    #trailing blanks
                                  #print "$count: $port_protocol,$service_name,$port, $protocol,$open_freq,\'$note\'\n";
            print $fh "$port_protocol,$service_name,$port,$protocol,$open_freq,\"$note\"\n"

        }
        else
        {

            #print "$count: $port_protocol,$service_name,$port,$protocol,$open_freq\n";
            print $fh "$port_protocol,$service_name,$port,$protocol,$open_freq\n";
        }
    }
    print "Loading data from $filename into the database\n";
    close($fh);                   # flush all data to disk
    chmod( oct(644), $filename ) or die "couldn't chmod some of $filename: $!";
    $bl_services_h->execute("$filename");
    $bl_services_h->finish();
}
################################################################################
# bulk load mod_evasive data into DB
# expects referenc to a hash,
# returns nothing, cereates a temporary file then
# excutes sql to bulk load a table in a mysql database
# this is fast and is the recommended way
#       'Sun 14 Apr 2013 13:40:03 -0500' => {
#         'message' => 'Blacklisted',
#         'processing_date' => '2013-04-14 13:40:03',
#         'protocol' => 'HTTP',
#         'source_ip' => '99.92.91.222'
################################################################################
sub bulk_load_mod_evasive_into_db( $ )
{
    my ($evasives) = @_;
    my $prepared_sqls = prepare_sqls();

    my $bulk_load_evasives_sql = $$prepared_sqls{"bulk_load_evasives"};
    my $bl_evasives_h          = $DBH->prepare($bulk_load_evasives_sql);

    my $template = "evasives_XXXX";
    my $suffix   = ".csv";

    #my $dir = getcwd;
    my ( $fh, $filename ) = tempfile( $template, SUFFIX => $suffix, UNLINK => 1 );

    #open $fh, "> $tmpfile" or die "Could not open $tmpfile: $!\n";

    foreach my $datekey ( keys %$evasives )
    {

        my $source_ip = $$evasives{$datekey}{source_ip};
        my $datetime  = $$evasives{$datekey}{processing_date};
        my $protocol  = $$evasives{$datekey}{protocol};
        my $message   = $$evasives{$datekey}{message};
        my $ip_name   = look_up_reverse_address_NETDNS( $source_ip, 3 );

        print $fh "$source_ip,$ip_name,$datetime,$protocol,$message\n";
        print "$source_ip, $ip_name, $datetime, $protocol, \"$message\"\n";
    }
    print Dumper($evasives) . "\n" if $DEBUG;
    print "Loading data from $filename into the database\n";
    close($fh);    # flush all data to disk
    chmod( oct(644), $filename ) or die "couldn't chmod some of $filename: $!";
    $bl_evasives_h->execute("$filename");
    $bl_evasives_h->finish();
}
################################################################################
# load data from a file
# expect:  files created by logwatch
# returns: nothing. populates a global array
#################################################################################
sub load_logwatch_data( $ )
{
    my ($data) = @_;

    my %mon2num = qw(
      jan 01  feb 02  mar 03  apr 04 may 05 jun 06
      jul 07  aug 08  sep 09  oct 10 nov 11 dec 12
    );

    open DATA, "$data" or die "Could not open $data: $!\n";

    my $count = 0;
    my ( $title, $processing_date );
    while ( my $data_line = <DATA> )
    {
        $count++;
        print STDERR "$count\r";
        chomp($data_line);
        $data_line =~ s/^\s+//;    #strip leading and
        $data_line =~ s/\s+$//;    #trailing blanks
                                   #skip comments and blank lines
        next if $data_line =~ /^#|^;/ or $data_line =~ /^\s*$/;

        if ( $data_line =~ /^Processing Initiated:/ )
        {

            # this is the start of a data file
            ( $title, $processing_date ) = split( /:/, $data_line, 2 );
            $processing_date =~ s/^\s+//;    #strip leading blanks

            # processing date: Sat Apr 20 03:10:13 2013

            my ( $weekday, $month, $monthday, $time, $year ) = split( /\s+/, $processing_date );
            my $monthnum = $mon2num{ lc $month };
            $log_data{$processing_date}{processing_date} = "$year-$monthnum-$monthday";
            next;
        }
        elsif ( $data_line =~ /^Processing Initiated:/ )
        {
            my ( $title, $processing_initiated ) = split( /:/, $data_line );
            $log_data{$processing_date}{processing_initiated} = $processing_initiated;
            next;
        }
        elsif ( $data_line =~ /^Date Range Processed:/ )
        {
            my ( $title, $date_range_processed ) = split( /:/, $data_line );
            $log_data{$processing_date}{date_range_processed} = $date_range_processed;
            next;
        }
        elsif ( $data_line =~ /^Type of Output:/ )
        {
            my ( $title, $type_of_output ) = split( /:/, $data_line );
            $log_data{$processing_date}{type_of_output} = $type_of_output;
            next;
        }
        elsif ( $data_line =~ /^Logfiles for Host:/ )
        {
            my ( $title, $logfiles_for_host ) = split( /:/, $data_line );
            $log_data{$processing_date}{logfiles_for_host} = $logfiles_for_host;
            next;
        }
        elsif ( $data_line =~ /^\-*iptables firewall Begin\-*$/ )
        {

            # start iptables processing
            next;
        }
        elsif ( $data_line =~ /^Dropped \d* packets on interface/ )
        {

            #my ( $dropped_comment) = split( /\s+/, $data_line );
            $log_data{$processing_date}{dropped_comment} = $data_line;
            next;
        }
        elsif ( $data_line =~ /^From/ )
        {

            # This assumes that all the source ip lines under a processing date are unique

            my ( $from, $source_ip, $dash, $packet_count, $packet, $to, $protocol_port ) = split( /\s+/, $data_line );

            my ( $protocol, $ports ) = split( /\(/, $protocol_port );
            $ports =~ s/\)//g;
            my @allports = split( /,/, $ports );

            if ( defined $log_data{$processing_date}{source_ips}{$source_ip} )
            {
                $log_data{$processing_date}{source_ips}{$source_ip} = {
                    source_ip    => $source_ip,
                    packet_count => $packet_count,
                    protocol     => $protocol,
                    ports        => \@allports,
                    count        => ++$log_data{$processing_date}{$source_ip}{count},
                };
            }
            else
            {
                $log_data{$processing_date}{source_ips}{$source_ip} = {
                    source_ip    => $source_ip,
                    packet_count => $packet_count,
                    protocol     => $protocol,
                    ports        => \@allports,
                    count        => 1,
                };
            }
            next;
        }
    }
    print Dumper( \%log_data ) . "\n" if $DEBUG;
    if ($DEBUG)
    {
        foreach my $date_key ( sort keys %log_data )
        {
            foreach my $source_ip ( sort keys %{ $log_data{$date_key}{source_ips} } )
            {
                my ( $response, $array_of_responses ) = whoisip_query( $source_ip, 0 );
                print Dumper($response) . "\n";
            }
        }

    }
}
################################################################################
# load data from a file
# expect:  files created with apache httpd in combined format
# we are oly interstee in what is hitting the login page
# 91.224.160.135 - - [21/Apr/2013:18:11:05 -0500] "POST /wp-login.php HTTP/1.1" 403 214 "-" "Mozilla/5.0 (compatible; MSIE 8.0; Windows NT 5.0; Trident/4.0; InfoPath.1; SV1; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET CLR 3.0.04506.30)"
# returns: reference to a hash
#################################################################################
sub read_http_log( $ )
{
    my ($data) = @_;
    my %http_data = ();

    my $template = "http_XXXX";
    my $suffix   = ".csv";
    my $dir      = getcwd;
    my ( $h_fh, $filename ) = tempfile( $template, SUFFIX => $suffix, UNLINK => 1 );

    my %mon2num = qw(
      jan 01  feb 02  mar 03  apr 04 may 05 jun 06
      jul 07  aug 08  sep 09  oct 10 nov 11 dec 12
    );

    open DATA, "$data" or die "Could not open $data: $!\n";

    my $count  = 0;
    my $serial = 0;
    my ( $title, $processing_date );
    while ( my $data_line = <DATA> )
    {
        $count++;
        print STDERR "Reading $data: $count                                                   \r" if $VERBOSE;
        chomp($data_line);
        $data_line =~ s/^\s+//;    #strip leading and
        $data_line =~ s/\s+$//;    #trailing blanks
                                   #skip comments and blank lines
        next if $data_line =~ /^#|^;/ or $data_line =~ /^\s*$/;
        next if ( lc($data_line) !~ /wp-login.php/ );
        next if ( lc($data_line) !~ /post / );

# this is the start of a data file
# 91.224.160.135 - - [21/Apr/2013:18:11:05 -0500] "POST /wp-login.php HTTP/1.1" 403 214 "-" "Mozilla/5.0 (compatible; MSIE 8.0; Windows NT 5.0; Trident/4.0; InfoPath.1; SV1; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET CLR 3.0.04506.30)"
        my $w = "(.+?)";
        $data_line =~ m/^$w $w $w \[$w:$w $w\] "$w $w $w" $w $w "$w" "$w"/;

        my $ip         = $1;
        my $remote_log = $2;
        my $username   = $3;
        my $date       = "$4 $5 $6";
        my $time       = $5;
        my $gmt        = $6;
        my ( $day, $month, $year ) = split( /\//, $4 );
        my $monnum         = $mon2num{ ( lc($month) ) };
        my $cmd            = $7;
        my $request        = $8;
        my $protocol       = $9;
        my $rcode          = $10;
        my $status_message = status_message($rcode);
        my $length         = $11;
        my $referer_url    = $12;
        my $agent          = $13;

        #print "4: $4 5:$5 6:$6\n";
        #print "1:$ip 2:$remote_log 3:$username 456:$date 7:$cmd 8:$request 9:$protocol 10:$rcode 11:$length 12:$referer_url 13:$agent\n";

        $serial++;
        $http_data{$serial} = {
            source_ip  => $ip,
            date       => "$year-$monnum-$day",
            time       => "$time",
            gmt        => "$gmt",
            cmd        => $cmd,
            request    => $request,
            protocol   => $protocol,
            rcode      => $rcode,
            rmessage   => $status_message,
            length     => $length,
            url        => $referer_url,
            agent      => $agent,
            remote_log => $remote_log,
            username   => $username,
        };
    }
    print Dumper( \%http_data ) . "\n" if $DEBUG;
    return ( \%http_data );
}
################################################################################
# bulk load http_log data into DB
# expects referenc to a hash,
# returns nothing, cereates a temporary file then
# excutes sql to bulk load a table in a mysql database
# this is fast and is the recommended way
################################################################################
sub bulk_load_http_log_into_db( $ )
{
    my ($httplogs) = @_;
    my $prepared_sqls = prepare_sqls();

    my $bulk_load_httplogs_sql = $$prepared_sqls{"bulk_load_httplogs"};
    my $bl_httplogs_h          = $DBH->prepare($bulk_load_httplogs_sql);

    my $http_template = "httplogs_XXXX";
    my $suffix        = ".csv";

    #my $dir = getcwd;
    my ( $fh, $filename ) = tempfile( $http_template, SUFFIX => $suffix, UNLINK => 1 );

    #open $fh, "> $tmpfile" or die "Could not open $tmpfile: $!\n";

    print Dumper($httplogs) . "\n" if $DEBUG;
    my $count       = 0;
    my $log_records = keys %$httplogs;
    foreach my $key ( keys %$httplogs )
    {
        $count++;
        print STDERR "loading httplogs: $count/$log_records                                   \r" if $VERBOSE;
        my $agent     = $$httplogs{$key}{agent};
        my $cmd       = $$httplogs{$key}{cmd};
        my $date      = $$httplogs{$key}{date};
        my $time      = $$httplogs{$key}{time};
        my $datetime  = "$date $time";
        my $gmt       = $$httplogs{$key}{gmt};
        my $length    = $$httplogs{$key}{length};
        my $protocol  = $$httplogs{$key}{protocol};
        my $request   = $$httplogs{$key}{request};
        my $rcode     = $$httplogs{$key}{rcode};
        my $rmessage  = $$httplogs{$key}{rmessage};
        my $source_ip = $$httplogs{$key}{source_ip};
        my $url       = $$httplogs{$key}{url};
        my $ip_name   = look_up_reverse_address_NETDNS( $source_ip, 3 );

        if ( !defined $ip_name ) { $ip_name = "" }
        print $fh "\"$agent\",\"$cmd\",\"$datetime\",\"$gmt\",\"$length\",\"$request\",\"$protocol\",\"$rcode\",\"$rmessage\",\"$source_ip\",\"$ip_name\",\"$url\"\n";

        #print "$agent,$cmd,$datetime,$protocol,$rcode,$source_ip,$ip_name,$url\n";
    }
    print Dumper($httplogs) . "\n" if $DEBUG;
    print "\nLoading data from $filename into the database\n";
    close($fh);    # flush all data to disk
    chmod( oct(644), $filename ) or die "couldn't chmod some of $filename: $!";
    $bl_httplogs_h->execute("$filename");
    $bl_httplogs_h->finish();
}
################################################################################
# load data from a file
# expect:  files created with mode_evasive data
#  Thu, 09 May 2013 17:45:52 -0500 mod_evasive HTTP Blacklisted 76.73.235.105
# returns: reference to a hash
#################################################################################
sub read_mod_evasive( $ )
{
    my ($data) = @_;
    my %mod_data = ();

    my %mon2num = qw(
      jan 01  feb 02  mar 03  apr 04 may 05 jun 06
      jul 07  aug 08  sep 09  oct 10 nov 11 dec 12
    );

    open DATA, "$data" or die "Could not open $data: $!\n";

    my $count = 0;
    my ( $title, $processing_date );
    while ( my $data_line = <DATA> )
    {
        $count++;
        print STDERR "$count\r";
        chomp($data_line);
        $data_line =~ s/^\s+//;    #strip leading and
        $data_line =~ s/\s+$//;    #trailing blanks
                                   #skip comments and blank lines
        next if $data_line =~ /^#|^;/ or $data_line =~ /^\s*$/;

        # this is the start of a data file
        my ( $dow, $dom, $month, $year, $time, $gmt, $title, $protocol, $message, $source_ip ) = split( /\s+/, $data_line );
        $processing_date = "$dow $dom $month $year $time $gmt";
        $processing_date =~ s/^\s+//;    #strip leading blanks
        $processing_date =~ s/\,//g;     #remove that comma

        # processing date: Sat Apr 20 03:10:13 2013

        my $monthnum = $mon2num{ lc $month };
        $mod_data{$processing_date} = {
            processing_date => "$year-$monthnum-$dom $time",
            source_ip       => $source_ip,
            protocol        => $protocol,
            message         => $message
        };
    }
    print Dumper( \%mod_data ) . "\n" if $DEBUG;
    return ( \%mod_data );
}
################################################################################
# simple function to clean up the text
# removes carrage returns and strange ascii codes
# expects a string
# returns a string
################################################################################
sub cleanup_text( $ )
{
    my ($data) = @_;
    $data =~ s/^\s+|\s+$//;    #strip leading and trailing blanks
    chomp($data);
    my $unaccented = unac_string( "US-ASCII", $data );
    $data = $unaccented;
    return ($data);
}
################################################################################
# create file data of whois data for later loading into a database
# uses whois::ip
# expects: nothing, uses a global hash
# returns:  created temproary files and loads the data into the database
################################################################################
sub bulk_load_whois_file_data()
{
    my $header_template = "header_XXXX";
    my $port_template   = "ports_XXXX";
    my $suffix          = ".csv";
    my $dir             = getcwd;
    my ( $h_fh, $header_filename ) = tempfile( $header_template, SUFFIX => $suffix, UNLINK => 1 );
    my ( $p_fh, $ports_filename )  = tempfile( $port_template,   SUFFIX => $suffix, UNLINK => 1 );

    my $iana = new Net::Whois::IANA;

    my $count = 0;
    foreach my $date_key ( keys %log_data )
    {
        my $processing_date = $log_data{$date_key}{processing_date};
        my $output_level    = "$log_data{$date_key}{type_of_output}" eq "unformatted" ? 0 : 1;
        my $host            = $log_data{$date_key}{logfiles_for_host};
        my $note            = $log_data{$date_key}{dropped_comment};
        $note =~ s/^\s+|\s+$//;    #strip leading and trailing blanks
        $host =~ s/^\s+|\s+$//;    #strip leading and trailing blanks

        foreach my $source_ip ( keys %{ $log_data{$date_key}{source_ips} } )
        {

            #$s_header_h->execute($source_ip);
            #my ($ip_address) = $s_header_h->fetchrow_array();    # this assumes uniqness on this field

            #if ($ip_address) # if ip_address in header table then just process all the port info. this creates port duplicates if you read the same data twice
            #{
            #    my $protocol     = $log_data{$date_key}{source_ips}{$source_ip}{protocol};
            #    my $packet_count = $log_data{$date_key}{source_ips}{$source_ip}{packet_count};
            #    my $ports        = $log_data{$date_key}{source_ips}{$source_ip}{ports};
            #    foreach my $port (@$ports)
            #    {
            #        print "$count: $source_ip, $processing_date, $output_level, $host, $note  $protocol, $packet_count, $port\n";
            #        print $p_fh "$source_ip,$protocol,$packet_count,$port,$processing_date\n";

            #$i_port_h->execute( $source_ip, $processing_date, $protocol, $packet_count, $port );
            #    }
            #}
            #else    # if ip_address not in header then insert it and any associated port info with that hash record.
            #{
            my ( $city, $state, $cc, $country, $address1, $address2, $notify, $mnt_by, $descr );
            my ( $org_tech_handle, $org_tech_name, $org_tech_info );

            my $name = look_up_reverse_address_NETDNS( $source_ip, 2 );

            my ( $response, $array_of_responses ) = whoisip_query( $source_ip, 0 );
            if ( defined $$response{City} )
            {
                $city = $$response{City};
                $city = cleanup_text($city);
            }
            else
            {
                $city = "";
            }

            if ( defined $$response{State} )
            {
                $state = $$response{State};
                $state = cleanup_text($state);
            }
            else
            {
                $state = "";
            }

            if ( defined $$response{StateProv} )
            {
                $state = $$response{StateProv};
                $state = cleanup_text($state);
            }

            if ( defined $$response{country} )
            {
                $cc = $$response{country};
                $cc =~ s/#.*$//g;
                $cc      = uc($cc);
                $cc      = cleanup_text($cc);
                $country = country("$cc");
                if ( defined $country )
                {
                    $country = cleanup_text($country);
                }
                else
                {
                    $country = "";
                }

            }
            elsif ( defined $$response{Country} )
            {
                $cc = $$response{Country};
                $cc =~ s/#.*$//g;
                $cc      = cleanup_text($cc);
                $country = country("$cc");
                if ( defined $country )
                {
                    $country = cleanup_text($country);
                }
                else
                {
                    $country = "";
                }
            }
            else
            {
                $iana->whois_query( -ip => $source_ip );
                $cc = $iana->country();
                $cc =~ s/#.*$//g;
                $cc = cleanup_text($cc);
                if ( defined $country )
                {
                    $country = cleanup_text($country);
                }
                else
                {
                    $country = "";
                }
            }

            if ( defined $$response{address} )
            {
                $address1 = $$response{address};
                $address1 = cleanup_text($address1);
            }
            elsif ( defined $$response{Address} )
            {
                $address1 = $$response{Address};
                $address1 = cleanup_text($address1);
            }
            else
            {
                $address1 = "";
            }

            if ( defined $$response{'address:        address'} )
            {
                $address2 = $$response{'address:        address'};
                $address2 = cleanup_text($address2);
            }
            else
            {
                $address2 = "";
            }
            if ( defined $$response{'notify'} )
            {
                $notify = $$response{'notify'};
                $notify = cleanup_text($notify);
            }
            else
            {
                $notify = "";
            }
            if ( defined $$response{'mnt-by'} )
            {
                $mnt_by = $$response{'mnt-by'};
                $mnt_by = cleanup_text($mnt_by);
            }
            if ( defined $$response{'OrgTechHandle'} )
            {
                $org_tech_handle = $$response{'OrgTechHandle'};
                $org_tech_handle = cleanup_text($org_tech_handle);
            }
            if ( defined $$response{'OrgTechName'} )
            {
                $org_tech_name = $$response{'OrgTechName'};
                $org_tech_name = cleanup_text($org_tech_name);
            }
            if ( defined $org_tech_handle or defined $org_tech_name )
            {
                $org_tech_name   = "" if !defined $org_tech_name;
                $org_tech_handle = "" if !defined $org_tech_handle;
                $org_tech_info   = "$org_tech_name:$org_tech_handle";
            }

            if ( defined $$response{'descr'} )
            {
                $descr = $$response{'descr'};
                $descr = cleanup_text($descr);
            }
            else
            {
                $iana->whois_query( -ip => $source_ip );
                $descr = $iana->descr();
                $descr = cleanup_text($descr);
            }

            if ( defined $mnt_by or defined $descr or defined $org_tech_info )
            {
                $mnt_by        = "" if !defined $mnt_by;
                $descr         = "" if !defined $descr;
                $org_tech_info = "" if !defined $org_tech_info;
                $mnt_by        = "$mnt_by:$descr:$org_tech_info";
            }
            else
            {
                $mnt_by = "";
            }

            my $protocol     = $log_data{$date_key}{source_ips}{$source_ip}{protocol};
            my $packet_count = $log_data{$date_key}{source_ips}{$source_ip}{packet_count};
            my $ports        = $log_data{$date_key}{source_ips}{$source_ip}{ports};

            print $h_fh "$source_ip,\"$name\",\"$note\",$host,$output_level,\"$city\",\"$state\",$cc,\"$country\",\"$address1\",\"$address2\",\"$mnt_by\"\n";
            $count++;
            foreach my $port (@$ports)
            {
                print "$count: $source_ip,\"$name\",\"$note\",$host,$output_level,\"$city\",\"$state\",$cc,\"$country\",\"$address1\",\"$address2\",\"$mnt_by\"\n";
                print "\t$processing_date, $output_level, $host, $note  $protocol, $packet_count, $port\n";

                print $p_fh "$source_ip,$protocol,$packet_count,$port,$processing_date\n";
            }

            #}
        }
    }

    # start the bulk load of the data
    my $prepared_sqls     = prepare_sqls();
    my $select_header_sql = $$prepared_sqls{"select_header"};
    my $s_header_h        = $DBH->prepare($select_header_sql);

    my $insert_bulk_header_sql = $$prepared_sqls{"bulk_load_header"};
    my $bl_header_h            = $DBH->prepare($insert_bulk_header_sql);

    my $insert_bulk_ports_sql = $$prepared_sqls{"bulk_load_ports"};
    my $bl_ports_h            = $DBH->prepare($insert_bulk_ports_sql);

    print "Loading data from $header_filename into the database\n";
    close($h_fh);    # flush data to disk;
    chmod( oct(644), $header_filename ) or die "couldn't chmod some of $header_filename : $!";
    $bl_header_h->execute("$dir/$header_filename");

    print "Loading data from $ports_filename into the database\n";
    close($p_fh);    # flush data to disk;
    chmod( oct(644), $ports_filename ) or die "couldn't chmod some of $ports_filename : $!";
    $bl_ports_h->execute("$dir/$ports_filename");

    $bl_header_h->finish();
    $bl_ports_h->finish();
    $s_header_h->finish();
}
################################################################################
# insert the data into the data base
# this is the slow method
# uses a global hash

#     {
#       'Sun Apr 21 03:26:15 2013' => {
#         'date_range_processed' => ' yesterday',
#         'dropped_comment' => 'Dropped 101 packets on interface eth0',
#         'logfiles_for_host' => ' zebulak01',
#         'processing_date' => '2013-04-21',
#         'source_ips' => {
#           '93.87.19.162' => {
#             'count' => 1,
#             'packet_count' => '1',
#             'ports' => [
#               '8080'
#             ],
#             'protocol' => 'tcp',
#             'source_ip' => '93.87.19.162'
#           },
#           '98.126.213.58' => {
#             'count' => 1,
#             'packet_count' => '2',
#             'ports' => [
#               '1433'
#             ],
#             'protocol' => 'tcp',
#             'source_ip' => '98.126.213.58'
#           }
#         },
#         'type_of_output' => ' unformatted'
#       },
#       'Wed Apr 24 03:12:13 2013' => {
#         'date_range_processed' => ' yesterday',
#         'dropped_comment' => 'Dropped 73 packets on interface eth0',
#         'logfiles_for_host' => ' zebulak01',
#         'processing_date' => '2013-04-24',
#         'source_ips' => {
#           '88.98.34.229' => {
#             'count' => 1,
#             'packet_count' => '5',
#             'ports' => [
#               '8'
#             ],
#             'protocol' => 'icmp',
#             'source_ip' => '88.98.34.229'
#           },
#           '98.126.213.58' => {
#             'count' => 1,
#             'packet_count' => '1',
#             'ports' => [
#               '1433'
#             ],
#             'protocol' => 'tcp',
#             'source_ip' => '98.126.213.58'
#           }
#         },
#         'type_of_output' => ' unformatted'
#       }
#     }
################################################################################
sub insert_data_into_logwatch()
{
    my $prepared_sqls     = prepare_sqls();
    my $select_header_sql = $$prepared_sqls{"select_header"};
    my $s_header_h        = $DBH->prepare($select_header_sql);

    my $insert_header_sql = $$prepared_sqls{"insert_header"};
    my $i_header_h        = $DBH->prepare($insert_header_sql);

    my $insert_port_sql = $$prepared_sqls{"insert_port"};
    my $i_port_h        = $DBH->prepare($insert_port_sql);

    foreach my $date_key ( keys %log_data )
    {
        my $processing_date = $log_data{$date_key}{processing_date};
        my $output_level    = "$log_data{$date_key}{type_of_output}" eq "unformatted" ? 0 : 1;
        my $host            = $log_data{$date_key}{logfiles_for_host};
        my $note            = $log_data{$date_key}{dropped_comment};
        $note =~ s/^\s+|\s+$//;    #strip leading and trailing blanks
        $host =~ s/^\s+|\s+$//;    #strip leading and trailing blanks

        foreach my $source_ip ( keys %{ $log_data{$date_key}{source_ips} } )
        {
            $s_header_h->execute($source_ip);
            my ($ip_address) = $s_header_h->fetchrow_array();    # this assumes uniqness on this field

            if ($ip_address)                                     # if ip_address in header table then just process all the port info. this creates duplicates if you read the same data twice
            {
                my $protocol     = $log_data{$date_key}{source_ips}{$source_ip}{protocol};
                my $packet_count = $log_data{$date_key}{source_ips}{$source_ip}{packet_count};
                my $ports        = $log_data{$date_key}{source_ips}{$source_ip}{ports};
                foreach my $port (@$ports)
                {
                    print "$source_ip, $processing_date, $output_level, $host, $note  $protocol, $packet_count, $port\n";
                    $i_port_h->execute( $source_ip, $processing_date, $protocol, $packet_count, $port );
                }
            }
            else                                                 # if ip_address not in header then insert it and any associated port info with that hash record.
            {
                my ( $city, $state, $cc, $country, $address1, $address2, $notify, $mnt_by, $descr );
                my ( $org_tech_handle, $org_tech_name, $org_tech_info );

                my $ip_name = look_up_reverse_address_NETDNS( $source_ip, 2 );

                my ( $response, $array_of_responses ) = whoisip_query( $source_ip, 0 );

                if ( defined $$response{City} )
                {
                    $city = $$response{City};
                    $city = cleanup_text($city);
                }

                if ( defined $$response{State} )
                {
                    $state = $$response{State};
                    $state = cleanup_text($state);
                }

                if ( defined $$response{StateProv} )
                {
                    $state = $$response{StateProv};
                    $state = cleanup_text($state);
                }

                if ( defined $$response{country} )
                {
                    $cc = $$response{country};
                    $cc = cleanup_text($cc);
                }
                elsif ( defined $$response{Country} )
                {
                    $cc = $$response{Country};
                    $cc = cleanup_text($cc);
                }
                if ( defined $cc )
                {
                    $country = country("$cc");
                    $country = cleanup_text($country);
                }

                if ( defined $$response{address} )
                {
                    $address1 = $$response{address};
                    $address1 = cleanup_text($address1);
                }
                elsif ( defined $$response{Address} )
                {
                    $address1 = $$response{Address};
                    $address1 = cleanup_text($address1);
                }

                if ( defined $$response{'address:        address'} )
                {
                    $address2 = $$response{'address:        address'};
                    $address2 = cleanup_text($address2);
                }
                if ( defined $$response{'notify'} )
                {
                    $notify = $$response{'notify'};
                    $notify = cleanup_text($notify);
                }
                if ( defined $$response{'mnt-by'} )
                {
                    $mnt_by = $$response{'mnt-by'};
                    $mnt_by = cleanup_text($mnt_by);
                }
                if ( defined $$response{'OrgTechHandle'} )
                {
                    $org_tech_handle = $$response{'OrgTechHandle'};
                    $org_tech_handle = cleanup_text($org_tech_handle);

                }
                if ( defined $$response{'OrgTechName'} )
                {
                    $org_tech_name = $$response{'OrgTechName'};
                    $org_tech_name = cleanup_text($org_tech_name);
                }
                if ( defined $org_tech_handle or defined $org_tech_name )
                {
                    $org_tech_name   = "" if !defined $org_tech_name;
                    $org_tech_handle = "" if !defined $org_tech_handle;
                    $org_tech_info   = "$org_tech_name:$org_tech_handle";
                }

                if ( defined $$response{'descr'} )
                {
                    $descr = $$response{'descr'};
                    $descr = cleanup_text($descr);
                }

                if ( defined $mnt_by or defined $descr or defined $org_tech_info )
                {
                    $mnt_by        = "" if !defined $mnt_by;
                    $descr         = "" if !defined $descr;
                    $org_tech_info = "" if !defined $org_tech_info;
                    $mnt_by        = "$mnt_by:$descr:$org_tech_info";
                }

                my $protocol     = $log_data{$date_key}{source_ips}{$source_ip}{protocol};
                my $packet_count = $log_data{$date_key}{source_ips}{$source_ip}{packet_count};
                my $ports        = $log_data{$date_key}{source_ips}{$source_ip}{ports};

                $i_header_h->execute( $source_ip, $output_level, $host, $note, $ip_name, $city, $state, $cc, $country, $address1, $address2, $mnt_by );
                foreach my $port (@$ports)
                {
                    print "$source_ip, $processing_date, $output_level, $host, $note  $protocol, $packet_count, $port\n";
                    $i_port_h->execute( $source_ip, $processing_date, $protocol, $packet_count, $port );
                }
            }
        }
    }
    $i_header_h->finish();
    $s_header_h->finish();
    $i_port_h->finish();
}
################################################################################
# print a report
# expects nothing
# prints to stanard out
################################################################################
sub print_report()
{
    my $prepared_sqls     = prepare_sqls();
    my $date_activity_sql = $$prepared_sqls{"date_activity"};
    my $s_date_activity_h = $DBH->prepare($date_activity_sql);

    my $country_activity_sql = $$prepared_sqls{"country_activity"};
    my $s_country_activity_h = $DBH->prepare($country_activity_sql);

    my $ip_activity_count_sql = $$prepared_sqls{"ip_activity_count"};
    my $s_ip_activity_count_h = $DBH->prepare($ip_activity_count_sql);

    my $ip_activity_country_sql = $$prepared_sqls{"ip_activity_country"};
    my $s_ip_activity_country_h = $DBH->prepare($ip_activity_country_sql);

    my $port_activity_sql = $$prepared_sqls{"port_activity"};
    my $s_port_activity_h = $DBH->prepare($port_activity_sql);

    my $http_login_activity_sql = $$prepared_sqls{"http_login_activity"};
    my $s_http_login_activity_h = $DBH->prepare($http_login_activity_sql);

    print "Report Generated at: " . localtime() . "\n";
    print_line("=");

    $s_date_activity_h->execute();
    print "\nDATE ACTIVITY\n";
    print_line("-");
    print "Item       Process Date Frequency\n";
    print "---------- ------------ ----------\n";
    my $count = 0;
    while ( my ( $process_date, $process_date_count ) = $s_date_activity_h->fetchrow_array() )
    {
        $count++;
        printf( "%-10d %-12s %-10d\n", $count, $process_date, $process_date_count );
    }
    $s_country_activity_h->execute();
    print "\nCOUNTRY ACTIVITY\n";
    print_line("-");
    print "Item       Country                         Frequency\n";
    print "---------- ------------------------------  ----------\n";
    $count = 0;
    while ( my ( $country, $country_count ) = $s_country_activity_h->fetchrow_array() )
    {
        $count++;
        printf( "%-10d %-30s %-10d\n", $count, $country, $country_count );
    }

    $s_ip_activity_count_h->execute();
    print "\nIP ACTIVITY - sort by count\n";
    print_line("-");
    print "Item       IP Address         IP Name                                           Country                       Frequency\n";
    print "---------- ------------------ ------------------------------------------------- ----------------------------- ----------\n";
    $count = 0;
    while ( my ( $ip, $ip_name, $country, $ip_count ) = $s_ip_activity_count_h->fetchrow_array() )
    {
        $count++;
        printf( "%-10d %-18s %-50s %-30s %-10d\n", $count, $ip, $ip_name, $country, $ip_count );
    }
    $s_ip_activity_country_h->execute();
    print "\nIP ACTIVITY sort by country, count\n";
    print_line("-");
    print "Item       IP Address         IP Name                                           Country                       Frequency\n";
    print "---------- ------------------ ------------------------------------------------- ----------------------------- ----------\n";
    $count = 0;
    while ( my ( $ip, $ip_name, $country, $ip_count ) = $s_ip_activity_country_h->fetchrow_array() )
    {
        $count++;
        printf( "%-10d %-18s %-50s %-30s %-10d\n", $count, $ip, $ip_name, $country, $ip_count );
    }

    $s_port_activity_h->execute();
    print "\nPORT ACTIVITY\n";
    print_line("-");
    print "Item       Port     Port Name            Protocol Frequency\n";
    print "---------- -------- -------------------- -------- ----------\n";
    $count = 0;
    while ( my ( $protocol, $port, $port_name, $port_count ) = $s_port_activity_h->fetchrow_array() )
    {
        $count++;
        printf( "%-10d %-8d %-20s %-8s %-10d\n", $count, $port, $port_name, $protocol, $port_count );
    }

    $s_http_login_activity_h->execute();
    print "\nHTTP_LOGIN ACTIVITY\n";
    print_line("-");
    print "Item       IP Address         IP Name                                           Frequency \n";
    print "---------- ------------------ ------------------------------------------------- ----------\n";
    $count = 0;
    while ( my ( $ip, $ip_name, $ip_count ) = $s_http_login_activity_h->fetchrow_array() )
    {
        $count++;
        printf( "%-10d %-18s %-50s %-10d\n", $count, $ip, $ip_name, $ip_count );
    }
    $s_date_activity_h->finish();
    $s_country_activity_h->finish();
    $s_ip_activity_count_h->finish();
    $s_ip_activity_country_h->finish();
    $s_port_activity_h->finish();
    $s_http_login_activity_h->finish();
}
################################################################################
## Main
#################################################################################
#################################################################################
## Globals
#################################################################################
my $length_argv = @ARGV;    # check on correct number o command line paramters
my $data_file;              # file of logwatch data from an email - type = unformatted
my $create_csv;
my $bulk_insert_db;
my $insert_db;
my $services_load;
my $wget_services;
my $insert_services_data;
my $ip_target;
my $help   = 0;
my $report = 0;
my $load_mod_evasive;
my $load_http_log;
#################################################################################
## set up signal handling
#################################################################################
$SIG{INT} = sub {
    print "Recieved INT signal $!\n";
    ReadMode 0;
    if ($DBH) { $DBH->disconnect() }
    exit;
};
$SIG{__DIE__} = sub {
    print "Recieved DIE signal $!\n";
    ReadMode 0;
    if ($DBH) { $DBH->disconnect() }
    exit;
};
$SIG{QUIT} = sub {
    print "Recieved QUIT signal $!\n";
    ReadMode 0;
    if ($DBH) { $DBH->disconnect() }
    exit;
};
$SIG{INT} = sub {
    print "Recieved INT signal $!\n";
    ReadMode 0;
    if ($DBH) { $DBH->disconnect() }
    exit;
};

#$SIG{HUP}     = sub { print "Recieved HUP signal\n";  ReadMode 0; if ( $DBH ){   $DBH->disconnect(); exit;};
$SIG{KILL} = sub {
    print "Recieved KILL signal $!\n";
    ReadMode 0;
    if ($DBH) { $DBH->disconnect() }
    exit;
};
$SIG{STOP} = sub {
    print "Recieved STOP signal $!\n";
    ReadMode 0;
    if ($DBH) { $DBH->disconnect() }
    exit;
};
$SIG{ABRT} = sub {
    print "Recieved ABRT signal $!\n";
    ReadMode 0;
    if ($DBH) { $DBH->disconnect() }
    exit;
};

#$SIG{CANCEL} = sub {
#    print "Recieved CANCEL signal $!\n";
#    ReadMode 0;
#    if ($DBH) { $DBH->disconnect() }
#    exit;
#};
$SIG{TERM} = sub {
    print "Recieved TERM signal $!\n";
    ReadMode 0;
    if ($DBH) { $DBH->disconnect() }
    exit;
};
#################################################################################
# Process options
#################################################################################
if ( $length_argv < 1 )
{
    usage;
    exit;
}

GetOptions(
    "f=s"     => \$data_file,
    "u=s"     => \$USER,
    "p=s"     => \$PASSWORD,
    "b"       => \$bulk_insert_db,
    "i"       => \$insert_db,
    "s=s"     => \$services_load,
    "w=s"     => \$wget_services,
    "l"       => \$insert_services_data,
    "t=s"     => \$ip_target,
    "m=s"     => \$load_mod_evasive,
    "r"       => \$report,
    "a=s"     => \$load_http_log,
    "verbose" => $VERBOSE,
    "debug"   => \$DEBUG,
    "help"    => \$help,
);

if ($help)
{
    usage();
    exit;
}
if ($ip_target)
{
    if ( check_for_ip($ip_target) )
    {
        my_whois_lookup($ip_target);
    }
    else
    {
        print "ERROR: $ip_target is not a valid ip address. exiting.\n";
    }
    exit;
}

if ($wget_services)
{
    download_services( "https://svn.nmap.org/nmap/nmap-services", "$wget_services" );
}
$ENV{'ENVVAR1'} ? $PASSWORD = $ENV{'ENVVAR1'} : 1;
if ( !$PASSWORD )
{
    $PASSWORD = get_password("Please enter the mysql password for $USER: ");
    $DBH      = get_mysql_connection();
}
else
{
    $DBH = get_mysql_connection();
}
if ($report)
{
    print_report();
    exit;
}
if ($load_http_log)
{
    my $data = read_http_log($load_http_log);
    bulk_load_http_log_into_db($data);
    exit;
}
if ($load_mod_evasive)
{
    my $data = read_mod_evasive($load_mod_evasive);
    bulk_load_mod_evasive_into_db($data);    # the default
    exit;
}

if ($services_load)
{
    my $data = read_services($services_load);
    if ($insert_services_data)
    {
        insert_services_into_db($data);      # kept this for historical purposes.
    }
    else
    {
        bulk_load_services_into_db($data);    # the default
    }
    exit;
}
if ($data_file)
{
    load_logwatch_data($data_file);
}
if ($insert_db)                               # left this for historical purposes
{
    insert_data_into_logwatch();
}
if ($bulk_insert_db)
{
    bulk_load_whois_file_data();
}
if ($DBH) { $DBH->disconnect() }

exit;