cancel
Showing results for 
Search instead for 
Did you mean: 

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

I'm still working on figuring

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.

This did not return any data

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

Use these:   Search by Master

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

 

the first version of code did

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?

This query will only show

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