Advanced Tips for Cognos Audit Data

Written by Rick Ryan | April 30, 2014

By Paul Hausser, Envisn, Inc.
In recent blogs we focused on the Cognos audit tables and the audit extensions. We made the point in one of these articles that working with the Cognos audit data was important in understanding the data itself and how it could be used. So we thought it might be appropriate to pass on some tips of things we learned along the way that may help you maximize the value in creating a set of auditing tools for Cognos BI.

Our experience here has been one of discovery, which along with some tedious work, has helped in learning not only how to use the audit data to its fullest but to combine it with other data from the Cognos Content Store to improve the BI admin solutions we offer. We hope you find these tips useful.

1. Use calculation on COGIPF_RUN_REPORT to determine if the run was Interactive or Batch:

IF ([COGIPF_TARGET_TYPE] like '%Batch%') THEN
('Batch')
ELSE
('Interactive')

Batch means it ran on a schedule or a job, which means it’s running in background. Interactive is where the user is waiting for the report to finish. Having this information is important if you want to determine what content is schedule driven versus user inquiry based (interactive).

2. Use this outer join to the link run/view tables with the logon table to get user info:

FROM COGIPF_RUN_REPORT RunReport
LEFT OUTER JOIN COGIPF_USERLOGON Logon
ON RunReport.COGIPF_SESSIONID = Logon.COGIPF_SESSIONID
AND Logon.COGIPF_LOGON_OPERATION = 'Logon'

This captures activity data and by linking it to the user session is able to identify the user.

3. Add filter to any COGIPF_RUN_REPORT query to remove prompting activity and eliminate double counting:

SQL Server: PATINDEX('%Prompt%', [COGIPF_TARGET_TYPE]) = 0
Oracle: NOT [COGIPF_TARGET_TYPE] LIKE '%Prompt%'

The issue of double counting occurs on reports with prompts. To get around this use this filter on your SQL query to eliminate double counting of report.

This next one is a bit more involved but it can also be very useful. It uses the actions table to determine when reports are created or updated from Cognos studios (report/query/analysis).

4. Use SQL Sub-Queries on the COGIPF_ACTION table to report on saves from Cognos Studios (Report, Query or Analysis).

This can be useful to verify Cognos Studio license compliance when also combined with a join to the COGIPF_USERLOGON table.

When a report is saved from a studio, multiple rows are written to the Cognos audit COGIPF_ACTIONS table. In one of these rows the Operation will be an ADD or UPDATE, and Target Type will be the Studio type. An additional row is written on the status of the Save operation.

By using a Sub-Query you can determine if that additional row was written to prove that the Add or Update occurred from a Cognos Studio:

SELECT
       Action.COGIPF_LOCALTIMESTAMP,
       Action.COGIPF_TARGET_PATH,
       (Case
              When Action.COGIPF_OPERATION = 'ADD' and
                     Action.COGIPF_TARGET_TYPE in ('REPORT','QUERY','ANALYSIS') and
              Exists (Select * from COGIPF_ACTION b
                           Where b.COGIPF_SUBREQUESTID = Action.COGIPF_REQUESTID and
                                  b.COGIPF_OPERATION = 'ReportAdd' and
                                  b.COGIPF_STATUS = 'Success')
              Then 'ReportAdd'

              When Action.COGIPF_OPERATION = 'UPDATE' and
                     Action.COGIPF_TARGET_TYPE in ('REPORT','QUERY','ANALYSIS') and
              Exists (Select * from COGIPF_ACTION b
                           Where b.COGIPF_SUBREQUESTID = Action.COGIPF_REQUESTID and
                                  b.COGIPF_OPERATION = 'ReportUpdate' and
                                  b.COGIPF_STATUS = 'Success')
              Then 'ReportUpdate'

              Else NULL End) As SUB_ACTION

              FROM COGIPF_ACTION Action WHERE

       (Case
              When Action.COGIPF_OPERATION = 'ADD' and
                     Action.COGIPF_TARGET_TYPE in ('REPORT','QUERY','ANALYSIS') and
              Exists (Select * from COGIPF_ACTION b
                           Where b.COGIPF_SUBREQUESTID = Action.COGIPF_REQUESTID and
                                  b.COGIPF_OPERATION = 'ReportAdd' and
                                  b.COGIPF_STATUS = 'Success')
              Then 'ReportAdd'

              When Action.COGIPF_OPERATION = 'UPDATE' and
                     Action.COGIPF_TARGET_TYPE in ('REPORT','QUERY','ANALYSIS') and
              Exists (Select * from COGIPF_ACTION b
                           Where b.COGIPF_SUBREQUESTID = Action.COGIPF_REQUESTID and
                                  b.COGIPF_OPERATION = 'ReportUpdate' and
                                  b.COGIPF_STATUS = 'Success')
              Then 'ReportUpdate'

Else NULL End) Is Not Null

These are all part of our UniVisn product.

What’s Wrong with IBM Cognos Audit Data?

View full post