Report regarding ecmimportjob details

[Harsha]

Hi Everyone,

We had a requirement:

We have schemaimportjob for 2 datafiles import, scheduled 3 times a day (because sometimes there will be delay in receiving the file). So, one job will fail everyday saying datafile not found. Now the report should be generated if 1 datafile will be missing (most often it will 1). If the savresponse is success 2 times it means 2 were recieved and processed. And if it is 1 success and 2 error datafile not found. Then 1 datafile is missing.

I tried couple of queries but that isnt giving me desired results can anyone help me with it? Issue is if it has 2 success is works fine gives no data found.But if it has 1 success and 2 failed, only 1 error message its showing the resultset but it has to show 2 resultset.

Below are the queries that I tried:

SELECT

    EC.JOBNAME,

    EC.EXTERNALCONNECTION,

    EC.SAVRESPONSE AS RESPONSE,

    EC.JOBSTARTDATE as 'JOBSTARTDATE (UTC)',

    EC.JOBENDDATE as 'JOBENDDATE (UTC)',

    IL.LOGDATAASXML

FROM

    ECMIMPORTJOB EC

JOIN

    IMPORTLOG IL ON EC.JOBID = IL.JOBID

WHERE

    EC.JOBNAME = 'SchemaUserJob'

    AND (

        (EC.JOBSTARTDATE >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND EC.JOBSTARTDATE < CURDATE())

        OR (EC.JOBENDDATE >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND EC.JOBENDDATE < CURDATE())

    )

    AND (

      (

            EC.SAVRESPONSE = 'success'

            AND (

                SELECT COUNT(*)

                FROM ECMIMPORTJOB

                WHERE JOBNAME = 'SchemaUserJob'

                  AND SAVRESPONSE = 'success'

                  AND EC.JOBSTARTDATE >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)

                  AND EC.JOBSTARTDATE < CURDATE()

            ) = 2

            AND (

                SELECT COUNT(*)

                FROM ECMIMPORTJOB

                WHERE JOBNAME = 'SchemaUserJob'

                  AND SAVRESPONSE LIKE 'Error Data File Not found%'

                  AND EC.JOBSTARTDATE >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)

                  AND EC.JOBSTARTDATE < CURDATE()

            ) >= 2

        )

    )

ORDER BY JOBSTARTDATE; 

OR

SELECT
EC.JOBNAME,
EC.EXTERNALCONNECTION,
EC.SAVRESPONSE AS RESPONSE,
EC.JOBSTARTDATE as 'JOBSTARTDATE (UTC)',
EC.JOBENDDATE as 'JOBENDDATE (UTC)',
IL.LOGDATAASXML
FROM
ECMIMPORTJOB EC
JOIN
IMPORTLOG IL ON EC.JOBID = IL.JOBID
WHERE
EC.JOBNAME = 'SchemaUserJob'
AND (

(EC.JOBSTARTDATE >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
AND EC.JOBSTARTDATE < CURDATE())
OR (EC.JOBENDDATE >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
AND EC.JOBENDDATE < CURDATE())
)
AND (

(
SELECT COUNT(*)
FROM ECMIMPORTJOB
WHERE
JOBNAME = 'SchemaUserJob'
AND SAVRESPONSE LIKE 'Error Data File Not found%'
AND (
(JOBSTARTDATE >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
AND JOBSTARTDATE < CURDATE())
OR (JOBENDDATE >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
AND JOBENDDATE < CURDATE())
)
)
>
(
SELECT COUNT(*)
FROM ECMIMPORTJOB
WHERE
JOBNAME = 'SchemaUserJob'
AND SAVRESPONSE NOT LIKE 'Error Data File Not found%'
AND (
(JOBSTARTDATE >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
AND JOBSTARTDATE < CURDATE())
OR (JOBENDDATE >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
AND JOBENDDATE < CURDATE())
)
)
)
ORDER BY JOBSTARTDATE;

If anyone knows what is the issue in above query please let me know.

can anyone help me regarding this.

Thank you,

Harsha

[This message has been edited by moderator to remove @ mentions to employees and other forum members]

[sudeshjaiswal]

Hello @Harsha,

Can you please try with the below query once,

SELECT
  EC.JOBNAME,
  EC.EXTERNALCONNECTION,
  EC.SAVRESPONSE AS RESPONSE,
  EC.JOBSTARTDATE AS 'JOBSTARTDATE (UTC)',
  EC.JOBENDDATE AS 'JOBENDDATE (UTC)',
  CASE
    WHEN IL.LOGDATAASXML LIKE 'Error Data File Not found%' THEN IL.LOGDATAASXML
    ELSE NULL
  END AS 'Error Message'
FROM ECMIMPORTJOB EC
LEFT JOIN IMPORTLOG IL ON EC.JOBID = IL.JOBID
WHERE EC.JOBNAME = 'SchemaUserJob'
  AND (
    EC.JOBSTARTDATE >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
    AND EC.JOBSTARTDATE < CURDATE()
  )
ORDER BY JOBSTARTDATE;

Thanks,

[rushikeshvartak]

SELECT i.filename AS Application,
jobname AS 'Job_Name',
max(jobstartdate) AS "JOBSTARTDATE",
(select max((updatedate)) d from accounts where endpointkey=ep.endpointkey and status=1)AS 'LastAccountImportDate',
logdataasxml AS Details,
ep.customproperty1 as importfrequency
FROM ecmimportjob e,
importlog i,
endpoints ep
WHERE jobname IN ( 'SchemaAccountJob', 'SchemaUserJob' )
AND savresponse != 'Success'
AND jobstartdate > Curdate() - INTERVAL ep.customproperty1 day
AND i.jobid = e.jobid
AND ep.endpointname = i.filename
AND ep.customproperty1 = 1 /*Daily*/
group by i.filename