Forum Discussion

Sid1987's avatar
Sid1987
Level 6
10 years ago

Vault Reporting

Hi Techiz,

  I am trying to look for an automated vaulting report for a month. Means how many and what all medias were ejected for an entire month. I tried looking into opscenter, there is only one report for vault which just tells the count. Then I looked into Opscenter tables, I could only find tables like nom_NBJob and nom_NBVolume which could give me few information.

I am looking for a report for a month containing columns Date on which offsiting was done(can be found in nom_NBVolume), number of tapes vaulted, mediaid of the tapes vaulted, expiration date of the media(domain_Media), mediaid of medias which are expired on that date as per vault.

Thanks

Sid

Backup and Recovery
OpsCenter
Reporting
Windows Server (2003-2008)
  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    10 years ago

    Hi Sid

     

    Try this (change the dates as required). [Keep in mind I don't have vault configured so I"m guessing what the result would be :) ]

     

    select nom_nbvolume.mediaid as "Media ID", UTCBigIntToNomTime(nom_nbvolume.DateVaulted) as "Vaulted Date", UTCBigIntToNomTime(nom_nbvolume.ExpirationDate) as "Expiration Date", UTCBigIntToNomTime(nom_nbvolume.ReturnDate) as "Return Date"
    from nom_nbvolume
    where UTCBigIntToNomTime(nom_nbvolume.DateVaulted) between 'Feb 12, 2015 12:00:00 AM' and 'Feb 13, 2015 12:00:00 AM'

     

9 Replies

Sort By
  • Sid1987's avatar
    Sid1987
    Level 6
    10 years ago

    Hi Ananya,

      Thanks for the reply. My opscenter is at 7.6.0.3. I have checked all the report templates there aren't any report which would provide me the details I am looking for.

    I am looking for a monthly report containing columns Date on which offsiting was done(can be found in nom_NBVolume), number of tapes vaulted, mediaid of the tapes vaulted, expiration date of the media(domain_Media), mediaid of medias which are expired on that date as per vault.

    Thanks

    Sid

  • Ananya_Bhattach's avatar
    Ananya_Bhattach
    Level 5
    10 years ago

    I do not think we can create reports outside the one's predefined by Opscenter. You can log a formal support case to confirm this.

  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    Level 6
    10 years ago

    Ananya, you can create custom SQL queries using the OpsCenter interface.

     

    Sid, I'll take a look at this and get back to you (don't have any vaulting configured now)

  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    Level 6
    10 years ago

    Hi Sid

     

    Try this (change the dates as required). [Keep in mind I don't have vault configured so I"m guessing what the result would be :) ]

     

    select nom_nbvolume.mediaid as "Media ID", UTCBigIntToNomTime(nom_nbvolume.DateVaulted) as "Vaulted Date", UTCBigIntToNomTime(nom_nbvolume.ExpirationDate) as "Expiration Date", UTCBigIntToNomTime(nom_nbvolume.ReturnDate) as "Return Date"
    from nom_nbvolume
    where UTCBigIntToNomTime(nom_nbvolume.DateVaulted) between 'Feb 12, 2015 12:00:00 AM' and 'Feb 13, 2015 12:00:00 AM'

     

  • Sid1987's avatar
    Sid1987
    Level 6
    10 years ago

    Hi Riaan,

     

      Thanks for your response, we are close. I changed the query a bit as expiration date wasn't coming up from nom_nbvolume table. One further request would be can it be generated for 30 days or for a month so? Not by specifying start and end date of the month.

    Would below work, I didn't try though.

    DATEDIFF(day,UTCBigIntToNomTime(DJ.startTime),GETDATE())>=30

    select 
    nom_nbvolume.mediaid as "Media ID",
    UTCBigIntToNomTime(nom_nbvolume.DateVaulted) as "Vaulted Date", 
    UTCBigIntToNomTime(domain_media.expirationTime) as "Expiration Date",
    domain_media.usedCapacity/1024/1024/1024 as "Used Capacity in GB"
    FROM
    nom_nbvolume, domain_media
    WHERE nom_nbvolume.barcode=domain_media.barcode
    AND UTCBigIntToNomTime(nom_nbvolume.DateVaulted) between 'Feb 11, 2015 12:00:00 AM' and 'Feb 12, 2015 12:00:00 AM' 

  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    Level 6
    10 years ago

    Nice, I noticed that but thought it might just be something on my test system

     

    Use this for AND instead

    AND DATEDIFF(day,UTCBigIntToNomTime(nom_nbvolume.DateVaulted), GETDATE()) <= 30