Friday, October 16, 2015

Custom Backup Exec SSRS Report

I'm not a Backup Exec administrator, but recently one of my co-workers wanted a custom SSRS report to track our backups, which also allows him to receive this report on a regular basis via a subscription.  This is what I came up with - perhaps you too will find it useful.

Query:
SELECT  CASE TaskTypeID
          WHEN 200 THEN 'Backup'
          WHEN 300 THEN 'Duplicate'
        END AS TaskType
       ,JobName
       ,ActualStartTime
       ,EndTime
       ,(ElapsedTimeSeconds / 3600.0) AS ElapsedTimeHours
       ,CASE FinalJobStatus
          WHEN THEN 'Success w/ Exception'
          WHEN THEN 'Success w/ Exception'
          WHEN THEN 'Canceled'
          WHEN 19 THEN 'Success'
          ELSE 'Unknown'
        END AS FinalJobStatus
       ,TargetName
       ,MediaSetName
       ,(TotalDataSizeBytes / 1024.0 / 1024.0 / 1024.0) AS TotalDataSizeGB
       ,TotalNumberOfDirectories
       ,FinalErrorDescription
FROM    BEDB.dbo.vwJobHistorySummary
WHERE   TaskTypeID IN (200, 300)
        AND ActualStartTime > DATEADD(MONTH, -1, GETDATE())
ORDER BY ActualStartTime DESC

RDL: click here.