Video Screencast Help

Operations Center Analytics

Created: 30 Jul 2013 | 5 comments

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)
Operating Systems:
Discussion Filed Under:

Comments 5 CommentsJump to latest comment

MilesVScott's picture

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.

If my answer was helpful, please mark as a solution so others can benefit as well.

NBU 7.1.0.3,Win 2k3 Enterprise x64 SP2

Miles Scott | Holland & Knight
IT Infrastructure Specialist

lisareinhart's picture

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

MilesVScott's picture
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]%'
 

 

If my answer was helpful, please mark as a solution so others can benefit as well.

NBU 7.1.0.3,Win 2k3 Enterprise x64 SP2

Miles Scott | Holland & Knight
IT Infrastructure Specialist

lisareinhart's picture

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 picture

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

 

If my answer was helpful, please mark as a solution so others can benefit as well.

NBU 7.1.0.3,Win 2k3 Enterprise x64 SP2

Miles Scott | Holland & Knight
IT Infrastructure Specialist