cancel
Showing results for 
Search instead for 
Did you mean: 

Operations Center Analytics

lisareinhart
Level 4

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)
5 REPLIES 5

MilesVScott
Level 6
Certified

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.

lisareinhart
Level 4

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

MilesVScott
Level 6
Certified
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
Level 4
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
Level 6
Certified

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]'