Subversion Repositories computer_asset_manager_v1

Rev

Rev 25 | Rev 32 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 25 Rev 27
Line 9... Line 9...
9 
 
 9 
 
10 
 
 10 
 
11 
my $VERSION = '0.8';
 11 
my $VERSION = '0.8';
12 
my $MY_DIRECTORY = abs_path(dirname(__FILE__) );
 12 
my $MY_DIRECTORY = abs_path(dirname(__FILE__) );
13 
my $message; # the message to send
 13 
my $message; # the message to send
- 
 
 14 
# query to find all reports in last 24 hours. Assumes the script
- 
 
 15 
# runs at the same time each day
14 
my $query = qq|select
16 
my $query = qq|select
15 
                  backups_run.backups_run_id 'ID',
 17 
                  backups_run.backups_run_id 'ID',
16 
                  device.device_id 'Device ID',
 18 
                  device.device_id 'Device ID',
17 
                  device.name 'Source',
 19 
                  device.name 'Source',
18 
                  timediff( backups_run.end_time,backups_run.start_time ) 'Run Time',
 20 
                  timediff( backups_run.end_time,backups_run.start_time ) 'Run Time',
Line 24... Line 26...
24 
               from
 26 
               from
25 
                  backups_run
 27 
                  backups_run
26 
                  join backups using (backups_id)
 28 
                  join backups using (backups_id)
27 
                  join device using (device_id)
 29 
                  join device using (device_id)
28 
               where
 30 
               where
29 
                  report_date > date_sub( now(), interval 1 day)
 31 
                  backups_run.start_time > date_sub( now(), interval 24 hour)
- 
 
 32 
               order by Source
30 
               |;
 33 
               |;
31 
 
 34 
 
32
35
33
36 
# Find number of backups in the past 24 hours.
34 
my $count = qq/select count(*) 'Count'
37 
my $count = qq/select count(*) 'Count'
35 
               from backups_run
38 
               from backups_run
- 
 
 39 
               where
36 
               where added_date > date_sub( now(), interval 1 day)/;
 40 
                  backups_run.start_time > date_sub( now(), interval 24 hour)/;
37 
 
 41 
 
- 
 
 42 
# looks for backups which did NOT happen in last 24 hours, but did
- 
 
 43 
# happen in last 7 days, ie backups that did not make it.
38 
my $missing = qq/select
44 
my $missing = qq/select
39 
                  device.device_id ID,
 45 
                  device.device_id ID,
40 
                  device.name name
 46 
                  device.name name
41 
               from
 47 
               from
42 
                  (
48 
                  (
43 
                     select distinct( backups_id )
49 
                     select distinct( backups_id )
44 
                     from
50 
                     from
45 
                        backups_run
51 
                        backups_run
46 
                     where
52 
                     where
47 
                        (datediff( current_date(),added_date) < 5 )
53 
                        (datediff( now(),added_date) < 7 )
48 
                  ) last_five
 54 
                  ) last_five
49 
                  left join
 55 
                  left join
50 
                  (
 56 
                  (
51 
                     select
57 
                     select
52 
                        distinct( backups_id )
58 
                        distinct( backups_id )
53 
                     from
59 
                     from
54 
                        backups_run
 60 
                        backups_run
55 
                     where
61 
                     where
56 
                        datediff( current_date(),added_date) < 1
 62 
                        backups_run.start_time > date_sub( now(), interval 24 hour)
57 
                  ) today using (backups_id)
 63 
                  ) today using (backups_id)
58 
                  join
64 
                  join
59 
                     backups using (backups_id)
 65 
                     backups using (backups_id)
60 
                  join
66 
                  join
61 
                     device using (device_id)
 67 
                     device using (device_id)