extracting Job Name from 'Job Path' in Audit Package

[rhythmz]

Hi all...

I am currently building a directory of audit reports to gain better insights into user activity. One of my current challenges is building a 'Failed Jobs' report.

However, the audit package does not contain a data item of just the Job Name like you see with Report Name in [RUNREPORTS].[REPORTNAME] namespace. In the RUNJOBS namespace, we are only given the path in which the job is located (which conveniently contains the Job Name):

/content/folder[@name='Credit Approval Group']/folder[@name='Portfolio Management']/folder[@name='Debt Reports']/jobDefinition[@name='zz_CAG Post-Purchase Rpt Daily Job']

While I know how to present the path as:

Credit Approval Group / Portfolio Management / Debt Reports / zz_CAG Post-Purchase Rpt Daily Job

I would like to create a column, using the example above, for just Job Name:

zz_CAG Post-Purchase Rpt Daily Job

Since the string length for Job name & path can vary, I have hit a wall in correctly using TRIM, SUBSTR, REGEX, or even REPLACE.
Excuse my ignorance if there is a simple solution, but I am experiencing a mental block arriving at a solution....HELP?!?!?

Thanks for ANY help you can give!

[Andrei I]

Try this Query Calculation:

Code Select Expand


substring(
  [Job name and path]
, position (   'jobDefinition[@name=', [Job name and path] )   +  character_length ( 'jobDefinition[@name=' ) +1
,   character_length ( [Job name and path] )
        -  position (   'jobDefinition[@name=', [Job name and path] )  -  character_length ( 'jobDefinition[@name=' )  - 2
)