OpsCenter custom report - finding the age of the backup image at time of restore

[Jeff_Mueller]

Hello,

I've been browsing the forum for the past week and everyone here seems very helpful! 

I am trying to write a custom report in OpsCenter to find the age of the backed up image relative to the time of the restore job. I think I need to look in "domain_Image" and "domain_jobArchive", but I am having trouble joining the tables; the jobID in domain_Image is the job ID of the backup job that created it, and has nothing to do with the restore.

Thanks very much for any help you can give!

EDIT: Figured it out!

select TOP 200

domain_jobArchive.id as "Restore jobId",
domain_MasterServer.friendlyName as "MasterServer",
domain_jobArchive.clientName as "BackupClient",
UTCBigIntToNomTime(domain_Image.writeStartTime) as "BackupTime",
domain_jobArchive.destClientName as "RestoreClient",
UTCBigIntToNomTime(domain_jobArchive.startTime) as "RestoreTime",
datediff(day, BackupTime, RestoreTime) as "Age (days) of Image at Restore",
CAST(domain_jobArchive.bytesWritten/1024.0/1024.0 AS NUMERIC (20,2)) as "RestoreSize (MiB)"
-- domain_jobImage.imageID as "Image Restored From"

from domain_jobArchive
JOIN domain_MasterServer ON domain_jobArchive.masterServerId = domain_MasterServer.id
JOIN domain_jobImage ON domain_jobArchive.masterServerId = domain_jobImage.masterServerId 
AND domain_jobArchive.id = domain_jobImage.jobId
JOIN domain_Image ON domain_jobImage.masterServerId = domain_Image.masterServerId
AND domain_Image.id = domain_jobImage.imageId

WHERE domain_jobArchive.type = '2'
AND RestoreTime > GETDATE() - 10
AND domain_jobArchive.statusCode = '0'
ORDER BY RestoreTime DESC

[RiaanBadenhorst]

Hi,

I've checked the schema and don't see anywhere this would be recorded. You could get this information from the NetBackup system though.
 

[DavidCos]

Hi Jeff-

How did you get it working?  We are trying to get that same exact data.  We believe that 80-90% of our restores are from data less than 1 month old.  But we can't scale our enviornment until we have hard numbers.

Any help would be appreciated.   Thx

Dave

[RiaanBadenhorst]

He updated his post, the query above is the answer