Job Failure Analytics Report

[asamaddar]

Hi,

We need to configure an Analytics Report that would show all the Jobs that have failed in the system.

I have tried this query but Endpoint Name, Endpoint and Comments are blank:

select jobid, jobname, jobstartdate, jobenddate, SAVRESPONSE, case when ecm.EXTERNALCONNECTION is not null then (select e.ENDPOINTNAME from endpoints e where e.SECURITYSYSTEMKEY IN (select s.SYSTEMKEY from securitysystems s where s.EXTERNALCONNECTION IN (select e.EXTERNALCONNECTIONKEY from externalconnection e where e.CONNECTIONNAME=ecm.EXTERNALCONNECTION)) limit 1) when ecm.SYSTEMNAME is not null then (select e.ENDPOINTNAME from endpoints e where e.SECURITYSYSTEMKEY = (select s.systemkey from securitysystems s where s.SYSTEMNAME = ecm.SYStemNAME ) limit 1) else null end as ENDPOINTNAME, case when ecm.EXTERNALCONNECTION is not null then (select e.ENDPOINTKEY from endpoints e where e.SECURITYSYSTEMKEY IN (select s.SYSTEMKEY from securitysystems s where s.EXTERNALCONNECTION IN (select e.EXTERNALCONNECTIONKEY from externalconnection e where e.CONNECTIONNAME=ecm.EXTERNALCONNECTION)) limit 1) when ecm.SYSTEMNAME is not null then (select e.ENDPOINTKEY from endpoints e where e.SECURITYSYSTEMKEY = (select s.systemkey from securitysystems s where s.SYSTEMNAME = ecm.SYStemNAME ) limit 1) else null end as endpointKey from ecmimportjob ecm where ecm.SAVRESPONSE !='success';

We would need the  Job Name and not Job Attribute Name. Also, Endpoint is a must in the report.

Can someone please help!!!

[rushikeshvartak]

SELECT ECM.JOBID,
       ECM.TRIGGERNAME                            AS jobname,
       ECM.JOBSTARTDATE,
       ECM.JOBENDDATE,
       ECM.SAVRESPONSE,
       COALESCE(E1.ENDPOINTNAME, E2.ENDPOINTNAME) AS ENDPOINTNAME,
       COALESCE(E1.ENDPOINTKEY, E2.ENDPOINTKEY)   AS ENDPOINTKEY
FROM   ECMIMPORTJOB ECM
       LEFT JOIN SECURITYSYSTEMS S1
              ON S1.EXTERNALCONNECTION = (SELECT E2.EXTERNALCONNECTIONKEY
                                          FROM   EXTERNALCONNECTION E2
                                          WHERE
                 E2.CONNECTIONNAME = ECM.EXTERNALCONNECTION)
       LEFT JOIN ENDPOINTS E1
              ON E1.SECURITYSYSTEMKEY = S1.SYSTEMKEY
       LEFT JOIN SECURITYSYSTEMS S2
              ON S2.SYSTEMNAME = ECM.SYSTEMNAME
       LEFT JOIN ENDPOINTS E2
              ON E2.SECURITYSYSTEMKEY = S2.SYSTEMKEY
WHERE  ECM.SAVRESPONSE != 'success';