Try this. One thing might not work is if the backup spans over 24 hours,
then I think u will incorrect value. But I think u can add the similar
line for date.
select entity as node_name, date(start_time) as date, cast(activity as
varchar(10)) as activity, -
time(start_time) as start,time(end_time) as end, -
cast(substr(cast(end_time-start_time as char(20)),3,8) as char(8)) as
"Length", -
cast(bytes/1024/1024 as decimal(6,0)) as -
megabytes,cast(affected as decimal(7,0)) as files, successful from summary
where -
start_time>=current_timestamp - 1 day and (entity like 'HM%' or entity like
-
'PAB%' or entity like 'VMS%' or entity like 'GEOHMKLG%' ) and -
activity='BACKUP' order by successful, node_name
Sung Y. Lee
----- Forwarded by Sung Y Lee/Austin/IBM on 04/18/2005 09:35 AM -----
"ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 04/18/2005
09:01:11 AM:
> Hello everyone!
>
> I have the following command running to see how much data is backed
> up/server each day. I was wondering if it is possible to take the start
&
> end time and figure out the amount of time it took to backup each server?
> It seems like it should be rather simple, but yet I am not sure of the
> syntax. Thank you in advance!
>
> ANS8000I Server command: 'select entity as node_name, date(start_time) as
> date, cast(activity as varchar(10)) as activity, time(start_time) as
start,
> time(end_time) as end, cast(bytes/1024/1024 as decimal(6,0)) as
megabytes,
> cast(affected as decimal(7,0)) as files, successful from summary where
> start_time>=current_timestamp - 1 day and (entity like 'HM%' or entity
like
> 'PAB%' or entity like 'VMS%' or entity like 'GEOHMKLG%' ) and
> activity='BACKUP' order by successful, node_name'
>
> NODE_NAME DATE ACTIVITY START
END
> MEGABYTES FILES SUCCESSFUL
> ------------------ ---------- ---------- --------
--------
> --------- --------- ------------------
> HMCH1016 2005-04-17 BACKUP 19:06:16
19:45:46
> 813 2326 YES
> HMCH1021 2005-04-17 BACKUP 19:11:00
19:23:14
> 856 2277 YES
> HMCH1147 2005-04-17 BACKUP 19:00:01
19:31:47
> 558 2162 YES
> HMCH1150 2005-04-17 BACKUP 19:02:11
19:14:38
> 750 0 YES
> HMCH1160 2005-04-17 BACKUP 19:12:14
19:20:57
> 525 1970 YES
> HMCH1161 2005-04-17 BACKUP 19:01:30
19:11:22
> 596 0 YES
> HMCH1162 2005-04-17 BACKUP 19:09:19
19:21:42
> 546 2064 YES
> HMCH1163 2005-04-17 BACKUP 19:02:24
19:22:12
> 625 1934 YES
> HMCH1164 2005-04-17 BACKUP 19:08:26
19:18:17
> 518 1944 YES
> HMCH1165 2005-04-17 BACKUP 19:02:31
19:16:55
> 525 1948 YES
> HMCH1169 2005-04-17 BACKUP 19:06:35
19:16:42
> 542 2096 YES
> HMCH1186 2005-04-17 BACKUP 19:14:04
19:21:10
> 556 2101 YES
> HMCH1187 2005-04-17 BACKUP 19:10:09
19:15:46
> 549 2130 YES
> HMCH1188 2005-04-17 BACKUP 19:12:20
19:25:01
> 688 2260 YES
> HMCH1189 2005-04-17 BACKUP 19:09:21
19:15:55
> 613 2209 YES
> HMCH1194 2005-04-17 BACKUP 19:02:48
19:19:11
> 1007 2779 YES
> HMPG1109 2005-04-17 BACKUP 19:06:26
19:22:05
> 3474 3153 YES
> HMPG1124 2005-04-17 BACKUP 19:11:48
19:23:05
> 640 2319 YES
> HMPG1143 2005-04-17 BACKUP 19:00:09
20:45:30
> 312 2129 YES
> HMPGCWSI 2005-04-17 BACKUP 19:08:53
19:25:23
> 3436 2475 YES
>
> ANS8002I Highest return code was 0.
>
> ********************************
> Joni Moyer
> Highmark
> Storage Systems
> Work:(717)302-6603
> Fax:(717)302-5974
> joni.moyer AT highmark DOT com
> ********************************
|