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
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.
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).
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|
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:
- From within IBM Cognos Connection, click Launch -> IBM Cognos Administration to launch the IBM Cognos administration console.
- Click the Configuration tab, and then click Dispatchers and Services.
- On the Configuration pane of the Dispatchers and Services window, click the Set properties – Configuration icon on the main toolbar.
- When presented with the Set Properties dialog box, click the Settings tab.
- Filter the displayed settings to show only settings related to logging by clicking the Category drop-down menu, and then clicking Logging.
- 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.
- 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’.
1) Before you can use them, you must set up the sample audit reports. The default location isc10_location/webcontent/samples/content/IBM_Cognos_Audit.zip. Copy the file toc10_location/deployment, and then import the sample IBM_Cognos_Audit.zip from Cognos Administrator -> Configuration Tab -> Content Administration -> New Import.
2) 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 –
|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:
- COGIPF_USERLOGON (consists of TENANTID as a column)
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.
|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.
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 –
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.|