05-23-2018 02:07 PM
The following code comes close to what I'm needing but I only want to report on those that the 'Days since Last successful' is 3 or more days. In other words the backups have been failing for 3 or more days. How can I modify?
SELECT DISTINCT B.friendlyName as 'Master Server', A.clientName as 'Client Name', A.policyName as 'Policy Name', LIST( DISTINCT A.statuscode) as 'Status Code', ( select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful' from domain_JobArchive CKP where CKP.masterServerId = A.masterServerId AND CKP.id = CKP.parentJobId AND CKP.clientId = A.clientId AND CKP.policyId = A.policyId AND CKP.policyName = A.policyName AND CKP.policyType = A.policyType AND ( CKP.statusCode = 0 OR CKP.statusCode = 1 ) ), ( select UtcBigIntToNomTime(MAX(BKP.endTime)) as 'Last successful' from domain_JobArchive BKP where BKP.masterServerId = A.masterServerId AND BKP.Id = BKP.parentJobId AND BKP.clientId = A.clientId AND BKP.policyId = A.policyId AND BKP.policyName = A.policyName AND BKP.policyType = A.policyType AND ( BKP.statusCode = 0 OR BKP.statusCode = 1 ) ) FROM "domain_JobArchive" A , "domain_masterserver" B WHERE A.masterServerId = B.id AND A.id = A.parentjobid AND DATEDIFF(hour,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 24 AND ( select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful' from domain_JobArchive CKP where CKP.masterServerId = A.masterServerId AND CKP.id = A.parentJobId AND CKP.clientId = A.clientId AND CKP.policyId = A.policyId AND CKP.policyName = A.policyName AND CKP.policyType = A.policyType AND CKP.scheduleType = A.scheduleType AND ( CKP.statusCode = 0 OR CKP.statusCode = 1 ) ) > 0 AND A.scheduleName != 'user_backup' AND A.statusCode not in (0,1,190,199,230,239,240,800,1000) AND A.policyName NOT LIKE 'SLP_%' AND A.type IN (0,22,28) AND A.scheduletype not in (-1,2) Group by A.clientName,A.masterServerId,A.policyName,B.friendlyName,policyType,scheduleType,clientid,policyid ORDER BY B.friendlyName , A.clientName
Thanks!
12-13-2018 12:06 PM
I use the canned "Client Risk Analysis" report to do exactly this. It is set to a 2-week time frame by default, but you can set it to 3 days and you will see any clients that haven't been backed up in 3 days, their most recent successful backup, most recent Full, and most recent Incr.
12-14-2018 11:50 AM
change GETDATE()) <= 24 to (24x3 = 72)