Enable Auditing in Multitenant Environment of IBM Cognos 10.2 BI

In today’s scenarios where many ISVs and partners are willing to provide ‘Analytics as a service’, they need a Business Intelligence platform which provide multitenancy environment with auditing/logging capabilities yet easy to manage. The IBM Cognos platform provides complete auditing capabilities that enable auditing and managing system usage in multitenancy environment. The information logged by IBM Cognos Platform auditing can be used to support administrative requirements such as:

• Capacity planning

• Planning down time by identifying quiet periods.

• Justifying additional infrastructure requirements.

• Tenant specific usage and activity tracking

• Support for Pay-as-use model

• Licensing conformance reporting

• Performance monitoring

• Identifying unused content

Multitenancy provides the capability to support multiple customers or organizations (tenants) by using a single deployment of an application, while ensuring that the users belonging to each tenant can access only the data that they are authorized to use. Such applications are called multi-tenant applications. IBM Cognos Business Intelligence (BI) provides capabilities that make it easier to administer and secure multi-tenant applications at the same time minimize the extra costs associated with these environments.

After multi-tenancy is enabled, you can record tenant activities using an audit logging database. IBM Cognos Business Intelligence provides sample audit reports that show how to use the tenancy information to monitor certain user activities. Since the tenants are now sharing the same application deployment, it is important to consider how to separate the log files for each tenant so that a tenant can only view the logging messages that were generated by its own executions. Separating out the trace files and log files by tenant helps the administrators troubleshoot issues that are specific to a certain account.

This article describes the step by step procedure to -

1)     Setting up Audit reporting environment

2)     Working with sample model & reports for customized auditing

Setting up Audit reporting environment

However sample audit reports that come with IBM Cognos software can be setup and used without enabling multitenancy also. We’ll set up Cognos 10.2 BI multitenancy environment here because our focus is to make tenant specific audit & log information available to them. If you are new and need help in setting up multi-tenant environment with Cognos 10.2, check out my blog -

Setting up multi-tenancy environment in IBM Cognos 10.2 BI using LDAP

 
The IBM Cognos services send information about errors and events to a local log server. Use the data contained in the default log files primarily for troubleshooting and not for tracking usage. IBM Cognos BI provides the ability to output usage information to a relational database. With the usage audit data stored in a relational data source, reporting then becomes possible.

1) Configure the audit database –

Open ‘IBM Cognos Configuration’ (Start -> All Programs -> IBM Cognos 10 (‘IBM Cognos 10 – 64’ in case of 64-bit installation).  In the Explorer pane, expand Environment, right-click Logging, and then click New Resource -> Destination. Type a name (we used AuditDBCon here) and click Database as the type.

Right-click the newly created AuditDBCon database and click New Resource -> Database. In the dialog box, type the database name (COGAUDIT in our case) and using the drop-down menu, click the type of database target (DB2 in our case).

You can choose among DB2, Informix, Oracle, Microsoft SQL Server and Sybase. The auditing database like content store is populated via a JDBC connection by the Content Manager Service so ensure that the appropriate JDBC drivers are available/copied in “<C10_install>\webapps\p2pd\WEB-INF\lib” folder.

In the Explorer pane, click on COGAUDIT and type the necessary parameters, such as database host name and port number, database login credentials, and the database name, into the fields in the ‘Resource Properties’ pane.

Test the audit database connectivity by selecting COGAUDIT and clicking the Test icon from the IBM Cognos Configuration toolbar.

If successful then save the configuration and restart Cognos services from toolbar.

2) During the start phase, the configuration change is identified, which prompts the application to create the necessary tables within the configured database (Administrator schema in GS_DB database in this case). When the service starts, 21 tables are added to the audit database. To avoid name conflicts with database keywords, all tables and column names in the database have the prefix "COGIPF". If you don’t find these tables, please check cogserver.log to for errors.

3) Set Logging Levels –

There are four report validation levels and five logging levels. The following table shows the correspondence between them.

Report validation level	Logging level
Error	Minimal, Basic
Warning	Request
Key Transformation	Trace
Information	Full

 
The higher you set the logging level, the more it degrades system performance. Normally, you set the level to Minimal or Basic to collect errors, or to Request to collect errors and warnings.

The following table indicates the details that each logging level logs.

Setting the audit levels is done through the dispatchers and services task in the administration console in IBM Cognos Connection:

1. From within IBM Cognos Connection, click Launch -> IBM Cognos Administration to launch the IBM Cognos administration console.

1. Click the Configuration tab, and then click Dispatchers and Services.

1. On the Configuration pane of the Dispatchers and Services window, click the Set properties - Configuration icon on the main toolbar.

4. When presented with the Set Properties dialog box, click the Settings tab.

5. Filter the displayed settings to show only settings related to logging by clicking the Category drop-down menu, and then clicking Logging.

6. From the Value menu, set the auditing level for each of the services that make up the IBM Cognos BI environment. If you want to create audit reports that include the queries that are run against your reporting data source, you must enable native query logging. You can use native query logging to learn what kinds of information users want or whether a report is running efficiently.

7. After the all 33 levels have been specified for the desired services, click OK to save the new parameter values.

Create data source connections and import audit reports

The database used to record audit information for IBM Cognos BI (GS_DB in our case) can also be used as a reporting data source for system administrators. IBM Cognos BI can be used to create reports to show information from the audit database and provide insight into what is happening on the entire IBM Cognos Platform. IBM provides sample reports to be used for various auditing scenarios. Given that the audit information for IBM Cognos BI is stored in a relational database, administrators can also use SQL queries to get a detailed view of system activities.

1) Create a data source connection to the logging database from Cognos Administrator -> Configuration Tab -> Datasource Connections -> New Data Source. The logging database and data source in IBM Cognos Connection must be named ‘Audit’.

2) If you are using the default application server (Tomcat) that is provided with IBM Cognos BI, then in a text editor, open the web.xml file located at c10_location\webapps\p2pd\WEB-INF, and add the following XML fragment:

<servlet>

      <servlet-name>DSServlet</servlet-name>

   <servlet-class>com.cognos.demo.DSServlet</servlet-class>

</servlet>

<servlet-mapping>

      <servlet-name>DSServlet</servlet-name>

      <url-pattern>/cognos/DSServlet.jsp</url-pattern>

</servlet-mapping>

Note that the url-pattern value can be anything you choose.

3) If you are using an application server other than Tomcat, or if Content Manager and Application Tier Components are installed in separate locations, add the XML fragment from step 1 to the following files:

·                         c10_location\webapps\p2pd\WEB-INF\web.xml.noCM

·                         c10_location\webapps\p2pd\WEB-INF\web.xml.withCM

4) If you do not have the following directory on your system, create it:

c10_location\webapps\p2pd\WEB-INF\classes\com\cognos\demo.

5) Copy the file build.bat for Microsoft Windows operating system or build.sh for UNIX operating system located in c10_location\webapps\Audit to c10_location\webapps\p2pd\WEB-INF\classes\com\cognos\demo.

6) Edit the build file to ensure the JAVA_HOME definition points to your JDK and ensure the CRN_HOME definition points to your IBM Cognos location.

7) If it is not already there, copy the DSServlet.java file from the c10_location\webapps\Audit directory to c10_location\webapps\p2pd\WEB-INF\classes\com\cognos\demo.

Do one of the following in the DSServlet.java file:

·         If you are allowing anonymous logon, comment out the following line: binding.logon(...)

·         If you are not allowing anonymous logon, make sure that the username, password, and namespace are correct and uncomment the following line: binding.logon(...)

At a command prompt, run build.bat or build.sh from c10_location\webapps\p2pd\WEB-INF\classes\com\cognos\demo to compile the Java source file into the class file.

8) Restart IBM Cognos services. If you are using an application server other than Tomcat, rebuild the application file and then redeploy IBM Cognos BI to the application server.

9) Create a data source connection named url_xml to the XML data source. In the Connection string field, enter the connection string. If you used the defaults, the connection string is http://localhost:9300/p2pd/cognos/DSServlet.jsp. Click OK.

10) Before you can use them, you must set up the sample audit reports. The default location is c10_location/webcontent/samples/content/IBM_Cognos_Audit.zip. Copy the file to c10_location/deployment, and then import the sample IBM_Cognos_Audit.zip from Cognos Administrator -> Configuration Tab -> Content Administration -> New Import.

11) In IBM Cognos Connection, click Public Folders > Samples_Audit > Audit, and click the audit report that you want to run. The Multi-tenancy reports folder contains the sample reports for a multi-tenant environment. Depending on the audit report that you select, you are prompted for report criteria.

Working with sample model & reports for customized auditing

The database used to record audit information for IBM Cognos BI can also be used as a reporting data source for system administrators. IBM Cognos BI can be used to create reports to show information from the audit database and provide insight into what is happening on the entire IBM Cognos Platform. IBM provides sample reports to be used for various auditing scenarios. Given that the audit information for IBM Cognos BI is stored in a relational database, administrators can also use SQL queries to get a detailed view of system activities.

1) To design your own auditing model and reports you need to know audit tables in details. Here is the brief detail -

Table Name	Description
COGIPF_ACTION	Stores information about operations performed on objects
COGIPF_AGENTBUILD	Stores information about agent mail delivery
COGIPF_AGENTRUN	Stores information about agent activity including tasks and delivery
COGIPF_ANNOTATIONSERVICE	Stores audit information about Annotation service operations
COGIPF_EDITQUERY	Stores information about query runs
COGIPF_HUMANTASKSERVICE	Stores audit information about Human Task service operations (tasks and corresponding task states)
COGIPF_HUMANTASKSERVICE _DETAIL	Stores additional details about Human Task service operations (not necessarily required for every audit entry, for example, notification details and human role details)
COGIPF_NATIVEQUERY	Stores information about queries that IBM Cognos software makes to other components
COGIPF_PARAMETER	Stores parameter information logged by a component
COGIPF_RUNJOB	Stores information about job runs
COGIPF_RUNJOBSTEP	Stores information about job step runs
COGIPF_RUNREPORT	Stores information about report runs
COGIPF_THRESHOLD _VIOLATIONS	Stores information about threshold violations for system metrics
COGIPF_USERLOGON	Stores user logon and logoff information
COGIPF_VIEWREPORT	Stores information about report view requests

The COGIPF_SYSPROPS table contains a single record that indicates logging version detail. The COGIPF_MIGRATION table is reserved for an upcoming migration application, and the COGIPF_THRESHOLD_VIOLATIONS records metric threshold exception details that are derived from the IBM Cognos BI system metrics.

Logging into IBM Cognos Connection causes audit data to be written into two tables:

a. COGIPF_USERLOGON (consists of TENANTID as a column)

b. COGIPF_ACTION

Details about user sessions, logons, security events, and so on can be obtained by query interactions with the COGIPF_USERLOGON table using COGIPF_SESSIONID. Detailed information about jobs and job steps can be obtained from the COGIPF_RUNJOB and COGIPF_RUNJOBSTEP tables using COGIPF_REQUESTID.

By joining the COGIPF_VIEWREPORT and COGIPF_PARAMETER tables on COGIPF_REQUESTID, additional information can be obtained, such as the package used and the format in which the report was viewed.

2) To work with audit tables you may want to build a model from scratch or use provided sample audit model to start with and change it to suit your requirements. You can get this sample model in “c10_location/webcontent/samples/models/Audit/Audit.cpf” location. Let us start with it.

Open ‘IBM Framework Manager’ (Start -> All Programs -> IBM Cognos 10).  Open the project using Audit.cpf file. Notice the query subjects under ‘Audit’ namespace, two data sources url_xml & audit and a package named ‘Audit’ in Project viewer pane. In properties pane, I have set properties based on my DB2 audit database (GS_DB). You can also analyze the relationships between the query subjects and test sample scenarios here. All audit reports are based on this model. You may want to change and republish ‘audit’ package as per your reporting requirements.

Keep in mind that additional changes might cause the provided reports in the audit content package to fail when executed.  

3) Here we’ll make all audit reports available to all tenants but tenants would be able to see the data specific to them not for others. Query subject COGIPF_USERLOGON had TENANT_ID as a query item. Open its definition window by double clicking on query subject. Add a new filter from ‘Filter’ tab by adding “[Audit].[COGIPF_USERLOGON].[TENANTID] = #sq($tenantID)#” as expression. Tenant ID relevant for your multi-tenant environment can be found in “Session Parameters” from Parameters tab.

From ‘Test’ tab you can test the results. Save the changes and re-publish ‘Audit’ package on Cognos connection. Now login as ‘user1’ and open report ‘Logon operation by tenant’. Notice that only tenant with ID no. 1 is available in list box. On submission all login & logoff can be seen for user1 only.

Similarly all tenants can see log and audit details for their activities and usage. 

4) Sample audit reports which we imported in previous section can also be changed to suit your auditing requirements. If you have changed model as shown in previous step using Framework Manager, you can add/update existing entries in audit reports. They can also be copied, renamed and customized for tenants by using TENANTID. Here’s the detail about default sample reports -

Audit report name	Description
Agent execution history by user	Lists agent execution history by user and date and time range and includes a bar chart. It also includes the total number of times each agent was executed and the total number of agents that were executed. You can select a date and time range.
Daily average and poor exceptions - all services	Shows how to monitor daily average and poor exceptions of thresholds set in IBM Cognos Administration for all services using an agent. An email with attached report output is sent to the administrator when average and poor exceptions occur. To run this report properly, you must first set thresholds in IBM Cognos Administration (see System Performance Metrics). To receive an email, you must specify a mail server account. For more information, see the IBM Cognos Business Intelligence Installation and Configuration Guide For more information on setting thresholds in IBM Cognos Administration, see System Performance Metrics.
Daily metric exceptions	Lists daily metric exceptions for all services.
Execute reports by package and report	Lists the reports that were run, by package. It also includes the user, timestamp, and execution time in milliseconds for each report. You can select a date and time range, one or more users, one or more packages, and one or more reports.
Execute reports by user	Lists the reports that were run, by user and by package. It also includes the timestamp and execution time in milliseconds for each report. You can select a date and time range, one or more users, one or more packages, and one or more reports.
Execution history by user	Lists the reports that were run alphabetically, along with the package and timestamp, by user, since the logging database was created. It includes the total number of reports each user ran and the total number of times each user ran each report. It also includes the total number of reports run by all users. You can select one or more users for the report. After you run the audit report, you can choose to view the statistics for a particular report or for all reports.
Failed report executions - by package	Lists report failure executions by package and includes a pie chart, which also shows the failed percentage of each package.
Failed service requests detect agent - all services	Detects preset thresholds for service request failures that are exceeded. An email is sent to the administrator with service failure metrics information. The report Service requests metrics - day report is run. To run this report properly, you must first set thresholds in IBM Cognos Administration (see System Performance Metrics). To receive an email, you must specify a mail server account. For more information, see the IBM Cognos Business Intelligence Installation and Configuration Guide.
Logon operations by time stamp	Shows logon and logoff timestamps and operations, by user. It also includes the total number of logons and the total number of logons for each user. You can select the time period and one or more users for the report.
Logon operations by user name	Shows logon and logoff timestamp by user, along with the type of logoff operation that occurred. It includes the total number of logons and the total number of logons for each user. You can select one or more users for the report.
Migration exceptions	A list report shows exceptions for migration tasks.
Operations by selected object and users	Shows the operations that are performed on target objects, by user. It includes the target object path, timestamp, and the status of the operation. You can select one or more objects, operations, or users for the report.
Report execution history (detailed report)	Lists reports alphabetically along with the associated package and the timestamp for each time the report was executed. It also shows the total number of times each report was executed and the total number of reports that were executed. It also includes a color-coded pie chart that gives an overview of how often the reports are used.
Report execution and user logon history	This active report displays the report execution history and user logon information for a specified period of time.
Report execution history (summary report)	Lists reports alphabetically along with the timestamp for each time the report was run since the logging database was created.
Report usage	Lists reports by frequency of use. For each report, it lists the user and the number of times it was run by the user since the logging database was created. This report can help you determine if there are any reports that are not being used. If so, you may want to remove them.
Service requests metrics - day report	Shows percentage of successful and failed requests for IBM Cognos services for the current day. Includes a bar chart.
User session - abnormal termination	Shows logon date and time of abnormally terminated user sessions. It also includes a total of session termination for all dates. You can select a date and time range.
User session - details	Shows user session details, including the logon time, logoff time, logoff operation, and session duration. It also includes the total amount of session time for each user and the total amount of session time for all users. You can select a date and time range and one or more users.
User session - logon errors for past 30 days chart	This audit report shows a bar graph of logon failures for the past 30 days.
User session - summary	This audit report shows the average session duration by user. It also shows the total average session duration by user. You can select a date and time range and one or more users.

These reports can be found under ‘Multi-tenancy reports’ folder:

Execute reports by tenant	Lists the tenant IDs and tenant users. This report provides package, report, and time stamp information.
Logon operations by tenant	Lists the logon actions for each tenant ID and provides the total number of logons for each user and tenant ID.
Report execution history by tenant	Lists the executed reports, timestamps, and the associated package names for a tenant. This report provides a summary of total activity and the report can by filtered for a specific tenant.
View reports by package and report	Lists users, reports, timestamps, and packages for the tenant that you select.

For more information, see the IBM Cognos Business Intelligence Administration and Security Guide.