I would like to make some reporting about BPM failed processes.
The reporting must show the process instance failed, the reason why the instance is failed and if it exists the step where the problem occurred.
So I made some sql query to get these information.
But I m facing performance issue with this query .
It looks like:
SELECT STEPS.INSTANCEID, STEPS.STEPID, ERR.ERRORMESSAGE FROM (SELECT DISTINCT STEP.INSTANCEID, STEP.STEPID, STEP.CONTEXTID FROM WMPROCESSRECENT PRREC INNER JOIN WMPROCESSSTEP STEP ON PRREC.INSTANCEID = STEP.INSTANCEID WHERE PRREC.AUDITTIMESTAMP BETWEEN TO_TIMESTAMP('01/04/2016 22:00', 'DD/MM/YYYY HH24:MI') AND TO_TIMESTAMP('02/04/2016 21:59', 'DD/MM/YYYY HH24:MI') AND PRREC.STATUS = 4 AND PRREC.PROCESSKEY IN ('BPM_SalesProcess_V2_0/BPM_SalesProcess_V2_0') AND STEP.AUDITTIMESTAMP BETWEEN PRREC.AUDITTIMESTAMP - INTERVAL '1' MINUTE AND PRREC.AUDITTIMESTAMP ) STEPS INNER JOIN WMERROR ERR ON STEPS.CONTEXTID = ERR.CONTEXTID
So how to do to get all these information in a fast way ?
I m new with it and I only know about the main tables (wmprocess, wmprocessstep, wmprocessrecent, wmerror…)