cancel
Showing results for 
Search instead for 
Did you mean: 

OpsCenter Report for Frozen Media

ctorkington-cra
Level 3

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?

1 ACCEPTED SOLUTION

Accepted Solutions

areznik
Level 5

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.

View solution in original post

3 REPLIES 3

sdo
Moderator
Moderator
Partner    VIP    Certified

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.

areznik
Level 5

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.

.