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!
Try this Query Calculation:
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
)