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!
Using SQL Server I can do:
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:
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.
you are DA MAN!!! Works perfectly...
Thank you so very much for this valuable fix!