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:
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:
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
Audit database table definitions
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
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:
Selecting the Configuration Properties to set audit levels
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:
Alternatively, the configuration settings can be reset from the top level and pushed down to the dispatchers in the environment. To do this:
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
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 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:
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
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
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).
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
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
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
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
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.
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
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
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:
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:
The results of each audit are logged to a database, and an IBM Cognos Framework Manager model is provided to help report the data.
IBM Cognos Related Interview Questions
|Microstrategy Interview Questions||IBM Cognos Interview Questions|
|IBM DB2 Interview Questions||Data Warehousing Interview Questions|
|SQL Interview Questions||IBM Cloud Computing Infrastructure Architect V1 Interview Questions|
|Framework7 Interview Questions||IBM BPM Interview Questions|
|IBM Cognos TM1 Interview Questions||Cognos ReportNet (CRN) Interview Questions|
|Structured Query Report (SQR) Interview Questions||IBM DataPower Interview Questions|
Ibm Cognos Tutorial
Introduction To Ibm Cognos Business Intelligence
Overview Of The Ibm Cognos Business Intelligence Architecture
Business Scenario And Personas Used In This
Create Reporting Packages With Ibm Cognos Framework Manager
Business Intelligence Simplified: An Overview
Individual And Collaborative User Experience
Self Service Interface For Business Users
Actionable Analytics Everywhere
Enterprise Ready Performance And Scalability
Ibm Cognos System Administration
Integrating Ibm Cognos Bi With Ibm Cognos Business Analytics Solutions
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.