#!/usr/bin/perl -wall
#use strict;
#######################################################################
# Open up DBI and the syssumm database on localhost
#######################################################################
use DBI;

$DBUserName = "";                       # Must be a valid MySQL user with the right perms
$DBUserPwd = "";                 # Must coincide with user - none is possible

my $dbh = DBI->connect('DBI:mysql:syssumm', "$DBUserName", "$DBUserPwd");

# slurp in the globbed file list
while(<>) {

###################################################################################
# Open the individual node's file.  Use the filename to determine the host name.
# Check and see if it's in the database.  If not add it. If so capture the unique node_id
# *** IF the host exists DELETE the current installed software and disks listing *** since
# it's faster and cleaner just to rebuild it rather than test/update/delete in a loop
##################################################################################
open(SYSSUMM_HOST, $_);

    $apparent_hostname = $_;
    $apparent_hostname =~ s/\/home\/httpd\/html\/syssumm\///g;
    $apparent_hostname =~ s/\/syssumm\.info//g;
    chomp ($apparent_hostname);
    $fqdn_name = $apparent_hostname;
    my $fqdn_name_quoted = $dbh->quote($fqdn_name);
    my $nodeid_number = 0;
    ($nodeid_number) = $dbh->selectrow_array("select node_id from nodes where fqdn = $fqdn_name_quoted");
       if ($nodeid_number < 1) {
           print "Adding $fqdn_name";
           my $q1 = "INSERT INTO nodes (node_id,fqdn) values (NULL,$fqdn_name_quoted)";
           my $in = $dbh->prepare($q1);
           $in->execute;
           $nodeid_number = $in->{'mysql_insertid'};
           $nodeid_number_quoted = $dbh->quote($nodeid_number);
           print "$nodeid_number_quoted";
       }  else {
           $nodeid_number_quoted = $dbh->quote($nodeid_number);
           print "Refreshing software, services and disks list for $fqdn_name";
           my $q3 = "DELETE FROM software_installed WHERE node_id = $nodeid_number_quoted";
           my $in3 = $dbh->prepare($q3);
           $in3->execute;
           my $q7 = "DELETE FROM disks WHERE node_id = $nodeid_number_quoted";
           my $in7 = $dbh->prepare($q7);
           $in7->execute;
           my $q8 = "DELETE FROM services_used WHERE node_id = $nodeid_number_quoted";
           my $in8 = $dbh->prepare($q8);
           $in8->execute;
       }
    my $number_of_cpus = 0;


   while(<SYSSUMM_HOST>) {
     ($category,$sub_category,$therest) = split(/:/, $_, 3);
     if ($category eq "Network") {
        if ($sub_category eq "DomainName") {
           chomp ($therest);
           $fqdn_name = $therest;
           if ($fqdn_name ne $apparent_hostname) {
              print "$nodeid_number $fqdn_name Houston we have a problem $apparent_hostname";
           }
           next;
        } elsif ($sub_category =~ /NetworkInterfaces/) {
          chomp ($therest);
          if ($therest !~ /vmnet/) {
             %network_stuff = /(\b\w+)=(\S*)/g;
             foreach $temp (keys(%network_stuff)) {
                if ($temp eq "mac" or $temp eq "macaddr") {
                    $ffff = $network_stuff{$temp};
                    $mac_quoted = $dbh->quote($ffff);
                }
                if ($temp eq "ip" or $temp eq "ipaddr") {
                    $gggg = $network_stuff{$temp};
                    $ip_quoted = $dbh->quote($gggg);
                }
                if ($temp eq "device") {
                    $hhhh = $network_stuff{$temp};
                    $hhhh =~ s/"//g;
                    $fulldesc_quoted = $dbh->quote($hhhh);
                }
             }   #foreach loop
                $mac_address_exists = $dbh->selectrow_array("select mac_address from inetcard where mac_address = $mac_quoted");
                if (! $mac_address_exists) {
                    my $q1 = "INSERT INTO inetcard (inetcard_id,mac_address,ip_address,card_description,node_id) values (NULL,$mac_quoted,$ip_quoted,$fulldesc_quoted,$nodeid_number_quoted)";
                    my $in = $dbh->prepare($q1);   
                    $in->execute;
                } else {
                    my $q1 = "UPDATE inetcard set ip_address = $ip_quoted, card_description = $fulldesc_quoted, node_id = $nodeid_number_quoted where mac_address = $mac_quoted";
                    my $in = $dbh->prepare($q1);
                    $in->execute;
                }
          }   
          next;
        } else {
          next;
        } # date if
     } elsif ($category eq "Software") {
        if ($sub_category =~ /Patches/) {
           chomp ($therest);
           ($rpm_name,$description) = split(/[ \t\n]+/, $therest, 2);
           if ($description) {
              $description =~ s/desc=//g;
              $description =~ s/"//g;
           }
           my $desc_quoted = $dbh->quote($description);
           my $rpm_name_quoted = $dbh->quote($rpm_name);
           my $software_query = $dbh->prepare("select * from software where software_name = $rpm_name_quoted");
           $software_query->execute;
           my $exists_software = $software_query->rows;
           if ($exists_software < 1) {
               print "Adding $rpm_name";
               my $q1 = "INSERT INTO software (software_id,software_name,software_desc) values (NULL,$rpm_name_quoted,$desc_quoted)";
               my $in = $dbh->prepare($q1);
               $in->execute;
               $softwareid_number = $in->{'mysql_insertid'};
               $softwareid_number_quoted = $dbh->quote($softwareid_number);
               my $q2 = "INSERT INTO software_installed (software_id,node_id) values ($softwareid_number_quoted,$nodeid_number_quoted)";
               my $in2 = $dbh->prepare($q2);
               $in2->execute;  
           } else {
               @row_of_data = $software_query->fetchrow_array;
               $softwareid_number = $row_of_data[0];
               $softwareid_number_quoted = $dbh->quote($softwareid_number);
               my $q2 = "INSERT INTO software_installed (software_id,node_id) values ($softwareid_number_quoted,$nodeid_number_quoted)";
               my $in2 = $dbh->prepare($q2);
               $in2->execute;
           }
           next;
        }  elsif ($sub_category =~ /Services-/) {
           chomp ($therest); 
           ($service_name,$description) = split(/:/, $therest, 2);
           my $desc_quoted = $dbh->quote($description);
           my $service_name_quoted = $dbh->quote($service_name);
           my $service_query = $dbh->prepare("select * from services where service_name = $service_name_quoted AND service_desc = $desc_quoted");
           $service_query->execute;
           my $exists_service = $service_query->rows;
           if ($exists_service < 1) {
               print "Adding $service_name";
               my $q9 = "INSERT INTO services (service_id,service_name,service_desc) values (NULL,$service_name_quoted,$desc_quoted)";
               my $in9 = $dbh->prepare($q9);
               $in9->execute;
               $serviceid_number = $in9->{'mysql_insertid'};
               $serviceid_number_quoted = $dbh->quote($serviceid_number);
               my $q10 = "INSERT INTO services_used (service_id,node_id) values ($serviceid_number_quoted,$nodeid_number_quoted)";
               my $in10 = $dbh->prepare($q10);
               $in10->execute;
           } else {
               @row_of_data = $service_query->fetchrow_array;
               $serviceid_number = $row_of_data[0];
               $serviceid_number_quoted = $dbh->quote($serviceid_number);
               my $q10 = "INSERT INTO services_used (service_id,node_id) values ($serviceid_number_quoted,$nodeid_number_quoted)";  
               my $in10 = $dbh->prepare($q10);
               $in10->execute;
           }
           next;
        }  elsif ($sub_category =~ /OsName/) {
           chomp ($therest);
           my $osname_quoted = $dbh->quote($therest);
           my $software_query = $dbh->prepare("update nodes set OsName = $osname_quoted where node_id = $nodeid_number_quoted");
           $software_query->execute;  
           $software_query->finish;
           next;
        }  elsif ($sub_category =~ /OsVersion/) {
           chomp ($therest);
           my $osversion_quoted = $dbh->quote($therest);
           my $software_query = $dbh->prepare("update nodes set OsVersion = $osversion_quoted where node_id = $nodeid_number_quoted");
           $software_query->execute;    
           $software_query->finish;
           next;
        }  elsif ($sub_category = /Swap/) {
           chomp ($therest);  
           my $swapspace_quoted = $dbh->quote($therest);
           my $swap_query = $dbh->prepare("update nodes set swapspace = $swapspace_quoted where node_id = $nodeid_number_quoted");
           $swap_query->execute;
           $swap_query->finish;
           next;
        }  else  {  
           next;
        }
     } elsif ($category eq "Hardware") {
       if ($sub_category =~ /Processors-/) {
          $number_of_cpus = $number_of_cpus + 1;
           chomp ($therest);
           $therest =~ s/^processor [0-9] //g;
           my $processor_quoted = $dbh->quote($therest);
           my $software_query = $dbh->prepare("update nodes set processor = $processor_quoted where node_id = $nodeid_number_quoted");
           $software_query->execute;
           $software_query->finish;
           next;
       } elsif ($sub_category eq "Memory") {
           chomp ($therest); 
           $therest =~ s/MB//g;
           my $memory_quoted = $dbh->quote($therest);
           my $software_query = $dbh->prepare("update nodes set memory = $memory_quoted where node_id = $nodeid_number_quoted");
           $software_query->execute;
           $software_query->finish;
           next;
        } elsif ($sub_category =~ /Disks-/) {
          chomp ($therest);
#             %network_stuff = /(\b\w+)=(\S*)/g;
#             foreach $temp (keys(%network_stuff)) {
           my $disk_desc_quoted = $dbh->quote($therest); 
           my $q5 = "INSERT INTO disks (disk_id,disk_description,node_id) values (NULL,$disk_desc_quoted,$nodeid_number_quoted)";
           my $in5 = $dbh->prepare($q5);   
           $in5->execute;
           next;  
        }
     } elsif ($category eq "General") {
       if ($sub_category eq "Vendor") {
           chomp ($therest);
           my $osvendor_quoted = $dbh->quote($therest);
           my $software_query = $dbh->prepare("update nodes set OsVendor = $osvendor_quoted where node_id = $nodeid_number_quoted");
           $software_query->execute;
           $software_query->finish;
       } elsif ($sub_category eq "LastUpdate") {
           chomp ($therest);
           $fgfgfg = `date -d "$therest" '+%Y-%m-%d %X'`;
           my $lastupdate_quoted = $dbh->quote($fgfgfg);
           my $last_query = $dbh->prepare("update nodes set lastupdate = $lastupdate_quoted where node_id = $nodeid_number_quoted");
           $last_query->execute;
           $last_query->finish; 
       } elsif ($sub_category eq "Location") {
           chomp ($therest);
           my $location_quoted = $dbh->quote($therest);
           my $location_query = $dbh->prepare("update nodes set location = $location_quoted where node_id = $nodeid_number_quoted");
           $location_query->execute;
           $location_query->finish;
       } elsif ($sub_category eq "Organization") {         
           chomp ($therest);       
           my $org_quoted = $dbh->quote($therest);         
           my $org_query = $dbh->prepare("update nodes set organization = $org_quoted where node_id = $nodeid_number_quoted");    
           $org_query->execute;
           $org_query->finish;    
       } else {
       }
       next;
     } else {
#       print "how did we get here? $category $sub_category";
     }
  } # while loop for a single host
#  print "Host is $fqdn_name";
#  print "Number of CPUs is $number_of_cpus";
     my $qty_processor_quoted = $dbh->quote($number_of_cpus);
     if ($number_of_cpus > 1) {
        my $qty_query = $dbh->prepare("update nodes set qty_processor = $qty_processor_quoted where node_id = $nodeid_number_quoted");
        $qty_query->execute;
        $qty_query->finish;
     }
} # while loop for all hosts
$dbh->disconnect;
$dbh = undef;
