SQL Query for last failed job in a workflow

[Anon Anon]

I am trying to query the database for failed jobs, but only return the job that was the actual root cause for the failure (not the related jobs which also display failed status codes). Since the timestamps do not include HH:MM I am not able to use this attribute in a helpful way. Is there another table or attribute that could help filter these results? AH_TimePeriod is null for all results.

[Legacy User]

Heather, i m afraid this might be very hard to determine with a SQL Statement if I understand correctly what you mean. Please give a bit more of detail about what exactly you would like to archive, I understand that from a Workflow where some tasks are running within, you want to find out which one was the last to fail?

Maybe we can provide you with some "out of the box" solution within the software to archive this.

[Pete Wirfs]

Perhaps group your failed jobs by the workflow they belong to, and select only the earliest failure within that workflow, thus assuming the rest of the failures were not root cause?

[Anon Anon]

That is what I am trying now.. I have this so far:

SELECT ah_client
  ,SUBSTR(ah_alias,-9) "Product Code"
  ,AH_Alias   "Job Name"
  ,max(to_char(ah_timestamp1, 'MM/DD HH24:MI')) "Activation Time"
  ,AH_TopNr "JobNr Top Parent"
FROM aev9_manager1.AH
WHERE AH_Alias LIKE '%_CAT%A'
AND AH_Alias NOT LIKE '%_AUTOMATIC'
AND (ah_client = '1992' OR ah_client='1993')
AND AH_STATUS !='1900'
AND AH_STATUS !='1933'
group BY  AH_TOPnr, AH_Alias,
AH_cancel, AH_CLIENT

I just want to be sure I am not filtering any jobs that I should not be.. does this query make sense to you? Any advice on what to add/change to ensure I am getting proper results?

[Anon Anon]

Harold,

I am trying to find the job that was ultimately the reason for the entire workflow failing, filtering other jobs from the results. Often when a job fails, the preceding tasks (jobs) are also ended with the same status/result code - this is where I am finding it difficult to get only the desired results.

[Anon Anon]

Any out of the box solutions or advice of any kind are more than welcome :)

[Legacy User]

This statement recursively browses down a past workflow execution (marked runid number) giving you a list of all sub-tasks (and sub-sub-sub... tasks). You can filter this list by the AH_Status like you did in your statement. This won't work with activated objects (by ACTIVATE_UC_OBJECT) - for this the query would need to slightly change.

 

== Using workflow runID ==

with taskIDs(TASKID) as (
  select AJPP_TASKIDNR
  from UC4.AJPP
  where AJPP_AH_Idnr = 97805249     
  and AJPP_TASKIDNR > 0
UNION ALL 
  select AJPP_TASKIDNR
  from UC4.AJPP, taskIDs
  where AJPP_AH_Idnr = taskIDs.TASKID
  and AJPP_TASKIDNR > 0
)

 

== Using latest statistic entry based on workflow name and client ==
select *
from taskids
join UC4.AH on AH_IDNR = taskids.taskid

with taskIDs(TASKID) as (
  select AJPP_TASKIDNR
  from UC4.AJPP
  where AJPP_AH_Idnr = (select AH_Idnr from UC4.AH where AH_Timestamp1 = (select max(AH_Timestamp1) from UC4.AH where AH_Name = 'WORKFLOWNAME' and AH_Client = 1000) and AH_Name = 'WORKFLOWNAME' and AH_Client = 1000)
  and AJPP_TASKIDNR > 0
UNION ALL 
  select AJPP_TASKIDNR
  from UC4.AJPP, taskIDs
  where AJPP_AH_Idnr = taskIDs.TASKID
  and AJPP_TASKIDNR > 0
)
select *
from taskids
join UC4.AH on AH_IDNR = taskids.taskid

[Anon Anon]

In my opinion it not possible with the info you provided us with cause a JOBS can have dozens of causes to fail,
an OS returncode >0, a fault_other in Automation engine, a post_pocessing error etc etc etc...

I am afraid you won´t catch the exact error without having a look at at least activation Report job report an Post processing report.

At the moment you should limit the possible causes to a small list - which possible causes do you want to catch ?

[Anon Anon]

Joel - i do not have a taskids table...

[Anon Anon]

After working on the query this is what I have now:

SELECT ah_topnr
  ,TO_CHAR(ah_timestamp1, 'MM/DD HH24:MI:ss') "Activated Time"
  ,ah_alias
  ,ah_status
FROM aev9_manager1.ah
WHERE AH_CLIENT = '1992'
AND AH_STATUS !=1900
  --OR ah_client = '1993'
AND ah_topnr  IN
  (SELECT ah_topnr
   FROM aev9_manager1.ah
   WHERE TO_CHAR(ah_timestamp1, 'MM/DD HH24:MI:ss') IN
    (SELECT TO_CHAR(MAX(ah_timestamp1), 'MM/DD HH24:MI:sS')
    FROM aev9_manager1.ah
    GROUP BY  ah_idnr, ah_topnr
    )
   GROUP BY ah_topnr
  )

[Legacy User]

I separated the above SQL at the wrong line... you're on MSSQL or Oracle? 

== Using workflow runID ==

with taskIDs(TASKID) as (
  select AJPP_TASKIDNR
  from UC4.AJPP 
  where AJPP_AH_Idnr = 97805249     
  and AJPP_TASKIDNR > 0
UNION ALL  
  select AJPP_TASKIDNR
  from UC4.AJPP, taskIDs
  where AJPP_AH_Idnr = taskIDs.TASKID
  and AJPP_TASKIDNR > 0
)
select *
from taskids
join UC4.AH on AH_IDNR = taskids.taskid

== Using latest statistic entry based on workflow name and client ==

with taskIDs(TASKID) as (
  select AJPP_TASKIDNR
  from UC4.AJPP 
  where AJPP_AH_Idnr = (select AH_Idnr from UC4.AH where AH_Timestamp1 = (select max(AH_Timestamp1) from UC4.AH where AH_Name = 'WORKFLOWNAME' and AH_Client = 1000) and AH_Name = 'WORKFLOWNAME' and AH_Client = 1000)
  and AJPP_TASKIDNR > 0
UNION ALL  
  select AJPP_TASKIDNR
  from UC4.AJPP, taskIDs
  where AJPP_AH_Idnr = taskIDs.TASKID
  and AJPP_TASKIDNR > 0
)
select *
from taskids
join UC4.AH on AH_IDNR = taskids.taskid

[Anon Anon]

I am using oracle. Where is the taskid attribute located?

[Legacy User]

If you look at the statement above you see that it begins with:

with taskIDs(TASKID) as (...)

So "taskids" is the result of this query like a subquery. There is no real table or attribute in the AE DB named like this.