08-23-2018 08:10 AM
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';
Solved! Go to Solution.
09-06-2018 08:59 AM - edited 09-06-2018 09:00 AM
Change any parentJobID to just ID, so CKP.parentJobID becomes CKP.id etc...
Then add the following, depending on how many VM Proxy Hosts you have, after A.clientName NOT LIKE 'SLP~'
A.clientName NOT LIKE 'VM Proxy Host' AND A.clientName NOT LIKE 'VM Proxy Host 2' AND
08-27-2018 05:38 AM
Hi,
Try change
A.type IN (0,22,28) AND
to
A.type IN (-1,0,1,22,28,19) AND
08-27-2018 08:13 AM
Thanks for the suggestion. Unfortunately that seems to be giving me similar results where it is still not reporting on any vms that are part of a query based intelligent policy. I'm only getting the parent job listed like before.
09-06-2018 08:59 AM - edited 09-06-2018 09:00 AM
Change any parentJobID to just ID, so CKP.parentJobID becomes CKP.id etc...
Then add the following, depending on how many VM Proxy Hosts you have, after A.clientName NOT LIKE 'SLP~'
A.clientName NOT LIKE 'VM Proxy Host' AND A.clientName NOT LIKE 'VM Proxy Host 2' AND
09-06-2018 11:28 AM
Changing parentjobID to id did the trick. Thanks for the help. Can't belive I didn't think of that!