Forum Discussion

lisareinhart's avatar
lisareinhart
Level 4
11 years ago

Operations Center Analytics

Does anyone have a report that shows Backup Image Retention Summary by Client report ?

What we need is a list of backup images with their retention (backup date and expiration date), showing the client name and tape names.

The input parameters….  E.g.

  • master server: giving a list of all images for all clients of the given master server or
  • client name: giving a list of all images for the given client server or
  • multiple client names
  • tape ID(s)
Backup and Recovery
OpsCenter
Reporting
Windows 8

5 Replies

Sort By
  • MilesVScott's avatar
    MilesVScott
    Level 6
    11 years ago

    This query will only show incs for 30 days and fulls for 90. To get the other column added it will take me a while to figure out the logic but I will work on it:

    Client Search
    SELECT ms.networkName AS 'Master Server'
            , di.id AS 'Image'
            , utcBigIntToNomTime(di.writeStartTime) AS 'Backup Date'
            , utcBigIntToNomTime(di.expirationTime) AS 'Expiration Date'
            , utcBigIntToNomTime(di.deletionTime) AS 'Deletion Date'
    FROM domain_Image di 
    INNER JOIN domain_MasterServer ms
    ON di.masterServerId = ms.id
    WHERE ((di.filesFileName LIKE '%INCR%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+30) 
        OR (di.filesFileName LIKE '%FULL%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+90))
    AND di.id LIKE '%[ClientName]%'
     
     
    Master Search
    SELECT ms.networkName AS 'Master Server'
            , di.id AS 'Image'
            , utcBigIntToNomTime(di.writeStartTime) AS 'Backup Date'
            , utcBigIntToNomTime(di.expirationTime) AS 'Expiration Date'
            , utcBigIntToNomTime(di.deletionTime) AS 'Deletion Date'
    FROM domain_Image di 
    INNER JOIN domain_MasterServer ms
    ON di.masterServerId = ms.id
    WHERE ((di.filesFileName LIKE '%INCR%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+30) 
        OR (di.filesFileName LIKE '%FULL%' AND utcBigIntToNomTime(di.expirationTime) > GETDATE()+90))
    AND ms.networkName = '[MasterServerName]'

     

  • lisareinhart's avatar
    lisareinhart
    Level 4
    11 years ago
    the first version of code did not return any data. I obtained data in the second batch of the report, so thanks a bunch! The deletion date was coming up as a hyphen. Most of our retention schedules are 1 month for inc's and 3 months for full's. I am trying to find the inc's exceeding 30 days and full's exceeding 90 days and then, by how many days. Can this be added into this report?
  • MilesVScott's avatar
    MilesVScott
    Level 6
    11 years ago
    Use these:
     
    Search by Master Server
    SELECT ms.networkName AS 'Master Server'
            , di.id AS 'Image'
            , utcBigIntToNomTime(di.writeStartTime) AS 'Backup Date'
            , utcBigIntToNomTime(di.expirationTime) AS 'Expiration Date'
            , utcBigIntToNomTime(di.deletionTime) AS 'Deletion Date'
    FROM domain_Image di 
    INNER JOIN domain_MasterServer ms
    ON di.masterServerId = ms.id
    WHERE ms.networkName = '[MasterServerName]'
     
    Search by Client
    SELECT ms.networkName AS 'Master Server'
            , di.id AS 'Image'
            , utcBigIntToNomTime(di.writeStartTime) AS 'Backup Date'
            , utcBigIntToNomTime(di.expirationTime) AS 'Expiration Date'
            , utcBigIntToNomTime(di.deletionTime) AS 'Deletion Date'
    FROM domain_Image di 
    INNER JOIN domain_MasterServer ms
    ON di.masterServerId = ms.id
    WHERE di.id LIKE '%[ClientName]%'
     

     

  • lisareinhart's avatar
    lisareinhart
    Level 4
    11 years ago

    This did not return any data for me. I'm not sure what to uncomment or comment.

  • MilesVScott's avatar
    MilesVScott
    Level 6
    11 years ago

    I'm still working on figuring out the tape # portion, but hopefully this will be a good start for you:

     

    SELECT ms.networkName AS 'Master Server'
            , di.id AS 'Image'
            , utcBigIntToNomTime(di.writeStartTime) AS 'Backup Date'
            , utcBigIntToNomTime(di.expirationTime) AS 'Expiration Date'
            , utcBigIntToNomTime(di.deletionTime) AS 'Deletion Date'
    FROM domain_Image di 
    INNER JOIN domain_MasterServer ms
    ON di.masterServerId = ms.id
    WHERE
    ms.networkName = '' --Master Server Name
    --di.id LIKE '%%' --Client Name
    --di.id IN ('', '', '') --Client List
     
     
    Just comment/uncomment the fields in the where clause.