Rev 93 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
#! /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;