Daily Backup Failure Report including intelligent policy VMs

Hello all,

I didn't want to revive an old thread marked resolved so figured I'd ask here. I found a post from 2016 created by nbritton

(https://vox.veritas.com/t5/OpsCenter/OpsCenter-Daily-Failure-Report/m-p/748832#M3946) where he shared a very helpful query showing daily failed jobs and when their last successful run was.

Seeing as NBU has progressed since then with various intelligent policies (mainly VMware and Hyper-V), that query provided back then is now missing a lot of those 'stream based' job statuses and I'm having some issues trying to get that added in to the query. Does anybody have any ideas on how to add that onto the original query? Any help would be appreciated.

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 ) ),
( select DATEDIFF(hour,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Hours 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 ) )
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.statusCode not in (0,1,150,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) AND
A.clientName not in ('server lists') AND -- NBU MASTER Servers
A.clientName not in ('server lists') AND -- NBU site1MEDIA SERVERS
A.clientName not in ('server lists') AND -- NBU site2 MEDIA SERVERS
A.clientName not in ('server lists') AND -- NBU site3 MEDIA SERVERS
A.clientName not in ('server lists') -- NBU site4 MEDIA SERVERS
Group by A.clientName,A.masterServerId,A.policyName,B.friendlyName,policyType,scheduleType,clientid,policyid,statuscode
ORDER BY B.friendlyName, 'Days since Last successful';