07-24-2015 01:57 AM
Can someone tell me how to get a report out of OpsCenter 7.6.0.4 that will show me how many tapes are frozen per day over a 3 month period?
Solved! Go to Solution.
07-27-2015 12:37 PM
This should come pretty close to what you're asking for -
select t1.snapTime, t1.mediaStatusName, t1.mediaTypeName, t1.LibraryName, count(t1.mediaId) as CountOfMedia
from (
select UTCBigINTToNomTime(x.snapshotTime) as snapTime,
case
when lower(x.volumePoolName) = 'scratch' then 'Scratch'
when lower((select name from lookup_mediaStatus where id = x.status)) like 'active%'
then 'Active'
when lower((select name from lookup_mediaStatus where id = x.status)) like 'full%'
then 'Full'
when lower((select name from lookup_mediaStatus where id = x.status)) like '%frozen%'
then 'Frozen'
when lower((select name from lookup_mediaStatus where id = x.status)) like '%suspend%'
then 'Frozen'
else 'Unknown: ' || (select name from lookup_mediaStatus where id = x.status)
end as mediaStatusName,
(select identifier from lookup_mediaType where y.type = id) as MediaTypeName,
z.libraryAlias as LibraryName,
x.id as MediaID
from domain_MediaHistory x left join domain_Media y on x.id = y.id, domain_TapeLibrary z
where x.librarySlotNumber != -1
and z.id = x.libraryId
) t1
group by t1.snapTime, t1.mediaStatusName, t1.mediaTypeName, t1.LibraryName
order by t1.snapTime desc
To explain a bit, every day at (default) midnight OpsCenter collects a snapshot of all your tapes and their current status and attributes. What this query is doing is summing up the tapes in different statuses for each day so you get data like this:
snapTime | mediaStatusName | mediaTypeName | LibraryName | CountOfMedia |
07/27/15 | Full | hcart | A | 6001 |
07/27/15 | Active | hcart | A | 591 |
07/27/15 | Frozen | hcart | A | 120 |
07/27/15 | Scratch | hcart3 | B | 960 |
07/26/15 | Full | hcart | A | 5958 |
07/26/15 | Active | hcart | A | 596 |
07/26/15 | Frozen | hcart | A | 120 |
07/26/15 | Scratch | hcart | A | 38 |
07/26/15 | Scratch | hcart3 | B | 960 |
Then you can take that data to Excel and graph it, I'd recommend the Stacked Area chart. This will show you exactly when your frozen media is getting high, and when you unfreeze some it will show that too.
This query is not for 3months, its for however long you have the data for. You can check/change how long your OpsCenter keeps media data by looking at Settings > Configuration > Data Purge tab > Media History field. You may need to tweak this to fit your needs, but hopefully this helps.
07-24-2015 04:31 PM
I've not seen a report that shows how many media were frozen per day. I think that instead you could configure an alert from OpsCenter, and then have an email filter rule to siphon the alerts off to a separate folder in your email client - and then periodically review that folder for counts of emails per day. This post covers alerts from frozen media:
https://www-secure.symantec.com/connect/forums/email-alert-if-media-frozen
.
Another way might be to run a script to collect the count of frozen media per day, and schedule the script to run once per day, and have the script write one line to a csv file each day. The script could keep a record of previous day total count of frozen media, and then it could quite easily write one line per day with the total count, and the increase since yesterday.
07-27-2015 12:37 PM
This should come pretty close to what you're asking for -
select t1.snapTime, t1.mediaStatusName, t1.mediaTypeName, t1.LibraryName, count(t1.mediaId) as CountOfMedia
from (
select UTCBigINTToNomTime(x.snapshotTime) as snapTime,
case
when lower(x.volumePoolName) = 'scratch' then 'Scratch'
when lower((select name from lookup_mediaStatus where id = x.status)) like 'active%'
then 'Active'
when lower((select name from lookup_mediaStatus where id = x.status)) like 'full%'
then 'Full'
when lower((select name from lookup_mediaStatus where id = x.status)) like '%frozen%'
then 'Frozen'
when lower((select name from lookup_mediaStatus where id = x.status)) like '%suspend%'
then 'Frozen'
else 'Unknown: ' || (select name from lookup_mediaStatus where id = x.status)
end as mediaStatusName,
(select identifier from lookup_mediaType where y.type = id) as MediaTypeName,
z.libraryAlias as LibraryName,
x.id as MediaID
from domain_MediaHistory x left join domain_Media y on x.id = y.id, domain_TapeLibrary z
where x.librarySlotNumber != -1
and z.id = x.libraryId
) t1
group by t1.snapTime, t1.mediaStatusName, t1.mediaTypeName, t1.LibraryName
order by t1.snapTime desc
To explain a bit, every day at (default) midnight OpsCenter collects a snapshot of all your tapes and their current status and attributes. What this query is doing is summing up the tapes in different statuses for each day so you get data like this:
snapTime | mediaStatusName | mediaTypeName | LibraryName | CountOfMedia |
07/27/15 | Full | hcart | A | 6001 |
07/27/15 | Active | hcart | A | 591 |
07/27/15 | Frozen | hcart | A | 120 |
07/27/15 | Scratch | hcart3 | B | 960 |
07/26/15 | Full | hcart | A | 5958 |
07/26/15 | Active | hcart | A | 596 |
07/26/15 | Frozen | hcart | A | 120 |
07/26/15 | Scratch | hcart | A | 38 |
07/26/15 | Scratch | hcart3 | B | 960 |
Then you can take that data to Excel and graph it, I'd recommend the Stacked Area chart. This will show you exactly when your frozen media is getting high, and when you unfreeze some it will show that too.
This query is not for 3months, its for however long you have the data for. You can check/change how long your OpsCenter keeps media data by looking at Settings > Configuration > Data Purge tab > Media History field. You may need to tweak this to fit your needs, but hopefully this helps.
05-22-2018 01:53 PM - edited 05-22-2018 02:35 PM
.