########################################################################## # package SomaCrud.pm # This Perl module contains functions useful for accessing databases # using DBI # V Who When What # --------------------------------------------------------------------------- # 2.9.5 skendric 02-13-2009 Support new skip_networks arg format # 2.9.4 skendric 01-26-2009 More logging in iu_hosts_netbios # 2.9.3 skendric 07-15-2008 Skip addresses belonging to unknown routes # 2.9.2 skendric 07-07-2008 Log NetBIOS changes # 2.9.1 skendric 07-03-2008 Wrap NetAddr::IP calls with eval # 2.9.0 skendric 07-02-2008 Change skip_routers to flag_router # 2.8.9 skendric 06-20-2008 Debugging in iu_cam_table # 2.8.8 skendric 06-16-2008 Stylistic mods # 2.8.7 skendric 05-21-2008 Stylistic mods # 2.8.6 skendric 03-12-2007 Stylistic mods # 2.8.5 skendric 09-26-2006 Support Cat 355x # 2.8.4 skendric 07-14-2006 More error handling in iu_hosts_ou # 2.8.3 skendric 07-11-2006 Employ Net::IPAddress to validate IP addresses # 2.8.2 skendric 09-24-2005 Skip slot 1 in iu_cam_table # 2.8.1 skendric 08-31-2005 Add delete_hosts # 2.8.0 skendric 08-25-2005 Add iu_if_admin_status, iu_wap_clients # 2.7.8 skendric 07-25-2005 Add iu_hosts_adou # 2.7.7 skendric 07-01-2005 Fix sthtstamp bug in iu_host_vulnerabilities # 2.7.6 skendric 06-17-2005 Fix bugs in iu_hosts_vulnerabilities and # iu_corrected_hosts_vulnerabilities # 2.7.5 skendric 06-16-2005 Walk %sysDescr instead of %nodename in # iu_hosts_snmp # 2.7.4 skendric 06-14-2005 Modify iu_wall_jack to accept a hashref param # 2.7.3 skendric 06-12-2005 In iu_cam_table, set ip_addr and dns_hostname # to NULL when vlan changes, check # subroutine parameters # 2.7.2 skendric 06-04-2005 Support simple-schema-v1.4 # 2.7.1 skendric 04-08-2005 Fix bug in sthtstamp in iu_corr...ilities # 2.7.0 skendric 02-17-2005 Remove iu_ports reference to describe_switch # 2.6.0 skendric 02-14-2005 Add iu_wall_jack # 2.5.0 skendric 02-14-2005 Add insert/update %netBiosUser to # iu_hosts_netbios # 2.4.1 skendric 02-13-2005 Add close_dbi_handles # 2.4.0 skendric 02-13-2005 Changed name to 'SomaCrud', moved # generic routines to 'DBTools' # 2.3.0 skendric 02-11-2005 Add iu_corrected_host_vulnerabilities # 2.2.1 skendric 02-09-2005 Convert %hostname to %nodename # 2.2.0 skendric 02-04-2005 Rework vulnerability routines # 2.1.1 skendric 01-26-2005 Fix multiple MAC bug in iu_hosts_osver # 2.1.0 skendric 01-12-2005 Add iu_hosts_dns # 2.0.1 skendric 01-11-2005 Fix sth bug in find_fresh_mac # 2.0.0 skendric 01-10-2005 Handle IP addresses with multiple MAC addr # 1.9.2 skendric 01-06-2005 Add error checking and debugging # 1.9.1 skendric 01-03-2005 Fix bug in iu_host_vulnerabilities # 1.9.0 skendric 12-31-2004 Add iu_host_vulnerabilities # 1.8.0 skendric 12-20-2004 Add iu_vulnerabilities # 1.7.0 skendric 09-23-2004 Add iu_host_os # 1.6.1 skendric 09-17-2004 Fix bug in iu_netbios_name # 1.6.0 skendric 09-15-2004 Add iu_organizations, iu_operating_systems, # iu_os_versions # 1.5.4 skendric 09-11-2004 Replace 'do' with 'prepare + execute' # 1.5.3 skendric 09-10-2004 Bug fixes # 1.5.2 skendric 09-02-2004 Bug fixes # 1.5.1 skendric 08-27-2004 Bug fixes # 1.5.0 skendric 08-26-2004 StreamAt line find_xxx routines # 1.4.1 skendric 08-24-2004 Add iu_netbios_name # 1.4.0 skendric 08-22-2004 Add iu_host_snmp # 1.3.0 skendric 08-16-2004 Add iu_arp_table # 1.2.0 skendric 08-09-2004 Moved shared code to subroutines # 1.1.0 skendric 07-30-2004 Add iu_cam_table, iu_ports, iu_switches # 1.0.0 skendric 07-19-2004 First version # # # # Authors: Stuart Kendrick # # 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::SomaCrud; #### Load modules #### use strict; use warnings; use Carp qw(carp cluck croak confess); use Data::Dumper; use DBI qw(:sql_types); use DBD::Pg qw(:pg_types); use Exporter; use Net::IPAddress qw(validaddr); use Perl6::Say; use Switch; use lib '/home/soma/lib'; use FHCRC::VDOPS::DBTools; use FHCRC::VDOPS::NetworkTools; use FHCRC::VDOPS::SomaData; use FHCRC::VDOPS::Utilities; #### Set-up export stuff #### our @ISA = qw(Exporter); our @EXPORT = qw( delete_hosts find_fresh_mac iu_arp_table iu_cam_table iu_corrected_host_vulnerabilities iu_hosts_adou iu_hosts_dns iu_hosts_netbios iu_hosts_osver iu_hosts_snmp iu_hosts_ou iu_host_vulnerabilities iu_if_admin_status iu_os_versions iu_operating_systems iu_organizations iu_versions iu_ports iu_switches iu_vulnerabilities iu_wall_jack iu_wap_clients ); ##### Assign global variables #### ##### Only subroutines below here #### ######################################################################## # Given a reference to a list of hosts.hostid, remove these hosts ######################################################################## sub delete_hosts { my @commit; # Tracks failed commits my $doomed_hosts_ref = shift; # Reference to list of hosts to delete my $dbh; # DBI handle my $numHosts; # Number of elements in $doomed_hosts_ref my @rollback; # Tracks failed rollbacks my $rows; # Number of rows returned from a delete my $sthdeletehost; # sth for deleting hosts my $sthdeletevuln; # sth for deleting host_vulnerabilities my $sthmacip; # sth for querying hosts.mac and hosts.ip_addr # Debug trace trace_location('begin') if $debug; # Sanity checking confess 'Not enough parameters' unless defined $doomed_hosts_ref; confess 'Parameter must be array hash' unless ref $doomed_hosts_ref eq 'ARRAY'; # Define local variables $numHosts = @$doomed_hosts_ref; # Log the magnitude log_it("Preparing to delete $numHosts from Soma"); # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRoUser", "$dbRoPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthdeletehost = $dbh->prepare('DELETE FROM hosts WHERE hostid = ? '); $sthdeletevuln = $dbh->prepare('DELETE FROM host_vulnerabilities WHERE host = ? '); $sthmacip = $dbh->prepare('SELECT mac, ip_addr FROM hosts WHERE hostid = ? '); $rows = 1; # Walk hostids for my $hostid (@$doomed_hosts_ref) { my ($mac, $ip); # Create a transaction eval { # Find MAC and IP eval { $sthmacip->execute($hostid) }; if ($@) { print_it("$@"); confess "sthmacip->execute for hostid $hostid failed"; } eval { ($mac, $ip) = $sthmacip->fetchrow_array }; if ($@) { print_it("$@"); confess "sthmacip->fetchrow_array for hostid $hostid failed"; } $mac = 'unknown' unless defined $mac; $ip = 'unknown' unless defined $ip; # Delete row from hosts eval { $rows = $sthdeletehost->execute($hostid) }; if ($@) { print_it("$@"); confess "sthdeletehost for hostid $hostid failed"; } if ($rows != 1) { confess "Deleting hostid $hostid from host_vulnerabilities resulted in multiple rows"; } # Delete row from hosts_vulnerabilities eval { $rows = $sthdeletevuln->execute($hostid) }; if ($@) { print_it("$@"); confess "sthdeletevuln for hostid $hostid failed"; } if ($rows != 1) { confess "Deleting hostid $hostid from hosts resulted in multiple rows"; } }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to delete hostid $hostid\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $hostid; # Rollback eval { $dbh->rollback(); if ($@) { print_it("$@\n"); print_it("Unable to rollback delete of hostid $hostid\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $hostid; } }; # Finish rolling } # Finish whining and rolling # Otherwise, log the event else { log_it("Deleting $ip" . "/" . "$mac from Soma"); } } # End 'Walk hostid' # Clean-up close_dbi_handles($dbh, $sthmacip, $sthdeletehost, $sthdeletevuln); # Debug trace trace_location('begin') if $debug; return 1; } ######################################################################## # Given an IP address and two DBI sth (one for finding MAC addresses, # the other for finding time stamps), return the freshest MAC address # from Soma, where freshest means the MAC address whose hosts.last_seen # time stamp is the most recent. Return undef if no MAC address found ######################################################################## sub find_fresh_mac { my $addr = shift; # IP address my $answer; # The freshest MAC address from Soma my $sthiptomac = shift; # sth for querying hosts.mac given hosts.ip_addr my $sthtstamp = shift; # sth for querying hosts.last_seen given hosts.mac my $MACs; # Reference to an array of hosts.mac given $addr # Debug trace trace_location('begin') if $debug > 3; # Sanity checking confess 'Not enough parameters' unless (defined $addr and defined $sthiptomac and defined $sthtstamp); confess "$addr is not an IP address" unless validaddr($addr); # Query Soma eval { $MACs = find_multiple($addr, $sthiptomac) }; if ($@) { print_it("$@\n"); confess "find_multiple sthiptomac failed for addr $addr"; } # If we found something if (defined $MACs) { # If Soma contains exactly one MAC address for $addr, then life is easy if (@$MACs == 1) { $answer = $MACs->[0]->[0]; } # Otherwise, find the freshest one elsif (@$MACs > 1) { my ($freshMAC, $freshTime); $freshTime = 0; # Walk MAC addresses for my $array_ref (@$MACs) { my ($epoch, $mac, $tstamp); # Find hosts.last_seen for this MAC address $mac = $array_ref->[0]; eval { $tstamp = find_one_scalar($mac, $sthtstamp) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthtstamp failed for MAC $mac"; } # Convert to epoch seconds $epoch = tstamp_to_epoch($tstamp); # If it is the most recent MAC address, save it if ($epoch > $freshTime) { $freshTime = $epoch; $freshMAC = $mac; } } # Finish walking MAC addresses # Save the answer $answer = $freshMAC if defined $freshMAC; } # Finish if @$MACs > 1 elsif (@$MACs < 1) { print_it("I don't understand what has happened ... MACs is empty\n"); } } # Finish 'if defined $MACs' # Debug info if ($debug > 3) { if (defined $answer) { say "Freshest MAC for $addr is $answer"; } else { say "Couldn't find a MAC for $addr"; } } # Debug trace trace_location('end') if $debug > 3; return $answer; } ######################################################################## # Given a host/vuln combination and a DBI sth, return: # (a) the associated timestamps from Soma (discovered_time, # corrected_time, last_checked_time) # (b) undef if not found ######################################################################## sub find_host_vuln { my $array_ref; my $discovered_time; my $corrected_time; my $last_checked_time; my ($host, $vuln, $sth) = @_; # Debug trace trace_location('begin') if $debug > 2; # Sanity checking confess 'Not enough parameters' unless (defined $host and defined $vuln and defined $sth); # Find time stamps $sth->bind_param(1, $host); $sth->bind_param(2, $vuln); $sth->execute(); $array_ref = $sth->fetchall_arrayref(); # Process results if (@{$array_ref} == 0) { # This host/vuln is not in Soma print_it("Vuln $vuln is new for host $host\n") if $debug == 4; undef $discovered_time; undef $corrected_time; undef $last_checked_time; } elsif (@{$array_ref->[0]} == 3) { # Found host/vuln print_it("Found host $host / vuln $vuln\n") if $debug > 3; $discovered_time = $array_ref->[0]->[0]; $corrected_time = $array_ref->[0]->[1]; $last_checked_time = $array_ref->[0]->[2]; } elsif (@{$array_ref->[0]} != 3 ) { # Something is broken for my $row (@$array_ref) { print_it("$row->[0]\n") } print_it("\n"); confess "Something is broken for host $host and vuln $vuln"; } # Debug trace trace_location('end') if $debug > 2; return ($discovered_time, $corrected_time, $last_checked_time); } ######################################################################## # Given a switchid/slot/port combination and a DBI sth, return: # (a) the associated switch_ports.portid from Soma # (b) undef if not found ######################################################################## sub find_portid { my $array_ref; my $portID; my ($switchID, $slot, $port, $sth) = @_; # Debug trace trace_location('begin') if $debug == 10; confess 'switchID not defined' unless defined $switchID; confess 'slot not defined' unless defined $slot; confess 'port not defined' unless defined $port; confess 'sth not defined' unless defined $sth; # Find switch_ports.portid $sth->bind_param(1, $switchID); $sth->bind_param(2, $slot); $sth->bind_param(3, $port); $sth->execute(); $array_ref = $sth->fetchall_arrayref(); # Process results if (@$array_ref == 1) { # Found portID $portID = $array_ref->[0]->[0]; } elsif (@$array_ref > 1) { # Something is broken for my $row (@$array_ref) { print_it("$row->[0] ") } print_it("\n"); confess "Uniqueness constraint on 'portid' for $switchID:$slot/$port violated"; } elsif (@$array_ref < 1) { # This switch/slot/port is not in Soma undef $portID; } # Debug trace trace_location('end') if $debug == 10; return $portID; } ######################################################################## # Insert and Update ARP: walk %arp, inserting IP addresses into Soma ######################################################################## sub iu_arp_table { my $dbh; # DBI handle my @commit; # Tracks failed commits my $newIP; # IP address in %arp my $newMAC; # MAC address in %arp my $oldIP; # IP address in Soma my $oldMAC; # MAC address in Soma my @rollback; # Tracks failed rollbacks my $rows; # Number of inserts performed, should never # be anything but "1" my $sthinsert; # sth for inserting a new record my $sthip; # sth for querying hosts.ip_addr my $sthmac; # sth for querying hosts.mac my $sthupdateip; # sth for updating hosts.ip_addr my $sthupdatetime; # sth for updating hosts.last_seen my $tStamp; # ISO 8601 time stamp # Debug trace trace_location('begin') if $debug; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Grab time $tStamp = epoch_to_tstamp(time); # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthinsert = $dbh->prepare('INSERT INTO hosts (mac, ip_addr, first_seen, last_seen, last_updated) VALUES (?, ?, ?, ?, ?) '); $sthip = $dbh->prepare('SELECT ip_addr FROM hosts WHERE mac = ? '); $sthmac = $dbh->prepare('SELECT mac FROM hosts WHERE mac = ? '); $sthupdateip = $dbh->prepare('UPDATE hosts SET ip_addr = ?, last_seen = ?, last_updated = ? WHERE mac = ? '); $sthupdatetime = $dbh->prepare('UPDATE hosts SET last_seen = ? WHERE mac = ? '); # Walk %arp IP: for $newIP (keys %arp) { my ($addr_obj, $mask); # Find mask $mask = find_mask($newIP); next IP unless defined $mask; # Create NetAddr::IP object eval { $addr_obj = NetAddr::IP->new($newIP, $mask) }; if ($@ or not defined $addr_obj) { log_it("Bad addr $newIP/ $mask: $@"); next IP; } # Skip routers next IP if flag_router($addr_obj); # Skip networks next IP if skip_networks($addr_obj, \%excludeRoute); # Focus on networks of interest next IP unless skip_networks($addr_obj, \%includeRoute); # Grab MAC ($newMAC) = ($arp{$newIP}) =~ /-(\w\w:\w\w:\w\w:\w\w:\w\w:\w\w)/; confess '%arp is broken' unless defined $newMAC; # Create a transaction eval { # Does Soma contain this MAC address? eval { $oldMAC = find_one_scalar($newMAC, $sthmac) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthmac failed for newMAC $newMAC"; } # If it does, then ask if Soma contains an associated IP address if (defined $oldMAC) { eval { $oldIP = find_one_scalar($newMAC, $sthip) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthip failed for newMAC $newMAC"; } } # If this MAC address exists already if (defined $oldMAC) { # If this IP address exists already if (defined $oldIP) { # If oldIP and newIP are equal, update time stamps if ($oldIP eq $newIP) { say "Update time stamps for $oldMAC, $oldIP" if $debug == 7; $rows = $sthupdatetime->execute($tStamp, $oldMAC); if ($rows != 1) { confess "Updating $oldMAC resulted in multiple rows"; } } # End 'if oldIP equals newIP' # Otherwise, oldIP does not equal newIP: change the value # for hosts.ip_addr and log the event elsif ($oldIP ne $newIP) { log_it("$newMAC has changed from $oldIP to $newIP"); pirint_it("Updating $newMAC to newIP $newIP\n") if $debug == 7; $rows = $sthupdateip->execute($newIP, $tStamp, $tStamp, $newMAC); if ($rows != 1) { confess "Updating $newMAC resulted in multiple rows"; } } # End 'if oldIP does not equal newIP' } # End 'if defined oldIP ' # Otherwise, Soma has seen this MAC address but hasn't seen # this IP address: update this record to include $newIP in # hosts.ip_addr elsif (not defined $oldIP) { say "Inserting new IP $oldMAC, $newIP" if $debug == 7; $rows = $sthupdateip->execute($newIP, $tStamp, $tStamp, $newMAC); if ($rows != 1) { confess "Updating $newMAC resulted in multiple rows"; } } # End 'if not defined oldIP' } # End 'if defined oldMAC' # Otherwise, if Soma hasn't seen this MAC address yet, insert it elsif (not defined $oldMAC) { say "Inserting new MAC $newMAC, $newIP" if $debug == 7; $rows = $sthinsert->execute($newMAC, $newIP, $tStamp, $tStamp, $tStamp); if ($rows != 1) { confess "Updating $newMAC resulted in multiple rows"; } } # End 'if not defined oldMAC' # Commit that puppy $dbh->commit(); }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $newMAC to HOSTS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $newMAC; # Rollback eval { $dbh->rollback(); if ($@) { print_it("$@\n"); print_it("Unable to rollback $newMAC insert into HOSTS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $newMAC; } }; # Finish rolling } # Finish whining and rolling } # Finish walking %arp # Clean-up close_dbi_handles($dbh, $sthinsert, $sthip, $sthmac, $sthupdateip, $sthupdatetime); # Debug trace trace_location('end') if $debug; return 1; } ######################################################################## # Insert and Update CAM: walk %cam, inserting/updating MAC addresses # and their associated switch information ######################################################################## sub iu_cam_table { my $slot; # Blade extracted from %cam my @commit; # Tracks failed commits my $dbh; # DBI handle my $newSwitchName; # Esx name extracted from %cam my $newMAC; # MAC address extracted from %cam my $newPort; # Port extracted from %cam my $newPortID; # switch_ports.portid of $newMAC, retrieved from Soma my $newSlot; # Slot extracted from %cam my $newSwitchID; # switches.switchid of esx containing $newMAC, # retrieved from Soma my $newVLAN; # VLAN number extracted from %cam my $oldMAC; # MAC address retrieved from Soma: either # $oldMAC equals $newMAC or $oldMAC is undef # (undef means that Soma hasn't seen this MAC # before) my $oldPortID; # switch_ports.portid of $oldMAC, retrieved from Soma my $oldSwitchID; # switches.switchid of esx containing $oldMAC, # retrieved from Soma my $oldSwitchName; # Esx name associated with $oldMAC, retrieved # from Soma my $oldVLAN; # VLAN number associated with $oldMAC, # retrieved from Soma my $qMAC; # DBI quoted version of $newMAC my @rollback; # Tracks failed rollbacks my $rows; # Number of rows returned by UPDATE; should # always be equal to "1" my $sthinsertcam; # sth for inserting hosts.mac, hosts.vlan, and # hosts.switch_port my $sthmac; # sth for querying hosts.mac my $sthportid; # sth for querying switches.switch_name my $sthswitch; # sth for querying switch_ports.switch my $sthswitchid; # sth for querying switches.switch_id my $sthswitchname; # sth for querying switches.switch_name my $sthswitchport; # sth for querying hosts.switch_port my $sthupdateport; # sth for updating hosts.switch_port my $sthupdatetime; # sth for updating hosts.last_seen my $sthupdatevlan; # sth for updating hosts.vlan my $sthvlan; # sth for querying hosts.vlan my $tStamp; # ISO 8601 time stamp # Debug trace trace_location('begin') if $debug; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Grab time $tStamp = epoch_to_tstamp(time); # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthinsertcam = $dbh->prepare('INSERT INTO hosts (mac, vlan, switch_port, first_seen, last_seen, last_updated) VALUES (?, ?, ?, ?, ?, ?) '); $sthmac = $dbh->prepare('SELECT mac FROM hosts WHERE mac = ? '); $sthportid = $dbh->prepare('SELECT portid FROM switch_ports WHERE switch = ? AND slot = ? AND port = ? '); $sthswitch = $dbh->prepare('SELECT switch FROM switch_ports WHERE portid = ? '); $sthswitchid = $dbh->prepare('SELECT switchid FROM switches WHERE switch_name = ? '); $sthswitchname = $dbh->prepare('SELECT switch_name FROM switches WHERE switchid = ? '); $sthswitchport = $dbh->prepare('SELECT switch_port FROM hosts WHERE mac = ? '); $sthupdateport = $dbh->prepare('UPDATE hosts SET switch_port = ?, last_seen = ?, last_updated = ? WHERE mac = ? '); $sthupdatetime = $dbh->prepare('UPDATE hosts SET last_seen = ? WHERE mac = ? '); $sthupdatevlan = $dbh->prepare('UPDATE hosts SET ip_addr = NULL, vlan = ?, dns_hostname = NULL, last_seen = ?, last_updated = ? WHERE mac = ? '); $sthvlan = $dbh->prepare('SELECT vlan FROM hosts WHERE mac = ? '); # Walk through %cam, pulling out the hash associated with each esx for my $addr (keys %cam) { # Assign local variable $newSwitchName = $esx{$addr}; # Debug info say "Begin processing $newSwitchName" if $debug > 1; # Walk through said hash CAM: for my $cam (keys %{$cam{$addr}}) { # Pull apart data structure say "cam = $cam" if $debug == 7; ($newMAC ) = ($cam) =~ /^(\w\w:\w\w:\w\w:\w\w:\w\w:\w\w)/; ($newSlot) = ($cam) =~ /(\d{1,2})\/\d{1,2}$/; ($newPort) = ($cam) =~ /\/(\d{1,2})$/; $newVLAN = $cam{$addr}->{$cam}; # Sanity check print_it("newMAC not defined in $addr: $cam\n") unless defined $newMAC; next CAM unless defined $newMAC; print_it("newSlot not defined in $addr: $cam\n") unless defined $newSlot; next CAM unless defined $newSlot; print_it("newPort not defined in $addr: $cam\n") unless defined $newPort; next CAM unless defined $newPort; print_it("newVLAN not defined in $addr: $cam\n") unless defined $newVLAN; next CAM unless defined $newVLAN; # Debug info say "$newMAC lives on $newSlot $SLASH $newPort in vlan $newVLAN" if $debug == 7; # Create a transaction eval { # Has Soma seen this MAC before? If it has, then find_one_scalar # will return $newMAC, and I will stuff this into $oldMAC # and I will look for switch-related parameters for $oldMAC. # We don't require that Soma have seen $oldMAC or related # switch parameters before. If it has, then I'll use this # information to determine just how much updating to do. # If I haven't, then we'll be inserting new information. eval { $oldMAC = find_one_scalar($newMAC, $sthmac) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthmac failed for newMAC $newMAC"; } if (defined $oldMAC) { if ($oldMAC ne $newMAC) { confess "Something is terribly broken in Soma: oldMAC $oldMAC does not equal newMAC $newMAC"; } } # If Soma has seen this MAC, then look for VLAN if (defined $oldMAC) { eval { $oldVLAN = find_one_scalar($oldMAC, $sthvlan) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthvlan failed for oldMAC $oldMAC"; } } # End 'if defined oldMAC' # If Soma has seen this MAC, then look for portid if (defined $oldMAC) { eval { $oldPortID = find_one_scalar($oldMAC, $sthswitchport) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthswitchport failed for oldMAC $oldMAC"; } # If Soma has seen this switch_port, then look for switchid if (defined $oldPortID) { eval { $oldSwitchID = find_one_scalar($oldPortID, $sthswitch) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthswitch failed for oldPortID $oldPortID"; } # If Soma has seen this switch_id, then retrieve switch_name if (defined $oldSwitchID) { eval { $oldSwitchName = find_one_scalar($oldSwitchID, $sthswitch) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthswitch failed for oldSwitchID $oldSwitchID"; } } # End 'if defined $oldSwitchID' } # End 'if defined $oldPortID' } # End 'if defined $oldMAC' # Now let's figure out whether or not Soma has seen this newly # discovered switch:slot/port combination. If it has, great -- # I can use that information to figure out whether this MAC # address has moved or not. If it hasn't, then I bail -- I don't # want to insert a MAC address into Soma all by its lonesome ... # that doesn't feel useful to me. # Find newSwitchID eval { $newSwitchID = find_one_scalar($newSwitchName, $sthswitchid) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthswitchid failed for $sthswitchid on $newSwitchName"; } unless (defined $newSwitchID) { confess "esx{$addr} $esx{$addr} does not exist in Soma"; } # Find newPortID $newPortID = find_portid($newSwitchID, $newSlot, $newPort, $sthportid); if ($@) { print_it("$@\n"); confess "find_portid failed for $newSwitchID:$newSlot/$newPort"; } unless (defined $newPortID) { confess "$esx{$addr}:$newSlot/$newPort does not exist in Soma"; } # OK, i've dug up whatever information Soma has on both # $oldMAC and $newMAC. Figure out how much to UPDATE and # how to much to INSERT # If oldMAC is not defined, then Soma hasn't seen this MAC # address before: insert hosts.mac if (not defined $oldMAC) { say "Inserting $newMAC, $newVLAN, $newPortID" if $debug == 7; $rows = $sthinsertcam->execute ($newMAC, $newVLAN, $newPortID, $tStamp, $tStamp, $tStamp); if ($rows != 1) { confess "Inserting $qMAC resulted in more than one row"; } } # End 'if ! defined $oldMAC' # Otherwise, oldMAC does exist, meaning that Soma has seen # this MAC address before. elsif (defined $oldMAC) { # If oldPortID is not defined, then Soma hasn't associated # this MAC address with a port yet: update hosts.switch_port if (not defined $oldPortID) { say "Updating port for $newMAC, $newPortID" if $debug == 7; $rows = $sthupdateport->execute ($newPortID, $tStamp, $tStamp, $newMAC); if ($rows != 1) { confess "Updating $newMAC resulted in more than one row"; } } # End 'if not defined $oldPortID' # Otherwise, oldPortID is defined, and Soma has associated # this MAC address with a port already. elsif (defined $oldPortID) { # Compare oldPortID and newPortID. If they are the same, # then update hosts.last_seen if ($oldPortID == $newPortID) { say "Updating last_seen/p for $newMAC, $newPortID" if $debug == 7; $rows = $sthupdatetime->execute($tStamp, $newMAC); if ($rows != 1) { confess "Updating $newMAC resulted in multiple rows"; } } # End oldPortID == newPortID # Otherwise, oldPortID is not the same as newPortID. Therefore, # Soma has seen this MAC before, but it has moved: update # hosts.switch_port, hosts.last_seen, and hosts.last_updated elsif ($oldPortID != $newPortID) { say "$newMAC has moved to $newPortID" if $debug == 7; $rows = $sthupdateport->execute ($newPortID, $tStamp, $tStamp, $newMAC); if ($rows != 1) { confess "Updating $qMAC resulted in multiple rows"; } } # End oldPortID != newPortID } # End 'if defined $oldPortID' # Now, let's think about VLANs. If $oldVLAN is not defined, # then Soma has not previously associated this MAC address # with a VLAN: update hosts.vlan if (not defined $oldVLAN) { say "Updating VLAN to $newVLAN for $newMAC" if $debug == 7; $rows = $sthupdatevlan->execute ($newVLAN, $tStamp, $tStamp, $newMAC); if ($rows != 1) { confess "Updating $qMAC resulted in multiple rows"; } } # End 'if not defined oldVLAN' # Otherwise, $oldVLAN is defined. elsif (defined $oldVLAN) { # Now, if $oldVLAN is different from $newVLAN, then # this MAC has moved (or, possibly, the VLAN membership # of the port to which it is attached has changed): # update hosts.vlan in hosts if ($oldVLAN != $newVLAN) { say "$newMAC has moved to VLAN $newVLAN" if $debug == 7; $rows = $sthupdatevlan->execute ($newVLAN, $tStamp, $tStamp, $newMAC); if ($rows != 1) { confess "Updating $qMAC resulted in multiple rows"; } } # End 'if oldVLAN != newVLAN' # Otherwise, $oldVLAN and $newVLAN are the same: update # hosts.last_seen. This is probably an unnecessary # step, because I likely did precisely this when I found # that oldPortID and newPortID were the same, above. elsif ($oldVLAN == $newVLAN) { say "Updating last_seen/v for $newMAC, $newPortID" if $debug == 7; $rows = $sthupdatetime->execute($tStamp, $newMAC); if ($rows != 1) { confess "Updating $qMAC resulted in multiple rows"; } } # End 'if oldVLAN == newVLAN' } # End 'if defined oldVLAN' } # End 'if defined $oldMAC' # Commit that puppy $dbh->commit(); }; # Finish transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit HOSTS where mac=$newMAC\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $esx{$addr}; # Rollback eval { $dbh->rollback(); if ($@) { print_it("Unable to rollback commit HOSTS where mac=$newMAC\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $esx{$addr}; } }; } # Finish whining and rolling } # Finish walking through said hash # Debug info say "Done processing $newSwitchName\n" if $debug > 1; } # Finish walking through %cam # Clean-up close_dbi_handles($dbh, $sthmac, $sthportid, $sthswitch, $sthswitchid, $sthswitchname, $sthswitchport, $sthupdateport, $sthupdatetime, $sthupdatevlan, $sthvlan); # Debug trace trace_location('end') if $debug; return 1; } ######################################################################## # Given an IP address and a reference to its associated # Net::Nessus::ScanLite object, update # hosts_vulnerabilities.corrected_time ######################################################################## sub iu_corrected_host_vulnerabilities { my $addr = shift; # IP address associated with $nessus my $dbh; # DBI handle my @commit; # Tracks failed commits my @corrected; # List of vulnerabilities.vuln_name which # have been corrected my $hostID; # hosts.hostid returned from Soma my $hostVulns; # Ref to list of hosts_vulnerabilities.vuln my $nessus = shift; # ref to Net::Nessus::ScanLite object my @newVulnName; # List of Nessus vulnerability names my $oldMAC; # the hosts.mac which Soma returns when queried # with $newIP my %oldVulnName; # Hash of vulnerabilities.vuln_name keyed # by vulnerabilities.vulnid my @rollback; # Tracks failed rollbacks my $rows; # Number of inserts performed, should never # be anything but "1" # host_vulnerabilities.corrected_time my $sthhost; # sth for querying hosts.hostid my $sthiptomac; # sth for querying hosts.mac using hosts.ip_addr my $sthhostvuln; # sth for querying host_vulnerabilities.vuln # using host_vulnerabilities.host my $sthtstamp; # sth for querying hosts.last_seen my $sthupdatecorr; # sth for updating # host_vulnerabilities.corrected_time my $sthvulnid; # sth for querying vulnerabilities.vulnid my $sthvulnname; # sth for querying vulnerabilities.vuln_name my $sthvulntstamps; # sth for querying discovered_time, corrected_time, # and last_checked_time in host_vulnerabilities my $tStamp; # ISO 8601 time stamp my $vulnID; # vulnerabilities.vulnid returned from Soma # Debug trace trace_location('begin') if $debug; # Sanity checking confess 'Not enough parameters' unless defined $addr and defined $nessus; confess "$addr is not an IP address" unless validaddr($addr); # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Grab time $tStamp = epoch_to_tstamp(time); # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthhost = $dbh->prepare('SELECT hostid FROM hosts WHERE mac = ? '); $sthhostvuln = $dbh->prepare('SELECT vuln FROM host_vulnerabilities WHERE host = ? '); $sthiptomac = $dbh->prepare('SELECT mac FROM hosts WHERE ip_addr = ? '); $sthtstamp = $dbh->prepare('SELECT last_seen FROM hosts WHERE mac = ? '); $sthupdatecorr = $dbh->prepare('UPDATE host_vulnerabilities SET corrected_time = ? WHERE host = ? AND vuln = ? '); $sthvulnid = $dbh->prepare('SELECT vulnid FROM vulnerabilities WHERE vuln_name = ? '); $sthvulnname = $dbh->prepare('SELECT vuln_name FROM vulnerabilities WHERE vulnid = ? '); $sthvulntstamps = $dbh->prepare('SELECT discovered_time, corrected_time, last_checked_time FROM host_vulnerabilities WHERE host = ? AND vuln = ? '); # Find $oldMac eval { $oldMAC = find_fresh_mac($addr, $sthiptomac, $sthtstamp) }; if ($@) { print_it("$@\n"); cluck "find_fresh_mac failed for addr $addr"; } unless (defined $oldMAC) { log_it("$addr not in 'hosts', skipping iu_corrected_host_vulnerabilities"); goto FINISH; } # Define $hostid eval { $hostID = find_one_scalar($oldMAC, $sthhost) }; if ($@) { print_it("$@\n"); cluck "find_one_scalar sthhost failed for $addr"; } unless (defined $hostID) { log_it("hostid for $addr not defined, something is broken"); goto FINISH; } # Build list of host.vulnerabilities.vuln eval { $hostVulns = find_multiple($hostID, $sthhostvuln) }; if ($@) { print_it("$@\n"); confess "find_multiple sthhostvuln failed for host $hostID"; } say 'hostVulns = ' if $debug == 7; say Dumper($hostVulns) if $debug == 7; # Build @oldVulnName for my $hostVuln (@$hostVulns) { my $corrected_time; my $vulnName; # Find vuln_name eval { $vulnName = find_one_scalar($hostVuln->[0], $sthvulnname) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthhostvuln failed for hostVuln $hostVuln->[0]"; } # Ask if this host_vulnerabilities.vuln has already been corrected eval { my ($discovered_time, $last_checked_time); ($discovered_time, $corrected_time, $last_checked_time) = find_host_vuln($hostID, $hostVuln->[0], $sthvulntstamps); }; if ($@) { print_it("$@\n"); confess "find_host_vuln sthvulntstamps failed for hostVuln:$hostVuln->[0]"; } # Debug info if (defined $corrected_time and $debug == 4) { say "For hostVuln $hostVuln->[0], vulnName $vulnName, ctime = $corrected_time"; } # If it hasn't been corrected, add it to oldVulnName if (not defined $corrected_time or $corrected_time eq $EMPTY_STR) { $oldVulnName{$vulnName} = $hostVuln->[0]; say "Adding $hostVuln->[0] to oldVulnName{$vulnName}" if $debug == 4; } } # End 'Build oldVulnName' # Debug info if ($debug == 4) { for my $key (sort keys %oldVulnName) { say "oldVulnName{$key} = $oldVulnName{$key}"; } } # Walk through $nessus, building @newVulnName for my $hole ($nessus->hole_list) { my $vulnName; $vulnName = $hole->ScanID; push @newVulnName, $vulnName if defined $vulnName; } # Debug info say "newVulnName = @newVulnName" if $debug == 4; # Now, walk thru @newVulnName, deleting each one from %oldVulnName # Whatever is left in %oldVulnName is the list of corrected # vulnerabilities for my $vulnName (@newVulnName) { delete $oldVulnName{$vulnName}; } # Walk through %oldVulnName, updating host_vulnerabilities.corrected_time for my $vulnID (values %oldVulnName) { say "Nessus sees $nodename{$addr}/$addr as having fixed $vulnID" if $debug == 4; # Create a transaction eval { $rows = $sthupdatecorr->execute($tStamp, $hostID, $vulnID); if ($rows != 1) { confess "Updating host $hostID with vuln $vulnID resulted in multiple rows"; } # Commit that puppy $dbh->commit(); }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $hostID:$vulnID to HOST_VULNERABILITIES\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $vulnID; # Rollback eval { $dbh->rollback(); if ($@) { print_it("$@\n"); print_it("Unable to rollback $hostID:$vulnID insert into HOST_VULNERABILITIES\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $vulnID; } }; # Finish rolling } # Finish whining and rolling } # Finish walking %oldVulnName FINISH: # This is where the various 'goto' functions above land # Clean-up close_dbi_handles($dbh, $sthhost, $sthiptomac, $sthhostvuln, $sthtstamp, $sthupdatecorr, $sthvulnid, $sthvulnname, $sthvulntstamps); # Debug trace trace_location('end') if $debug; return 1; } ######################################################################## # Insert and Update ad_ou in 'hosts'. Given a reference to a hash of # OUs keyed by hosts.mac, update hosts.ad_ou ######################################################################## sub iu_hosts_adou { my $dbh; # DBI handle my @commit; # Tracks failed commits my $newOU; # Acquired from $ou my $oldOU; # Contents of hosts.ad_ou my $osHostname; # Contents of hosts.os_hostname my $ou = shift; # Hash of OUs keyed by hostid my @rollback; # Tracks failed rollbacks my $rows; # Number of inserts performed, should never # be anything but "1" my $sthadou; # sth for querying hosts.ad_ou my $sthoshostname; # sth for querying hosts.os_hostname my $sthtstamp; # sth for querying hosts.last_seen my $sthupdateadou; # sth for updating hosts.ad_ou my $sthwipeadou; # sth for setting hosts.ad_ou to NULL my $tStamp; # ISO 8601 time stamp # Debug trace trace_location('begin') if $debug; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Grab time $tStamp = epoch_to_tstamp(time); # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthadou = $dbh->prepare('SELECT ad_ou FROM hosts WHERE mac = ? '); $sthoshostname = $dbh->prepare('SELECT os_hostname FROM hosts WHERE mac = ? '); $sthupdateadou = $dbh->prepare('UPDATE hosts SET ad_ou = ?, last_updated = ? WHERE mac = ? '); $sthwipeadou = $dbh->prepare('UPDATE hosts SET ad_ou = NULL, last_updated = ? WHERE mac = ? '); # Walk $ou say "Walking ou, inserting/updating as needed" if $debug; MAC: for my $mac (keys %$ou) { $newOU = $ou->{$mac}; # Find $oldOU eval { $oldOU = find_one_scalar($mac, $sthadou) }; if ($@) { print_it("$@\n"); confess "sthadou failed for mac $mac"; } # Skip ahead if both newOU and oldOU are undefined next MAC if (not defined $newOU and not defined $oldOU); # Debug info say "oldOU = $oldOU" if defined $oldOU and $debug == 7; # Skip ahead if they are the same next MAC if (defined $oldOU and defined $newOU and $oldOU eq $newOU); # Create a transaction eval { # Find os_hostname eval { $osHostname = find_one_scalar($mac, $sthoshostname) }; if ($@) { print_it("$@\n"); confess "sthoshostname failed for mac $mac"; } $osHostname = "undef" unless defined $osHostname; # If the OU has changed to something new, update this entry if (defined $newOU) { say "Updating ad_ou $newOU for $osHostname = $mac" if $debug == 7; $rows = $sthupdateadou->execute($newOU, $tStamp, $mac); if ($rows != 1) { confess "Updating $mac = $osHostname with $newOU resulted in multiple rows"; } } # If newOU is undefined, then wipe whatever is in Soma else { say "Wiping ad_ou for $osHostname = $mac" if $debug == 7; $rows = $sthwipeadou->execute($tStamp, $mac); if ($rows != 1) { confess "Wiping ad_ou for $mac = $osHostname resulted in multiple rows"; } } # Commit that puppy $dbh->commit(); # Log the event $oldOU = 'undef' unless defined $oldOU; $newOU = 'undef' unless defined $newOU; log_it("$mac = $osHostname changed from $oldOU to $newOU"); }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $mac = $osHostname / $newOU to HOSTS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $mac; # Rollback eval { $dbh->rollback(); if ($@) { print_it("$@\n"); print_it("Unable to rollback $mac = $osHostname / $newOU to HOSTS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $mac; } }; # Finish rolling } # Finish whining and rolling } # Finish walking $ou # Clean-up close_dbi_handles($dbh, $sthadou, $sthoshostname, $sthupdateadou); # Debug trace trace_location('end') if $debug; return 1; } ######################################################################## # Insert and Update dns_hostname in 'hosts'. This routine only updates # existing entries in 'hosts'; because it receives only IP address and # DNS information, it cannot insert into 'hosts' (i.e. it would need a # MAC address in order to insert into 'hosts', and it doesn't have that) ######################################################################## sub iu_hosts_dns { my $dbh; # DBI handle my @commit; # Tracks failed commits my $newDnsName; # DNS name from %dnsname my $newIP; # IP address from %dnsname my $oldDnsName; # the hosts.dns_hostname which Soma returns # when queried with $newIP my $oldIP; # the hosts.ip_addr which Soma returns when # queried with $newIP my $oldMAC; # the hosts.mac which Soma returns when queried # with $newIP my @rollback; # Tracks failed rollbacks my $rows; # Number of inserts performed, should never # be anything but "1" my $sthiptomac; # sth for querying hosts.mac using hosts.ip_addr my $sthdnsname; # sth for querying hosts.dns_hostname my $sthtstamp; # sth for querying hosts.last_seen my $sthupdatednsname; # sth for updating hosts.dns_hostname my $tStamp; # ISO 8601 time stamp # Debug trace trace_location('begin') if $debug; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Grab time $tStamp = epoch_to_tstamp(time); # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthdnsname = $dbh->prepare('SELECT dns_hostname FROM hosts WHERE mac = ? '); $sthiptomac = $dbh->prepare('SELECT mac FROM hosts WHERE ip_addr = ? '); $sthtstamp = $dbh->prepare('SELECT last_seen FROM hosts WHERE mac = ? '); $sthupdatednsname = $dbh->prepare('UPDATE hosts SET dns_hostname = ? WHERE mac = ? '); # Walk %dnsname IP: for $newIP (keys %dnsname) { # Sanity check next IP unless defined $dnsname{$newIP}; # Populate $newHostName $newDnsName = $dnsname{$newIP}; # Debug info say "Begin processing $newIP / $newDnsName" if $debug > 2; # Find $oldMac eval { $oldMAC = find_fresh_mac($newIP, $sthiptomac, $sthtstamp) }; if ($@) { print_it("$@\n"); confess "find_fresh_mac failed for newIP $newIP"; } # If we didn't find $oldMAC, then skip this one next IP unless defined $oldMAC; # Create a transaction eval { # Is dns_hostname populated for this MAC? eval { $oldDnsName = find_one_scalar($oldMAC, $sthdnsname) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthdnsname failed for oldMAC $oldMAC"; } # Process dnsname if (not defined $oldDnsName or $oldDnsName ne $newDnsName) { say "Updating $newIP:$newDnsName for $oldMAC" if $debug == 7; $rows = $sthupdatednsname->execute($newDnsName, $oldMAC); if ($rows != 1) { confess "Updating $oldMAC resulted in multiple rows"; } } # Commit that puppy $dbh->commit(); }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $oldMAC:$newDnsName to HOSTS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $newIP; # Rollback eval { $dbh->rollback(); if ($@) { print_it("$@\n"); print_it("Unable to rollback $oldMAC:$newDnsName to HOSTS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $newIP; } }; # Finish rolling } # Finish whining and rolling # Debug info say "Done processing $newIP / $newDnsName" if $debug > 2; } # Finish walking %dnsname # Clean-up close_dbi_handles($dbh, $sthdnsname, $sthiptomac, $sthtstamp, $sthupdatednsname); # Debug trace trace_location('end') if $debug; return 1; } ######################################################################## # Insert and Update NetBIOS Name: walk %NetBiosName, inserting # the contents into Soma ######################################################################## sub iu_hosts_netbios { my $dbh; # DBI handle my @commit; # Tracks failed commits my $newIP; # IP address in %netBiosName my $newNetBiosMac; # the MAC address from %netBiosMAC my $newNetBiosName; # netbios name in %netBiosName my $newNetBiosUser; # the username in %netBiosUser my $newMAC; # the hosts.mac in Soma associated with $newIP my $oldMAC; # the hosts.mac which Soma returns when queried # by $newMAC (should be identical to $newMAC) my $oldIP; # the hosts.ip_addr which Soma returns when # queried with $newIP my $oldNetBiosName; # the hosts.os_hostname which Soma returns # when queried with $newMAC my $oldNetBiosUser; # the hosts.os_descr which Soma returns # when queried with $newMAC my @rollback; # Tracks failed rollbacks my $rows; # Number of inserts performed, should never # be anything but "1" my $sthinsert; # sth for inserting my $sthip; # sth for querying hosts.ip_addr using hosts.mac my $sthiptomac; # sth for querying hosts.mac using $newIP my $sthmac; # sth for querying hosts.mac using $oldMAC my $sthoshostname; # sth for querying hosts.os_hostname my $sthcurrentosuser; # sth for querying hosts.current_os_user my $sthtstamp; # sth for querying hosts.last_seen using hosts.mac my $sthupdate; # sth for updating several fields my $tStamp; # ISO 8601 time stamp # Debug trace trace_location('begin') if $debug; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Grab time $tStamp = epoch_to_tstamp(time); # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthcurrentosuser = $dbh->prepare('SELECT current_os_user FROM hosts WHERE mac = ? '); $sthinsert = $dbh->prepare('INSERT INTO hosts (mac, ip_addr, os_hostname, descr, first_seen, last_seen, last_updated) VALUES (?, ?, ?, ?, ?, ?, ?) '); $sthip = $dbh->prepare('SELECT ip_addr FROM hosts WHERE mac = ? '); $sthiptomac = $dbh->prepare('SELECT mac FROM hosts WHERE ip_addr = ? '); $sthmac = $dbh->prepare('SELECT mac FROM hosts WHERE mac = ? '); $sthoshostname = $dbh->prepare('SELECT os_hostname FROM hosts WHERE mac = ? '); $sthtstamp = $dbh->prepare('SELECT last_seen FROM hosts WHERE mac = ? '); $sthupdate = $dbh->prepare('UPDATE hosts SET ip_addr = ?, os_hostname = ?, current_os_user = ?, last_seen = ?, last_updated = ? WHERE mac = ? '); # Walk %netBiosName IP: for $newIP (keys %netBiosName) { # Debug info say "Begin processing $newIP / $netBiosName{$newIP}" if $debug > 2; # Clean up undef $newMAC; undef $oldMAC; undef $oldIP; undef $newNetBiosMac; undef $newNetBiosName; undef $newNetBiosUser; # Find $newMAC eval { $newMAC = find_fresh_mac($newIP, $sthiptomac, $sthtstamp) }; if ($@) { print_it("$@\n"); confess "find_fresh_mac failed for newIP $newIP"; } # If $newMAC isn't defined (i.e. if Soma hasn't seen $newIP), then # use %netBiosMac if (not defined $newMAC and defined $netBiosMac{$newIP}) { $newMAC = $netBiosMac{$newIP}; } # If $newMAC still isn't defined, bail unless (defined $newMAC) { log_it("Soma hasn't seen $newIP / $netBiosName{$newIP} yet, skipping"); next IP; } # Populate temporary variables $newNetBiosName = $netBiosName{$newIP} if defined $netBiosName{$newIP}; $newNetBiosName = $DASH unless defined $newNetBiosName; $newNetBiosUser = $netBiosUser{$newIP} if defined $netBiosUser{$newIP}; $newNetBiosUser = $DASH unless defined $newNetBiosUser; # Has Soma already seen $newMAC? eval { $oldMAC = find_one_scalar($newMAC, $sthmac) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthmac failed for newMac $newMAC"; } # If so, what other fields are already populated? if (defined $oldMAC) { # Check for total weirdness confess "Soma is broken: $oldMAC ne $newMAC" unless $oldMAC eq $newMAC; # Is hosts.ip_addr populated for this MAC? eval { $oldIP = find_one_scalar($newMAC, $sthip) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthip failed for newMAC $newMAC"; } # Is hosts.os_hostname populated for this MAC? eval { $oldNetBiosName = find_one_scalar($newMAC, $sthoshostname) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthoshostname failed for newMac $newMAC"; } # Is hosts.current_os_user populated for this MAC? eval { $oldNetBiosUser = find_one_scalar($newMAC, $sthcurrentosuser) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthdescr failed for newMac $newMAC"; } } # End 'if defined $oldMAC' # Create a transaction eval { # If Soma has seen this MAC, update if needed (notice that # $newMAC and $oldMAC are identical at this point) if (defined $oldMAC) { # New NetBIOS information if (not defined $oldIP or not defined $oldNetBiosName or not defined $oldNetBiosUser) { print_it("$newIP had incomplete NetBIOS info, updating to $newNetBiosName / $newNetBiosUser"); $rows = $sthupdate->execute($newIP, $newNetBiosName, $newNetBiosUser, $tStamp, $tStamp, $newMAC); if ($rows != 1) { confess "Updating $oldMAC resulted in multiple rows"; } } # New IP address and/or new NetBIOS information elsif ($oldIP ne $newIP or $oldNetBiosName ne $newNetBiosName or $oldNetBiosUser ne $newNetBiosUser) { print_it("$newMAC has changed from $oldIP to $newIP, $oldNetBiosName to $newNetBiosName, and $oldNetBiosUser to $newNetBiosUser"); $rows = $sthupdate->execute($newIP, $newNetBiosName, $newNetBiosUser, $tStamp, $tStamp, $newMAC); if ($rows != 1) { confess "Updating $oldMAC resulted in multiple rows"; } } } # If Soma hasn't seen this MAC, then insert elsif (not defined $oldMAC) { print_it("Soma hasn't seen $newIP, inserting $newMAC / $newNetBiosName / $newNetBiosUser"); $rows = $sthinsert->execute($newMAC, $newIP, $newNetBiosName, $newNetBiosUser, $tStamp, $tStamp, $tStamp); if ($rows != 1) { confess "Inserting newMAC $newMAC resulted in multiple rows"; } } # End 'if not defined $oldMAC' # Commit that puppy $dbh->commit(); }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $newIP to HOSTS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $newIP; # Rollback eval { $dbh->rollback(); if ($@) { print_it("$@\n"); print_it("Unable to rollback $newIP insert into HOSTS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $newIP; } }; # Finish rolling } # Finish whining and rolling # Debug info say "Done processing $newIP / $netBiosName{$newIP}" if $debug > 2; } # Finish walking %netBiosName # Clean-up close_dbi_handles($dbh, $sthcurrentosuser, $sthinsert, $sthip, $sthiptomac, $sthmac, $sthoshostname, $sthtstamp, $sthupdate); # Debug trace trace_location('end') if $debug; return 1; } ######################################################################## # Insert and Update Host OS guesses: given a reference to a hash of # strings ('os_name', 'version', 'revision', 'version_name') # keyed by IP address, insert/update 'osver' in 'hosts' ######################################################################## sub iu_hosts_osver { my $dbh; # DBI handle my @commit; # Tracks failed commits my $newVersionID; # os_versions.versionid my $oldMAC; # the hosts.mac which Soma returns when queried # with $newIP my $oldOsVers; # reference to an array of hosts.osver from # when queried by hosts.ip_addr my $oldOsVer; # Iterates across @$oldOsVers my $osVersions = shift; # Reference to hash of strings ('os_name', # 'version', 'revision', 'version_name') # keyed by IP address my @rollback; # Tracks failed rollbacks my $rows; # Number of inserts performed my $sthiptomac; # sth for querying hosts.mac using hosts.ip_addr my $sthosver; # sth for querying hosts.osver using hosts.ip_addr my $sthtstamp; # sth for querying hosts.last_seen my $sthupdateosver; # sth for updating hosts.osver my $sthversionid; # sth for querying os_versions.versionid my $tStamp; # ISO 8601 time stamp # Debug trace trace_location('begin') if $debug; # Sanity checking confess 'No parameters' unless defined $osVersions; confess "Wrong type for parameter" unless ref $osVersions eq 'HASH'; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Grab time $tStamp = epoch_to_tstamp(time); # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthiptomac = $dbh->prepare('SELECT mac FROM hosts WHERE ip_addr = ? '); $sthosver = $dbh->prepare('SELECT osver FROM hosts WHERE mac = ? '); $sthtstamp = $dbh->prepare('SELECT last_seen FROM hosts WHERE mac = ? '); $sthupdateosver = $dbh->prepare('UPDATE hosts SET osver = ?, last_updated = ? WHERE mac = ? '); $sthversionid = $dbh->prepare('SELECT versionid FROM os_versions WHERE version_name = ? '); # Walk hash IP: for my $newIP (keys %$osVersions) { my ($os_name, $version, $revision, $version_name) = split $COMMA , $osVersions->{$newIP}; # %osVersions outta be populated ... but just in case, check next IP unless defined $version_name; # Debug info say "Begin processing $newIP / $version_name" if $debug > 2; # Find $newVersionID eval { $newVersionID = find_one_scalar($version_name, $sthversionid) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthversionid failed for version_name $version_name"; } # If Soma does not contain this $version_name, bail ... this shouldn't # happen, because the data collecting script should have already # inserted the necessary entry in 'os_versions' ... but if somehow # that didn't happen, log the event and move on. unless (defined $newVersionID) { log_it("version_name $version_name not defined in Soma, skipping $newIP"); next IP; } # Find $oldMac eval { $oldMAC = find_fresh_mac($newIP, $sthiptomac, $sthtstamp) }; if ($@) { print_it("$@\n"); confess "find_fresh_mac failed for newIP $newIP"; } # If we didn't find $oldMAC, then skip this one next IP unless defined $oldMAC; # Create a transaction eval { # Find hosts.osver eval { $oldOsVer = find_one_scalar($oldMAC, $sthosver) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthosver failed for oldMAC $oldMAC"; } # If hosts.osver isn't defined or if hosts.osver # doesn't equal the new 'osver' we've found, update if (not defined $oldOsVer or $oldOsVer != $newVersionID) { $rows = $sthupdateosver-> execute($newVersionID, $tStamp, $oldMAC); if ($rows != 1) { confess "Updating $oldMAC resulted in multiple rows"; } } # Commit that puppy $dbh->commit(); }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit osver $newVersionID for $oldMAC to HOSTS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $newVersionID; # Rollback eval { $dbh->rollback(); if ($@) { print_it("$@\n"); print_it("Unable to rollback osver $newVersionID HOSTS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $newVersionID; } }; # Finish rolling } # Finish whining and rolling # Debug info say "Done processing $newIP / $version_name" if $debug > 2; } # Finish walking %$osVersions # Clean-up close_dbi_handles($dbh, $sthiptomac, $sthosver, $sthtstamp, $sthupdateosver, $sthversionid); # Debug trace trace_location('end') if $debug; } ######################################################################## # Insert and Update snmp_sys_descr and snmp_sys_objectid in 'hosts' ######################################################################## sub iu_hosts_snmp { my $dbh; # DBI handle my @commit; # Tracks failed commits my $newIP; # IP address from %nodename my $newSysDescr; # sysDescr from %sysDescr my $newSysObjectID; # sysObjectID from %sysObjectID my $oldIP; # the hosts.ip_addr which Soma returns when queried # for $newIP my $oldMAC; # the hosts.mac which Soma returns when queried # with $newIP my $oldSysDescr; # the hosts.snmp_sys_descr which Soma returns when # queried with $newIP my $oldSysObjectID; # the hosts.snmp_sys_objectid which Soma returns # when queried with $newIP my @rollback; # Tracks failed rollbacks my $rows; # Number of inserts performed, should never # be anything but "1" my $sthip; # sth for querying hosts.ip_addr my $sthiptomac; # sth for querying hosts.mac using hosts.ip_addr my $sthsysdescr; # sth for querying hosts.snmp_sys_descr my $sthsysobjectid; # sth for querying hosts.snmp_sys_objectid my $sthtstamp; # sth for querying hosts.last_seen using hosts.mac my $sthupdatesysdescr; # sth for updating hosts.snmp_sys_descr my $sthupdatesysobjectid; # sth for updating hosts.snmp_sys_objectid my $tStamp; # ISO 8601 time stamp # Debug trace trace_location('begin') if $debug; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Grab time $tStamp = epoch_to_tstamp(time); # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthip = $dbh->prepare('SELECT ip_addr FROM hosts WHERE mac = ? '); $sthiptomac = $dbh->prepare('SELECT mac FROM hosts WHERE ip_addr = ? '); $sthsysdescr = $dbh->prepare('SELECT snmp_sys_descr FROM hosts WHERE mac = ? '); $sthsysobjectid = $dbh->prepare('SELECT snmp_sys_objectid FROM hosts WHERE mac = ? '); $sthtstamp = $dbh->prepare('SELECT last_seen FROM hosts WHERE mac = ? '); $sthupdatesysdescr = $dbh->prepare('UPDATE hosts SET snmp_sys_descr = ?, last_seen = ?, last_updated = ? WHERE mac = ? '); $sthupdatesysobjectid = $dbh->prepare('UPDATE hosts SET snmp_sys_objectid = ?, last_seen = ?, last_updated = ? WHERE mac = ? '); # Walk %sysDescr IP: for $newIP (keys %sysDescr) { # Debug info say "Begin processing $newIP" if $debug > 2; # Find $oldMAC eval { $oldMAC = find_fresh_mac($newIP, $sthiptomac, $sthtstamp) }; if ($@) { print_it("$@\n"); confess "find_fresh_mac failed for newIP $newIP"; } # If we didn't find $oldMAC, then skip this one next IP unless defined $oldMAC; # Populate temporary variables $newSysDescr = $sysDescr{$newIP} if defined $sysDescr{$newIP}; $newSysObjectID = $sysObjectID{$newIP} if defined $sysObjectID{$newIP}; # Debug info say "Processing $newIP: $newSysObjectID" if $debug == 7; # Create a transaction eval { # Is sysdescr populated for this MAC? eval { $oldSysDescr = find_one_scalar($oldMAC, $sthsysdescr) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthsysdescr failed for oldMAC $oldMAC"; } # Is sysobjectid populated for this MAC? eval { $oldSysObjectID = find_one_scalar($oldMAC, $sthsysobjectid) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthsysobjectid failed for oldMAC $oldMAC"; } # Process sysdescr if (defined $newSysDescr) { if (not defined $oldSysDescr or $oldSysDescr ne $newSysDescr) { say "Updating sysDescr{$newIP} $newSysDescr for $oldMAC" if $debug == 7; $rows = $sthupdatesysdescr-> execute($newSysDescr, $tStamp, $tStamp, $oldMAC); if ($rows != 1) { confess "Updating $oldMAC resulted in multiple rows"; } } } # Process sysobjectid if (defined $newSysObjectID) { if (not defined $oldSysObjectID or $oldSysObjectID ne $newSysObjectID) { say "Updating sysObjectID{$newIP} $newSysObjectID for $oldMAC" if $debug == 7; $rows = $sthupdatesysobjectid-> execute($newSysObjectID, $tStamp, $tStamp, $oldMAC); if ($rows != 1) { confess "Updating $oldMAC resulted in multiple rows"; } } } # Commit that puppy $dbh->commit(); }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $newIP to HOSTS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $newIP; # Rollback eval { $dbh->rollback(); if ($@) { print_it("$@\n"); print_it("Unable to rollback $newIP insert into HOSTS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $newIP; } }; # Finish rolling } # Finish whining and rolling # Debug info say "Done processing $newIP" if $debug > 2; } # Finish walking %sysDescr # Clean-up close_dbi_handles($dbh, $sthip, $sthiptomac, $sthsysdescr, $sthsysobjectid, $sthtstamp, $sthupdatesysdescr, $sthupdatesysobjectid); # Debug trace trace_location('end') if $debug; return 1; } ######################################################################## # Insert and Update Host Vulnerabilites: given an IP address and a # reference to its associated Net::Nessus::ScanLite object, insert and # update into the host_vulnerabilities table ######################################################################## sub iu_host_vulnerabilities { my $addr = shift; # IP address associated with $nessus my $dbh; # DBI handle my @commit; # Tracks failed commits my $descr; # Description of hole from Nessus my $corrected_time; # hosts_vulnerabilities.corrected_time my $discovered_time; # hosts_vulnerabilities.discovered_time my $hostID; # hosts.hostid my $last_checked_time; # hosts_vulnerabilities.last_checked_time my $nessus = shift; # ref to Net::Nessus::ScanLite object my $oldMAC; # the hosts.mac which Soma returns when queried # with $newIP my @rollback; # Tracks failed rollbacks my $rows; # Number of inserts performed, should never # be anything but "1" my $severity; # Severity rating of hole from Nessus my $sthhost; # sth for querying hosts.hostid my $sthiptomac; # sth for querying hosts.mac using 'ip_addr' my $sthinserthostvuln; # sth for inserting a vuln in host_vulnerabilities my $sthvulntstamps; # sth for querying discovered_time, corrected_time, # and last_checked_time in host_vulnerabilities my $sthtstamp; # sth for querying hosts.last_seen my $sthupdatelast; # sth for updating # host_vulnerabilities.last_checked_time my $sthvulnid; # sth for querying vulnerabilities.vulnid my $tStamp; # ISO 8601 time stamp # Debug trace trace_location('begin') if $debug; # Sanity checking confess 'Not enough parameters' unless (defined $addr and defined $nessus); confess "$addr is not an IP address" unless validaddr($addr); # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Grab time $tStamp = epoch_to_tstamp(time); # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthhost = $dbh->prepare('SELECT hostid FROM hosts WHERE mac = ? '); $sthinserthostvuln = $dbh->prepare('INSERT into host_vulnerabilities (host, vuln, discovered_time, last_checked_time) VALUES (?, ?, ?, ?) '); $sthiptomac = $dbh->prepare('SELECT mac FROM hosts WHERE ip_addr = ? '); $sthvulntstamps = $dbh->prepare('SELECT discovered_time, corrected_time, last_checked_time FROM host_vulnerabilities WHERE host = ? AND vuln = ? '); $sthtstamp = $dbh->prepare('SELECT last_seen FROM hosts WHERE mac = ? '); $sthupdatelast = $dbh->prepare('UPDATE host_vulnerabilities SET last_checked_time = ?, corrected_time = ? WHERE host = ? AND vuln = ? '); $sthvulnid = $dbh->prepare('SELECT vulnid FROM vulnerabilities WHERE vuln_name = ? '); # Find $oldMac eval { $oldMAC = find_fresh_mac($addr, $sthiptomac, $sthtstamp) }; if ($@) { print_it("$@\n"); confess "find_fresh_mac failed for addr $addr"; } # Keep going only if we found $oldMAC unless (defined $oldMAC) { log_it("$addr not in 'hosts', skipping iu_host_vulnerabilities"); goto FINISH; } # Define $hostid eval { $hostID = find_one_scalar($oldMAC, $sthhost) }; if ($@) { print_it("$@\n"); cluck "find_one_scalar sthhost failed for $addr"; } unless (defined $hostID) { log_it("hostid for $addr not defined, something is broken"); goto FINISH; } # Walk through $nessus HOLE: for my $hole ($nessus->hole_list) { my ($vulnID, $vulnName); # Define $vulnName $vulnName = $hole->ScanID; next HOLE unless defined $vulnName; # Define $vulnID eval { $vulnID = find_one_scalar($vulnName, $sthvulnid) }; if ($@) { print_it("$@\n"); cluck "find_one_scalar sthvuln failed for $vulnName"; } unless (defined $vulnID) { log_it("vulnName $vulnName not defined in 'vulnerabilities', at $addr"); next HOLE; } # Create a transaction eval { # Retrieve time stamps for this host:vuln combination eval { ($discovered_time, $corrected_time, $last_checked_time) = find_host_vuln($hostID, $vulnID, $sthvulntstamps) }; if ($@) { print_it("$@\n"); cluck "find_host_vuln sthvulntstamps failed for $hostID:$vulnID"; next HOLE; } # If corrected_time is defined, log a worried message if (defined $corrected_time) { if (defined $nodename{$addr}) { log_it("For $nodename{$addr}, Soma says that $vulnName was corrected on $corrected_time ... but now Nessus sees this hole open again"); } else { log_it("For $addr, Soma says that $vulnName was corrected on $corrected_time ... but now Nessus sees this hole open again"); } } # If we've seen it before, update last_checked_time and wipe # corrected_time if (defined $discovered_time) { $rows = $sthupdatelast->execute($tStamp, undef, $hostID, $vulnID); if ($rows != 1) { confess "Updating $hostID:$vulnID resulted in multiple rows"; } } # If we haven't seen it, insert it if (! defined $discovered_time) { $rows = $sthinserthostvuln->execute($hostID, $vulnID, $tStamp, $tStamp); if ($rows != 1) { confess "Uniqueness constraint on 'host' and/or 'vuln' for $vulnName violated"; } } # Commit that puppy $dbh->commit(); }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $hostID:$vulnID to HOST_VULNERABILITIES\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $vulnName; # Rollback eval { $dbh->rollback(); if ($@) { print_it("$@\n"); print_it("Unable to rollback $hostID:$vulnID insert into HOST_VULNERABILITIES\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $vulnName; } }; # Finish rolling } # Finish whining and rolling } # Finish walking $nessus FINISH: # This is where the various 'goto' functions above land # Clean-up close_dbi_handles($dbh, $sthhost, $sthiptomac, $sthinserthostvuln, $sthtstamp, $sthvulntstamps, $sthupdatelast, $sthvulnid); # Debug trace trace_location('end') if $debug; return 1; } ######################################################################## # Insert and Update Operating Systems: given a reference to a hash of # 'os_name:org_name' keyed by something (likely IP addresses), # insert/update these os_names into 'operating_systems' ######################################################################## sub iu_operating_systems { my $dbh; # DBI handle my @commit; # Tracks failed commits my $oldOrgID; # organizations.orgid my $oldOSID; # operating_systems.osid my $newOS; # newly gathered operating system name my $newOrgName; # Iterates through $vendors my $oses = shift; # Reference to hash of "os_name:org_name" # keyed by something (likely IP addresses) my @rollback; # Tracks failed rollbacks my $rows; # Number of inserts performed my $sthorgid; # sth for querying organizations.org_name my $sthosname; # sth for querying operating_systems.os_name my $sthinsertos; # sth for inserting operating_systems.os_name # Debug trace trace_location('begin') if $debug; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthinsertos = $dbh->prepare('INSERT INTO operating_systems (vendor, os_name) VALUES (?, ?) '); $sthosname = $dbh->prepare('SELECT os_name FROM operating_systems WHERE os_name = ? '); $sthorgid = $dbh->prepare('SELECT orgid FROM organizations WHERE org_name = ? '); # Walk the hash OS: for my $key (keys %$oses) { my ($newOS, $newOrgName); ($newOrgName, $newOS) = split $COLON, $oses->{$key}; # Does Soma contain this vendor? eval { $oldOrgID = find_one_scalar($newOrgName, $sthorgid) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthorgid failed for newOrgName $newOrgName"; } # If Soma does not contain this vendor, then try adding it and # querying again if (not defined $oldOrgID) { my %temp = ( $newOrgName => '1' ); iu_organizations(\%temp); # OK, does Soma contain this vendor now? eval { $oldOrgID = find_one_scalar($newOrgName, $sthorgid) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthorgid failed for newOrgName $newOrgName"; } } # If Soma still does not contain this vendor, then skip to # the next OS next OS unless defined $oldOrgID; # OK, so at this point, Soma contains this vendor. Does Soma # contain this operating system? eval { $oldOSID = find_one_scalar($newOS, $sthosname) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthos failed for newOS $newOS"; } # If Soma already contains this operating system, skip to the next one next OS if defined $oldOSID; # Create a transaction eval { # OK, so at this point, Soma does not contain this operating # system: insert it $rows = $sthinsertos->execute($oldOrgID, $newOS); if ($rows != 1) { confess "Updating $newOS resulted in multiple rows"; } # Commit that puppy $dbh->commit(); }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $newOS to OPERATING_SYSTEMS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $newOS; # Rollback eval { $dbh->rollback(); if ($@) { print_it("$@\n"); print_it("Unable to rollback $newOS insert into OPERATING_SYSTEMS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $newOS; } }; # Finish rolling } # Finish whining and rolling } # Finish walking $orgs # Clean-up close_dbi_handles($dbh, $sthinsertos, $sthorgid, $sthosname); # Debug trace trace_location('end') if $debug; } ######################################################################## # Insert and Update Organizations: given a reference to a hash of # organization names keyed by something, insert/update these # organizations into 'organizations' ######################################################################## sub iu_organizations { my $dbh; # DBI handle my @commit; # Tracks failed commits my %inverted; # inverted version of %$orgs my $oldOrg; # 'org_name' from Soma my $orgs = shift; # Reference to hash containing organization # names keyed by something my @rollback; # Tracks failed rollbacks my $rows; # Number of inserts performed my $sthorg; # sth for querying organizations.org_name my $sthinsertorg; # sth for inserting organizations.org_name # Debug trace trace_location('begin') if $debug; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthinsertorg = $dbh->prepare('INSERT INTO organizations (org_name) VALUES (?) '); $sthorg = $dbh->prepare('SELECT org_name FROM organizations WHERE org_name = ? '); # Invert the hash: the hash we receive contains organizations # keyed by something ... likely IP addresses. I don't care about # the keys; I only care about the values, i.e. the organization # names ... and this hash may well contain non-unique values. # No sense in adding a given organization multiple times ... so # reverse the hash, throw away all but one instance of each # organization, and continue %inverted = reverse %$orgs; # Walk the inverted result for my $newOrg (keys %inverted) { # Create a transaction eval { # Does Soma contain this organization? eval { $oldOrg = find_one_scalar($newOrg, $sthorg) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthorg failed for org $newOrg"; } # If it does, then we're done. However, if it doesn't, then # insert it if (not defined $oldOrg) { $rows = $sthinsertorg->execute($newOrg); if ($rows != 1) { confess "Updating $newOrg resulted in multiple rows"; } } # End 'if ! defined $oldOrg' # Commit that puppy $dbh->commit(); }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $newOrg to ORGANIZATIONS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $newOrg; # Rollback eval { $dbh->rollback(); if ($@) { print_it("$@\n"); print_it("Unable to rollback $newOrg insert into ORGANIZATIONS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $newOrg; } }; # Finish rolling } # Finish whining and rolling } # Finish walking $orgs # Clean-up close_dbi_handles($dbh, $sthinsertorg, $sthorg); # Debug trace trace_location('end') if $debug; } #######################################################################o # Insert and Update switch_ports.status: walk %ifAdminStatus ######################################################################## sub iu_if_admin_status { my $slot; # Blade extracted from %ifAdminStatus my $dbh; # DBI handle my @commit; # Tracks failed commits my $port; # Port extracted from %ifAdminStatus my $portID; # switch_port.portid of current ifAdminStatus entry my @rollback; # Tracks failed rollbacks my $rows; # Number of rows returned by UPDATE; should # always be equal to "1" my $sthupdatestatus; # sth for updating switch_ports.status my $sthport; # sth for querying switch_port.portid my $sthswitch; # sth for querying switches.switchid my $switchID; # switches.switchid of current $esx # Debug trace trace_location('begin') if $debug; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthupdatestatus = $dbh->prepare('UPDATE switch_ports SET status = ? WHERE portid = ? '); $sthport = $dbh->prepare('SELECT portid FROM switch_ports WHERE switch = ? AND slot = ? AND port = ? '); $sthswitch = $dbh->prepare('SELECT switchid FROM switches WHERE switch_name = ? '); # Walk %ifAdminStatus for my $addr (keys %ifAdminStatus) { my $esx = $esx{$addr}; # Debug info say "Processing $esx ..." if $debug > 2; # Walk through ifName/ifAdminStatus data structure IFNAME: for my $ifName (keys %{$ifAdminStatus{$addr}}) { # Skip if the ifName contains letters, likely me1, sc0, sl0, or a vlan next IFNAME if $ifName =~ /[a-zA-Z]/; # Find slot and port my ($slot, $port) = split $SLASH, $ifName; # Find ifAdminStatus my $ifAdminStatus = $ifAdminStatus{$addr}->{$ifName}; # Debug info say "slot $slot, port $port is $ifAdminStatus" if $debug == 4; # Create a transaction eval { # Find switches.switchID eval { $switchID = find_one_scalar($esx, $sthswitch) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthswitch failed for $esx"; } # Find switch_ports.portid eval { $portID = find_portid($switchID, $slot, $port, $sthport) }; if ($@) { print_it("$@\n"); confess "find_portid failed for $switchID:$slot/$port"; } # Update status $rows = $sthupdatestatus->execute($ifAdminStatus, $portID); if ($rows != 1) { confess "Updating portID $portID on $esx with sthupdatestatus resulted in multiple rows"; } # Commit that puppy $dbh->commit(); }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $esx:$slot/$port to 'switch_ports'\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $esx; # Rollback eval { $dbh->rollback(); if ($@) { print_it("Unable to rollback $esx:$slot/$port insert into 'switch_ports'\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $esx; } }; } # Finish whining and rolling } # Finish walking ifName/ifAdminStatus data structure } # Finish walking %ifAdminStatus # Clean-up close_dbi_handles($dbh, $sthupdatestatus, $sthport, $sthswitch); # Debug trace trace_location('end') if $debug; return 1; } ######################################################################## # Insert and Update OS Versions: given a reference to a hash of # strings ('os_name', 'version', 'revision', 'version_name') # keyed by IP address, insert/update these fields into 'os_versions' ######################################################################## sub iu_os_versions { my $dbh; # DBI handle my @commit; # Tracks failed commits my $newOsID; # operating_systems.osid my $newVersionID; # os_versions.versionid my $osVersions = shift; # Reference to hash of strings ('os_name', # 'version', 'revision', 'version_name') # keyed by IP address my @rollback; # Tracks failed rollbacks my $rows; # Number of inserts performed my $sthosid; # sth for querying operating_systems.osid my $sthversionid; # sth for querying os_versions.versionid my $sthinsertver; # sth for inserting fields into 'os_versions' my $tStamp; # ISO 8601 time stamp # Debug trace trace_location('begin') if $debug; # Sanity checking confess 'No parameters' unless defined $osVersions; confess 'Parameter must be hash ref' unless ref $osVersions eq 'HASH'; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Grab time $tStamp = epoch_to_tstamp(time); # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthinsertver = $dbh->prepare('INSERT INTO os_versions (os, version, revision, version_name) VALUES (?, ?, ?, ?) '); $sthversionid = $dbh->prepare('SELECT versionid FROM os_versions WHERE version_name = ? '); $sthosid = $dbh->prepare('SELECT osid FROM operating_systems WHERE os_name = ? '); # Walk hash IP: for my $newIP (keys %$osVersions) { my ($os_name, $version, $revision, $version_name) = split $COMMA , $osVersions->{$newIP}; # Create a transaction eval { # Does Soma contain this 'os_name'? eval { $newOsID = find_one_scalar($os_name, $sthosid) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthosid failed for os_name $os_name"; } # If Soma does not contain this $os_name, bail ... this shouldn't # happen, because the data collecting script should have already # inserted the necessary entries in 'organizations' and in # 'operating_systems' ... but if somehow that didn't happen, # log the event and move on. log_it("os_name $os_name not defined in Soma, skipping \"$version_name\" for $newIP") unless defined $newOsID; next IP unless defined $newOsID; # Does Soma contain this os_versions.version_name? eval { $newVersionID = find_one_scalar($version_name, $sthversionid) }; if ($@) { confess "find_one_scalar sthversionid failed for version_name $version_name"; } # If Soma does not contain this 'version_name', insert it if (not defined $newVersionID) { $rows = $sthinsertver-> execute($newOsID, $version, $revision, $version_name); if ($rows != 1) { confess "Inserting osid $newOsID resulted in multiple rows"; } } # Commit that puppy $dbh->commit(); }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $version_name to OS_VERSIONS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $version_name; # Rollback eval { $dbh->rollback(); if ($@) { print_it("$@\n"); print_it("Unable to rollback $version_name insert into OS_VERSIONS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $version_name; } }; # Finish rolling } # Finish whining and rolling } # Finish walking %$osVersions # Clean-up close_dbi_handles($dbh, $sthinsertver, $sthversionid, $sthosid); # Debug trace trace_location('end') if $debug; } #######################################################################o # Insert and Update Ports: walk %esx, inserting new ports into Soma ######################################################################## sub iu_ports { my $slot; # Blade extracted from %cam my $dbh; # DBI handle my @commit; # Tracks failed commits my $port; # Port extracted from %cam my $portID; # switch_port.portid of current CAM entry my $ref_ports; # Data structure containing vlans, slots, ports my @rollback; # Tracks failed rollbacks my $rows; # Number of rows returned by UPDATE; should # always be equal to "1" my $sthinsertport; # sth for inserting switch_ports.port my $sthport; # sth for querying switch_port.portid my $sthswitch; # sth for querying switches.switch_name my $switchID; # switches.switchid of esx containing current port # Debug trace trace_location('begin') if $debug; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthinsertport = $dbh->prepare('INSERT INTO switch_ports (switch, slot, port) VALUES (?, ?, ?) '); $sthswitch = $dbh->prepare('SELECT switchid FROM switches WHERE switch_name = ? '); $sthport = $dbh->prepare('SELECT portid FROM switch_ports WHERE switch = ? AND slot = ? AND port = ? '); # Walk %esx for my $addr (keys %esx) { my $esx = $esx{$addr}; # Debug info say "Processing $esx" if $debug == 7; # Acquire slot/port data structure $ref_ports = $ports{$addr}; # Walk through $ref_ports for my $entry (@$ref_ports) { my ($slot, $port) = split $SLASH, $entry; # Debug info say " Slot $slot, port $port" if $debug == 7; # Create a transaction eval { # Find switches.switchID eval { $switchID = find_one_scalar($esx, $sthswitch) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthswitch failed for $esx"; } # Find switch_ports.portid eval { $portID = find_portid($switchID, $slot, $port, $sthport) }; if ($@) { print_it("$@\n"); confess "find_portid failed for $switchID:$slot/$port"; } # Insert new port unless (defined $portID) { say " Inserting $switchID, $slot, $port" if $debug == 7; $rows = $sthinsertport->execute($switchID, $slot, $port); if ($rows != 1) { confess "Uniqueness constraint on 'switch_ports' for portid $portID violated"; } # Commit that puppy $dbh->commit(); } }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $esx:$slot/$port to 'switch_ports'\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $esx; # Rollback eval { $dbh->rollback(); if ($@) { print_it("Unable to rollback $esx:$slot/$port insert into 'switch_ports'\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $esx; } }; } # Finish whining and rolling } # Finish walking $ref_ports } # Finish walking %esx # Clean-up close_dbi_handles($dbh, $sthinsertport, $sthport, $sthswitch); # Debug trace trace_location('end') if $debug; return 1; } ######################################################################## # Insert and Update Switch: walk %esx, inserting new switches into Soma ######################################################################## sub iu_switches { my $dbh; # DBI handle my @commit; # Tracks failed commits my @rollback; # Tracks failed rollbacks my $rows; # Number of inserts performed, should never # be anything but "1" my $sthinsertswitch; # sth for inserting a switch into 'switches' my $sthswitch; # sth for querying switches.switch_name my $switchID; # switches.switchid returned from Soma # Debug trace trace_location('begin') if $debug; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthinsertswitch = $dbh->prepare('INSERT into switches (switch_name) VALUES (?) '); $sthswitch = $dbh->prepare('SELECT switchid FROM switches WHERE switch_name = ? '); # Walk through %esx for my $esx (values %esx) { # Create a transaction eval { # Find switchid eval { $switchID = find_one_scalar($esx, $sthswitch) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthswitch failed for $nodename{$esx}"; } # Unless it exists already, insert unless (defined $switchID) { $rows = $sthinsertswitch->execute($esx); if ($rows != 1) { confess "Uniqueness constraint on 'switches' for $esx violated"; } # Commit that puppy $dbh->commit(); } }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $esx to SWITCHES\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $esx; # Rollback eval { $dbh->rollback(); if ($@) { print_it("Unable to rollback $esx insert into SWITCHES\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $esx; } }; } } # Clean-up close_dbi_handles($dbh, $sthinsertswitch, $sthswitch); # Debug trace trace_location('end') if $debug; return 1; } ######################################################################## # Insert and Update Vulnerabilites: given a reference to a # Net::Nessus::ScanLite object, insert and updat into the # vulnerabilities table ######################################################################## sub iu_vulnerabilities { my $bid; # List of BID vulnerability IDs my $dbh; # DBI handle my @commit; # Tracks failed commits my $cve; # List of CVE, BID, etc. vulnerability IDs my $descr; # Description of hole from Nessus my $moreInfoRef; # List of CVE names from Nessus my $nessus = shift; # ref to Net::Nessus::ScanLite object my $other; # List of other vulnerability IDs my @rollback; # Tracks failed rollbacks my $rows; # Number of inserts performed, should never # be anything but "1" my $severity; # Severity rating of hole from Nessus my $sthinsertvuln; # sth for inserting a vuln into vulnerabilities my $sthvuln; # sth for querying vulnerabilities.vuln_name my $tStamp; # ISO 8601 time stamp my $vulnID; # vulnerabilities.vulnid returned from Soma my $vulnName; # vulnerabilities.vuln_name returned from Soma # Debug trace trace_location('begin') if $debug; # Sanity checking confess 'No parameters' unless defined $nessus; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Grab time $tStamp = epoch_to_tstamp(time); # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthinsertvuln = $dbh->prepare('INSERT into vulnerabilities (vuln_name, descr, severity, more_info_ref, discovered_time) VALUES (?, ?, ?, ?, ?) '); $sthvuln = $dbh->prepare('SELECT vuln_name FROM vulnerabilities WHERE vuln_name = ? '); # Walk through $nessus HOLE: for my $hole ($nessus->hole_list) { # Define vuln_name $vulnName = $hole->ScanID; next unless defined $vulnName; # Define descr $descr = $hole->Description; $descr =~ s/^Risk factor : .*//ms; next HOLE unless defined $descr; # Define severity ($severity) = ($hole->Description) =~ /Risk factor : (\w+)/i; switch($severity) { case 'Critical' { $severity = 5 } case 'High' { $severity = 4 } case 'Medium' { $severity = 3 } case 'Low' { $severity = 2 } case 'None' { $severity = 1 } else { $severity = 0 } } log_it("Something is wrong with severity $severity for vulnName $vulnName") unless ($severity >= 0 and $severity <= 5); # Define more_info_ref ($cve) = ($hole->Description) =~ /(CVE : .*)/; ($bid) = ($hole->Description) =~ /(BID : .*)/; ($other) = ($hole->Description) =~ /(Other references : .*)/; $moreInfoRef = ''; $moreInfoRef .= $cve . $CR if defined $cve; $moreInfoRef .= $bid . $CR if defined $bid; $moreInfoRef .= $other . $CR if defined $other; # Create a transaction eval { # Find vulnid eval { $vulnID = find_one_scalar($vulnName, $sthvuln) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthvulnname failed for $vulnName"; } # If it exists, do nothing if (defined $vulnID) { # Do nothing } # Otherwise insert it elsif (not defined $vulnID) { $rows = $sthinsertvuln->execute($vulnName, $descr, $severity, $moreInfoRef, $tStamp); if ($rows != 1) { confess "Uniqueness constraint on 'vulnid' for $vulnName violated"; } } # Commit that puppy $dbh->commit(); }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $vulnName to VULNERABILITIES\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $vulnName; # Rollback eval { $dbh->rollback(); if ($@) { print_it("Unable to rollback $vulnName insert into VULNERABILITIES\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $vulnName; } }; # Finish rolling } # Finish whining and rolling } # Finish walking $nessus # Clean-up close_dbi_handles($dbh, $sthinsertvuln, $sthvuln); # Debug trace trace_location('end') if $debug; return 1; } ####################################################################### # Given a reference to a hash of wall jacks keyed by the string # "Switch~Slot~Port", Insert and Update switch_ports.wall_jack ######################################################################## sub iu_wall_jack { my $slot; # Blade extracted from %jack my $dbh; # DBI handle my @commit; # Tracks failed commits my $port; # Port extracted from %jack my $portid; # switch_port.portid of current %jack entry my @rollback; # Tracks failed rollbacks my $rows; # Number of rows returned by UPDATE; should # always be equal to "1" my $sthupdatejack; # sth for updating switch_ports.wall_jack my $sthport; # sth for querying switch_port.portid my $sthswitch; # sth for querying switches.switchid my $switchid; # switches.switchid of esx containing current port my $wallJacks = shift; # Hashref of walljack/switch-slot-ports # Debug trace trace_location('begin') if $debug; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Sanity check confess 'No parameters' unless defined $wallJacks; confess 'Parameter must be hash ref' unless ref $wallJacks eq 'HASH'; # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthport = $dbh->prepare('SELECT portid FROM switch_ports WHERE switch = ? AND slot = ? AND port = ? '); $sthswitch = $dbh->prepare('SELECT switchid FROM switches WHERE switch_name = ? '); $sthupdatejack = $dbh->prepare('UPDATE switch_ports SET wall_jack = ? WHERE portid = ? '); # Walk through %wallJack my $n; for my $entry (keys %$wallJacks) { my ($esx, $jack, $port, $slot); # Debug info $n++; say "Processed $n jacks" if ($debug and $n/100 == int($n/100)); # Pull apart data structure ($esx, $slot, $port) = split $TILDE, $entry; $jack = $wallJacks->{$entry}; my $len = length($jack); # Create a transaction eval { # Find switches.switchID eval { $switchid = find_one_scalar($esx, $sthswitch) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthswitch failed for $esx"; } # Find switch_ports.portid if (defined $switchid) { eval { $portid = find_portid($switchid, $slot, $port, $sthport) }; if ($@) { print_it("$@\n"); confess "find_portid failed for $esx --> $switchid:$slot/$port"; } # Update jack if (defined $portid) { $rows = $sthupdatejack->execute($jack, $portid); if ($rows != 1) { confess "Uniqueness constraint on 'switch_ports' for portid $portid violated"; } } } # Commit that puppy $dbh->commit(); }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $esx:$slot/$port to 'switch_ports'\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $esx; # Rollback eval { $dbh->rollback(); if ($@) { print_it("Unable to rollback $esx:$slot/$port insert into 'switch_ports'\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $esx; } }; } # Finish whining and rolling } # Finish walking %jack # Clean-up close_dbi_handles($dbh, $sthport, $sthswitch, $sthupdatejack); # Debug trace trace_location('end') if $debug; return 1; } ######################################################################## # Insert and Update WAP client associations: walk %wapClients, # inserting/updating MAC address <-> WAP associations ######################################################################## sub iu_wap_clients { my $dbh; # DBI handle my @commit; # Tracks failed commits my @newMAC; # Array of MACs from %wapClients my $newMAC; # MAC address in @newMAC my $oldMAC; # MAC address in Soma my $newWAP; # WAP name in %wapClients my $oldWAP; # WAP name in Soma my @rollback; # Tracks failed rollbacks my $rows; # Number of inserts performed, should never # be anything but "1" my $sthinsert; # sth for inserting a new record my $sthmac; # sth for querying hosts.mac my $sthupdatetime; # sth for updating hosts.last_seen my $sthupdatewap; # sth for updating hosts.wireless_ap my $sthwap; # sth for querying hosts.wireless_ap my $tStamp; # ISO 8601 time stamp # Debug trace trace_location('begin') if $debug; # Debug info dbi_trace($dbiDebug) if $dbiDebug > 0; # Grab time $tStamp = epoch_to_tstamp(time); # Connect to the database $dbh = DBI->connect("$dbDriver:dbname=$dbName;host=$dbHost;port=$dbPort;", "$dbRwUser", "$dbRwPass", { AutoCommit => 0, PrintError => 1, RaiseError => 1 } ); # Prepare $sthinsert = $dbh->prepare('INSERT INTO hosts (mac, wireless_ap, first_seen, last_seen, last_updated) VALUES (?, ?, ?, ?, ?) '); $sthmac = $dbh->prepare('SELECT mac FROM hosts WHERE mac = ? '); $sthupdatetime = $dbh->prepare('UPDATE hosts SET last_seen = ? WHERE mac = ? '); $sthupdatewap = $dbh->prepare('UPDATE hosts SET wireless_ap = ?, last_seen = ?, last_updated = ? WHERE mac = ? '); $sthwap = $dbh->prepare('SELECT wireless_ap FROM hosts WHERE mac = ? '); # Walk %wapClients for my $addr (keys %wapClients) { $newWAP = $wap{$addr}; # Debug info say "Processing $newWAP" if $debug > 3; # Grab MACs @newMAC = @{$wapClients{$addr}}; # Walk @newMAC for $newMAC (@newMAC) { # Debug info say "Processing $newMAC" if $debug == 7; # Create a transaction eval { # Does Soma contain this MAC address? eval { $oldMAC = find_one_scalar($newMAC, $sthmac) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthmac failed for newMAC $newMAC"; } # If it does, then ask if Soma contains an associated WAP if (defined $oldMAC) { eval { $oldWAP = find_one_scalar($newMAC, $sthwap) }; if ($@) { print_it("$@\n"); confess "find_one_scalar sthwap failed for newMAC $newMAC"; } # If WAP is defined if (defined $oldWAP) { # If oldWAP and newWAP are equal, update time stamps if ($oldWAP eq $newWAP) { say "Update time stamps for $oldMAC, $oldWAP" if $debug == 7; $rows = $sthupdatetime->execute($tStamp, $oldMAC); if ($rows != 1) { confess "Updating $oldMAC resulted in multiple rows"; } } # End 'if oldWAP equals newWAP' # Otherwise, oldWAP does not equal newWAP: change the value # for hosts.wireless_ap and log the event elsif ($oldWAP ne $newWAP) { log_it("$newMAC has moved from $oldWAP to $newWAP\n"); print_it("Updating $newMAC to newWAP $newWAP\n") if $debug == 7; $rows = $sthupdatewap->execute($newWAP, $tStamp, $tStamp, $newMAC); if ($rows != 1) { confess "Updating $newMAC resulted in multiple rows"; } } # End 'if oldWAP does not equal newWAP' } # End 'if defined oldWAP ' # Otherwise, Soma has seen this MAC address but hasn't seen # it associated with a WAP: update this record to include newWAP # hosts.wireless_ap elsif (not defined $oldWAP) { say "Inserting new WAP $oldMAC, $newWAP" if $debug == 7; $rows = $sthupdatewap->execute($newWAP, $tStamp, $tStamp, $newMAC); if ($rows != 1) { confess "Updating $newMAC resulted in multiple rows"; } } # End 'if not defined oldWAP' } # End 'if defined oldMAC' # Otherwise, if Soma hasn't seen this MAC address yet, insert it, # along with the WAP information elsif (not defined $oldMAC) { say "Inserting new MAC $newMAC, $newWAP" if $debug == 7; $rows = $sthinsert->execute($newMAC, $newWAP, $tStamp, $tStamp, $tStamp); if ($rows != 1) { confess "Updating $newMAC resulted in multiple rows"; } } # End 'if not defined oldMAC' # Commit that puppy $dbh->commit(); }; # Complete transaction # If the transaction failed, whine and rollback if ($@) { print_it("$@\n"); print_it("Unable to commit $newMAC to HOSTS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @commit, $newMAC; # Rollback eval { $dbh->rollback(); if ($@) { print_it("$@\n"); print_it("Unable to rollback $newMAC insert into HOSTS\n"); print_it("$DBI::errstr\n") if defined $DBI::errstr; push @rollback, $newMAC; } }; # Finish rolling } # Finish whining and rolling } # Finish walking @newMAC } # Finish walk %wapClients # Clean-up close_dbi_handles($dbh, $sthinsert, $sthmac, $sthupdatetime, $sthupdatewap, $sthwap); # Debug trace trace_location('end') if $debug; return 1; } 1;