Forum Discussion

lisareinhart's avatar
lisareinhart
Level 4
12 years ago

Backup Image Retention Summary by Client Report Needed

Report that shows the retentions to avoid or identify retentions that deviate from standards of tapes held more than 90 days.

If backup is still active, fields include:

Server Name;Client Name;Policy Name;Schedule Name;Retention in days for different schedules-->[Daily;Weekly;Monthly:Yearly:Others;]

Retention is based on a policy not a client;Report that shows the retentions to avoid or identify retentions that deviate from standards of tapes held more than 90 days.

Backup and Recovery
OpsCenter
Reporting
Windows Server (2003-2008)

5 Replies

Sort By
  • lisareinhart's avatar
    lisareinhart
    Level 4
    11 years ago

    This is interesting, but it provides everything which is a very big report in our environment. Sometimes I get an IO exception error or invalid query randomly when running these very large reports.

    In order to streamline and make our environment more efficient, we need to report to show the deviations to the normal retention cycles. We are looking for the backup policies that exceed 1 month and 3 month retention cycles.  If days are used instead of months, the report could be off.

    If backup is still active, fields include Server Name;Client Name;Policy Name;Schedule Name;Retention in days for different schedules-->[Daily;Weekly;Monthly:Yearly:Others;] Retention is based on a policy not a client;

    For example, others could be litigation hold policies or infinity or somebody just made something up.

    I applaud your effort for this report, but still need something different.

    • Dollypee's avatar
      Dollypee
      Moderator
      7 years ago

      Hi tom_sprouse , great Job. As lisareinhart stated, if you can work something out regarding lisa last request, that will be great for me too. Thank you for your effort.

  • tom_sprouse's avatar
    tom_sprouse
    Level 6
    12 years ago

    Lisa,

    I've been looking at this... and have come up with the following:

    

    SELECT friendlyname AS "Master",
    

    domain_image.id AS "Image ID",
    

    utcbiginttonomtime(writeendtime) AS "Backup EndTime",
    

    utcbiginttonomtime(expirationTime) AS "Expires",
    

    DATEDIFF(dd, utcbiginttonomtime(writeendtime), utcbiginttonomtime(expirationTime)) AS "Days of Retention" ,
    

    clientname AS "Client",
    

    policyname AS "Policy",
    

    scheduleLabel AS "Schedule Name"
    

    FROM domain_masterserver
    

    JOIN domain_image
    

    ON domain_image.masterserverid = domain_masterserver.id
    

    JOIN domain_jobimage
    

    ON domain_jobimage.imageid = domain_image.id
    

    WHERE unexpiredcopycount > 0 
    

    There are a few tweeks that could be done here... but this will return a list of images that have not expired.
    

    It will display the requested fields, in addition to Backup End Time, Expiration Time, and the difference between the 2 in number of days.
    

    This will return a list of all unexpired images... and not just those in the past 90 days.
    

    I had not added that, but did want to provide something in the meantime.
    

    If this works for you, please mark as resolved... if not, let me know, and I will update when available.
    

    --Tom
    

     
  • tom_sprouse's avatar
    tom_sprouse
    Level 6
    12 years ago
    Lisa,
    

    Can you provide a bit more information... 
    

    Are we looking for:
    

    1) Tapes that are currently offsite? 
    

    2) Backup Images (regardless of location) 
    

    or 
    

    3) To Confirm Policy Configuration?
    

     
    

    This should tell us where to pull the information from... 
    

     
    

    --Tom