Skip to content

Instantly share code, notes, and snippets.

@matt40k
Created May 28, 2015 13:18
Show Gist options
  • Save matt40k/f366c3e1db8490f82b94 to your computer and use it in GitHub Desktop.
Save matt40k/f366c3e1db8490f82b94 to your computer and use it in GitHub Desktop.
Queries the Cognos BI Audit DB for the QueryStudio executions
SELECT
CASE
WHEN eq.[COGIPF_PACKAGE] = '' THEN ' '
WHEN eq.[COGIPF_PACKAGE] = 'null' THEN ' '
WHEN CHARINDEX('package', eq.[COGIPF_PACKAGE]) >0 THEN
(SUBSTRING (eq.[COGIPF_PACKAGE], (CHARINDEX('package', eq.[COGIPF_PACKAGE]) +15), (CHARINDEX(']', (SUBSTRING (eq.[COGIPF_PACKAGE], (CHARINDEX('package', eq.[COGIPF_PACKAGE]) +17), 500))))))
ELSE (SUBSTRING (eq.[COGIPF_PACKAGE], 1, LEN(eq.[COGIPF_PACKAGE])))
END AS [PACKAGE]
,eq.[COGIPF_TARGET_TYPE]
,ul.[COGIPF_USERID]
,eq.[COGIPF_LOCALTIMESTAMP]
,eq.[COGIPF_QUERYNAME]
,eq.[COGIPF_RUNTIME]
FROM
[Audit].[Audit].[COGIPF_EDITQUERY] eq
INNER JOIN [Audit].[COGIPF_USERLOGON] ul on
eq.[COGIPF_SESSIONID] = ul.[COGIPF_SESSIONID]
WHERE
eq.[COGIPF_STATUS] = 'Success'
AND ul.[COGIPF_LOGON_OPERATION] = 'Logon'
AND ul.[COGIPF_STATUS] = 'Success'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment