cancel
Showing results for 
Search instead for 
Did you mean: 

Daily Backup Failure Report including intelligent policy VMs

bcraig88
Level 2

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';

1 ACCEPTED SOLUTION

Accepted Solutions

Krutons
Moderator
Moderator
   VIP   

@bcraig88

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

View solution in original post

4 REPLIES 4

M_henriksen
Level 4
Partner

Hi,

Try change

A.type IN (0,22,28) AND

to

A.type IN (-1,0,1,22,28,19) AND

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.

Krutons
Moderator
Moderator
   VIP   

@bcraig88

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

Changing parentjobID to id did the trick. Thanks for the help. Can't belive I didn't think of that!