Rev 1 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
#! /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.
my $diskPartition = (exists ($$thisHashRow{'fstype'}) && exists ($$thisHashRow{'size'}) && exists ($$thisHashRow{'mount'}) );
# now, determine if we need to update the disk_info for some reason
# this condition is based upon two types of entries
# Type #1 (top) is a standard partition, so we see if fstype, mount and capacity are the same
# type #2 (after ||) us a "directory to watch" (with no fstype, size or mount)
if ( $diskPartition ) { # it is a partition, so check if something has changed in the entry
#print "\n\nDevice: [" . $$thisDBRow{'disk_device'} . "] is a partition\n";
#print "thisHashRow fstype [" . $$thisHashRow{'fstype'} . "]\n";
#print "thisHashRow size [" . $$thisHashRow{'size'} . "]\n";
#print "thisHashRow mount [" . $$thisHashRow{'mount'} . "]\n";
#print "thisRow filesystem [" .
unless ( &checkEquals($$thisHashRow{'fstype'}, $$thisDBRow{'filesystem'}) and
&checkEquals($$thisHashRow{'mount'}, $$thisDBRow{'mount_point'}) and
&checkEquals($$thisHashRow{'size'}, $$thisDBRow{'capacity'}) ) {
# yes, a change. If we just remove this entry, the add loop (below) will set it as a new device
$sql = "update disk_info set removed_date = $reportDate where disk_info_id = " . $$thisDBRow{'disk_info_id'};
&GenericSQL::doSQL( $dbh, $sql );
#print "$sql\n";
next;
}
}
$usedSpace = $$diskHash{$$thisDBRow{'disk_device'}}{'used'};
#print "\tupdating entry, looking at disk has => $$thisDBRow{'disk_device'} with space $usedSpace\n";
$sql = "insert into disk_space (disk_info_id,space_used,added_date) values ";
$sql .= '(' . join (',', ($$thisDBRow{'disk_info_id'}, &fixDatabaseValue($usedSpace), $reportDate)) . ')';
&GenericSQL::doSQL( $dbh, $sql );
# and delete the hash entry so we don't process it as a change
delete $$diskHash{$$thisDBRow{'disk_device'}};
}
# at this point, all we have left are additions and changes
foreach my $partition ( keys %$diskHash ) {
$sql = 'insert into disk_info(device_id,added_date,disk_device,filesystem,mount_point,capacity) values ';
$sql .= '(' . join( ',', ( $computerID,
$reportDate,
&fixDatabaseValue($partition),
&fixDatabaseValue($$diskHash{$partition}{'fstype'}),
&fixDatabaseValue($$diskHash{$partition}{'mount'}),
&fixDatabaseValue($$diskHash{$partition}{'size'})
)
) . ')';
&GenericSQL::doSQL($dbh, $sql);
$sql = "select disk_info_id from disk_info where removed_date is null and device_id = $computerID and disk_device = " . &fixDatabaseValue($partition);
$temp = &GenericSQL::getOneValue( $dbh, $sql );
$sql = 'insert into disk_space(disk_info_id,added_date,space_used) values (';
$sql .= join( ',', ($temp, $reportDate, fixDatabaseValue($$diskHash{$partition}{'used'}))) . ')';
&GenericSQL::doSQL( $dbh, $sql );
}
}
# routine to ensure the hardware returned as PCI hardware is in the attributes area
sub processPCI {
my ($pciHash) = @_;
# print "Entering processPCI\n";
#print Data::Dumper->Dump([$pciHash],[$key]);
return unless $pciHash && keys %$pciHash;
#my %attributeMappings = ('class' => 'Class', # v2 database has these items, but we want to have a pretty name
# 'device' => 'Device Name',
# 'sdevice' => 'Subsystem Device',
# 'svendor' => 'Subsystem Vendor',
# 'vendor' => 'Vendor',
# 'name' => 'Name',
# 'slot' => 'Slot'
# );
# The two keys we'll check for uniquness are device.name and device_type with a key value of 'slot'. If these
# are the same, we assume this is the same record
# print Data::Dumper->Dump([$pciHash]);
my $key;
# normalize the data
foreach $key ( keys %$pciHash ) {
unless ( defined ($$pciHash{$key}{'slot'}) ) { # doesn't have a slot field
my $slotField = '';
my $test = $$pciHash{$key};
foreach $subkey ( keys %$test) { # scan through all keys and see if there is something with a "slot looking" value in it
$slotField = $key if $$test{$subkey} =~ m/^[0-9a-f:.]+$/;
}
if ( $slotField ) {
$$pciHash{$key}{$subkey}{'slot'} = $$pciHash{$key}{$subkey}{$slotField};
} else {
$$pciHash{$key}{'slot'} = 'Unknown';
}
}
# Each entry must have a name. Use 'device' if it doesn't exist
$$pciHash{$key}{'name'} = $$pciHash{$key}{'device'} unless defined($$pciHash{$key}{'name'}) && $$pciHash{$key}{'name'};
$$pciHash{$key}{'name'} = $$pciHash{$key}{'sdevice'} unless defined($$pciHash{$key}{'name'}) && $$pciHash{$key}{'name'};
$$pciHash{$key}{'name'} =~ s/^ +//;
unless ( $$pciHash{$key}{'name'} ) {
push @warnings, &createLogMessage("No name given for one or more PCI devices at normalize, Computer ID: [$computerID], Report Date: [$reportDate]");
return;
}
# Following is what will actually be put in the device table, ie device.name
$$pciHash{$key}{'keyFieldValue'} = $$pciHash{$key}{'slot'} . ' - ' . $$pciHash{$key}{'name'};
}
# at this point, we should have a slot and a name field in all pci devices
# print Data::Dumper->Dump([$pciHash]);
# die;
# Get list of all PCI cards in database for this computer
my @toDelete;
$sql = qq/select device_id,
device.name name
from device join device_type using (device_type_id)
where device_type.name = 'PCI Card'
and device.removed_date is null
and device.part_of = $computerID/;
my $sth = &GenericSQL::startQuery( $dbh, $sql );
while (my $thisRow = &GenericSQL::getNextRow($sth)) { # for each row in the database
my $deleteMe = $$thisRow{'device_id'}; # assume we will delete it
foreach $key (keys %$pciHash ) { # look for it in the hash
#print "Checking [$$pciHash{$key}{'name'}] eq [$$thisRow{'name'}]\n";
#print " [$$pciHash{$key}{'slot'}] eq [$$thisRow{'slot'}]\n\n";
if (
($$pciHash{$key}{'keyFieldValue'} eq $$thisRow{'name'})
&&
! defined ($$pciHash{$key}{'device_id'}) # this keeps us from ignoring a card when two are installed
) { # it is in the database and in pciHash
$deleteMe = ''; # so let's keep it
$$pciHash{$key}{'device_id'} = $$thisRow{'device_id'}; # and mark it as there
#print "\tfound equality at $$thisRow{'device_id'}\n";
last; # and exit the foreach loop
}
}
push @toDelete, $deleteMe if $deleteMe; # if we did not find it, mark for deletion
}
# remove stale items from the database
if (@toDelete) {
my $toDelete = join ",", @toDelete; # this is a list of device_id's
push @warnings, &createLogMessage( scalar(@toDelete) . " PCI Devices removed");
# remove from the device_attrib table
$sql = qq/update device_attrib set removed_date = $reportDate where device_id in ($toDelete)/;
# print "$sql\n";
&GenericSQL::doSQL($dbh, $sql);
# and from the device table itself
$sql = qq/update device set removed_date = $reportDate where device_id in ($toDelete)/;
&GenericSQL::doSQL($dbh, $sql);
}
undef @toDelete; # don't need this anymore
my $added = 0;
my $updated = 0;
# now, we have either inserts or updates
foreach $key (keys %$pciHash) {
unless ( $$pciHash{$key}{'device_id'} ) { # we did not find it in the database, so it is an insert
my $thisKey = &fixDatabaseValue($$pciHash{$key}{'keyFieldValue'});
$sql = qq/insert into device (site_id,device_type_id,name,part_of,added_date)
select site_id,device_type.device_type_id, $thisKey, device_id, $reportDate
from device,device_type
where device.device_id = $computerID
and device_type.name = 'PCI Card'/;
&GenericSQL::doSQL($dbh, $sql);
# get the inserted key
$$pciHash{$key}{'device_id'} = &GenericSQL::getOneValue($dbh, qq/select max(device_id) from device where part_of = $computerID and name = $thisKey and added_date = $reportDate/);
$added++;
} # unless
my $thisEntry = $$pciHash{$key};
$value = 0;
foreach my $subkey ( keys %$thisEntry ) {
# $test = $attributeMappings{$subkey} ? $attributeMappings{$subkey} : $subkey;
# print "checking $subkey [$$thisEntry{$subkey}]\n";
$value += &checkAndUpdateAttribute($$pciHash{$key}{'device_id'},
$attributeMappings{$subkey} ? $attributeMappings{$subkey} : $subkey,
$$thisEntry{$subkey} )
unless ($subkey eq 'device_id') or ($subkey eq 'keyFieldValue');
}
$updated++ if $value;
}
push @warnings, &createLogMessage("$added PCI Devices added") if $added;
push @warnings, &createLogMessage("$updated PCI Devices modified") if $updated;
}
# figure out the software_id and software_version_id of a package. Adds the package/version if
# it doesn't exist in the database
sub getSoftwareID {
my ( $packageName,$versionInfo,$description ) = @_;
#print "In getSoftwareID, paramters are [$packageName][$versionInfo][$description]\n";
#return;
# escape and quote the values for SQL
$packageName = &GenericSQL::fixStringValue($dbh, $packageName );
$versionInfo = &GenericSQL::fixStringValue($dbh, $versionInfo );
# does the package exist?
my $sql = qq/select software_id from software where package_name = $packageName and removed_date is null/;
my $result = &GenericSQL::getOneValue( $dbh, $sql );
unless ( $result ) { # NO, package doesn't exist, so add it to the database
$description = &GenericSQL::fixStringValue($dbh, $description );
$sql = qq/insert into software (package_name,description, added_date) values ($packageName,$description, $reportDate)/;
&GenericSQL::doSQL( $dbh, $sql );
$sql = qq/select software_id from software where package_name = $packageName and removed_date is null/;
$result = &GenericSQL::getOneValue( $dbh, $sql );
}
# does this version number exist?
$sql = qq/select software_version_id from software_version where version = $versionInfo and removed_date is null/;
my $version = &GenericSQL::getOneValue( $dbh, $sql );
unless ( $version ) { # nope, so add it
$sql = qq/insert into software_version ( version,added_date ) values ($versionInfo,$reportDate)/;
&GenericSQL::doSQL( $dbh, $sql );
$sql = qq/select software_version_id from software_version where version = $versionInfo and removed_date is null/;
$version = &GenericSQL::getOneValue( $dbh, $sql );
}
return ($result,$version);
}
# process each package. We will only add entries if a package has changed, either version number
# added, or deleted. Deleted packages are not handled well right now.
sub processPackages {
my ($softwareHash) = @_;
my %softwareIDs;
my $count;
# since we go by software and version id's, let's just precalculate them
foreach my $package (keys %$softwareHash) {
# this will also insert the package and/or version in the software or software_version tables
($$softwareHash{$package}{'softwareid'},$$softwareHash{$package}{'versionid'}) =
&getSoftwareID( $package, $$softwareHash{$package}{'version'}, $$softwareHash{$package}{'description'}, $reportDate );
# this is just a shortcut for when we need to query
#$$softwareHash{$package}{'complexkey'} = $$softwareHash{$package}{'softwareid'} . '-' . $$softwareHash{$package}{'versionid'};
#push @installedPackages,$$softwareHash{$package}{'softwareid'};
$softwareIDs{$$softwareHash{$package}{'softwareid'}} = $$softwareHash{$package}{'versionid'};
}
# remove any software for this machine that no longer exists
my $temp = join( ',', grep { /^\d+$/ } keys %softwareIDs); # make sure we only have numerics
my $sql = "update installed_packages set removed_date = $reportDate where device_id = $computerID and removed_date is null and software_id not in ($temp)";
&GenericSQL::doSQL( $dbh, $sql);
# ok, at this point, all software in the database exists in the computer
# now, lets see if there are any modified versions or something
$sql = qq/select installed_packages_id,software_id,software_version_id from installed_packages where device_id = $computerID and removed_date is null/;
my $sth = &GenericSQL::startQuery( $dbh, $sql );
#print "Before we start processing, we have " . (scalar keys %$diskHash) . " Items in hash\n";
while (my $thisRow = &GenericSQL::getNextRow($sth)) {
# if the version is the same, just do the next one
if ( $softwareIDs{$$thisRow{'software_id'}} == $$thisRow{'software_version_id'}) {
delete $softwareIDs{$$thisRow{'software_id'}};
} else { # we have a change. We simply remove the entry and let the "add new packages" section take care of it
$sql = qq/update installed_packages set removed_date = $reportDate where installed_packages_id = $$thisRow{'installed_packages_id'}/;
&GenericSQL::doSQL( $dbh, $sql);
}
}
# at this point, the only items left in $softwareIDs are the packages that have changed or been added
$count = 0;
foreach my $softwareID ( keys %softwareIDs ) {
$count++;
$sql = qq/insert into installed_packages( device_id,software_id,software_version_id,added_date ) values
($computerID,$softwareID,$softwareIDs{$softwareID},$reportDate)/;
&GenericSQL::doSQL( $dbh, $sql);
}
push @warnings, &createLogMessage("$count Software Packages changed or added") if $count;
}
###############################################################################
# BEGIN MAIN ROUTINE
###############################################################################
BEGIN{
# load the configuration file
eval ( &loadConfigurationFile );
push @INC, $LIBRARIES;
}
use strict;
no strict 'vars';
use Data::Dumper;
use GenericSQL; # generic, home grown MySQL access routines
#use GenericTemplates;
use Logging; # generic, home grown logging routines
use Date::Format; # allows us to format our dates nicely
use Date::Parse; # VERY KEWL, parses out a huge number of date formats
$dbh = DBI->connect( $DSN, $DB_USER , $DB_PASS ) or die $DBI::errstr; # try to connect to db first
# read the input, parse it into useable information
my $data = &readAndParseInput;
#print Data::Dumper->Dump([$data]);
#die;
$reportDate = &dateToMySQL($$data{'report'}{'date'});
$clientName = $$data{'report'}{'client'};
$FATAL = 'No client name' unless $clientName;
$computerName = $$data{'system'}{'hostname'} unless $FATAL;
$FATAL = 'No computer name' unless $computerName;
# print STDERR "[$computerName]\n";
# try to figure out who the client is, creating if necessary
$clientID = &getClientID( ) unless $FATAL;
# try to figure out the computer ID, creating an entry if necessary
$computerID = &getComputerID( ) unless $FATAL;
# Ok, we have enough info, now let's make sure we aren't re-runing a report and record the current one.
my $reportID = &recordReport( ) unless $FATAL;
# we will simply verify memory, cpu, etc...
&updateComputerMakeup($$data{'system'}) unless $FATAL;
# check if the operating system has changed
&updateOS( $$data{'operatingsystem'} ) unless $FATAL;
# see if the machine has been rebooted and, if so, record it
&updateBootTime ($$data{'system'}) unless $FATAL;
# see what IP's this machine has
&doIPAddresses($$data{'network'}) unless $FATAL;
# Look at the disk usage, and report if they are above limits
&processDisks($$data{'diskinfo'}) unless $FATAL;
# and also if any hardware has changed
&processPCI($$data{'pci'}) unless $FATAL;
# see if any software packages have changed
&processPackages($$data{'software'}) unless $FATAL;
if ($FATAL) { # we had a fatal error, so just return it
print "ERROR: $FATAL\n";
exit 0;
}
# ok, work is done. If there are any values in $warnings, they should be either printed or e-mailed
# to whoever the sysadmin is.
if (@warnings) {
my $warnings = join ("\n", @warnings);
if ($iMailResults) {
&sendmail( $mailFrom, $mailTo, 'Process Sysinfo Warnings', $warnings, $mailCC, $mailBCC, $mailServer, $mailServerPort );
} else {
print "$warnings\n";
}
}
exit 1;