Introduction

Purpose

The standard auditing features that come out of the box with IBM Cognos Analytics cover many aspects of operation. However, some areas such as the auditing of users and capability assignments are not included. The aim of the c11AuditExtension application is to provide additional auditing for these 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 etc.). This allows reporting on the IBM Cognos user base and provides additional information to go with the role/capability audit. This type of audit will also by default record the content of users’ My Folders.

Content Audit

An audit of the objects that exist in the main Content Store. This audit will process through the content store tree and log all the objects (folders, reports, queries, etc.) that it finds. It will log the basic information (such as name, search path, object permissions, created and modified date), as well as some 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).

In order to also record the items in the Content Store that are located inside individual users’ My Folders areas, this audit type should be used in conjunction with the Account Audit (see above).

Role/Capability Audit

An audit of all capabilities configured in the Cognos namespace (such as report authoring) and which roles, groups and users have been assigned access to those capabilities. Where a role or group is assigned access, the audit will log all the individual users that make up the role or group, so it is possible to accurately determine which individual users have access to a given capability. Note that if you wish to produce a basic license audit report, Cognos Analytics now contains a license management function, see: https://ibm.biz/calicensemgr.

Usage

The application is managed via 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:

  • via the management web interface
  • via a simple URL/web form call
  • via a web services call.

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

Applicability

This application is designed to be run against IBM Cognos Analytics 11. It is also intended to interact with any third party application that can issue commands via web services.

Exclusions and Exceptions

This application may not be compatible with all Custom Authentication Providers, depending on the implementation of the provider – for example, it generally expects a provider/namespace to require a simple username and password to log on – where a custom provider uses some other form of credential, the audit extension may be unable to authenticate to that namespace to audit it. This does not affect trusted signon type custom providers, providing that the underlying provider can be authenticated to in the normal way.

The databases this application is known to work with are:

  • IBM DB2 10.x & 11.x
  • MS SQL Server 2008 and greater
  • Oracle 11g (including RAC environments)

This application will only work within a JRE version 1.8 or above.

Usage, support and feedback

It is recommended that this application be used as part of an IBM services package to ensure successful implementation and interpretation of the results.

The application and model are provided on a strictly “as is” basis and IBM Support is not able to offer any support for it. However, any feedback, bug reports or suggestions are welcomed.

Process overview and architecture

The application is a web application and web service written in Java/AXIS and is intended to be installed on a machine where IBM Cognos Analytics Server has been installed. The application will make use of the IBM WebSphere Liberty application server that is installed with IBM Cognos Analytics.

After installation, the application will create its own database tables if they do not already exist and present an interface to allow the administrator to enter the details of IBM Cognos Analytics servers and namespaces. Generally only one server entry would be used for each group of IBM Cognos Analytics servers that makes use of the same Content Store. However, separate server entries are often used for different functional groups such as Production and Development.

The application can be secured from the management interface by defining a local password which will subsequently be required to access the interface or run audits. The reason a local password is used for this rather than utilising IBM Cognos Analytics security is that the application can interact with multiple IBM Cognos Analytics installations, so there is no single IBM Cognos Analytics security namespace to tie it to.

When the administrator enters the details of a new Cognos Dispatcher, the application will connect to that Dispatcher and gather details of the configured security namespaces. These details will be added to the properties page for that Dispatcher and made ready for editing. It is essential that an entry exists, complete with valid login details, for every namespace that is used for object security or capability assignment in the Content Store so that they can be audited. This is because the application needs to be able to authenticate to the namespace in order to audit its contents. If the application cannot authenticate to a namespace that is used for object security or users, then its objects cannot be audited. If multiple namespaces are specified in a single server entry, authentication to all namespaces must be made otherwise the application will terminate the audit run.

Namespace login details will be encrypted and stored in the application database.

Installation

The application is deployed as a WAR (Web Archive) file that can be used with any application server such as IBM WebSphere or servlet container such as Tomcat. You must first build the WAR file within your IBM Cognos Analytics installation using the supplied scripts and then deploy the WAR file to your server.

The procedures outlined in this document cover deploying the application into the IBM WebSphere Liberty application server that is installed with IBM Cognos Analytics. Consult the documentation of the specific application server/servlet container for instructions on deploying a WAR file into alternative destinations.

Generally speaking, it is desirable for a production environment to run this application within its own instance of IBM WebSphere or Tomcat. Please refer to the documentation of IBM WebSphere, Tomcat or other application server for details on how to deploy a web application.

The process for installation is as follows:

  • Unpack the installation to an IBM Cognos Analytics server
  • Customise any files that you wish to modify
  • Import any third party JDBC drivers that you intend to use
  • Build the WAR file
  • Deploy the WAR file to an application server or servlet container
  • Configure the application using the web user interface.

Unpack the installation and copy to an IBM Cognos Analytics server

Unpack the zip file containing the Audit Extension application to a suitable temporary location. This contains two main folders:

reporting

This folder contains materials to allow you to report on the Audit Extension output in IBM Cognos Analytics and contains a Framework Manager model and an IBM Cognos Analytics deployment archive. You do not need to do anything with this at this stage.

build-war

This folder contains the application itself, in a subdirectory named cognos.analytics.war/AuditExt. You need to use this to build the WAR file.

contents of distribution zip file contents of distribution zip file Figure 1: contents of distribution zip file

From the cognos.analytics.war folder, copy the AuditExt directory and its subdirectories to your Cognos Analytics installation directory under the directory <c11install>/war.

AuditExt content extracted to war directory Figure 2: AuditExt content extracted to war directory

At this point, you can customise the application before building the WAR file. The default settings should be fine in most cases but if you need to make any changes to the configuration settings in the c11AuditExtension.properties file or the logging settings in the log4j.properties file, the files are located in the <c11install>/war/AuditExt/classes directory.

Location of .properties files that can be modified Figure 3: Location of .properties files that can be modified

Install any required JDBC drivers

Any driver jar files that have been placed in the <c11install>/drivers/ directory will be automatically included in the application when you build the WAR file. You should obtain the correct JDBC driver files (for DB2, Microsoft SQL Server or Oracle) and place them in the drivers directory before proceeding to the next step.

If you do not install the correct driver at this stage, the application will warn you when you attempt to first configure the database. If you later add the jar files to the drivers directory then you will need to re-build the WAR file.

Build and deploy the WAR file

Build the WAR file by running the <c11install>/war/AuditExt/build.bat (Windows) or <c11install>/war/AuditExt/build.sh (UNIX/Linux) script. The method to deploy the WAR will depend on the installation target used.

build script location under war directory Figure 4: build script location under war directory

output of build script (Windows platform) Figure 5: output of build script (Windows platform)

After the script has executed, you should see two new directories created under the <c11install>/war/AuditExt/ location: wlpdropins and war. These contain the files needed to deploy to WebSphere Liberty and other application servers respectively.

war and wlpdropins directories created after build script execution Figure 6: war and wlpdropins directories created after build script execution

Deploy to IBM WebSphere Liberty supplied with IBM Cognos Analytics

The build script will have created an unpacked WAR directory under the wlpdropins subdirectory. This is ready to be copied or moved to the <c11install>/wlpdropins/ directory.

AuditExt.war directory ready to be copied
Figure 7: AuditExt.war directory ready to be copied

AuditExt.war directory copied Figure 8: AuditExt.war directory copied

Re-start the IBM Cognos Analytics service. Once restarted, the application is ready to be configured from the web-based UI. For the remainder of this document, references to <AuditExtDir> refer to <c11install>/wlpdropins/AuditExt.war.

Deploy to Tomcat or other application servers

The build script will have placed a WAR file under the war subdirectory. This is ready to be deployed to an application server or Tomcat in the usual way. Please refer to the documentation for the application server for specific details.

AuditExt.war file ready to be deployed Figure 9: AuditExt.war file ready to be deployed

The application is now ready to be configured from the web-based UI. For the remainder of this document, references to <AuditExtDir> refer to the location of the installed WAR in the application server (i.e. D:\Program Files\Apache Software Foundation\Tomcat 8.5\webapps\AuditExt for Tomcat).

Configure via UI

Access the web administration URL at http://servername:9300/AuditExt/. A screen prompting for database connection details will be presented. The prompts include the database type, the name and port number of the host database server, the database name and the userid/password used to connect to the database.

database connection configuration screen Figure 10: database connection configuration screen

The Audit Extension application can use either an existing IBM Cognos audit database or a separate database created specifically for this application. It is strongly recommended not to use a database that is already in use for an IBM Cognos Analytics Content Store.

IMPORTANT: The database specified in the Database name field must already exist prior to connecting. The application will create the necessary tables under this database.

The process of creating and populating database tables may take some time, depending on the speed of the server. Once OK has been clicked, wait for the screen to refresh before continuing – do not click OK more than once.

Database preparation

To prepare the database for use by this application, you should configure it in the same as described in the IBM Cognos Installation and Configuration Guide as if it were being used for a Content Store. You may also use a standard IBM Cognos audit logging database, which should also have been configured in this way.

IMPORTANT: For DB2, you must create an additional regular user tablespace with a page size of 16 KB. If you are using a database that has already been set up for IBM Cognos audit logging, you may already have done this.

IMPORTANT: For Oracle, you may need to increase the maximum number of open cursors supported by the database. The default is 50, which will probably be insufficient for this application – a more suitable value would be 500. For more information see http://www.orafaq.com/node/758.

Reconfiguration

To reconfigure the main database connection, click on the Reset configuration link on the Manage Servers page. This allows for the re-entry of the database connection details. Alternatively, the main database connection can be reset manually by following these steps:

* Stop the IBM Cognos Analytics service
* Edit the file `<AuditExtDir>/WEB-INF/classes/c11AuditExtension.properties`
* Re-set the JDBC connection details as follows:
    # JDBC connection details:
    jdbc.url=
    jdbc.user=
    jdbc.password=
* Restart IBM Cognos Analytics

When IBM Cognos Analytics is restarted and the administration URL is accessed again, the screen prompting for the JDBC connection details will be presented.

Configuration file reference

The main configuration file is called c11AuditExtension.properties and can be found at <AuditExtDir>/WEB-INF/classes. This configuration file contains the following parameters:

jdbc.url

jdbc.user

jdbc.password: Connection details for the database used by the audit extension application. These are generated by the application configuration interface and should not be edited manually, except to reset to empty values for reconfiguration. Note that the password is stored in an encrypted format.

option.ra.recurse.everyone: A Role Audit option that determines if the audit should perform a full recursion of the namespace(s) when the Everyone group is encountered in a capability or membership. This will cause users that have access via Everyone to be explicitly audited, but will increase database space and processing time required. Possible values are true and false. The default value is false.

option.ra.recurse.auth: A Role Audit option that specifies if the audit should perform a full recursion of the namespace(s) when the Authenticated Users group is encountered in a capability or membership. This will cause users that have access via Authenticated Users to be explicitly audited but will increase database space and processing time required. Possible values are true and false. The default value is true.

option.ra.recurse.anon: A Role Audit option that determines if the audit should perform a full recursion of the namespace(s) when the Anonymous Users group is encountered in a capability or membership. This will cause users that have access via Anonymous Users to be explicitly audited, but will increase database space and processing time required. Possible values are true and false. The default value is false.

option.ra.max.items: A Role Audit option that limits the maximum number of items that will be processed by the audit. If the number is exceeded, the audit is terminated and recorded as a failure. If it is set to a value of zero, no limit will be applied. The default value is 20000.

option.ra.max.duration: A Role Audit option that limits the maximum length of time, in seconds, that the audit should be run for. If this time is exceeded, the audit is terminated and recorded as a failure. If it is set to a value of zero, no time limit will be applied. The default value is 1800 (30 minutes).

option.ra.trackitems.enabled

option.ra.trackitems.initsize: This option controls an internal optimisation where the application tracks and caches items encountered in a Role Audit in memory rather than re-querying them from the Content Store multiple times in a single audit (a Role Audit may log the same objects multiple times in normal operation due to objects having multiple references in the content store). The default value is true (to enable to item tracking), with an initial memory allocation of 30,000 items. You should not need to change these values.

option.ca.include.specifications: A Content Audit option that determines if the audit should record the specification XML of any reports/queries/analyses that it finds. Possible values are true and false. The default value is true. If this parameter is set to false, less database space will be used.

option.ca.include.output: A Content Audit option that determines if the audit should record details of report versions and outputs for report objects that it finds. Possible values are true and false. The default value is true. If this parameter is set to false, less database space will be used and the audit may run faster.

option.ca.max.items: A Content Audit option that limits the maximum number of items that will be processed by the audit. If the number is exceeded, the audit is terminated and recorded as a failure. The default value is zero which means no limit will be applied.

option.ca.max.duration: A Content Audit option that limits the maximum length of time, in seconds, that the audit should be run for. If this time is exceeded, the audit is terminated and recorded as a failure. If it is set to a value of zero, no time limit will be applied. The default value is 1800 (30 minutes).

option.ca.policy.calculation: A Content Audit option that determines whether security policy calculation should be done in FM. Possible values are true and false. If set to false, the calculation will instead be done at runtime. The default value is true meaning there will be no calculation done at runtime.

option.aa.flatsearch: An Account Audit option that changes the method of scanning the namespace from the default recursive approach to a flat search. This option may be useful for very large namespaces. If this option is set, instead of processing recursively through the entire namespace, the app will perform a flat search only of users that have previously logged in to Cognos, and record those only. Users who exist in the source namespace but who have never logged in will be ignored. This approach can greatly improve processing times in cases where the source namespace is large but only a small fraction of its members are Cognos users. The default value is false, meaning that a traditional recursive search of all users will be performed.

option.aa.max.items: An Account Audit option that limits the maximum number of items that will be processed by the audit. If the number is exceeded, the audit is terminated and recorded as a failure. If it is set to a value of zero, no limit will be applied. The default value is 10000.

option.aa.max.duration: An Account Audit option that limits the maximum length of time, in seconds, that the audit should be run for. If this time is exceeded, the audit is terminated and recorded as a failure. If it is set to a value of zero, no time limit will be applied. The default value is 1800 (30 minutes).

option.aa.include.content: An Account Audit option that determines if the audit should process the content of users’ My Folders. If set, this will cause a mini-Content Audit to be run for each user’s content where it exists. Possible values are true and false. The default value is true.

security.keystore.filename: The location of the keystore file that is used for security. If the file does not exist at this location, a new one will be generated. Note that this must be a writeable location otherwise the application will fail. The default value will place the keystore in the configuration directory of the IBM Cognos Analytics installation using a relative file path. If this application was deployed anywhere other than the Tomcat servlet container that was installed with IBM Cognos Analytics, this value will need to be edited.

option.db.setdefault.audittypes: A database option that determines if the application should reset the audit type descriptions in the database to their default values if they have changed. Possible values are true and false. The default value is false.

option.db.setdefault.statusresulttypes: A database option that determines if the application should reset the status result type descriptions in the database to their default values if they have changed. Possible values are true and false. The default value is false.

option.db.setdefault.serverversiondesc: A database option that determines if the application should reset the server version descriptions in the database to their default values if they have changed. Possible values are true and false. The default value is false.

option.db.setdefault.pingtype: A database option that determines if the application should reset the ping test type descriptions in the database to their default values if they have changed. Possible values are true and false. The default value is false.

option.db.setdefault.pingresult: A database option that determines if the application should reset the ping test result descriptions in the database to their default values if they have changed. Possible values are true and false. The default value is false.

option.db.dimension.time.populate: A database option that determines if the application should fully populate the time dimension table when the table is created on first startup. Note that any missing times will be added when the audit for that time runs, so pre-population is not required, although it is considered better for reporting purposes. Possible values are true and false. The default value is true.

option.db.dimension.date.initdays: A database option that specifies the number of days (starting from the current date) to pre-populate in the date dimension table when it creates it on first startup. Note that any missing dates will be added when the audit for that time runs, so full pre-population is not required, although it is considered better for reporting purposes. The default value is 730 (2 years).

option.db.maxbatch: An option that specifies the maximum number of items that should be processed before a database write. This applies to all audit types and is designed to reduce overall memory consumption for very large audits. The default value is 2000.

option.db.random-audit-id: An option that controls whether the database ID generated for each audit should be a pseudo-random number (a value of true) or sequential (a value of false). The default value is false.

option.db.messageloglevel: An option that specifies the level of detail of messages logged to the database during the audit run. Valid values are: 1 (errors and above), 2 (warnings and above), 3 (informational and above) and 4 (debug). The default value is 3.

Considerations for deploying into another application server

As noted above, if installing into a heavily used production environment, you should consider installing into a separate application server or servlet container. Generally, the procedure is as simple as deploying any web application but there are two file path considerations to keep in mind.

  1. In <AuditExtDir>/WEB-INF/classes/log4j.properties, the log file name is specified using a relative path, ../logs/c11AuditExtension.log. While this works with IBM Cognos Analytics, you may need to change this to either a different relative path or a fully qualified path such as C:/logs/c11AuditExtension.log.
  2. The file <AuditExtDir>/WEB-INF/classes/c11AuditExtension.properties contains a relative path in the security.keystore.filename property (../configuration/c11AuditExtension.keystore). Verify that this path works in your environment and if necessary update it. Remember to use forward slashes for the pathname.

Another alternative configuration is to install into a dedicated IBM Cognos Analytics installation. As the audit extension can communicate with multiple IBM Cognos Analytics servers and not just the one it is installed in, you could have a dedicated IBM Cognos Analytics instance for running this audit and reporting off it. Verify your licensing to determine if this is a viable alternative.

Removal and reinstallation

To uninstall the application, stop IBM Cognos Analytics and delete the following:

  • the <c11install>/wlpdropins/AuditExt.war directory

Optionally, the keystore file can also be deleted. By default, the keystore file is located at <c11install>/configuration/c11AuditExtension.keystore but this location will be different if the application has been into an environment other than described here.

IMPORTANT: If the keystore file is deleted or the application is installed on another machine without copying the keystore file to the new machine, it will no longer be possible to access saved passwords and there may be errors running and administering audits.

Optionally, delete the database tables created by the application can be deleted. The database tables are all prefixed with “AE_”.

Considerations for upgrading Cognos Analytics

Please note that there are two potential issues when updating your version of Cognos Analytics.

Firstly, the Cognos Analytics installer will remove user-added files from the file system during upgrade. See: https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.inst_cr_winux.doc/c_ca_upgrade_latest.html.

To avoid this, add the following lines to the preserve.txt file in the Cognos Analytics installation that is hosting the Audit Extension application:

configuration/c11AuditExtension.keystore
wlpdropins/AuditExt.war
war/AuditExt

Secondly, it is possible that the Cognos SDK API might change from time to time, potentially causing compatibility issues. To avoid this, remove the old wlpdropins/AuditExt.war directory after the upgrade is completed and re-run the build process above to generate a new WAR with the current version of the SDK client libraries. Install this in place of the previous WAR.

Using the application

Manage servers

After the application has been configured, the main interface is the Manage Servers page, which is accessed via http://servername:9300/AuditExt/. The initial Manage Servers page will contain no servers.

default (empty) Manage Servers screen Figure 11: default (empty) Manage Servers screen

Add a server

From the Manage Servers page, add a new server entry by filling in the fields beside the label Add new server and clicking the Add button. The fields to fill in are:

ID: A text identifier for the Cognos Analytics server to be audited. This could be a hostname or simple identifier like “Production”. As the value of this identifier may be used to refer to the server for commands, it is suggested that this value be a simple, short string with only standard characters.

URL: The URL the application will use to connect to the Cognos Analytics server to perform the audit. This URL could either be directly to a Cognos Analytics Dispatcher or to a dedicated gateway for IBM Cognos SDK applications.

Version: Shows the version Cognos Analytics.

Once a new server has been added successfully, the Edit Server page that contains the various properties that apply to the IBM Cognos server just added will automatically be shown. There are several fields on this page and these fields are grouped into three sections, the Set properties section, the Saved namespace logins section and the Add new namespace login section.

Set properties section

URL: The URL of the IBM Cognos 11 BI server to be audited.

Description: An optional description generally used to describe the auditing that will take place on the IBM Cognos 11 BI server behind the specified URL.

Filter (Content Audit): A text field used to define a filter that will be used on a Content Audit. The Content Audit filter will be described shortly.

Filter (Account Audit): A text field used to define a filter that will be used on an Account Audit. The Account Audit filter will be described shortly.

Role audit active: When checked, the Role Audit will be performed. This item is checked by default.

Content audit active: When checked, the Content Audit will be performed. This item is checked by default.

Account audit active: When checked, the Account Audit will be performed. This item is checked by default.

Saved namespace logins section

User: The userid for the associated security namespace that will be used by the application to logon to the IBM Cognos 11 BI server being audited.

Password: The password associated with the userid.

Password (verify): To verify the value in the password field.

Save icon: Save the login information for the associated namespace.

Delete Login icon: Delete the login information for the associated namespace.

Add new namespace login section

Namespace ID: The ID of the security namespace to login into. This needs to be the same value as the one in the Namespace ID field for the security namespace definition in IBM Cognos Configuration.

Username: The userid for the specified security namespace that will be used by the application to logon to the IBM Cognos 11 BI server being audited.

Password: The password associated with the userid.

Password (verify): To verify the value in the password field.

Add button: Add the login information for this namespace to the Saved namespace logins section.

Click the Update button to save the changes, click the Return button to return to the Manage Servers page.

Edit server screen Figure 12: Edit server screen

When a new server has been added, any namespaces that have been configured on the IBM Cognos server to be audited will have been automatically added to the properties page without userids and passwords.

NOTE: If using this application in a test or development environment and Anonymous access has been enabled on the IBM Cognos Analytics instance, the configured namespaces will not automatically appear. Configured namespaces can be added manually by filling in the fields under the label Add new namespace login.

When a new server has been added, the Manage Servers page will contain a list of all the servers that can be audited by this application.

Manage Servers screen showing two saved server configurations Figure 13: Manage Servers screen showing two saved server configurations

Delete a server

A server can be deleted from the Manage Servers page. To delete a server, click the Delete Server icon next to the server entry. A page will appear asking the user to confirm or cancel the deletion. If confirmed, the Manage Servers screen will reappear with the deleted server removed from the list.

Manage server namespaces

For each server, namespaces can be managed from the properties page of the specified server. If a new server has just been added, the properties page will be displayed automatically. To access and edit the properties page for any server shown in the list on the Manage Servers page, click the Set Properties icon beside the targeted server and the Edit Servers page will be displayed. The fields on the Edit Servers page were described earlier in the section titled Add a server.

Before a namespace can be included in an audit, the login credentials the application is to use must be supplied. Enter usernames and passwords one namespace at a time, clicking the Save icon next to that namespace entry after each one. If an attempt is made to save multiple namespace credentials at the same time, only the credentials that correspond to the Save icon that was clicked will be saved. Note that saved namespaces will display the saved user but will never display the saved password.

For any unwanted or unused namespaces, they should be deleted these by clicking the Delete Login icon. If they are not deleted, the application will attempt to login to the namespace and the audit may fail if unable to login. An example of such a namespace is one that is used only for single sign-on.

To add a new namespace, enter the details in the section labelled Add new namespace login at the bottom of the screen. The fields for this section were described earlier in the section titled Add a server.

When finished with the server properties page, click the Return button to go back to the Manage Servers page.

Edit Server screen showing namespace login entry Figure 14: Edit Server screen showing namespace login entry

Configure server properties and audit types

Within the server properties page, additional properties can be set for the server. The properties are:

  • Update the dispatcher URL
  • Add or modify a description for the server
  • Set which audit types should be run for that server
  • Apply filters to the audits

Click on the Update button to save the modified properties.

Set audit filters

For Account and Content Audits, it is possible to specify a filter to limit the scope of the auditing to a subsection of the namespace/content store. These filters are specified in the filter boxes of the server properties screen. If no filter is to be applied (the default), leave the filter values empty.

The filters take the form of a series of regular expressions, separated by forward slashes to denote folders. Usage is slightly different depending on whether it is a content or account audit. More information about regular expressions can be found at http://www.regular-expressions.info/.

Account audit filter

The filter is assumed to start at the namespace level, so the first item in the filter will refer to the namespace. For example, to restrict the audit to members of the Users folder that is within Accounts, for all configured namespaces, the filter term would be:

*/Accounts/Users

The asterisk in the first item means that all namespaces will be matched. Alternatively, to restrict the audit to the same set of folders but also restrict to just the namespace with the ID “ADNamespace”, the filter term would be,

ADNamespace/Accounts/Users

The following filter would be slightly less restrictive and pick up all items under Accounts for all namespaces,

*/Accounts

Any regular expression can be used. However, it is important to remember that the ‘/’ character is a special case and will be treated as a folder separator.

Content audit filter

The filter is assumed to start from the top content (package) level. For example, the following filter would restrict the content to everything within the “GO Sales and Retailers” package:

GO Sales and Retailers

To restrict it further to the “Report Studio Report Samples” folder within that package, the filter would be:

GO Sales and Retailers/Report Studio Report Samples

To limit the content audit to all packages that begin with “GO”, use the following:

GO*

Note that the filter is case sensitive.

Set security

As stated earlier, the application uses its own security mechanism. Click the link named Set admin password on the main Manage Servers page to specify the password required to run the application. A screen with Password and Confirm Password fields will be presented. Specify the password in both of these fields and click the OK button to set the admin password.

Set Admin Password screen Figure 15: Set Admin Password screen

After a password has been set, users will be presented with a prompt screen that will ask them to enter the password required to access the application.

Login screen with password set Figure 16: Login screen with password set

Run audits via web interface

To run an audit via the web interface, go to the Manage Servers page. Each server entry has a Run button that will cause the configured audits to be run for that server. To run the audit for all configured servers, click on the Execute button next to the All servers field.

Run audits via URL

To run an audit for a server ID using a URL use the following syntax:

http://servername:9300/AuditExt/AuditServlet?action=run_audit&server_id=serverId

To run an audit for all servers specified in the Manage Servers page using a URL use the following syntax:

http://servername:9300/AuditExt/AuditServlet?action=run_audit&server_id=serverId

Run audits via web service call

The WSDL for the web services interface can be found at the URL:

http://servername:9300/AuditExt/services/AuditService?wsdl

There are two methods available in the web services interface:

  • runAudit – Takes one parameter, the server ID, and runs the configured audits for that server.
  • runAuditAll – Takes no parameters and runs the configured audits for all servers.

This web service can be called from any application, but an example will be presented here that uses Event Studio to create an IBM Cognos agent that will call the web service interface to run an audit. The example will use the sample package that accompanies this application.

When Event Studio is invoked to create a new agent, the first screen that appears is Specify an event condition… Use a measure in the model that is known to be greater than zero or non-null. This will force the event condition to be true and the agent will be guaranteed to run on demand or as scheduled. In this instance the condition will be set to:

[Server Added(Timestamp)] <> null

Agent with new Event showing condition expression Figure 17: Agent with new Event showing condition expression

From the Add a Task list, select Advanced > Call a Web service…

adding a web service call to Agent Figure 18: adding a web service call to Agent

Enter the URL to the WSDL into the Web service URL: field and click on the Retrieve button to get the available methods.

Enter the URL to the WSDL into the Web service URL: field and click on the Retrieve button to get the available methods.

WSDL URL entry for Agent Figure 19: WSDL URL entry for Agent

Once the WSDL has been retrieved, the operations that can be performed will be stored into the dropdown list labelled Operation: and a box labelled Arguments: will allow setting the value for each argument associated with each operation. In this instance, the runAudit method has been selected from the dropdown list and the previously configured server ID PrimaryServer has been supplied as the serverIdentifier argument.

configure Agent web service call with server identifier Figure 20: configure Agent web service call with server identifier

save Agent Figure 21: save Agent

Save the agent. The agent can now be scheduled through IBM Cognos.

Sample deployment

An IBM Cognos BI deployment archive consisting of a package containing some sample reports and agents created against the sample Framework Manager model is provided with this application. The sample deployment and model are contained within the file AuditExt_reporting_ver_yyyymmdd.zip, where the ver portion of the name is the version(s) of IBM Cognos Analytics to use and the yyyymmdd portion of the name represents the date the reporting package was released.

To import the sample deployment so that it can be used by the IBM Cognos Analytics studios:

  • Copy the file AuditExt_deployment_ver_yyyymmdd.zip to the IBM Cognos deployment directory, usually /deployment.
  • From IBM Cognos Administration, click the Configuration tab, select Content Administration, and click the New Import icon. Select the deployment archive named AuditExt_deployment_ver yyyymmdd. From there, follow the instructions and options presented by the New Import wizard. In most instances, the default settings will suffice. Note that the internal deployment name is Cognos_Audit_Extension.

Before the package can be used, it is necessary to create a new data source in the IBM Cognos Content Store that will interact with the audit database specified when this application was initially installed. From IBM Cognos Administration, click the Configuration tab and select Data Source Connection. Click on the New Data Source icon and give the new data source the name audit_extension. From there, follow the instructions and options presented by the New Data Source wizard to create the data source.

The package can now be used by the IBM Cognos Analytics studios.

Sample model

A sample IBM Cognos Framework Manager model is also supplied with the application as a basis for further development. The sample model is provided in the file AuditExt_model_yyyymmdd.zip where the yyyymmdd portion of the name represents the date the model was released.

Before this model can be used, a data source named audit_extension must exist in the Cognos Content Store. This data source is the same as the data source described in the section titled Sample deployment.

Unzip the sample model to a suitable directory and from Framework Manager, open the project file AuditExt.cpf.

Framework Manager model
Figure 22: Framework Manager model

Other

Upgrading from c10AuditExtension

The c11AuditExtension application recognises the existence of the c10AuditExtension application.

It is recommended that the c11AuditExtension application use a separate database from the c10AuditExtension application. However, it is possible for c11AuditExtension to make use of the database that was established with the older version. If the c11AuditExtension application is configured to use an existing database that will be shared with c10AuditExtension then the following apply:

  • Older IBM Cognos 10 servers that were defined in c10AuditExtension will appear on the Manage Servers page and can be edited on the Edit Server page.
  • Audits for IBM Cognos 8 or 10 servers will not run from the c11AuditExtension application.
  • Reporting should be done using the reporting package supplied with c11AuditExtension.

Logging

This application uses log4j to provide logging services. To change the logging settings, edit the file <c11install>/webapps/c11AuditExtension/WEB-INF/classes/log4j.properties. The log file defaults to <c11install>/logs/c11AuditExtension.log. See the log4j documentation at https://logging.apache.org/log4j/1.2/manual.html for more information on how to configure log4j.

Database tables

The application creates/uses the following tables:

General configuration

  • AE_CONFIG_MAIN: Main application configuration containing the configured servers.
  • AE_CONFIG_NS: The saved namespaces configured for each server.
  • AE_CONFIG_AUDIT_FILTERS: Details of filters saved for Role and Content audits.
  • AE_AUDIT_TYPES: List of possible audit types.
  • AE_SERVER_VERSIONS: List of supported Cognos server versions.
  • AE_CONFIG_AUDIT_TYPES: Which audit types are configured for each server.
  • AE_SECURITY: Table containing encrypted admin password.

Account audit

  • AE_ACCOUNTAUDIT_MAIN: Main detail table.
  • AE_ACCOUNTAUDIT_PORTALPAGES: Records of any user portal pages.

Content audit

  • AE_CONTENTAUDIT_MAIN: Main detail table.
  • AE_CONTENTAUDIT_HIER: Table containing a flattened hierarchy of content items, used for reporting.
  • AE_CONTENTAUDIT_PARAMS: Record of saved parameters for reports and views.
  • AE_CONTENTAUDIT_POLICIES: Record of all security policies applied to all objects.
  • AE_CONTENTAUDIT_SPEC: Record of report, query and analysis specifications.
  • AE_CONTENTAUDIT_SPEC_MODHIST: Record of modifications of report specifications over time.
  • AE_CONTENTAUDIT_RPT_VERSIONS: Record of report output versions saved in the Content Store.
  • AE_CONTENTAUDIT_RPT_OUTPUTS: Record of report outputs saved in the Content Store.

Role audit

  • AE_ROLEAUDIT_DETAIL: Main detail table.
  • AE_ROLEAUDIT_HIER: Table containing a flattened hierarchy of items, used for reporting.

General audit data

  • AE_STATUS: History and status of audit runs.
  • AE_AUDIT_TYPE_LOG: Log of the audit types run for each audit.
  • AE_AUDIT_FILTER_LOG: Log of the filters (if any) applied to each audit run.
  • AE_ITEM_LOOKUP: Lookup table mapping item store IDs to names.
  • AE_ITEM_LOOKUP_FAILURES: Record of all items that could not be looked up (for example because they were removed from the content store but were found in audits as owners of other items etc.)
  • AE_MAP_DATETIME: Table for mapping timestamps (such as the audit start and end times) to date and time dimension table keys.
  • AE_DIM_DATE: Date dimension table. The granularity is days.
  • AE_DIM_TIME: Time dimension table. The granularity is minutes.
  • AE_SECURITY_MEMBERS: Optional data on security policies generated during an Account or Content Audit when the option.ca.policy.calculation option is set to false.
  • AE_LOG_GROUPS: Logging groups lookup table.
  • AE_LOG_LEVELS: Lookup table of available logging levels.
  • AE_LOG_MESSAGES: Log messages recorded by the application during audit runs.
  • AE_METADATA: Internal metadata used by the application to manage database updates.

Troubleshooting

Build script fails on Windows when using “Run as administrator”

When using the ‘Run as administrator’ context menu to run the batch file, Windows may use a different working directory, causing a ‘cannot find the path specified’ error. If you need to run as administrator rather than the logged-in user, instead open a command prompt as administrator, change directory to the /war/AuditExt/ and then run build.bat from that command window.

Audit fails if one of the namespace logins is incorrect

By design, the Audit Extension will not run an audit if it is unable to correctly authenticate with all of the namespaces that have been configured for the Audit Extension. If One or more namespaces is invalid, either enter correct details or remove that namespace entry from the Audit Extension configuration (the namespace does not need to be removed from Cognos Configuration).

Namespace hierarchy report is empty

The namespace hierarchy is only populated once a certain number of levels of users in the hierarchy exist. It may be empty for shallow security hierarchies.