Subversion Repositories computer_asset_manager_v1

#! /usr/bin/perl -w

# Version 0.9 20160713 RWR

# Added ability to have individual owners receive a summary report

#

# Version 1.0 20191020 RWR

# Major change to query used for report. Instead of start time of backup,

# report will look for date report was added to CAMP. This keeps reports

# of backups which take extra time from showing up AFTER they are complete.

# For example, if a backup starts on a Tuesday and takes 36 hours to complete

# it will be reported as missing on Wed, but will be correctly reported as

# completed on Thu.

use DBI;

use Cwd 'abs_path';

#use Data::Dumper;

use File::Basename;

use YAML::XS;

my $VERSION = '1.0';

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

my $message; # the message to send

# query to find all reports in last 24 hours. Assumes the script

# runs at the same time each day

my $query = qq|select 

                  backups.responsible_party 'mailto',

                  backups_run.backups_run_id 'ID',

                  device.device_id 'Device ID',

                  device.name 'Source',

                  backups_run.start_time 'Start Time',

                  timediff( backups_run.end_time,backups_run.start_time ) 'Run Time',

                  backups_run.transferred_count 'Files',

                  round(backups_run.transferred_size/1024/1024,2) 'Size (M)',

                  backups_run.files_deleted 'Deleted',

                  backups_run.skipped 'Skipped',

                  round( backups_run.data_sent/1024/1024,2) 'Traffic (M)'

               from

                  backups_run

                  join backups using (backups_id)

                  join device using (device_id)

               where

                  backups_run.added_date > date_sub( now(), interval 24 hour)

               order by Source

               |;

# Find number of backups in the past 24 hours.                  

my $count = qq/select count(*) 'Count' 

               from backups_run 

               where

                  backups_run.start_time > date_sub( now(), interval 24 hour)/;

# looks for backups which did NOT happen in last 24 hours, but did

# happen in last 7 days, ie backups that did not make it.

my $missing = qq/select 

                  device.device_id ID,

                  device.name name

               from 

                  ( 

                     select distinct( backups_id ) 

                     from 

                        backups_run 

                     where 

                        (datediff( now(),added_date) < 7 ) 

                  ) last_five

                  left join

                  (

                     select 

                        distinct( backups_id ) 

                     from 

                        backups_run

                     where 

                        backups_run.added_date > date_sub( now(), interval 24 hour)

                  ) today using (backups_id)

                  join 

                     backups using (backups_id)

                  join 

                     device using (device_id)

               where 

                  today.backups_id is null

               /;

# following are used to find the configuration file

my $confFileName = "rsbackupRead.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

sub sendViaSendmail {

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

   my @header;

   push @header, 'To: ' . qq/$$parameters{'mailTo'}/;

   push @header, 'From: ' . qq/$$parameters{'mailFrom'}/;

   push @header, 'Subject: ' . qq/$$parameters{'mailSubject'}/;

   $report = join( "\n", @header ) . "\n\n" . $report . "\n.\n";

   open SENDMAIL, "|/usr/sbin/sendmail $$parameters{mailTo}" or die "could not open sendmail: $!\n";

   print SENDMAIL $report;

   close SENDMAIL;

} 

sub individualReport {

   my $data = shift;

   my @report;

   for my $key ( 'Device ID', 'Run Time', 'Files', 'Size (M)', 'Deleted', 'Skipped', 'Traffic (M)' ) {

      push @report, "$key: " . $$data{$key} unless $key eq 'mailto';

   }

   return join( "\n", @report );

}

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

# 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:database=' . $$configuration{'database'}{'database'} . ';host=' . $$configuration{'database'}{'databaseServer'},

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

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

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

#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 backup 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";

}

# we're going to reuse the mail configuration for the individual reports

# if any, so let's save the mailTo and mailSubject

my $saveMailTo = $$configuration{'sendReport'}->{'mailTo'};

my $saveSubject = $$configuration{'sendReport'}->{'mailSubject'};

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

$message .= 'Device  Device Name                               Files Size (M) Delete   Skip     xfer  Start Time           Run Time' . "\n" . '='x40 . "\n";

# following sorts the results by the name ('source'} field

foreach my $id ( sort { $results->{$a}->{'Source'} cmp $results->{$b}->{'Source'} } keys %$results ) {

   $message .= sprintf( "%7u %-40s %6u %8.2f %6u %6u %8.2f %20s %9s\n",

                  $results->{$id}->{'Device ID'} , 

                  $results->{$id}->{'Source'} , 

                  $results->{$id}->{'Files'} ,

                  $results->{$id}->{'Size (M)'} ,

                  $results->{$id}->{'Deleted'} ,

                  $results->{$id}->{'Skipped'} ,

                  $results->{$id}->{'Traffic (M)'},

                  $results->{$id}->{'Start Time'},

                  $results->{$id}->{'Run Time'}

               );

   if ( $results->{$id}->{'mailto'} ) {

      $$configuration{'sendReport'}->{'mailTo'} = $results->{$id}->{'mailto'};

      $$configuration{'sendReport'}->{'mailSubject'} = 'Backup Report for ' . $results->{$id}->{'Source'};

      if ( $$configuration{'sendReport'}{'emailScript'} ) {

         &sendReport( $$configuration{'sendReport'}, &individualReport( $results->{$id} ) );

      } else {

         &sendViaSendmail( $$configuration{'sendReport'}, &individualReport( $results->{$id} ) );

      }

   }

}

$dbh->disconnect();

# reset mailTo and MailSubject to original values

$$configuration{'sendReport'}->{'mailTo'} = $saveMailTo;

$$configuration{'sendReport'}->{'mailSubject'} = $saveSubject;

#print $message;

if ( $$configuration{'sendReport'}{'emailScript'} ) {

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

} else {

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

}

1;