WebSVN – computer_asset_manager_v1 – Diff – /modules/backup/cli_tools/sendReport.pl



#! /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',

               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.                  

# 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 

                     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 );

# 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'} ) {

Rev 79	Rev 93
Line 1...	Line 1...
1	`#! /usr/bin/perl -w`	1	`#! /usr/bin/perl -w`
2		2
3	`# Version 0.9 20160713 RWR`	3	`# Version 0.9 20160713 RWR`
4	`# Added ability to have individual owners receive a summary report`	4	`# Added ability to have individual owners receive a summary report`
-		5	`#`
-		6	`# Version 1.0 20191020 RWR`
-		7	`# Major change to query used for report. Instead of start time of backup,`
-		8	`# report will look for date report was added to CAMP. This keeps reports`
-		9	`# of backups which take extra time from showing up AFTER they are complete.`
-		10	`# For example, if a backup starts on a Tuesday and takes 36 hours to complete`
-		11	`# it will be reported as missing on Wed, but will be correctly reported as`
-		12	`# completed on Thu.`
5		13
6	`use DBI;`	14	`use DBI;`
7	`use Cwd 'abs_path';`	15	`use Cwd 'abs_path';`
8	`#use Data::Dumper;`	16	`#use Data::Dumper;`
9	`use File::Basename;`	17	`use File::Basename;`
10	`use YAML::XS;`	18	`use YAML::XS;`
11		19
12		20
13		21
14	`my $VERSION = '0.9';`	22	`my $VERSION = '1.0';`
15	`my $MY_DIRECTORY = abs_path(dirname(__FILE__) );`	23	`my $MY_DIRECTORY = abs_path(dirname(__FILE__) );`
16	`my $message; # the message to send`	24	`my $message; # the message to send`
17	`# query to find all reports in last 24 hours. Assumes the script`	25	`# query to find all reports in last 24 hours. Assumes the script`
18	`# runs at the same time each day`	26	`# runs at the same time each day`
19	`my $query = qq\|select`	27	`my $query = qq\|select`
20	`backups.responsible_party 'mailto',`	28	`backups.responsible_party 'mailto',`
21	`backups_run.backups_run_id 'ID',`	29	`backups_run.backups_run_id 'ID',`
22	`device.device_id 'Device ID',`	30	`device.device_id 'Device ID',`
23	`device.name 'Source',`	31	`device.name 'Source',`
-		32	`backups_run.start_time 'Start Time',`
24	`timediff( backups_run.end_time,backups_run.start_time ) 'Run Time',`	33	`timediff( backups_run.end_time,backups_run.start_time ) 'Run Time',`
25	`backups_run.transferred_count 'Files',`	34	`backups_run.transferred_count 'Files',`
26	`round(backups_run.transferred_size/1024/1024,2) 'Size (M)',`	35	`round(backups_run.transferred_size/1024/1024,2) 'Size (M)',`
27	`backups_run.files_deleted 'Deleted',`	36	`backups_run.files_deleted 'Deleted',`
28	`backups_run.skipped 'Skipped',`	37	`backups_run.skipped 'Skipped',`
Line 30...	Line 39...
30	`from`	39	`from`
31	`backups_run`	40	`backups_run`
32	`join backups using (backups_id)`	41	`join backups using (backups_id)`
33	`join device using (device_id)`	42	`join device using (device_id)`
34	`where`	43	`where`
35	`backups_run.start_time > date_sub( now(), interval 24 hour)`	44	`backups_run.added_date > date_sub( now(), interval 24 hour)`
36	`order by Source`	45	`order by Source`
37	`\|;`	46	`\|;`
38		47
39		48
40	`# Find number of backups in the past 24 hours.`	49	`# Find number of backups in the past 24 hours.`
Line 46...	Line 55...
46	`# looks for backups which did NOT happen in last 24 hours, but did`	55	`# looks for backups which did NOT happen in last 24 hours, but did`
47	`# happen in last 7 days, ie backups that did not make it.`	56	`# happen in last 7 days, ie backups that did not make it.`
48	`my $missing = qq/select`	57	`my $missing = qq/select`
49	`device.device_id ID,`	58	`device.device_id ID,`
50	`device.name name`	59	`device.name name`
51	`from`	60	`from`
52	`(`	61	`(`
53	`select distinct( backups_id )`	62	`select distinct( backups_id )`
54	`from`	63	`from`
55	`backups_run`	64	`backups_run`
56	`where`	65	`where`
Line 61...	Line 70...
61	`select`	70	`select`
62	`distinct( backups_id )`	71	`distinct( backups_id )`
63	`from`	72	`from`
64	`backups_run`	73	`backups_run`
65	`where`	74	`where`
66	`backups_run.start_time > date_sub( now(), interval 24 hour)`	75	`backups_run.added_date > date_sub( now(), interval 24 hour)`
67	`) today using (backups_id)`	76	`) today using (backups_id)`
68	`join`	77	`join`
69	`backups using (backups_id)`	78	`backups using (backups_id)`
70	`join`	79	`join`
71	`device using (device_id)`	80	`device using (device_id)`
72	`where`	81	`where`
73	`today.backups_id is null`	82	`today.backups_id is null`
74	`/;`	83	`/;`
75		84
76		85
77	`# following are used to find the configuration file`	86	`# following are used to find the configuration file`
78	`my $confFileName = "rsbackupRead.conf.yaml";`	87	`my $confFileName = "rsbackupRead.conf.yaml";`
79	`my @searchPaths = ( '/etc/camp', '/opt/camp', '/opt/camp/sysinfo', $MY_DIRECTORY );`	88	`my @searchPaths = ( '/etc/camp', '/opt/camp', '/opt/camp/sysinfo', $MY_DIRECTORY );`
Line 187...	Line 196...
187	`# if any, so let's save the mailTo and mailSubject`	196	`# if any, so let's save the mailTo and mailSubject`
188	`my $saveMailTo = $$configuration{'sendReport'}->{'mailTo'};`	197	`my $saveMailTo = $$configuration{'sendReport'}->{'mailTo'};`
189	`my $saveSubject = $$configuration{'sendReport'}->{'mailSubject'};`	198	`my $saveSubject = $$configuration{'sendReport'}->{'mailSubject'};`
190		199
191	`$results = $dbh->selectall_hashref( $query, 'ID' );`	200	`$results = $dbh->selectall_hashref( $query, 'ID' );`
192	`$message .= 'Device Device Name Run Time Files Size (M) Delete Skip xfer' . "\n" . '='x40 . "\n";`	201	`$message .= 'Device Device Name Files Size (M) Delete Skip xfer Start Time Run Time' . "\n" . '='x40 . "\n";`
193	`# following sorts the results by the name ('source'} field`	202	`# following sorts the results by the name ('source'} field`
194	`foreach my $id ( sort { $results->{$a}->{'Source'} cmp $results->{$b}->{'Source'} } keys %$results ) {`	203	`foreach my $id ( sort { $results->{$a}->{'Source'} cmp $results->{$b}->{'Source'} } keys %$results ) {`
195	`$message .= sprintf( "%7u %-40s %9s %6u %8.2f %6u %6u %8.2f\n",`	204	`$message .= sprintf( "%7u %-40s %6u %8.2f %6u %6u %8.2f %20s %9s\n",`
196	`$results->{$id}->{'Device ID'} ,`	205	`$results->{$id}->{'Device ID'} ,`
197	`$results->{$id}->{'Source'} ,`	206	`$results->{$id}->{'Source'} ,`
198	`$results->{$id}->{'Run Time'} ,`	-
199	`$results->{$id}->{'Files'} ,`	207	`$results->{$id}->{'Files'} ,`
200	`$results->{$id}->{'Size (M)'} ,`	208	`$results->{$id}->{'Size (M)'} ,`
201	`$results->{$id}->{'Deleted'} ,`	209	`$results->{$id}->{'Deleted'} ,`
202	`$results->{$id}->{'Skipped'} ,`	210	`$results->{$id}->{'Skipped'} ,`
203	`$results->{$id}->{'Traffic (M)'}`	211	`$results->{$id}->{'Traffic (M)'},`
-		212	`$results->{$id}->{'Start Time'},`
-		213	`$results->{$id}->{'Run Time'}`
204	`);`	214	`);`
205	`if ( $results->{$id}->{'mailto'} ) {`	215	`if ( $results->{$id}->{'mailto'} ) {`
206	`$$configuration{'sendReport'}->{'mailTo'} = $results->{$id}->{'mailto'};`	216	`$$configuration{'sendReport'}->{'mailTo'} = $results->{$id}->{'mailto'};`
207	`$$configuration{'sendReport'}->{'mailSubject'} = 'Backup Report for ' . $results->{$id}->{'Source'};`	217	`$$configuration{'sendReport'}->{'mailSubject'} = 'Backup Report for ' . $results->{$id}->{'Source'};`
208	`if ( $$configuration{'sendReport'}{'emailScript'} ) {`	218	`if ( $$configuration{'sendReport'}{'emailScript'} ) {`

Subversion Repositories computer_asset_manager_v1

(root)/modules/backup/cli_tools/sendReport.pl – Rev 79 → 93