Auditing - IBM Cognos

IBM Cognos Platform provides a complete auditing capability that permits administrators to report on and manage system usage. The information logged by IBM Cognos Platform auditing can be used to support administrative requirements such as:

  • Capacity planning
  • Licensing conformance reporting
  • Performance monitoring
  • Identifying unused content

By default, system messages, errors, and other product details are logged to flat files that reside in the <c8_install>/logs directory. Although the information provides the ability to identify possible errors that have occurred in the environment, the information is volatile because of the versioning mechanism (that is, file rollover parameter in IBM Cognos Configuration). Even if the desired date range was still accessible, viewing or reporting on the data is complicated and difficult to manage. 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. In fact, a sample audit model is supplied that includes several sample reports to assist in providing immediate benefit from the audit data.

Configure the audit database

For the usage data to be captured in a relational source, an audit destination must first be configured in IBM Cognos Configuration:

  1. Open IBM Cognos Configuration.
  2. Within the Explorer pane, expand Environment, right-click Logging, and then click Resource Destination.
  3. In the New Resource – Destination dialog box, type a name (we used Audit in our example) and click Database as the type.
  4. Right-click the newly created Audit database and click New Resource Database.
  5. In the New Resource – Database dialog box, type the database name and, using the drop-down menu, click the type of database target (DB2, Informix, SQL Server, and so on).
  6. In the Explorer pane, click the newly created database name 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 Properties pane.
  7. Test the audit database connectivity either by right-clicking the newly created database name in the Explorer pane and then clicking Test or by clicking the new database name and then clicking the Test icon from the IBM Cognos Configuration toolbar.
  8. Save the configuration by clicking Save on the IBM Cognos Configuration toolbar.
  9. Start (or restart if already running) the IBM Cognos BI services by clicking the Start icon (or the Restart icon) from the toolbar.

Changes do not take effect until after the IBM Cognos BI services have been restarted. These steps define the JDBC connection that will be used to populate the audit database. During the start phase, the configuration change is identified, which prompts the application to create the necessary tables within the configured database.

Audit table definitions

After an audit database has been added to the configuration parameters in IBM Cognos Configuration, the audit database schema is added to the database the next time that the application is started. When the application starts, 18 tables are added to the audit database, but only 11 are used for auditing usage. 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.

IBM Cognos Audit database tables
IBM Cognos Audit database tables

Audit database table definitions
Audit database table definitions

Audit levels

The auditing facility within IBM Cognos BI provides five levels of detail. The levels start at minimal, which for the purposes of auditing means disabled, and at the end of the spectrum is full, which should only be used for troubleshooting purposes under the guidance of customer support. For collecting audit detail, the only choices are minimal(disabled) and basic (enabled). Request provides essentially the same level of detail as basic. Only use trace, similar to the full level, under the guidance of IBM Customer Support. Table shows IBM Cognos audit and logging levels.

IBM Cognos audit and logging levels
IBM Cognos audit and logging levels

Audit and logging for IBM Cognos BI services

The IBM Cognos BI architecture comprises various services. Each of these services has a configurable audit level. Assigning the correct level of audit detail to the appropriate service provides a customized view of usage data. For example, if only login information is desired, the only service that needs to have auditing enabled is the Content Manager Service. Understanding what functions are performed by each of the services provides greater insight into the audit detail that they record.

Setting audit levels

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.
  2. Click the Configuration tab, and then click Dispatchers and Services.
  3. On the Configuration pane of the Dispatchers and Services window, click the Set properties - Configuration icon on the main toolbar.
  4. Selecting the Configuration Properties to set audit levels
    Selecting the Configuration Properties to set audit levels

  5. When presented with the Set Properties dialog box, click the Settings tab.
  6. Filter the displayed settings to show only settings related to logging by clicking the Category drop-down menu, and then clicking Logging.
  7. Using the drop-down menus or check boxes, set the auditing level for each of the services that make up the IBM Cognos BI environment.
  8. After the levels have been specified for the desired services, click OK to save the new parameter values.

Because of the inheritance model, audit settings made at the top level will be pushed down to all dispatchers and services that make up the environment. This is the suggested way to set the audit settings, as typically all dispatchers should be recording the same level of detail. However, other unique situations might require that settings differ from the dispatcher, and in those cases, audit settings must be configured for each dispatcher individually.

To configure customized settings on a dispatcher, select the dispatcher by clicking the link with the dispatcher’s name. This reveals the list of services that make up the dispatcher. Repeat the steps above to set the individual auditsettings on the dispatcher. After you configure the customized settings for an individual dispatcher, examine the properties shows that the value changed from Yes to No.

Overriding the settings on a dispatcher breaks the inheritance model, and parameters with an acquired value of No will no longer be acquired from the parent configuration. To re- synchronize the parameters so that the values can be acquired:

  1. Select the dispatcher that you want to configure by clicking the dispatcher’s link in the Configuration pane.
  2. Click the Set properties – dispatcher icon.
  3. On the “Set properties – Dispatcher” dialog box, click the Settings tab.
  4. Change the drop-down menu to Logging, which filters the parameter list to only display the audit-related entries.
  5. Click the check box in the upper-left corner, which selects all of the logging configuration settings.
  6. Click the Reset to parent value link at the bottom-right corner of the window, which resets the parameter values to the parent configuration and resets the acquired values to Yes.
  7. Click OK to save the changes.

Alternatively, the configuration settings can be reset from the top level and pushed down to the dispatchers in the environment. To do this:

  1. Click the Set Properties - configuration icon on the main page of the Dispatchers and Services panel.
  2. Click the Settings tab.
  3. At the bottom of the dialog box, there is an option to delete the configuration settings of the children. This does not really delete them, it simply overwrites them with the current configuration settings from the global configuration. Select this option, and then click OK to save the changes .
  4. Option to reset all dispatcher configuration settings to the parent values
    Option to reset all dispatcher configuration settings to the parent values

Maintaining audit detail while troubleshooting

Among applications, there is a periodic need for troubleshooting. One of the troubleshooting mechanisms with the IBM Cognos BI application is through the IPF component, called IPF traces.

Enabling an IPF trace does not require a restart of the application and is enabled when a file called ipfclientconfig.xml is found in the <install_location>/configuration directory. Within that file, various levels of detail can be output to different sources.

By default, the master template file called ipfclientconfig.xml template is configured to continue recording regular audit detail to the audit database, providing that server logging configuration settings are correctly entered into the ipfclientconfig.xml file. Each IBM Cognos software component provides a sample IPF Client Trace file to be used when instructed by IBM Support, for example, ip fRSVP client config.xml.sample.

Example highlights the fields that are important to ensuring that audit logging is not interrupted during IPF trace activities. As long as the TCP connectivity parameters are correct and the audit level is set to warn, the IPF client trace functions and audit records continue to be logged to the audit database.

Example Important fields to ensure uninterrupted audit logging

Example highlights the TCP connection parameters and the important parameter for the audit level. Change the remote Host value and the Port value to match the log server host and port number in IBM Cognos Configuration. Change the appender reference within the <category name=”Audit”> section to match the Log Server Enable TCP value in IBM Cognos Configuration.

To verify whether clientRemote or clientTCP needs to be used as the appender-ref value, the parameters within IBM Cognos Configuration need to be examined. Selecting the Logging entry beneath the Environment section displays the logging parameters in the right frame. If the Enable TCP? Parameter is set to False, then the clientRemote must be used. If the value is set to True, the clientTCP will be the require entry in the IPF file.

Logging server port and TCP settings in IBM Cognos Configuration
Logging server port and TCP settings in IBM Cognos Configuration

Audit scenarios

In discussing audit scenarios, we apply audit functionality and walk through common scenarios to provide an overview of how IBM Cognos audit data can be used to provide a complete view of system activity.

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.

To demonstrate possible uses for the information stored within the IBM Congos audit database, we explore the table structures of the audit database and provide example queries to satisfy common audit requirements.

Tables within the IBM Cognos audit database can be joined to provide further information about user sessions, reporting activity, jobs, and so forth. Details about parameters used by IBM Cognos components can be obtained by query interactions with the COGIPF_PARAMETER table using COGIPF_REQUESTID.

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.

Authentication

Authentication is handled through the IBM Cognos Content Manager Service. Therefore, recording authentication-related detail requires auditing to be enabled for the IBM Cognos Content Manager Service. In the following scenario, auditing is set to minimal for all services except the IBM Cognos Content Manager Service. Logging into IBM Cognos Connection causes audit data to be written into two tables:

  • COGIPF_USERLOGON
  • COGIPF_ACTION

The primary information related to the user logon (that is, user name and authenticating namespace) is contained in the COGIPF_USERLOGON table, and secondary information such as group membership is recorded in the COGIPF_ACTION table. For example, John Walker (jwalker) logs into IBM Cognos Connection. Upon examining the COGIPF_USERLOGON table, we see a single entry for the login process .

Example Sample SQL query to retrieve user logon information

Figure shows the results of the query.

Results of a query showing the audit table row created for user John Walker’s logon

As shown in Figure, the user logging in and the time of the operation are recorded. The namespace that the user belonged to is also included, so it becomes possible to identify users from different business units if they are not part of the same security namespace.

The same login operation also records two audit entries in the COGIPF_ACTION table. The only record that is important from an audit standpoint is the record that queries the security namespace for the group membership of the user. (The complete entry is truncated in Example due to the length of the field.)

When users log out of the application, a single record is written to both the COGIPF_USERLOGON and COGIPF_ACTION tables.

User session expirations (the default passport timeout is 60 minutes of inactivity) are also indicated in the COGIPF_USERLOGON table.

Records are logged to the audit database, which shows logon operations and sessions expiring due to inactivity. The default inactivity timeout is 60 minutes. In a busy environment where many users are logging in, the records are not consecutive and therefore are hard to correlate. To identify corresponding entries, the records must be matched on COGIPF_SESSIONID.

SQL query using COGIPF_SESSIONID to select record for one session

Whereas tracking user authentication is crucial for identifying usage patterns and license management, the ability to track unsuccessful login attempts is critical for identifying unauthorized user access. Whenever an unsuccessful login attempt occurs, a record is written to the COGIPF_USERLOGON table .

An audit entry for a failed login attempt
An audit entry for a failed login attempt

Examining the COGIPF_ERRORDETAILS column reveals the true source of the failure. Figure shows a record from COGIPF_USERLOGON.

An audit entry for user logon attempts - both failed and successful
An audit entry for user logon attempts - both failed and successful

Example shows the value for the COGIPF_ERRORDETAILS column for an invalid logon attempt.

COGIPF_ERRORDETAILS column for an invalid logon attempt

Because the audit record only indicates a success or failure status, paying attention to the error details is important when trying to isolate unauthorized access to the application versus users incorrectly typing their passwords. In the case of incorrect passwords, the records are identical except for the error details

Example Error message

Viewing a saved report

Serving up saved content is the responsibility of the presentation service. Therefore, to just track saved report access, auditing for the presentation service must be enabled. In the following scenario, auditing is set to minimal for all services except the presentation service. Example shows the SQL query.

Example SQL query

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. All of this information is also available as part of a single record in the COGIPF_VIEWREPORT table. You can also determine the internal storeID of the report that was viewed, and using the operation type of VIEW indicates that saved content has been viewed, versus a report actually being executed.

The information that is missing is the ability to identify which user viewed the saved report output. By enabling auditing for the Content Manager service, it becomes possible to obtain that level of detail. Logging audit data for the Content Manager service is done by setting the logging level for the Content Manager service to basic.

Running the same report view query again with the additional auditing enabled reveals an entry in the COGIPF_USERLOGON table.

If the COGIPF_USERLOGON table is joined with the COGIPF_VIEWREPORT table on COGIPF_SESSIONID, it becomes possible to tie the report view with the correct user (Example).

Example Linking the report view with the correct user

Executing a simple report interactively

There are various items of detail that might be required when tracking report executions. The most basic requirement is tracking the fact that a report was executed. Because interactive reports are handled by the report service, enabling auditing on the report service at a basic level records individual report executions (Example).

Example SQL query

The SQL statement in Example provides the necessary information to obtain details such as the report name and where the report was executed from (COGIPF_REPORTPATH), the amount of time it took to execute in milliseconds (COGIPF_RUNTIME), the package that the report was executed against (COGIPF_PACKAGE), and the time of the execution (COGIPF_LOCALTIMESTAMP). The COGIPF_TARGET_TYPE contains various pieces of information that are all part of the same record.

For example, the type of object executed (report or analysis), the service that handled the request (report or batch report), and certain records indicate whether the action was from a series of steps involved in a prompted report. The sample audit package has all of this information contained in the same query item, but the package that was modified as part of the IBM System Management Methodology isolates each piece of information in its own query item.

In addition to these details, it also becomes possible to correlate the report execution to the BIBUS process that handled the execution. By examining the COGIPF_PROC_ID column, the BIBUS process ID (PID) can be obtained.

As part of the report execution audit detail, four records is written to the COGIPF_PARAMETER table. From an information standpoint, there are no useful parameter details for a report execution unless the internal object storied is required.

What is missing from this level of detail is the ability to determine who executed the report. For this to occur, auditing must be enabled for the Content Manager Service (Examples).

Example SQL query

Example SQL query

Joining the COGIPF_USERLOGON and COGIPF_RUNREPORT tables on COGIPF_SESSIONID provides a list of reports executed and the users who executed them (Example).

Example List of reports executed and the users who executed them

One of the major differences between viewing saved report output is that running a report interactively executes queries at the database layer. By enabling an auditing parameter called audit the native query for report service, it becomes possible to isolate the queries being sent to the reporting database.

Examining a sample from COGIPF_REQUESTSTRING shows that the SQL statement being used in the query is contained within the record detail

(Example).
Example Sample from COGIPF_REQUESTSTRING

To determine which user executed the SQL statement, join the COGIPF_NATIVEQUERY and COGIPF_USERLOGON tables on COGIPF_SESSIONID (Example).

Example Determining which user executed the SQL statement

Executing reports through a job

Depending on the steps contained within the job, various tables can be written to as a result of a job execution. This scenario details the audit entries that are recorded when a job is executed that contains two report steps.

Setting the audit level to basic for the job service provides a sufficient amount of audit data pertaining to the job execution to complete this task. Executing the job produces a lone record in the COGIPF_RUNJOB table (Example).

Example SQL query

Examining the audit entry provides information such as the path to the executed job, when it was executed, and how long it took to run in milliseconds (COGIPF_RUNTIME).

Query results showing records related to running jobs
Query results showing records related to running jobs

What is not provided are the details regarding the contents of the executed job. Because the job service is responsible for executing jobs, it is not the component responsible for executing the report steps. Therefore, it does not record specifics regarding the report
steps.

To capture that additional level of detail, logging for the batch report service must be set to Basic.

Executing the same job results in a single entry in the COGIPF_RUNJOB table and two
entries in the COGIPF_RUNREPORT table. There are two entries because there were two report steps contained within the job (Example).

Example SQL query

With auditing enabled at both the job service and batch report service levels, the information is there to determine that one job and two reports were executed. But further examination of the audit records indicates that there is no association between the job and reports. This is because the request IDs are different, and joining the tables on this field does not provide the necessary information (Figure).

Query results showing the two batch report service entries for the steps in our job
Query results showing the two batch report service entries for the steps in our job

The constant that ties these records together is the user that executed the job and reports. Even if the job was scheduled to run during off hours, the schedule object uses trusted credentials to authenticate the user. By enabling auditing at a basic level for the IBM Cognos Content Manager Service, the user authentication action is recorded (Example).

Example SQL query

The identifier of a user’s session is the session ID. Therefore, joining the three tables together on that column provides the necessary detail to see who executed the job and which reports were associated to that job. Figure is based on the job execution with contents – by user report that is available as part of the System Management Methodology content package.

Query results showing logon and logoff for job step execution
Query results showing logon and logoff for job step execution

Sample audit package

This section introduces the IBM Cognos Framework Manager model and DS Servlet.

IBM Cognos Framework Manager model

IBM Cognos Framework Manager is a model that is based on the audit detail contained within the audit database. Although the metadata is designed to provide a head start to interpret and analyze the usage detail, the IBM Cognos Framework Manager model can be modified to suit any particular need. Keep in mind that additional changes might cause the provided reports in the audit content package to fail when executed.

DS Servlet

Audit detail is captured as actions occur within the application. For example, when a user logs in and runs a report, the login event is recorded along with the report execution details. This allows activities to be traced, but what about the need to trace something that does not happen and therefore is not recorded? Specifically, there might be a desire to discover content that is not being used. The fact that a report is never accessed means that it will never appear in the audit files.

To obtain this information, an SDK application is provided with IBM Cognos BI that queries the IBM Cognos Content Manager component and provides a list of content. The information is returned in an XML format that can be consumed as a data source and can therefore be reported on.

Audit content package

This provides a summary of the default sample audit package that is provided with IBM Cognos BI, as well as the additional content that is part of the IBM Cognos System Management Methodology. Before any deployments are executed, perform an entire backup of the content store. Consult the IBM CognosAdministration and Security Guide documentation regarding the steps required to perform a content store backup, how to create data sources, and how to import content packages.

Sample audit reports

The audit package provided as part of the IBM Cognos BI samples contains various reports that are intended as a head start to begin the analysis of the usage data contained within the audit database. Additional information regarding the configuration and deployment of the audit reports can be found as part of the core product documentation.

Table shows reports available with the System Management Methodology.

reports available with the System Management Methodology.

Reports available with the System Management Methodology
Reports available with the System Management Methodology

In addition to the reports that are based on the sample audit package, reports have been created based on the Audit Extension SDK application (Table). The reports are located in the folder within the System Management Methodology folder.

Audit extension reports
Audit extension reports

Audit extension

The standard auditing features with IBM Cognos cover many aspects of operation. However, certain areas, such as the auditing of users and capability assignments, are not included. The aim of the audit extension application is to provide additional auditing for these areas. It currently covers the following areas:

  • Account audit
    An audit of all the user accounts that are found in all configured namespaces and certain properties of those accounts (basic details, portal pages, created and modified dates, and so on). This allows reporting on the IBM Cognos user base.
  • Content audit
    An audit of all the objects that exist in the main content store. This audit processes through the content store tree and logs all the objects (folders, reports, queries, and so on) that it finds. It logs the basic information (such as name, search path, object permissions, created and modified date) and certain details more specific to the item types (such as the specification XML of reports and queries, any saved parameter values applied to saved reports, and the details of report output versions).
  • Status audit
    An audit of the current state of a server and related dispatchers. For each dispatcher registered in the target system, the configuration and activity is logged, saving information such as time taken to connect, number of active processes, and request duration.

Usage

The application is managed through a web front end that allows the configuration of server and namespace information and can be used to turn on or off individual audit types for a given server. Audits can be initiated in three ways:

  • Using the management web interface
  • Using a web services call (that is, from Event Studio)
  • Using a simple URL/web form call

The results of each audit are logged to a database, and an IBM Cognos Framework Manager model is provided to help report the data.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Cognos Topics