Subversion Repositories computer_asset_manager_v1

#! /usr/bin/perl -w

# process_sysinfo.pl

# Author: R. W. Rodolico

# Part of sysinfo package. This application takes the output from sysinfo.pl as input, parsing

# it and populating a database (see sysinfo.sql) with the results.

# Application will bypass anything up to the line beginning with [sysinfo version], allowing

# the output of sysinfo.pl to be e-mailed to a central server, where this app will correctly parse

# it.

# Application also has limited sensing capabilities, and will respond to certain conditions with a

# message on STDOUT indicating conditions, such as disk usage above an alarm value, new Operating Systems

# installed, etc... The goal is to allow this program to be called by a cron job, and the result (if any)

# returned in the cron specified e-mail.

# OVERVIEW:

#    The input file contains two types of information, both beginning with a tag surrounded by square

#    brackets. The single line data has the value for that tag immediately following the tag, ie:

#       [tagname]value

#    Multiline data has a beginning tag, then data following on a line by line basis, until the next

#    tag (denoted by a tag name surrounded by square brackets) is reached. Every line between the first

#    and subsequent tag are considered data for the previous tag, ie:

#       [tagname1]

#       value 1

#       value 2

#       [tagname2]

#    thus, value 1, and value 2 are data for tagname1. Multiline data is stored in its own hash, and handled

#    by separate routines.

#    See bottom of this app for main routine; subroutines are in between here and the beginning of the main

#    code

# Required Libraries:

#      GenericSQL        - Home Grown MySQL access routine, included with package

#      GenericTemplates  - Home Grown routine, included with package

#      Logging           - Home Grown logging routine, included with package

#      Date (uses format and parse)

# Suggested Uses: See process_sysinfo.sh. This is an example that looks for all messages in a directory

#                 (in this case, a maildir directory), processes each e-mail, then moves the e-mail

#                 to the Processed folder. The advantage to this is that it can be called by a nightly

#                 cron job, with any warnings e-mailed back to the sysadmin

# version 0.10 20071103

#      Ready for distribution. Database fairly normalized.

# version 0.11 20071104

#      Bug fix, and set up for "uninstalled" software

# version 0.12 20071104

#      Adjusted so it will ignore blank package lines, and will handle directories_to_watch

# version 0.13 20071120

#      Fixed problem where a file that was NOT a valid sysinfo file would cause a run-away

# version 1.00 20071206

#  Modified for new database format

# version 1.01 20071208

# Modified for report_date to be a date/time stamp instead of just the date

# version 2.0.0b 20081208

# converted to read XML data. Also uses older style data, but converts it to standard hash to mimic xml

# Adds requirement for libxml-simple-perl

# version 2.0.1 20090416

# Bug fix

my $VERSION = '2.0.1';

# only required if reports are sent from process_sysinfo. Not the norm.

my $iMailResults;

my $mailTo;

my $mailCC;

my $mailBCC;

my $mailServer;

my $mailServerPort;

my $mailFrom;

my $SENDMAIL;

my $DiskUsageAlert = 90; # will generate a warning if any disk has more than this percent capacity used

# information for the database

#my $DSN = 'DBI:mysql:camp'; # and, set up the database access

#my $DB_USER = 'test';

#my $DB_PASS = 'test';

#my $LIBRARIES = '/home/www/common-cgi/'; # where the extra libraries are stored

# global variables (not configuration information)

my $dbh; # global variable for database handle

my @warnings; # variable to hold errors and warnings

my $FATAL = ''; # in case of a fatal error, this variable will hold the reason

# Globals that hold information after parsing by readAndParseInput

my $datafileVersion;# store the data file version here.

my $reportDate;      # global for the report date

my $clientID;

my $computerID;

my $clientName;

my $computerName;

#my %info;            # stores any single line tag/value pair

#my $ipAddresses;     # stores IP's for this machine

#my @diskInfo;        # stores the disk info

#my @packages;        # stores package info

#my @directoriesToWatch; # stores directories to watch

#my @pciInfo;        # stores pci info

# safe check for equality. Handles undefined

sub checkEquals {

   my ($first, $second) = @_;

   return ($first eq $second) if (defined $first) and (defined $second);

   return 1 if (! defined $first) and (! defined $second); # both undefined, so equal

   return 0;

}

# standard find and load configuration file

sub loadConfigurationFile {

   my $configuration_file = shift;

   use File::Basename;

   use Cwd qw(realpath);

   my $filename  = realpath($0); # get my real path

   my $directories;

   my $suffix;

   #print "$configuration_file\n";

   $configuration_file = $filename unless $configuration_file;

   #print "$configuration_file\n";

   if ( $configuration_file !~ m/\// ) { # no path information

      ($filename, $directories, $suffix) = fileparse($filename,qr/\.[^.]*/); # break filename apart

      #print "No Path Given\n";

   } else {

      ($filename, $directories, $suffix) = fileparse($configuration_file,qr/\.[^.]*/); # break filename apart

      $configuration_file = '';

      #print "Path included\n";

   }

   unless (-e $directories . ($configuration_file ? $configuration_file : $filename) . '.conf' ) {

      $lookingIn = $directories;

      while ($lookingIn) {

         $lookingIn =~ m/^(.*\/)[^\/]+\//;

         $lookingIn = $1;

         #print "$lookingIn\n";

         if (-e $lookingIn . ($configuration_file ? $configuration_file : $filename) . '.conf' ) {

            $directories = $lookingIn;

            $lookingIn = '';

         }

      }

   }

   $configuration_file = $directories . ($configuration_file ? $configuration_file : $filename) . '.conf'; # add the .conf

#   print "$configuration_file\n";

#   die;

   open CONFFILE, "<$configuration_file" or die "Can not open configuration file $configuration_file";

   my $confFileContents = join( '', <CONFFILE> );

   close CONFFILE;

   return $confFileContents;

}

# just a nice place to format any warnings/errors. Just prepend the client and computer name

sub createLogMessage {

   my $message = shift;

   $message = "$clientName - $computerName: " . $message;

   return $message;

}

# generic routine to send an e-mail

sub sendmessage {

   my ( $from, $to, $subject, $message, $cc, $bcc, $server, $port ) = @_;

   open SENDMAIL, "|$SENDMAIL" or die "Could not open sendmail";

   print SENDMAIL "From: $from\nTo: $to\nSubject: $subject\n";

   print SENDMAIL "cc: $cc\n" if $cc;

   print SENDMAIL "bcc: $bcc\n" if $bcc;

   print SENDMAIL "$message\n";

   print SENDMAIL ".\n";

   close SENDMAIL;

}

# simply used to get an attrib_id. If it does not exit, will create it

sub getAttributeID {

   my ($attributeName ) = @_;

   my $sql = qq/select attrib_id from attrib where name = $attributeName and removed_date is null/;

   my $result = &GenericSQL::getOneValue($dbh,$sql);

   unless ( $result ) {

      my $insertSQL = qq/insert into attrib (name,added_date) values ($attributeName,$reportDate)/;

      &GenericSQL::doSQL( $dbh,$insertSQL );

      $result = &GenericSQL::getOneValue($dbh,$sql);

      push @warnings, "Added a new attribute type [$attributeName]";

   }

   return $result;

}

sub fixDatabaseValue {

   # just return NULL if the parameter is invalid

   return 'NULL' unless defined $_[0];

   my ($value,$alwaysQuote) = @_;

   # remove leading and trailing blank spaces

   $value =~ s/^ +//gi;

   $value =~ s/ +$//gi;

   if ($alwaysQuote or ($value !~ m/^\d+$/)) { # Not a numeric value

      $value = &GenericSQL::fixStringValue($dbh, $value); # so get it ready for SQL (ie, put quotes around it, etc...

   }

   #$value = "'$value'" if $alwaysQuote && $value ;

   #print "AlwaysQuote [$alwaysQuote], value [$value]\n";

   return $value;

}

sub checkAndUpdateAttribute {

   my ($ID,$attribute,$value ) = @_;

   unless ($attribute && $value) {

      push @warnings, "Error: attempt to use null value for [$attribute], value [$value] for ID $ID in checkAndUPdateAttribute";

      return 0;

   }

   $value = &fixDatabaseValue($value, 1); # we want to always quote the value on this particular one

   #print "\tcheckAndUpdateAttribute:attribute/value = [$attribute][$value]\n";

   $attribute = &fixDatabaseValue( $attribute );

   my $attrib_id = &getAttributeID( $attribute, $reportDate );

   my $sql = qq/

      select device_attrib.value

      from device_attrib join attrib using (attrib_id)

      where device_attrib.device_id = $ID

            and device_attrib.removed_date is null

            and attrib.attrib_id = $attrib_id

      /;

   $result = &GenericSQL::getOneValue($dbh,$sql);

   $result = &fixDatabaseValue( $result, 1 ); # we must do this since we are comparing to $value which has had this done

   if ( $result ) { # got it, now see if it compares ok

      if ( $value ne $result ) { # nope, this has changed. Note, must use fixDatabaseValue for escapes already in $value

         # first, set the removed_date to now on the old part

         #die "[$reportDate][$ID][$attrib_id]\n";

         #print "\tresult = [$result], value = [$value]\n";

         $sql = qq/

            update device_attrib

            set removed_date = $reportDate

            where device_id = $ID

                  and attrib_id = $attrib_id

                  and removed_date is null

         /;

         &GenericSQL::doSQL( $dbh, $sql );

         undef $result; # this will force the insert in the next block of code

      } # if $result ne $value

   } # if ($result)

   unless ( $result ) { # we have no valid entry for this attribute

      $sql = qq/

         insert into device_attrib(device_id,attrib_id,value,added_date)

            values ($ID,$attrib_id,$value,$reportDate)

         /;

      &GenericSQL::doSQL( $dbh, $sql );

      return 1;

   }

   return 0;

}

# main subroutine that reads and parses the input

# it will place the appropriate values into the arrays/hashes

sub readAndParseInput {

   my @lines = <STDIN>; # suck all data lines into this array

   chomp @lines; # and remove eol chars

   while ( @lines && ( $lines[0] !~ m/^[\[\<]sysinfo/ ) ) { # skip any header lines that may exist

      shift @lines;

   }

   unless ( @lines ) {

      $FATAL = 'Invalid Report File';

      return;

   }

   if ( $lines[0] =~ m/<sysinfo([^>]*)>/ ) { # this is xml data

      $datafileVersion = $1;

      use XML::Simple; 

      my $output = XML::Simple->new();

      my $theXML = $output->XMLin(join( "\n",@lines));

      # XML::Simple is inconsistent in that the resulting hashes are different depending

      # on if they only have one instance of a block or multiples. This is ok most of the 

      # time, but for diskinfo, network, etc..., our code assumes the resulting hashes

      # will have a key into a hash reference, not simply a hash reference. The following 

      # kludge fixes that until I figure out the "right" way to do it.

      %toFix = ( 'diskinfo' => 'name', 'pci' => 'name' ); # , 'network'

      for $key ( keys %toFix ) { 

         #print "\n\nChecking $key\n";

         #print Data::Dumper->Dump([$$theXML{$key}],[$key]);

         my $test = $$theXML{$key};

         if ( defined $$test{ $toFix{$key}} ) { # This occurs if there is only one entry

            #print "We found a single entry hash\n";

            my $thisName = $$test{$toFix{$key}};

            delete $$test{$toFix{$key}};

            foreach $thisKey ( keys %$test ) {

               $$test{$thisName}{$thisKey} = $$test{$thisKey};

               delete $$test{$thisKey};

            }

         #print Data::Dumper->Dump([$$theXML{$key}],[$key]);

         }

      }

      # another problem, this time if the hostname is blank it the library sets it to an empty hash

      #print STDERR ref($$theXML{'system'}{'hostname'});

      # if the hostname is anything other than a pure scalar, just make it empty

      $$theXML{'system'}{'hostname'} = '' if ( ref($$theXML{'system'}{'hostname'}) );

      #print "\n\nAfter the check\n";

      #print Data::Dumper->Dump([$$theXML{'system'}],['system']);

      #die;

      return $theXML;

   } elsif ($lines[0] =~ m/\[sysinfo version\]/i) { # old style data file

      my %returnValue;

      my $linecount = 0; # just a pointer into the array

      # ok, we are in the actual data (this can be an e-mail)

      while ($linecount < @lines ) {

         if ($lines[$linecount++] =~ m/^\[([^\]]+)\](.*)$/ ) { # Assume single line entry, ie [tag]value

            $section = lc $1; # tag, section, whatever you want to call it. The thing inside the square brackets

            $value = $2; # this is what we are working with

            #$value = &fixDatabaseValue($2); # this is what we are working with

            # first, look for anything we want to place in global variables

            if ( $section eq 'sysinfo version' ) {

               $returnValue{'report'}{'version'} = $value;

               $datafileVersion = $value;

            } elsif ($section eq 'client name') {

               $returnValue{'report'}{'client'} = $value;

            } elsif ($section eq 'hostname' ) {

               $returnValue{'system'}{'hostname'} = $value;

            } elsif ($section eq 'report date') { # ok, so we put quotes around this and we don't want them

               $value =~ s/'//gi; # remove the quotes

               my $reportDate = substr($value,0,12); # and grab only the date, hours and minutes portion of the value

               $reportDate = substr($value,0,4) .'-' . substr($value,4,2) .'-' . substr($value,6,2) .' ' . substr($value,8,2) .':' . substr($value,10,2);

               $returnValue{'report'}{'date'} = $reportDate;

            } elsif ($section eq 'ip addresses' ) { # A machine can have multiple IP's

               @ipAddresses = split(' ', $value);

               my $counter = 0;

               while (my $ip = shift @ipAddresses) {

                  $returnValue{'network'}{$counter++}{'address'} = $ip;

               }

            # following are processing the multi-line tags. We just look through them and get the start and end

            # indicies of the section so they can be processed by the appropriate routines

            } elsif ($section eq 'disk info') {

               # disk info can be one or more line, so work until we find the next tag

               while ($linecount < @lines && $lines[$linecount] !~ m/^\[([^\]]+)\](.*)$/) {

                  my ( $device, $fstype, $size, $used, $mount ) = split( "\t",$lines[$linecount++] );

                  $returnValue{'diskinfo'}{$device}{'fstype'} = $fstype if $fstype;

                  $returnValue{'diskinfo'}{$device}{'size'} = $size if $size;

                  $returnValue{'diskinfo'}{$device}{'used'} = $used if $used;

                  $returnValue{'diskinfo'}{$device}{'mount'} = $mount if $mount;

                  #   push @diskInfo, $lines[$linecount++];

               }

            } elsif ($section eq 'packages installed') { # and, of course, software packages

               while ($linecount < @lines && $lines[$linecount] !~ m/^\[([^\]]+)\](.*)$/) {

                  if ( $lines[$linecount] =~ m/^\s*$/) {

                     $linecount++;

                     next;

                  }

                  my ($package, $version, $description) = split( "\t", $lines[$linecount++]);

                  $returnValue{'software'}{$package}{'version'} = $version;

                  $returnValue{'software'}{$package}{'description'} = $description;

                  #push @packages, $lines[$linecount++];

               }

            } elsif ($section eq 'pci info') { # this gives us some hardware info

               my $x = 0;

               while ($linecount < @lines && $lines[$linecount] !~ m/^\[([^\]]+)\](.*)$/) {

                  if ($lines[$linecount] =~ m/^\s*([0-9.:]+[\S])\s+(.*\S)\s*$/) { # we have a slot/name pair

                     $returnValue{'pci'}{$x}{'slot'} = $1;

                     $returnValue{'pci'}{$x}{'name'} = $2;

                  } else {

                     $returnValue{'pci'}{$x}{'slot'} = 'v1.x-unk';

                     $returnValue{'pci'}{$x}{'name'} = $lines[$linecount];

                  }

                  $x++;

                  $linecount++;

               }

            # we have a single line tag, so just store it in the right place

            } elsif ($value) {

               if ( $section eq 'client name' ) {

                  $returnValue{'report'}{'client'} = $value;

               } elsif ( $section eq 'distro_name' ) {

                  $returnValue{'operatingsystem'}{'distribution'} = $value;

               } elsif ( $section eq 'distro_description' ) {

                  $returnValue{'operatingsystem'}{'description'} = $value;

               } elsif ( $section eq 'distro_release' ) {

                  $returnValue{'operatingsystem'}{'release'} = $value;

               } elsif ( $section eq 'distro_codename' ) {

                  $returnValue{'operatingsystem'}{'codename'} = $value;

               } elsif ( $section eq 'hostname' ) {

                  $returnValue{'system'}{'hostname'} = $value;

               } elsif ( $section eq 'memory' ) {

                  $returnValue{'system'}{'memory'} = $value;

               } elsif ( $section eq 'num_cpu' ) {

                  $returnValue{'system'}{'num_cpu'} = $value;

               } elsif ( $section eq 'cpu_speed' ) {

                  $returnValue{'system'}{'cpu_speed'} = $value;

               } elsif ( $section eq 'cpu_type' ) {

                  $returnValue{'system'}{'cpu_type'} = $value;

               } elsif ( $section eq 'cpu_sub' ) {

                  $returnValue{'system'}{'cpu_sub'} = $value;

               } elsif ( $section eq 'os_name' ) {

                  $returnValue{'operatingsystem'}{'os_name'} = $value;

               } elsif ( $section eq 'os_version' ) {

                  $returnValue{'operatingsystem'}{'os_version'} = $value;

               } elsif ( $section eq 'kernel' ) {

                  $returnValue{'operatingsystem'}{'kernel'} = $value;

               } elsif ( $section eq 'boot' ) {

                  $returnValue{'system'}{'last_boot'} = $value;

               } elsif ( $section eq 'uptime' ) {

                  $returnValue{'system'}{'uptime'} = $value;

               }

               #$info{$section} = $value;

            } else { # we should never get here, a single line with no value is BAD

               push @warnings, &createLogMessage( "Unknown line '$lines[$linecount-1]'" ) . "\n";

            }

         } else { # this is also an error, no [tagname]. Maybe a space inserted?

            push @warnings, &createLogMessage( "unknown line '$lines[$linecount-1]'" ) . "\n";

         }

      } # while

      return \%returnValue;

   }

}

# tries to figure out the client. If the client does not exist, will create a null record

# for them. Stores result in $client_id (reading $clientName)

sub getClientID {

   # let's see if the client exists

   $client = &fixDatabaseValue($clientName);

   $sql = qq/select client_id from client where name = $client and removed_date is null/;

   my $client_id = &GenericSQL::getOneValue( $dbh, $sql );

   unless ($client_id) { # no entry, check the alias table

      $sql = qq/select client_id from client_alias where alias=$client and removed_date is null/;

      $client_id = &GenericSQL::getOneValue( $dbh, $sql );

   }

   # the following has been changed to simply return a message

   unless ( $client_id ) { # nope, client does not exist, so add them

      $device = &fixDatabaseValue($computerName);

      $sql = qq/select report_date from unknown_entry where client_name = $client and device_name = $device/;

      #$report = &GenericSQL::getOneValue( $dbh, $sql );

      #print STDERR "Report Date $report\n";

      if ($report = &GenericSQL::getOneValue( $dbh, $sql )) {

         $FATAL = "New Client detected, but entry already made. You must update Camp before this can be processed";

      } else {

         $sql = qq/insert into unknown_entry(client_name,device_name,report_date) values ($client, $device, $reportDate)/;

         &GenericSQL::doSQL( $dbh, $sql );

         $FATAL = "Warning, new client $client found with new device $device. You must update Camp before this report can be processed\n";

      }

   }

   return $client_id;

}

# get a device type id from the device table. Create it if it does not exist

sub getDeviceTypeID {

   my $typeDescription = &GenericSQL::fixStringValue( $dbh, shift );

   my $reportDate = shift;

   $sql = qq/select device_type_id from device_type where name = $typeDescription and removed_date is null/;

   my $id = &GenericSQL::getOneValue( $dbh, $sql );

   unless ($id) {

      my $sql_insert = qq/insert into device_type ( name,added_date ) values ($typeDescription, $reportDate) /;

      &GenericSQL::doSQL( $dbh, $sql_insert );

      $id = &GenericSQL::getOneValue( $dbh, $sql );

   }

   return $id;

}

# gets the computer ID. If the computerID does not exist, creates it.

# returns the id of the computer.

sub getComputerID {

   # ok, does this computer name exist (each computer name per site must be unique)

   $computer = &fixDatabaseValue($computerName);

   my $sql = qq/

      select device_id

      from device join site using (site_id)

      where site.client_id = $clientID

            and device.name = $computer

            and device.removed_date is null

         /;

   my $computer_id = &GenericSQL::getOneValue( $dbh, $sql ); # actually, result of query above

   unless ( $computer_id ) { # didn't find it. Let's see if it is in the alias table

      $sql = qq/select device_id from device_alias join device using (device_id) join site using (site_id) where device_alias.alias = $computer and site.client_id = $clientID/;

      $computer_id = &GenericSQL::getOneValue( $dbh, $sql );

   }

   # changed to just give a warning

   unless ( $computer_id ) { # nope, computer does not exist so create it

      $client = &fixDatabaseValue($clientName);

      $sql = qq/select report_date from unknown_entry where client_name = $client and device_name = $computer and processed_date is null/;

      #$report = &GenericSQL::getOneValue( $dbh, $sql );

      #print STDERR "Report Date $report\n";

      if ($report = &GenericSQL::getOneValue( $dbh, $sql )) {

         $FATAL = "New Device detected, but entry already made. You must update Camp before this can be processed";

      } else {

         $sql = qq/insert into unknown_entry(client_name,device_name,report_date) values ($client, $computer, $reportDate)/;

         &GenericSQL::doSQL( $dbh, $sql );

         $FATAL = "Warning, new device $computer found associated with client $client. You must update Camp before this report can be processed\n";

      }

   }

   return $computer_id;

}

# checks for a duplicate report, ie one that has already been run.

# the only thing that always changes is disk space usage, so just look to see

# if this computer has a report already for this date/time

sub recordReport {

   my $sql = qq/select count(*) from sysinfo_report where device_id = $computerID and report_date = $reportDate/;

   if (&GenericSQL::getOneValue( $dbh, $sql ) > 0) {

      my $thisDevice = &GenericSQL::getOneValue( $dbh, "select name from device where device_id = $computerID" );

      $FATAL = qq/Duplicate Report for $thisDevice (id $computerID) on $reportDate/;

      return;

   }

   $version = &fixDatabaseValue($datafileVersion);

   # if we made it this far, we are ok, so just add the report id

   $sql = qq/insert into sysinfo_report(device_id,version,report_date,added_date) values ($computerID,$version,$reportDate,now())/;

   &GenericSQL::doSQL($dbh,$sql);

   $sql = qq/select sysinfo_report_id from sysinfo_report where device_id = $computerID and report_date = $reportDate/;

   return &GenericSQL::getOneValue( $dbh, $sql );

}

# gets operating system ID. If it does not exist, creates it

sub getOSID {

   my ($osHash) = shift;

   my $os_id;

   my $osName = &fixDatabaseValue($$osHash{'os_name'});

   my $kernel = &fixDatabaseValue($$osHash{'kernel'});

   my $distro_name = &fixDatabaseValue($$osHash{'distribution'});

   my $release = &fixDatabaseValue($$osHash{'release'});

   my $version = &fixDatabaseValue($$osHash{'os_version'});

   my $description = &fixDatabaseValue($$osHash{'description'});

   my $codename = &fixDatabaseValue($$osHash{'codename'});

   $sql = qq/select operating_system_id from operating_system

            where name = $osName

               and kernel = $kernel

               and distro = $distro_name

               and distro_release = $release /;

   unless ( $os_id = &GenericSQL::getOneValue( $dbh, $sql ) ) {

      $sql = qq/insert into operating_system (name,version,kernel,distro,distro_description,distro_release,distro_codename, added_date) values

               ($osName,$version,$kernel,$distro_name,$description,$release,$codename, $reportDate)/;

      &GenericSQL::doSQL( $dbh, $sql );

      $sql = qq/select operating_system_id from operating_system

            where name = $osName

               and kernel = $kernel

               and distro = $distro_name

               and distro_release = $release /;

      $os_id = &GenericSQL::getOneValue( $dbh, $sql );

   }

   return $os_id;

}

# simply verifies some attributes of the computer

sub updateComputerMakeup {

   my ($systemHash) = @_;

   #print "[$$systemHash{'memory'}]\n";

   &checkAndUpdateAttribute($computerID,'Memory',$$systemHash{'memory'});

   #print "[$$systemHash{'num_cpu'}]\n";

   &checkAndUpdateAttribute($computerID,'Number of CPUs',$$systemHash{'num_cpu'});

   #die;

   &checkAndUpdateAttribute($computerID,'CPU Type',$$systemHash{'cpu_type'});

   &checkAndUpdateAttribute($computerID,'CPU SubType',$$systemHash{'cpu_sub'});

   &checkAndUpdateAttribute($computerID,'CPU Speed',$$systemHash{'cpu_speed'});

}

sub updateOS {

   my ($osHash) = @_;

   # verify the operating system

   my $os_id = &getOSID($osHash, $reportDate);

   $sql = qq/select operating_system_id from device_operating_system where device_id = $computerID and removed_date is null/;

   $registeredOS = &GenericSQL::getOneValue( $dbh, $sql );

   unless ($registeredOS && $registeredOS eq $os_id ) {

      if ( $registeredOS ) { #we have the same computer, but a new OS???

         $sql = qq/update device_operating_system set removed_date = $reportDate where device_id = $computerID and removed_date is null/;

         &GenericSQL::doSQL( $dbh, $sql);

         push @warnings, &createLogMessage("Computer $computerName has a new OS" );

         $os_id = &getOSID($osHash, $reportDate);

      }

      $sql = qq/insert into device_operating_system( device_id,operating_system_id,added_date) values ($computerID,$os_id,$reportDate)/;

      &GenericSQL::doSQL( $dbh, $sql );

   }

}

sub dateToMySQL {

   my $date = shift;

   # print "Date In $date\n";

   $date =~ s/'//g; # some of the older reports put quotes around this

   return &fixDatabaseValue($date) if $date =~ m/\d{4}[-\/]\d{2}[-\/]\d{2} \d{2}:\d{2}/;  # this is already in the correct format

   my ($ss,$mm,$hh,$day,$month,$year,$zone);

   unless  ( $date =~ m/^\d+$/ ) { # If it is not a unix time stamp

      $date = str2time($date); # try to parse it

   }

   return '' unless defined $date && $date; # bail if date is not defined or zero

   # standard processing of a date

   ($ss,$mm,$hh,$day,$month,$year,$zone) = localtime($date);

   $year += 1900;

   ++$month;

   # printf( "Answer Is: %4d-%02d-%02d %02d:%02d\n", $year,$month,$day,$hh,$mm);

   return &fixDatabaseValue(sprintf( '%4d-%02d-%02d %02d:%02d', $year,$month,$day,$hh,$mm));

}

# every time we get a report, we need to see if the computer was rebooted

# if last reboot date is not the same as what our report shows, we will

# remove the existing entry, then add a new one

sub updateBootTime {

   my ($systemHash) = @_;

   my $lastReboot;

   if ($$systemHash{'last_boot'}) {

      #print "Checking Boot Time\n";

      if ($lastReboot = &dateToMySQL($$systemHash{'last_boot'})) {

         my $sql = qq/select computer_uptime_id from computer_uptime where device_id = $computerID and last_reboot = $lastReboot/;

         unless ( &GenericSQL::getOneValue( $dbh, $sql ) ) {

            push @warnings, &createLogMessage("Computer was rebooted at $lastReboot");

            my $sql_insert = qq/update computer_uptime set removed_date = $reportDate where device_id = $computerID and removed_date is null/;

            &GenericSQL::doSQL( $dbh, $sql_insert );

            $sql_insert = qq/insert into computer_uptime (device_id,added_date,last_reboot) values ($computerID,$reportDate,$lastReboot)/;

            &GenericSQL::doSQL( $dbh, $sql_insert );

         }

      } else {

         push @warnings, &createLogMessage('Invalid reboot time [' . $$systemHash{'last_boot'} . ']');

      }

   } else {

      push @warnings, &createLogMessage('No Boot time given');

   }

}

# routine will check for all IP addresses reported and check against those recorded in the

# database. It will remove any no longer in the database, and add any new ones

sub doIPAddresses {

   my ( $networkHash ) = @_;

   # delete $$networkHash{'lo'}; # we don't process lo

   # first, remove any interfaces that no longer exist

   my $interfaces = join ',', (map { &fixDatabaseValue($_) } keys %$networkHash); # get a list of interfaces being passed in

   if ( $interfaces ) {

      my $sql = qq/update network set removed_date = $reportDate where device_id = $computerID and removed_date is null and interface not in ($interfaces)/;

      &GenericSQL::doSQL( $dbh, $sql );

   }

   # let's get all remaining network information

   $sql = qq/select network_id,interface,address,netmask,ip6,ip6net,mac,mtu from network where device_id = $computerID and removed_date is null/;

   my $sth = &GenericSQL::startQuery( $dbh, $sql );

   while (my $thisRow = &GenericSQL::getNextRow($sth)) {

      if ( defined $$thisRow{'interface'} ) { # pre 2.0 versions did not have an interface object

         # long drawn out thing to check if they are the same

         if ( &checkEquals($$networkHash{$$thisRow{'interface'}}{'address'}, $$thisRow{'address'}) && 

              &checkEquals($$networkHash{$$thisRow{'interface'}}{'ip6address'}, $$thisRow{'ip6'}) &&

              &checkEquals($$networkHash{$$thisRow{'interface'}}{'ip6networkbits'}, $$thisRow{'ip6net'}) &&  

              &checkEquals($$networkHash{$$thisRow{'interface'}}{'mac'}, $$thisRow{'mac'}) &&

              &checkEquals($$networkHash{$$thisRow{'interface'}}{'mtu'}, $$thisRow{'mtu'}) && 

              &checkEquals($$networkHash{$$thisRow{'interface'}}{'netmask'}, $$thisRow{'netmask'}) ) {

            # they are the same, so just mark it off the list

            delete $$networkHash{$$thisRow{'interface'}};

         } else { # it has changed, so invalidate the current line in the database

            $sql = qq/update network set removed_date = $reportDate where network_id = $$thisRow{'network_id'}/;

            &GenericSQL::doSQL( $dbh, $sql ); 

         }

      } else { # the database is still using pre 2.0 values, so we must see if we need to upgrade this

         if ($datafileVersion =~ m/^2/) { # version 2.x, so we will need to update this record

            # in this case, we are going to just "remove" all current entries and reload them below.

            # this code will only be run once for each machine that needs to conver to the new format

            $sql = qq/update network set removed_date = $reportDate where removed_date is null and device_id = $computerID/;

            last;

         }

      }

   }

   # at this point, the only items left are either new or have changed, so just insert them.

   foreach my $device ( keys %$networkHash ) {

      $sql = qq/insert into network (device_id,added_date,interface,address,netmask,ip6,ip6net,mtu,mac) values /;

      $sql .= '( ' . join(',',

                           $computerID,

                           $reportDate,

                           &fixDatabaseValue($device),

                           &fixDatabaseValue($$networkHash{$device}{'address'}),

                           &fixDatabaseValue($$networkHash{$device}{'netmask'}),

                           &fixDatabaseValue($$networkHash{$device}{'ip6address'}),

                           &fixDatabaseValue($$networkHash{$device}{'ip6networkbits'}),

                           &fixDatabaseValue($$networkHash{$device}{'mtu'}),

                           &fixDatabaseValue($$networkHash{$device}{'mac'})

                           ) .

              ')';

      &GenericSQL::doSQL( $dbh, $sql );

      push @warnings,&createLogMessage("Network Device $device was added/modified");

   }

} # sub doIPAddresses

sub processDisks {

   my ($diskHash) = @_;

   #print Data::Dumper->Dump([$diskHash],['$diskHash']);

   #print "Upon entry, we have " . (scalar keys %$diskHash) . " Items in hash\n";

   # first, see if there are any alerts

   foreach my $partition (keys %$diskHash) {

      if ($$diskHash{$partition}{'size'}) {

         my $usedPercent = sprintf('%4.2f', ($$diskHash{$partition}{'used'}/$$diskHash{$partition}{'size'}) * 100);

         push @warnings, &createLogMessage("Partition $partition at $usedPercent%% capacity") if $usedPercent > $DiskUsageAlert;

      }

   }

   # now, remove any that are no longer reported

   my $temp = join ',', (map { &fixDatabaseValue($_) } keys %$diskHash); # get a list of interfaces being passed in

   my $sql = qq/select disk_info_id from disk_info where removed_date is null and device_id = $computerID and disk_device not in ($temp)/;

   #print "\n$sql\n";

#   die;

   my @idsToDelete = &GenericSQL::getArrayOfValues( $dbh, $sql );

#   print '[' . join ('][', @idsToDelete) . "]\n";

   foreach my $id ( @idsToDelete ) {

      next unless $id;

      push @warnings,&createLogMessage("Disk Partition removed");

      $sql = qq/update disk_info set removed_date = $reportDate where removed_date is null and disk_info_id = $id/;

      &GenericSQL::doSQL( $dbh, $sql );

      $sql = qq/update disk_space set removed_date = $reportDate where removed_date is null and disk_info_id = $id/;

      &GenericSQL::doSQL( $dbh, $sql );

   }

   # now, we have a "clean" database

   # do a query to retrieve all disk entries for this device

  $sql = qq/select disk_info.disk_info_id,disk_space_id,disk_device,filesystem,mount_point,capacity 

            from disk_info join disk_space using (disk_info_id) 

            where disk_space.removed_date is null and disk_info.removed_date is null and device_id = $computerID/;

   my $sth = &GenericSQL::startQuery( $dbh, $sql );

   #print "Before we start processing, we have " . (scalar keys %$diskHash) . " Items in hash\n";

   while (my $thisDBRow = &GenericSQL::getNextRow($sth)) {

      my $thisHashRow = $$diskHash{$$thisDBRow{'disk_device'}} ; # just for convenience 

      # Always invalidate the disk space entry. We'll either add a new row, or it is changed too much

      $sql = "update disk_space set removed_date = $reportDate where removed_date is null and disk_info_id = " . $$thisDBRow{'disk_info_id'};

      &GenericSQL::doSQL( $dbh, $sql );

      # we know this exists in both

      #print "\n\n" . $$thisDBRow{'disk_device'} . "\n";

      #print Data::Dumper->Dump([$thisDBRow],['thisRow']);

      #print Data::Dumper->Dump([$thisHashRow],['thisHashRow']);

      #print $$thisHashRow{'fstype'} . "\n";

      #print $$thisHashRow{'size'} . "\n";

      #print $$thisHashRow{'mount'} . "\n";

      # is it a partition, or a directory to watch. This is defined as a directory to watch does not contain a size,

      # mount point or file system type.