See if these will help:
/* SQL Script: */
/* */
/* backup_volume_last_24hours.sql */
/* Date Description */
/* 2002-06-10 PDS Created */
/* Create Report of total MBs per each session */
select entity as "Node Name ", cast(bytes/1024/1024 as decimal(10,3))
as "Total MB ", cast(substr(cast(end_time-start_time as char(17)),3,8) as
char(8)) as "Elapsed ", substr(cast(start_time as char(26)),1,19) as
"Date/Time ", case when cast((end_time-start_time) seconds as
decimal) >0 then cast(bytes/cast((end_time-start_time) seconds as
decimal)/1024/1024 as decimal(6,3)) else cast(0 as decimal(6,3)) end as "
MB/Sec" from summary where start_time>=current_timestamp - 1 day and
activity='BACKUP'
/* Create Report of total MBs and length of backup for each node */
select entity as "Node Name ", cast(sum(bytes/1024/1024) as
decimal(10,3)) as "Total MB", substr(cast(min(start_time) as
char(26)),1,19) as "Date/Time ",
cast(substr(cast(max(end_time)-min(start_time) as char(20)),3,8) as
char(8)) as "Length " from summary where start_time>=current_timestamp -
22 hours and activity='BACKUP' group by entity
/* Create Report of total backed up*/
select sum(cast(bytes/1024/1024/1024 as decimal(6,3))) "Total GB Backup"
from summary where start_time>=current_timestamp - 1 day and
activity='BACKUP'
/* Create Report of Storage Pool Copies */
select entity as "Storage Pool ",
cast(bytes/1024/1024 as decimal(10,3)) as " Total MB ", ' ' as " ",
substr(cast(start_time as char(26)),1,19) as "Date/Time ",
cast(substr(cast(end_time-start_time as char(20)),3,8) as char(8)) as
"Length " from summary where start_time>=current_timestamp - 22 hours and
activity='STGPOOL BACKUP' order by 3, entity
/* Create Summary Report for each Storage Pool */
select entity as "Storage Pool ",
cast(sum(bytes/1024/1024) as decimal(10,3)) as " Total MB ", ' ' as " ",
substr(cast(min(start_time) as char(26)),1,19) as "Start Date/Time ",
substr(cast(max(end_time) as char(26)),1,19) as "End Date/Time "
from summary where start_time>=current_timestamp - 22 hours and
activity='STGPOOL BACKUP' group by entity
/* Create Report of Total Storage Pool GB copied */
select sum(cast(bytes/1024/1024/1024 as decimal(6,3))) "Total STG Pool GB
Backup" from summary where start_time>=current_timestamp - 1 day and
activity='STGPOOL BACKUP'
/* Create a report of File Spaces that have been backed up in the last 7
days but missed */
/* being backed up in the last 22 hours. */
select node_name as "Node Name", filespace_name as "File Space Name",
filespace_type as "File Space Type", substr(cast(backup_start as
char(26)),1,16) as "Last Backup Begin", substr(cast(backup_end as
char(26)),1,16) as "Last Backup End" from filespaces where backup_start
>current_timestamp - 7 days and backup_end < current_timestamp - 22 hours
and node_name not in (select node_name from nodes where domain_name in
('DOMAIN_TO_EXCLUDE') or upper(contact) in ('*SUSPENDED*', '*RETIRED*') or
upper(contact) like '%*NO AUDIT*%') order by 3,4,1,2
/* Create a report of the Nodes that do not have auditing turned on that
have not formed */
/* a session in the last 24 hours to 30 days */
select node_name as "Node Name", substr(cast(lastacc_time as char(26)),1,16)
as "Last Access" from nodes where upper(contact) not in ('*SUSPENDED*',
'*RETIRED*') and upper(contact) not like '%*NO AUDIT*%' and lastacc_time <
current_timestamp - 23 hours and lastacc_time >current_timestamp - 30 days
and domain_name not in ('DOMAIN_TO_EXCLUDE') order by 2 desc
Paul D. Seay, Jr.
Technical Specialist
Naptheon Inc.
757-688-8180
-----Original Message-----
From: Arty Ecock [mailto:ECKCU AT CUNYVM.CUNY DOT EDU]
Sent: Monday, September 16, 2002 9:30 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Backup reporting
Hi,
We're trying to put together some TSM backup reports for our Help Desk
staff. We're using something like
SELECT * FROM EVENTS ...
and things like
SELECT NODE_NAME, LASTACC_TIME FROM NODES
and
SELECT NODE_NAME, FILESPACE_NAME, (CURRENT_TIMESTAMP-BACKUP_END) DAYS
AS "Days_Since_Backup" FROM FILESPACES WHERE
CAST((CURRENT_TIMESTAMP-BACKUP_END) DAYS AS DECIMAL) >= 30 ORDER BY
"Days_Since_Backup"
What we need is a single, consolidated report that lists all our client
nodes and the (real) status of the morning's backup run. "Query Events" (and
the Select * from Events ...) will tell us if the scheduled backup was
"Completed", "Missed", or "Failed". From our experience, "Completed" only
means that the scheduled command actually completed, not that it was truly
successful. By way of exmple, we use a scheduled command script to drive our
Domino TDP backups. The script invokes the DOMDSMC command to actually
perform the backup. DOMDSMC may fail miserably, but as long as the invoking
(scheduled) script completes, the status of the backup shows "Complete".
(We now pass a bad RC from the script if DOMDSMC returns a bad RC).
For our Oracle TDP backups, I believe there is a way to issue a SELECT
against the Oracle (RMAN?) instance to determine the state of the most
recent backup.
Our Help Desk needs to determine if a backup for a given node actually
completed successfully, and if it failed, they need to determine the cause
(all without having exceptional knowledge of TSM).
I'm sure this problem has been addressed by many sites in many ways. Our
ideal solution would be a number of SELECTs, which we could drop in a CGI
script on some Web Server.
Any words of advice?
Arty Ecock
CUNY/CIS - (212) 541-0956
|
