Extracting the 'Job Name' from 'Path' in the 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!

[dougp]

Using SQL Server I can do:

Code Select Expand

SUBSTRING(@a, CHARINDEX('jobDefinition[@name=', @a) + 21, CHARINDEX(']', @a, CHARINDEX('jobDefinition[@name=', @a)) - CHARINDEX('jobDefinition[@name=', @a) - 22)

If you want to restrict the function set to Cognos:

Code Select Expand

substring(substring([Audit].[Run Jobs].[Job name and path], position('jobDefinition[@name=', [Audit].[Run Jobs].[Job name and path]) + 21, 4000), 1, position(']', substring([Audit].[Run Jobs].[Job name and path], position('jobDefinition[@name=', [Audit].[Run Jobs].[Job name and path]) + 21, 4000)) - 2)

Both of these will have problems if there is a ] in your job name.

[rhythmz]

you are DA MAN!!! Works perfectly...

Thank you so very much for this valuable fix!