Queries the Cognos BI Audit DB for the reports executed

CognosBI_Audit_Reports

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'