Subversion Repositories computer_asset_manager_v1

#! /usr/bin/perl -w

#

# v0.8.1 20160218 RWR

# removed print statement to STDOUT (just commented it out)

#

# v0.8.2 20160407 RWR

# Added checking for missing sysinfo reports

# if a report is not shown in current day, but has been seen in the 

# past five days, an entry is made on the report

use DBI;

use Cwd 'abs_path';

#use Data::Dumper;

use File::Basename;

use YAML::XS;

my $VERSION = '0.8.1';

my $MY_DIRECTORY = abs_path(dirname(__FILE__) );

my $message; # the message to send

my $query = qq/select

                  sysinfo_report.sysinfo_report_id 'ID', 

                  sysinfo_report.report_date 'Date',

                  client.name 'Client',

                  device.name 'Device',

                  sysinfo_report.notes 'Notes'

               from 

                  sysinfo_report 

                  join device using (device_id) 

                  join site site using (site_id) 

                  join client using (client_id) 

               where 

                  sysinfo_report.added_date > date_sub( now(), interval 1 day)

                  and sysinfo_report.notes is not null/;

my $count = qq/select 

                  count(*) 'Count'

               from sysinfo_report

               where sysinfo_report.added_date > date_sub( now(), interval 1 day)/;

#my $missing = qq/select

#                  device_id ID,

#                  device.name name

#               from (

#                  select 

#                     distinct( device_id ) 

#                  from 

#                     sysinfo_report

#                  where 

#                     datediff( current_date(),added_date) < 5

#                  ) last_five

#                  join device using ( device_id )

#               where 

#                  device.device_id not in (

#                     select 

#                        distinct( device_id ) 

#                     from 

#                        sysinfo_report 

#                     where 

#                        datediff( current_date(),added_date) < 1)/;

my $missing = qq/select

                  last_five.device_id ID,

                  device.name name

               from (

                  select 

                     distinct( device_id ) 

                  from 

                     sysinfo_report

                  where 

                     datediff( current_date(),added_date) < 5

                  ) last_five

                  left join

                  (

                  select 

                     distinct( device_id ) 

                  from 

                     sysinfo_report

                  where 

                     datediff( current_date(),added_date) < 1

                  ) today using (device_id)

                  join device using ( device_id )

               where 

                  today.device_id is null/;

# following are used to find the configuration file

my $confFileName = "sysinfoRead.conf.yaml";

my @searchPaths = ( '/etc/camp', '/opt/camp', '/opt/camp/sysinfo', $MY_DIRECTORY );

sub loadConfig {

   my ( $confFileName, @searchPaths ) = @_;

   my $configuration;

   for ( $i = 0; $i < @searchPaths; $i++ ) {

      $filename = $searchPaths[$i] . '/' . $confFileName;

      if ( -e $filename ) {

         #print "Found $filename\n";

         open CONF, "<$filename" or warn "Could not read $filename: $!\n";

         $configuration = Load( join( '', <CONF> ) );

         close CONF;

         last; # exit out of the loop; we don't try to load it more than once

      } # if

   } # foreach

   return $configuration;

} # sub loadConfig

sub sendReport {

   my ($parameters,$report) = @_;

   my %CLIParams ;

   $CLIParams{'-f'}  = qq/$$parameters{'mailFrom'}/    if $$parameters{'mailFrom'};

   $CLIParams{'-t'}  = qq/$$parameters{'mailTo'}/      if $$parameters{'mailTo'};

   $CLIParams{'-u'}  = qq/$$parameters{'mailSubject'}/ if $$parameters{'mailSubject'};

   $CLIParams{'-s'}  = qq/$$parameters{'mailServer'}/  if $$parameters{'mailServer'};

   $CLIParams{'-xu'} = qq/$$parameters{'smtpUser'}/    if $$parameters{'smtpUser'};

   $CLIParams{'-xp'} = qq/$$parameters{'smtpPass'}/    if $$parameters{'smtpPass'};

   $CLIParams{'-cc'} = qq/$$parameters{'mailCC'}/      if $$parameters{'mailCC'};

   $CLIParams{'-bcc'}= qq/$$parameters{'mailBCC'}/     if $$parameters{'mailBCC'};

   $CLIParams{'-l'}  = qq/$$parameters{'logFile'}/     if $$parameters{'logFile'};

   $CLIParams{'-a'}  = qq/$$parameters{'attachment'}/  if $$parameters{'attachment'};

   $CLIParams{'-o tls='} = qq/$$parameters{'tls'}/     if $$parameters{'tls'};

   $commandLine = qq/$$parameters{'emailScript'}/;

   die "Could not find executable $commandLine in sendEmailScript\n" unless -x $commandLine;

   $commandLine .= ' -q'; # make it act quietly

   foreach my $key ( keys %CLIParams ) {

      # depending on whether the key ends in an = or not, we will or will not use a space

      # between the key and the parameter

      $commandLine .= $key =~ m/=$/ ? " $key'$CLIParams{$key}'" : " $key '$CLIParams{$key}'";

   }

   $commandLine .= ' ' . $$parameters{'otherCLParams'} if $$parameters{'otherCLParams'};

   open SENDMAIL, "|$commandLine" or die "Could not open [$commandLine]: $!\n";

   print SENDMAIL $report;

   close SENDMAIL;

} # sendReport

########################################################################

# Main Program

########################################################################

# Read anything passed on STDIN to prepend to e-mail

while ( my $line = <> ) {

   $message .= $line;

}

my $configuration = &loadConfig( $confFileName, @searchPaths );

die "Could not find configuration file $confFileName in " . join( ', ', @searchPaths) . "\n" unless $configuration;

my $dbh = DBI->connect('DBI:mysql:' . $$configuration{'database'}{'database'},

                       $$configuration{'database'}{'databaseUsername'}, 

                       $$configuration{'database'}{'databasePassword'} )

         || die "Could not connect to database: $DBI::errstr";

my $sth = $dbh->prepare( $count );

$sth->execute();

my $results = $sth->fetchrow_hashref();

$message .= "$results->{Count} reports in last 24 hours\n";

$sth->finish();

$results = $dbh->selectall_hashref( $missing, 'ID' );

if ( %$results ) {

   $message .= '='x40 . "\nWARNING, no sysinfo reports for the following machines\n" . '='x40 . "\n";

   foreach my $device ( keys %$results ) {

      $message .= sprintf( "%6d\t%s\n", $device, $results->{$device}->{name} );

   }

   $message .= '='x40 . "\n\n";

}

$results = $dbh->selectall_hashref( $query, 'ID' );

foreach my $id ( keys %$results ) {

   $message .= $results->{$id}->{Date} . "\t" . $results->{$id}->{Client} . "\t" . $results->{$id}->{Device} . "\n";

   $message .= $results->{$id}->{Notes} . "\n\n";

}

$dbh->disconnect();

#print $message;

&sendReport( $$configuration{'sendReport'}, $message );

1;