SQL Query - trace all (updated)

Discussion created by Simon_B_9159 on Apr 20, 2017
Latest reply on Apr 29, 2017 by Wolfgang_Brueckler_1288
I have an application, against which UC4 performs many actions, via multiple Workflows.
Ok, so there is ONE main workflow, but other workflows can and do, also call some of the individual components.

When something is 'wrong' in my application, I need to trace/analyse which UC4 actions have/not occurred, and which workflows they belong to.

Now I 'think' that I have created a SQL process to gather all the required information... but it is a long, convoluted and batch driven process. 
When anything becomes so convoluted, I can't help but think that there must be an easier way !!

Thought I would throw the outline theory out here, to see if anyone can see an easier way of achieving this ?
  • (A) Identify all the components of my main Workflow by recursively walk JPP from the parent downwards, to collect all parent-child relations 
  • (B) Collect all entries from AH for each item in (A)
  • Recursively run (B) against AH again, to collect all the 'AH_ParentAct'  - getting the chain of Jobs that ran, ending in the top level Workflow that initiated each.
So I guess the question is ... does this logic seem 'reasonable' ?  Or is there a big hole that I have not spotted, or an easier way ?

Would be nice if the AH entries had a reference to point straight back to the exact JPP entry they related to. I did see an AH field [AH_AJPP_Lnr],  but was unable to join it with anything else that gave me decent results - but the fact that it is there makes me think that there must be a way ??

For the record, I am completely new to this system - but being a SQL-er, was asked to try and see what I could do !

Many thanks for any input :)