Skip to content

Instantly share code, notes, and snippets.

@matt40k
Created May 28, 2015 13:17
Show Gist options
  • Save matt40k/fe71408ef39e4fdd66a5 to your computer and use it in GitHub Desktop.
Save matt40k/fe71408ef39e4fdd66a5 to your computer and use it in GitHub Desktop.
Queries the Cognos BI Audit DB for the reports executed
SELECT
CASE
WHEN rr.[COGIPF_REPORTNAME] = 'null' THEN 'Unsaved report'
WHEN rr.[COGIPF_REPORTNAME] = '' THEN 'Unsaved report'
WHEN rr.[COGIPF_REPORTNAME] IS NULL THEN 'Unsaved report'
WHEN rr.COGIPF_REPORTNAME = 'adHocReport' THEN 'Ad-Hoc'
WHEN SUBSTRING(rr.[COGIPF_REPORTNAME], 1, 10) = 'reportView' then (SUBSTRING (rr.[COGIPF_REPORTNAME], 19, LEN (rr.[COGIPF_REPORTNAME]) - 20))
WHEN SUBSTRING(rr.[COGIPF_REPORTNAME], 1, 6) = 'report' then (SUBSTRING (rr.[COGIPF_REPORTNAME], 15, LEN (rr.[COGIPF_REPORTNAME]) - 16))
WHEN (SUBSTRING(rr.[COGIPF_REPORTNAME], 1, 5) = 'query') then (SUBSTRING (rr.[COGIPF_REPORTNAME], 14, LEN (rr.[COGIPF_REPORTNAME]) - 15 ))
ELSE 'Unsaved report'
END AS [REPORT]
,CASE
WHEN rr.[COGIPF_PACKAGE] = '' THEN ' '
WHEN rr.[COGIPF_PACKAGE] = 'null' THEN ' '
WHEN CHARINDEX('package', rr.[COGIPF_PACKAGE]) >0 THEN (SUBSTRING (rr.[COGIPF_PACKAGE], (CHARINDEX('package', rr.[COGIPF_PACKAGE]) +15), (CHARINDEX(']', (SUBSTRING (rr.[COGIPF_PACKAGE], (CHARINDEX('package', rr.[COGIPF_PACKAGE]) +17), 500))))))
ELSE (SUBSTRING (rr.[COGIPF_PACKAGE], 1, LEN(rr.[COGIPF_PACKAGE])))
END AS [PACKAGE]
,rr.[COGIPF_TARGET_TYPE]
,ul.[COGIPF_USERID]
,rr.[COGIPF_LOCALTIMESTAMP]
,rr.[COGIPF_SESSIONID]
,rr.[COGIPF_RUNTIME]
FROM
[Audit].[COGIPF_RUNREPORT] rr
INNER JOIN [Audit].[COGIPF_USERLOGON] ul on
rr.[COGIPF_SESSIONID] = ul.[COGIPF_SESSIONID]
WHERE
ul.[COGIPF_LOGON_OPERATION] = 'Logon'
AND ul.[COGIPF_STATUS] = 'Success'
AND rr.[COGIPF_REPORTNAME] != ''
AND rr.[cogipf_status] = 'Success'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment