##########################################################################
# package QuerySoma.pm
# This module contains functions which asks Soma questions

# V      Who       When        What
# ---------------------------------------------------------------------------
# 1.1.1  skendric  06-16-2008  Stylistic mods
# 1.1.0  skendric  03-12-2007  Stylistic mods
# 1.0.9  skendric  07-11-2006  Employ Net::IPAddress to validate IP addresses
# 1.0.8  skendric  08-31-2005  Add grab_old_hosts
# 1.0.7  skendric  07-25-2005  Add grab_os_hostname
# 1.0.6  skendric  07-17-2005  Generalize count_thresh_vuln_hosts
#                              Add find_wall_jack
# 1.0.5  skendric  07-12-2005  Add count_hosts
# 1.0.4  skendric  07-03-2005  Add count_thresh_vuln_hosts
# 1.0.3  skendric  06-09-2005  Check subroutine parameters
# 1.0.2  skendric  06-04-2005  Support simple-schema-1.4
# 1.0.1  skendric  02-13-2004  Add close_dbi_handles
# 1.0.0  skendric  02-06-2004  First version
#
#
#
# Authors:  Stuart Kendrick <sbk {insert at sign here } skendric
#                                {insert dot here} com>
#
# Source:  http://www.skendric.com/device/soma
#
# This software is available under the GNU GENERAL PUBLIC LICENSE, see
# http://www.fsf.org/licenses/gpl.html
#


package FHCRC::VDOPS::QuerySoma;

#### Load modules ####
use strict;
use warnings;
use threads;
use threads::shared;
use Carp qw(carp cluck croak confess);
use Data::Dumper;
use Exporter;
use Net::IPAddress qw(validaddr);
use Perl6::Say;

use lib '/home/soma/lib';
use FHCRC::VDOPS::DBTools;
use FHCRC::VDOPS::SomaCrud;
use FHCRC::VDOPS::SomaData;
use FHCRC::VDOPS::Utilities;

#### Set-up export stuff ####
our @ISA = qw(Exporter);
our @EXPORT =    qw(
                     ask_snmp
                     ask_osname
                     count_hosts
                     count_thresh_vuln_hosts
                     find_wall_jack
                     grab_old_hosts
                     grab_os_hostname
                    );

# Declare package local variables


##### Only subroutines below here ####


########################################################################
# Given an IP address, return hosts.snmp_sys_descr and i
# hosts.snmp_sys_objectid
########################################################################
sub ask_snmp {
  my $addr = shift;       # IP address
  my $dbh;                # DBI handle
  my $mac;                # hosts.mac
  my $sthiptomac;         # sth for querying hosts.mac using 'ip_addr'
  my $sthsnmp;            # sth for querying hosts.snmp_sys_descr and 
                          # hosts.snmp_sys_objectid
  my $sthtstamp;          # sth for querying hosts.last_seen
  my $sysDescr;           # hosts.snmp_sys_descr
  my $sysObjectID;        # hosts.snmp_sys_objectid

  # Debug trace
  trace_location('begin') if $debug > 2;

  # Sanity checking
  confess 'No parameters'              unless defined $addr;
  confess "$addr is not an IP address" unless validaddr($addr);

  # Connect to the database
  $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;",
                      "$dbRoUser", "$dbRoPass", {
           AutoCommit => 0,
           PrintError => 1,
           RaiseError => 1
  }  );

  # Prepare
  $sthiptomac = $dbh->prepare('SELECT mac
                               FROM hosts
                               WHERE ip_addr = ?
                              ');
  $sthsnmp = $dbh->prepare('SELECT snmp_sys_descr, snmp_sys_objectid
                            FROM hosts
                            WHERE mac = ?
                           ');
  $sthtstamp = $dbh->prepare('SELECT last_seen
                              FROM hosts
                              WHERE mac = ?
                             ');

  # Find $mac
  eval { $mac = find_fresh_mac($addr, $sthiptomac, $sthtstamp) };
  if ($@) {
    print_it("$@\n");
    confess "find_fresh_mac failed for addr $addr";
  }

  # If we found $mac, populate $sysDescr and $sysObjectID
  if (defined $mac) {
    eval { ($sysDescr, $sysObjectID) = find_one_list($mac, $sthsnmp) };
    if ($@) {
      print_it("$@\n");
      confess "find_one_list sthsnmp failed for mac $mac";
    }
  }

  # Otherwise, whine
  else {
    log_it("$addr not in 'hosts.ip_addr', skipping ask_snmp");
  }

  # Clean-up
  close_dbi_handles($dbh, $sthiptomac, $sthsnmp, $sthtstamp);

  # Debug trace
  trace_location('end') if $debug > 2;

  return ($mac, $sysDescr, $sysObjectID);
}



########################################################################
# Given an IP address, return os_versions.version_name
########################################################################
sub ask_osname {
  my $addr = shift;       # IP address
  my $dbh;                # DBI handle
  my $mac;                # hosts.mac
  my $osname;             # os_versions.version_name
  my $sthiptomac;         # sth for querying hosts.mac using 'ip_addr'
  my $sthosname;          # sth for querying os_versions.version_name
  my $sthtstamp;          # sth for querying hosts.last_seen

  # Debug trace
  trace_location('begin') if $debug > 2;

  # Sanity checking
  confess 'No parameters'              unless defined $addr;
  confess "$addr is not an IP address" unless validaddr($addr);

  # Connect to the database
  $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;",
                      "$dbRoUser", "$dbRoPass", {
           AutoCommit => 0,
           PrintError => 1,
           RaiseError => 1
  }  );

  # Prepare
  $sthiptomac = $dbh->prepare('SELECT mac
                               FROM hosts
                               WHERE ip_addr = ?
                              ');
  $sthosname = $dbh->prepare('SELECT version_name
                              FROM os_versions, hosts
                              WHERE versionid = hosts.osver
                              AND mac = ?
                             ');
  $sthtstamp = $dbh->prepare('SELECT last_seen
                              FROM hosts
                              WHERE mac = ?
                             ');

  # Find $oldMac
  eval { $mac = find_fresh_mac($addr, $sthiptomac, $sthtstamp) };
  if ($@) {
    print_it("$@\n");
    confess "find_fresh_mac failed for addr $addr";
  }

  # If we found $mac, populate $osname
  if (defined $mac) {
    eval { $osname = find_one_scalar($mac, $sthosname) };
    if ($@) {                   
      print_it("$@\n");         
      confess "find_one_scalar sthosname failed for mac $mac";
    }
  }

  # Otherwise, whine
  else {
    log_it("$addr not in 'hosts.ip_addr', skipping ask_osname");
  }

  # Clean-up
  close_dbi_handles($dbh, $sthiptomac, $sthosname, $sthtstamp);

  # Debug trace
  trace_location('end') if $debug > 2;

  return ($mac, $osname);
}


  

########################################################################
# Count the number of hosts defined in Soma
########################################################################
sub count_hosts {
  my $count;              # Number of hosts
  my $dbh;                # DBI handle
  my $sthcount;           # sth for querying hosts.hostid

  # Debug trace
  trace_location('begin') if $debug > 2;

  # Connect to the database
  $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;",
                      "$dbRoUser", "$dbRoPass", {
           AutoCommit => 0,
           PrintError => 1,
           RaiseError => 1
  }  );

  # Prepare
  $sthcount = $dbh->prepare('SELECT hosts.hostid
                             FROM hosts 
                           ');

  # Find the count
  eval { $count = $sthcount->execute() };
  if ($@) {
    print_it("$@");
    confess 'sthcount failed';
  }
 
  # Clean-up
  close_dbi_handles($dbh, $sthcount);

  # Debug trace
  trace_location('end') if $debug > 2;

  return $count;
}




########################################################################
# Given an integer, query Soma for the number of hosts which report more 
# than that number of open vulnerabilities and return the answer
########################################################################
sub count_thresh_vuln_hosts {
  my $count;              # Reference to the array of hosts reporting
                          # $thresh vulnerabilities
  my $dbh;                # DBI handle
  my $sthcount;           # sth for querying hosts.mac using 'ip_addr'
  my $thresh = shift;     # Vulnerability threshhold
  my $vuln;               # The answer

  # Debug trace
  trace_location('begin') if $debug > 2;

  # Sanity checking
  confess 'No parameters' unless defined $thresh;

  # Connect to the database
  $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;",
                      "$dbRoUser", "$dbRoPass", {
           AutoCommit => 0,
           PrintError => 1,
           RaiseError => 1
  }  );

  # Prepare
  $sthcount = $dbh->prepare('SELECT hosts.hostid
                             FROM hosts 
                             LEFT JOIN host_vulnerabilities 
                             ON hosts.hostid = host_vulnerabilities.host 
                             WHERE host_vulnerabilities.corrected_time ISNULL 
                             GROUP BY hosts.hostid 
                             HAVING COUNT(host_vulnerabilities.vuln) > ?
                           ');

  # Find the count
  eval { $count = find_multiple($thresh, $sthcount) };
  if ($@) {
    print_it("$@");
    confess "find_multiple sthcount failed for thresh $thresh";
  }
  else {
    $vuln = @$count;
  }

  # Clean-up
  close_dbi_handles($dbh, $sthcount);

  # Debug trace
  trace_location('end') if $debug > 2;

  return $vuln;
}




########################################################################
# Given ethernet switch information, return the associated wall_jack
########################################################################
sub find_wall_jack {
  my $dbh;                # DBI handle
  my $port;               # port hosting this wall_jack
  my $slot;               # slot hosting this port
  my $sthswitchid;        # sth for querying switch_name.switches
  my $sthwalljack;        # sth for querying wall_jack.switch_ports
  my $switch;             # Switch hosting this port
  my $switchid;           # switchid.switches associated with $switch
  my $switchport = shift; # "switch:module/port"
  my $walljack;           # The answer

  # Debug trace
  trace_location('begin') if $debug > 2;

  # Sanity check
  confess 'No parameters' unless defined $switchport;

  # Connect to the database
  $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;",
                      "$dbRoUser", "$dbRoPass", {
           AutoCommit => 0,
           PrintError => 1,
           RaiseError => 1
  }  );

  # Define local variables
  ($switch, $slot, $port) = $switchport =~ /(.*):(\d+)\/(\d+)/;

  # Prepare
  $sthwalljack = $dbh->prepare('SELECT wall_jack
                                FROM switch_ports
                                WHERE switch = ?
                                AND   slot = $slot
                                AND   port = $port
                               ');
  $sthswitchid = $dbh->prepare('SELECT switchid 
                                FROM switches
                                WHERE switch_name = ?
                               ');

  # Find switchid
  eval { $switchid = find_one_scalar($switch, $sthswitchid) };
  if ($@) {
    print_it("$@");
    confess "find_one_scalar sthswitchid failed for switch $switch";
  }
  return undef unless defined $switchid;

  # Find wall_jack
  eval { $walljack = find_one_scalar($switchid, $sthwalljack) };
  if ($@) {
    print_it("$@");
    confess "find_one_scalar sthwalljack failed for $switchid, $slot, $port";
  }
 
  # Clean-up
  close_dbi_handles($dbh, $sthwalljack, $sthswitchid);

  # Debug trace
  trace_location('end') if $debug > 2;

  return $walljack;
}



########################################################################
# Return a hash of hosts.os_hostname keyed by mac
########################################################################
sub grab_os_hostname {
  my $array_ref;
  my $dbh;                # DBI handle
  my %osHostname;         # The answer
  my $sthmacoshostname;   # sth for querying hosts

  # Debug trace
  trace_location('begin') if $debug > 2;

  # Connect to the database
  $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;",
                      "$dbRoUser", "$dbRoPass", {
           AutoCommit => 0,
           PrintError => 1,
           RaiseError => 1
  }  );

  # Prepare
  $sthmacoshostname = $dbh->prepare('SELECT mac, os_hostname
                                     FROM hosts 
                                    ');

  # Execute
  eval { $sthmacoshostname->execute() };
  if ($@) {
    print_it("$@");
    confess 'sthmacoshostname failed';
  }
 
  # Build data structure
  eval { $array_ref = $sthmacoshostname->fetchall_arrayref ( 
                            { mac         => 1, 
                              os_hostname => 1
                            }
                                                         )
       };

  
  # Clean-up
  close_dbi_handles($dbh, $sthmacoshostname);

  # Build data structure
  for my $element (@$array_ref) {
    if (defined $element->{os_hostname}) {
      $osHostname{$element->{mac}} = $element->{os_hostname};
    }
  }

  # Debug info
  if ($debug == 9) {
    say 'Dumping osHostname:';
    for my $mac (sort keys %osHostname) {
      say "$mac = $osHostname{$mac}";
    }
  }

  # Debug trace
  trace_location('end') if $debug > 2;

  return \%osHostname;
}




########################################################################
# Given a date, return a reference to a list of hosts.hostid for which 
# hosts.last_seen is older than that date
########################################################################
sub grab_old_hosts {
  my $array_ref;          # Temporary variable for holding the data 
                          # structure the DBI returns to us
  my $dbh;                # DBI handle
  my $killDate = shift;   # Cut-off date for calling a host 'old'
  my @oldHostid;          # List of hosts.hostid
  my $sth;                # sth for querying hosts

  # Debug trace
  trace_location('begin');

  # Connect to the database
  $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;",
                      "$dbRoUser", "$dbRoPass", {
           AutoCommit => 0,
           PrintError => 1,
           RaiseError => 1
  }  );

  # Prepare
  $sth = $dbh->prepare('SELECT hostid
                        FROM hosts 
                        WHERE last_seen < ?
                       ');

  # Build transaction
  eval { 

    # Execute query
    eval { $sth->execute($killDate) };
    if ($@) {
      print_it("$@");
      confess 'sth->execute failed';
    }
 
    # Retrieve results
    eval { $array_ref = $sth->fetchall_arrayref };
    if ($@) {
      print_it("$@");
      confess 'sth->fetchall failed';
    }

  };

  
  # Build data structure
  for my $row (@$array_ref) {
    push @oldHostid, $row->[0];
  }
  @oldHostid = sort { $a <=> $b } @oldHostid;

  # Clean-up
  close_dbi_handles($dbh, $sth);

  # Debug info
  if ($debug == 9) {
    say 'Found ' . scalar @oldHostid . ' old hosts:';
    say join($CR, @oldHostid);
  }

  # Debug trace
  trace_location('end');

  return \@oldHostid;
}



1;