OpsCenter - Servers Not Backed Up Report

Here's query that refers to your what you are looking for. Just copy, paste and run report.

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