Reporting logged parameter values

During the past few years, I have had - and still have - the privilege to administer one of the largest IBM Cognos BI 10.2.1 configurations in The Netherlands. The production configuration consists of 2 webserver/gateways servers, 10 dispatcher servers and 2 (active/passive) content manager servers. Each day, approximately 80,000 reports are requested and processed. Each month the system serves approx. 6,000 unique users. Substantial growth is anticipated.

One of the security requirements, as demanded by the client, is to log all report and job requests. This is achieved by enabling the out-of-the-box Cognos Audit Logging to a database (Oracle). See the BI Administration and Security Guide, Chapter 5, for more information on this topic.

In addition, my client not only wants to see who has run which report, but also which parameter (or prompt) values were used when running these reports. To enable parameter value logging, we added the Advanced setting “RSVP.PARAMETERS.LOG” value “TRUE” in Cognos Administration for the ReportService and BatchReportService of each requested dispatcher. This setting populates the Audit log table COGIPF_PARAMETER with records containing COGIPF_TARGET_TYPE = 'JobStepDefinition' (for job step request parameter values) or COGIPF_PARAMETER_NAME = 'parameterValues' (for report request parameter values). Note: this setting is not well known or documented! We found and applied the information from this IBM document.

After enabling parameter value logging, for report requests all parameter value information is stored in a CLOB column named COGIPF_PARAMETER_VALUE_BLOB. As you might have guessed, this is XML type information.

Now the challenge is: how to elegantly retrieve this information for presenting in our audit report?

We solved this puzzle by defining some XML functions this native SQL query in Report Studio:

select

       "t"."COGIPF_REQUESTID"       "REQUEST_ID"      ,

       "t"."COGIPF_SUBREQUESTID"    "SUBREQUEST_ID"   ,

       "t"."COGIPF_TARGET_TYPE"     "TARGET_TYPE"     ,

       "t"."COGIPF_PARAMETER_NAME"  "PARAMETER_NAME"  ,

       "t"."COGIPF_PARAMETER_VALUE" "PARAMETER_VALUE" ,

       a.*

from

       COGIPF_PARAMETER "t",

       xmltable(

             xmlnamespaces('http://developer.cognos.com/schemas/bibus/3/' as "bus"),

             '/parameterValues/item/bus:value/item/bus:use'

             passing xmltype(

                    --substitute missing XML by some empty XML to avoid syntax errors

                    case when length(cogipf_parameter_value_blob) > 0

                           then cogipf_parameter_value_blob

                           else to_clob('') end)

             columns

                    naam            varchar (250)   path './../../../bus:name',

                    usevalue        varchar(2500)   path '.' ,

                    displayvalue    varchar(2500)   path './../bus:display'  ) a

Explanation regarding the XML “path” as used in the column definitions above:

• the node “bus:use” is the referral point, as mentioned in '/parameterValues/item/bus:value/item/bus:use'
• column “naam” (Dutch for “name”) is retrieved from three levels up
• column “usevalue” is at the referral level
• column dispayvalue is at the same level, adjacent node

Next step is to feed it to a normal query, here it's named Query2:

Query2 contains the filter on JobStepDefinition and parameterValues (this could alternatively have been defined in the native query as well):

The filter on REQUEST_ID gets its parameter ?parRequestId? passed by an overview report drilling through to this parameter report.

The result is presented in a normal list object, containing the following columns and values per logged parameter:

Using this type of native query was key to extract the information we need in our log reports.

Please let me know in the comments below if this information was of any value to you!

PS Kudos to co-worker Fred Bouwhuis for sorting this technique out!