Query to find Failed System Processing Jobs

[Pratham]

Hi Team, I need to write a query to find failed system processing jobs.

[rushikeshvartak]

Refer https://forums.saviynt.com/t5/identity-governance/sql-query-to-get-the-long-running-amp-failed-jobs/...

[Pratham]

Hi @rushikeshvartak ,

In above link there is long running job analytics and 2nd is Failed Application Import Job analytics

I want Failed System Processing Jobs.

[naveenss]

Hi @Pratham there is already an OOTB analytics for this. Did you check this?

[Pratham]

Hi @naveenss ,

Yes, I check that one 

Is it sufficient for the Failed System Processing Jobs?

select jobid,
jobname,
jobstartdate,
jobenddate,
SAVRESPONSE 
from ecmimportjob 
where EXTERNALCONNECTION is null and
SAVRESPONSE != 'Success';

[naveenss]

Hi @Pratham yes this should suffice. This is a pre-packaged analytics from Saviynt. If you have any additional requirements, I would recommend a similar analytics by enhancing the current query. 

[Abhishek0406]

SELECT TRIGGERNAME, JOBGROUP, JOBSTARTDATE AS 'JOBSTARTDATE(UTC)', JOBENDDATE AS 'JOBENDDATE(UTC)', SAVRESPONSE AS JOBRESPONSE from ecmimportjob where ( SAVRESPONSE NOT IN ('Success', 'InProgress') ) AND ( JOBSTARTDATE >= CURRENT_TIMESTAMP - INTERVAL 15 DAY )

This is what we are suing currently to locate all failed jobs within last 15 days.

[rushikeshvartak]

Data will be huge so apply date filters

[Pratham]

Hi @rushikeshvartak , @Abhishek0406 , @naveenss 

Can you check this query, if it is not correct can you make some changes to get exact data.

select jobid as 'JOB ID',
jobname as 'JOB NAME',
JOBGROUP as 'JOB GROUP',
SAVRESPONSE as 'SAV RESPONSE',
TRIGGERNAME as 'TRIGGER NAME',
jobstartdate as 'JOB START DATE',
jobenddate as 'JOB END DATE',
timediff(JOBENDDATE,JOBSTARTDATE) as 'Time Taken'
from ecmimportjob 
where EXTERNALCONNECTION is null and
SAVRESPONSE NOT IN ('Success','InProgress')AND(JOBSTARTDATE >= CURRENT_TIMESTAMP - INTERVAL 10 DAY)

[rushikeshvartak]

Query is correct

[naveenss]

Looks good to me.