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:
- 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
- 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 -
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:
- 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.
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.
|
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.
|
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.
