Script Coleta Dados TSM

select count(*) as "Number of Scheduled Backups" from associations where schedule_name not in (select schedule_name from client_schedules where current_date > expiration) and schedule_name not in (select schedule_name from client_schedules where perunits like 'ONETIME' and current_date > startdate) and schedule_name not like '@%' and schedule_name not like 'BRARCHIVE%'
select count(*) as TAPE_COUNT, status from libvolumes group by status order by status DESC
select pct_utilized as "Log Percent Util", MAX_PCT_UTILIZED, LOG_POOL_PCT_WAIT from log
select pct_utilized as "DB Percent Util", CACHE_HIT_PCT from db
issue message i " "
issue message i "Show any drives not online"
select library_name, Drive_name, drive_state, online from drives where online<>'YES'
issue message i " "
issue message i "Show any paths not online"
select Source_name, Destination_name, device, online from paths where Online<>'YES'
issue message i " "
issue message i "Show any disk volumes that cannot be accessed"
select volume_name, stgpool_name, cast(access as char(11)) as ACCESS, cast(error_state as char(3)) as ERROR, status from volumes where devclass_name = 'DISK' and (error_state = 'YES' or access like 'READONLY' or access like 'DESTROYED' or status = 'OFFLINE') order by volume_name
issue message i " "
issue message i "Show any DR tapes missing from the DRMEDIA list"
select * from volumes where stgpool_name in (select stgpool_name from stgpools where pooltype='COPY') and volume_name not in (select volume_name from drmedia)
issue message i " "
issue message i "Show any tape mount failures"
q actl begint=-24 msgno=8381
q actl begint=-24 msgno=8355
q actl begint=-24 msgno=8302
issue message i " "
issue message i "Show any client exceptions from yesterday"
query event * * begind=today-1 begint=08:30 endd=today endt=now+1 exception=yes
issue message i " "
issue message i " "
issue message i "Show nodes without connection for more that 2 days"
select node_name as node, cast(lastacc_time as date) as LAST_ACCESS, contact from nodes where cast(cast(lastacc_time as date) as integer)<(cast(current_date as integer)-1) order by LAST_ACCESS desc
issue message i " "
issue message i " "
issue message i "Show TDP for Mail failures from last night"
q actl begint=18:00:00 begind=-1 endd=today endt=now msgno=4993
issue message i " "
issue message i "Show failed admin schedules from yesterday"
query event * t=a begind=today-1 begint=00:00 endd=today endt=now+1 exceptions=yes
issue message i " "
issue message i "Show any tape pools over 70% utilized"
select stgpool_name, pooltype, pct_utilized, maxscratch from stgpools where devclass not like 'DISK' and pct_utilized > 70
issue message i " "
issue message i "Show priviledged admin ID's which have not logged in for more than 150 days"
select admin_name, cast(lastacc_time as date) as LAST_ACCESSED from admins where (cast(cast(lastacc_time as date) as integer) +156) < (cast(current_date as integer)) and (system_priv='Yes' or policy_priv='Yes' or storage_priv='Yes') order by last_accessed
issue message i " "
issue message i "Show any error tapes"
select cast(volume_name as char(10)) as VOLUME, stgpool_name, cast(pct_utilized as char(5)) as PCT_UTIL, cast(access as char(11)) as ACCESS, cast(error_state as char(3)) as ERROR, cast(last_write_date as date) as LAST_WRITTEN from volumes where (error_state <> 'NO' or access = 'READONLY') and stgpool_name in (select stgpool_name from stgpools where pooltype='PRIMARY' and devclass <> 'DISK') order by stgpool_name, volume
issue message i " "
issue message i "Show primary tapes not in library"
select cast(volume_name as char(6)) as VOLUME, stgpool_name, cast(pct_utilized as char(5)) as PCT_UTIL, cast(access as char(11)) as ACCESS, cast(error_state as char(3)) as ERROR, cast(last_write_date as date) as LAST_WRITTEN from volumes where stgpool_name in (select stgpool_name from stgpools where pooltype = 'PRIMARY' and devclass <> 'DISK') and volume_name not in (select Volume_name from libvolumes) order by stgpool_name, volume
issue message i " "
issue message i "Show primary tapes in library but marked UNAVAILABLE"
select cast(volume_name as char(6)) as VOLUME, stgpool_name, cast(pct_utilized as char(5)) as PCT_UTIL, cast(access as char(11)) as ACCESS, cast(error_state as char(3)) as ERROR, cast(last_write_date as date) as LAST_WRITTEN from volumes where stgpool_name in (select stgpool_name from stgpools where pooltype = 'PRIMARY' and devclass <> 'DISK') and volume_name in (select Volume_name from libvolumes) and access='UNAVAILABLE' order by stgpool_name, volume
issue message i " "
issue message i "Show any unused (scratch) tapes in library listed as Private"
select volume_name from libvolumes where volume_name not in (select volume_name from drmedia) and volume_name not in (select volume_name from volumes) and status = 'Private'
issue message i " "
issue message i " "
issue message i "Show Domino full backups from last night"
q actl begint=18:00:00 begind=-1 endd=today endt=now msgno=4991 search="ACD5209"
issue message i " "
issue message i " "
issue message i "Show today's admin schedules (all)"
query event * t=a begind=today-1 begint=08:30 endd=today endt=now+1
issue message i " "
issue message i "Show the recent TSM DB backups"
Select date_time, type, volume_name, location from volhistory where (cast(cast(date_time as date) as integer) > (cast(current_date as integer)-2)) and type like 'BACKUPFULL'
issue message i " "
issue message i " "
issue message i "Show mount activity"
query mount
issue message i " "
issue message i " "
issue message i "Show current processes"
query process
issue message i " "
issue message i " "
issue message i "Show current sessions"
query session
issue message i " "
issue message i "Show all client events from yesterday"
query event * * begind=today-1 begint=08:30 endd=today endt=now+1
select node_name as NO_BACKUP,substr(char(lastacc_time), 1, 16) as LAST_ACCESS, contact from nodes where  cast((current_timestamp-lastacc_time)days as decimal) >=2 and cast((current_timestamp-lastacc_time)days as decimal) <= 30 order by LAST_ACCESS
issue message i " "
issue message i "Show all W Warning message type"
set sqldisplaymode wide
select date_time,message FROM actlog WHERE severity='W' AND date_time>current_timestamp-24 hours
set sqldisplaymode narrow
issue message i " "