Forum Discussion

ctorkington-cra's avatar
10 years ago

OpsCenter Report for Frozen Media

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?

  • 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.

3 Replies

Replies have been turned off for this discussion
  • 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.

  • 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.