Product:
Cognos Analytics Extended Audit 11
Microsoft Windows 2019 server

Issue:

Setup AuditExt as of this page, https://developer.ibm.com/technologies/analytics/tutorials/ibm-cognos-11-audit-extension/

but when run, it fails, check of log file (D:\Program Files\ibm\cognos\analytics\logs\c11AuditExtension.log) show a error message:

Violation of PRIMARY KEY constraint ‘PK_AE_CA_SEC_MEM’. Cannot insert duplicate key in object ‘dbo.AE_SECURITY_MEMBERS’

Solution:

The issue can be that you reach max.items limit, and get above error in the log file.

Stop the Cognos BI service.
Go to folder D:\Program Files\ibm\cognos\analytics\wlpdropins\AuditExt.war\WEB-INF\classes

Open c11AuditExtension.properties in Notepad++

Change this lines:

false — will not save the report xml in the audit database. 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.
0 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).
0 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).
0 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.
0 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).
0 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 30000.
false — will not check my folders 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.

Save the file.
Start IBM Cognos windows service.
Browse to http://servername.domain.com:9300/AuditExt/ to run the “collection event” again.

After you have loaded the Audit Extension report package (AuditExt_deployment_c11_20181003), you can can run a report called “Audit Run Report” to see if the collection of audit data was successful.

Of course you need to create a data source called “audit_extension” to your database where you store the audit data.

Under the teams folder – Cognos Audit Extension – Role Audit – Capabilites available to Users report, can be the one that give you a detail view of the license possibility for each user. You need to test your way forward.

 

More information:
https://www.ironsidegroup.com/video/bi-expert/cognos-audit-extension-your-secret-weapon/

https://www.envisn.com/envisn-cognos-blog/bid/102863/Using-IBM-Cognos-10-Audit-Extensions

https://www.wisdomjobs.com/e-university/ibm-cognos-tutorial-196/auditing-4398.html

https://www.bspsoftware.com/knowledgebase/how-does-license-auditor-work/

Product:
Cognos Analytics 11

Microsoft Windows 2019 server

Issue:
Faster way to check license, than use Audit extensions?

https://developer.ibm.com/technologies/analytics/tutorials/ibm-cognos-11-audit-extension/

Suggested solution:
Download and install MetaManager.
https://www.bspsoftware.com/products/metamanager/Download/

Unzip the 64bit file to a folder. You can install MetaManager on your laptop. (You only need port 80 and 9300 open in the firewall to be able to access the cognos servers from your laptop).

Run installation by click on the MetaManagerWixSetup.msi file

Click Next

Accept the license and click Next

Click Next

Click Install

Request a trail licenses from https://www.bspsoftware.com/products/metamanager/freestuff/

You need a trail license for each computer.

Activate the license, from inside the program by click on Enter license key;
Enter the information you got in mail from techdata:

“Thank you for your interest in MetaManager.

This license key will unlock free functionality in MetaManager including the BSP License Auditor module”

Setup the Cognos Connection, by go to tools – options. Click on IBM Cognos and add:

Enter a name, and the FQDN for the cognos server, and expand and enter your Cognos Admin account.

Namespace ID must be same as you have set as namespace in cognos configuration.

Click Test and OK.

You can access all your CA11 environment from one installation of Meta Manager. (you only need port 80 and 9300 to be open in firewall between your computer and the cognos servers).

Run the license scan:

Go to License Auditor – select your site from drop-down – click on Run.

Click OK and Close, to go to the result.

To see the administrators, click on the number or click on Accounts.

By select “Analytics Administrator” in drop down, you see what yellow capabilities that trigger that this user is Administrator.  The astrix* before a name indicate that the user is in the System Administrator group. Yellow lines tell that this user is consider as a Cognos Administrator, if he should not be it, you must go to Cognos Administration, Security tab, and Capabilities. Then click on “set properties” to find the users in that area.

Check you Cognos Security tabs Capabilities, to remove users and groups that should not be in the one capability; that will be triggered as administrator license.

Under permissions, remove the user or group that should not be there. If a user has Grant Execute rights he is consider to have this capability.

This list show the values that trigger different license roles (all values in the link):

https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=licenses-default-permissions-based

Meta-manager calculate license this way;

https://www.bspsoftware.com/knowledgebase/how-does-license-auditor-work/

More information:

Avnet BSP Software

https://www.bspsoftware.com/products/metamanager/pricing/

https://www.pmsquare.asia/pmsquare/

https://www-03.ibm.com/software/sla/sladb.nsf/lilookup/441D74E2925A72EA8525828300720001?OpenDocument

https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=licenses-license-roles

https://www.bspsoftware.com/knowledgebase/installing-metamanager/
Contact your IBM partner for help with the license audit:

http://www.middlecon.se/licensoversyner-behovs/

Product:
Planning Analytics 2.0.9.7
Microsoft Windows 2019 server

Problem:
What java keystore should i use for PA?

Follow official documentation in the first place, as the solution change with different versions of Cognos.

Suggested solution:

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=itw-configure-ssl-planning-analytics-tm1-webspreadsheet-services-existing-keystore

Make a backup of the ssl folder before you change anything.

The JAVA Keystore used by PMPSVC.  Open Command Prompt as an Administrator and navigate to the <tm1 install dir>\bin64\jre\bin\ directory.

Execute the following command to import/trust the certificate:

keytool.exe -import -trustcacerts -file “C:\Program Files\ibm\cognos\tm1_64\bin64\ssl\caRoot.cer” -keystore “C:\Program Files\ibm\cognos\tm1_64\bin64\ssl\tm1store” -alias caRoot -storepass applix

The JAVA Keystore used by TM1WEB.  Open Command Prompt as an Administrator and navigate to the <tm1 install dir>\bin64\jre\bin\ directory.

Execute the following command to import/trust the certificate:

keytool.exe -import -trustcacerts -file “C:\Program Files\ibm\cognos\tm1web\bin64\ssl\caRoot.cer” -keystore “C:\Program Files\ibm\cognos\tm1web\bin64\ssl\tm1store” -alias caRoot -storepass applix

 

More information:

https://www.sslshopper.com/article-most-common-java-keytool-keystore-commands.html

Java Keytool Commands for Creating and Importing

These commands allow you to generate a new Java Keytool keystore file, create a CSR, and import certificates. Any root or intermediate certificates will need to be imported before importing the primary certificate for your domain.

  • Generate a Java keystore and key pair
    keytool -genkey -alias mydomain -keyalg RSA -keystore keystore.jks  -keysize 2048
  • Generate a certificate signing request (CSR) for an existing Java keystore
    keytool -certreq -alias mydomain -keystore keystore.jks -file mydomain.csr
  • Import a root or intermediate CA certificate to an existing Java keystore
    keytool -import -trustcacerts -alias root -file Thawte.crt -keystore keystore.jks
  • Import a signed primary certificate to an existing Java keystore
    keytool -import -trustcacerts -alias mydomain -file mydomain.crt -keystore keystore.jks
  • Generate a keystore and self-signed certificate (see How to Create a Self Signed Certificate using Java Keytoolfor more info)
    keytool -genkey -keyalg RSA -alias selfsigned -keystore keystore.jks -storepass password -validity 360 -keysize 2048

Java Keytool Commands for Checking

If you need to check the information within a certificate, or Java keystore, use these commands.

  • Check a stand-alone certificate
    keytool -printcert -v -file mydomain.crt
  • Check which certificates are in a Java keystore
    keytool -list -v -keystore keystore.jks
  • Check a particular keystore entry using an alias
    keytool -list -v -keystore keystore.jks -alias mydomain

Other Java Keytool Commands

  • Delete a certificate from a Java Keytool keystore
    keytool -delete -alias mydomain -keystore keystore.jks
  • Change a Java keystore password
    keytool -storepasswd -new new_storepass -keystore keystore.jks
  • Export a certificate from a keystore
    keytool -export -alias mydomain -file mydomain.crt -keystore keystore.jks
  • List Trusted CA Certs
    keytool -list -v -keystore $JAVA_HOME/jre/lib/security/cacerts
  • Import New CA into Trusted Certs
    keytool -import -trustcacerts -file /path/to/ca/ca.pem -alias CA_ALIAS -keystore $JAVA_HOME/jre/lib/secur

https://www.ibm.com/support/pages/how-configure-custom-ssl-certificates-planning-analytics-20-and-201

https://www.ibm.com/support/pages/how-configure-planning-analytics-connect-ssl-secured-cognos-dispatcher

https://docs.bmc.com/docs/decisionsupportserverautomation/89/using-third-party-certification-authority-certificates-656916032.html 

 

 

Product:
Cognos Analytics 11.1.7

Microsoft Windows 2016 server

Issue:
When browse to IBMCOGNOS site you get a error message. This after a upgrade of cognos on a old server.

You change the Cognos Application pool to use localsystem as identity, but it did not help.

If you browse to controller test site; http://servername.domain.com/ibmcognos/controllerserver/ccrws.asmx or if you try http://servername.domain.com/ibmcognos/bi/v1/disp , then it works.

Solution:
Check the folder C:\inetpub\wwwroot for any web.config file.

This can be like this:

Rename the web.config file and restart iis with dos command : iisreset.

More Information:
https://www.ibm.com/support/pages/http-403-forbidden-error-message-when-accessing-cognos-anlaytics-1104-or-higher-gateway-url-iis-server

https://docs.microsoft.com/en-us/troubleshoot/iis/http-status-code

Product:
Cognos Analytics 11.0.13

kit_version=11.0.13.18102214
kit_name=IBM Cognos Analytics

Microsoft Windows 2012 server

Issue:
What is Director Administrator counted as kind of license?

Guessed Solution:
Director Administrator is classified as a administrator license.

Inside Cognos Analytics you can get a list of the users that have access to the system.
Go to Manage – Licenses – Export to get a list of user.

The view show only users who have logged into Cognos.
If you change a user from Administrator to User, that person must login to Cognos, before above list is updated with his correct license status.

The exported csv file, list all people that have somehow connected to cognos security.

The level 3 is a Analytic Administrator and level 1 is a Analytic User.
-1 in level show the user have not logged into Cognos, but he is listed in the security groups.

https://www.ibm.com/docs/en/cognos-analytics/11.0.0?topic=managing-licenses

To be sure about license talk to a IBM partner or read the license documents:

https://www-03.ibm.com/software/sla/sladb.nsf/lilookup/F7DA13804AF37FA3852586D8005820E1?OpenDocument

More information:

https://senturus.com/blog/cognos-analytics-using-license-roles-specify-permissions/

https://www.ibm.com/docs/en/cognos-analytics/11.0.0?topic=licenses-default-permissions-based

https://www.bspsoftware.com/products/metamanager/

To get a detailed report of users capabilities, you can install and use Cognos 11 audit extensions;

https://developer.ibm.com/technologies/analytics/tutorials/ibm-cognos-11-audit-extension/

http://www.middlecon.se/vara-omraden/licensoptimering/

Product:
Cognos Analytics 11.1.x
Microsoft Windows 2016 server

Problem:
When you enter url http://servername/ibmcognos then you do not get logged into Cognos Analytics, instead you get a blank screen.

Solution:

Add a rule to allow Cognos Analytics pages to load without a trailing slash in URL. Start IIS Manager and go to IBMCOGNOS.

  1. Click the ibmcognos alias.
  2. Double-click URL Rewrite
  3. Click Add Rules > Inbound Rules > Blank Rule
    • Name is Add Trailing Slash
    • Pattern is ^bi$
    • Action type is Redirect
    • Redirect URL is {R:0}/
    • Check Append query string.
    • Redirect type is Permanent (301)
    • Click Apply and Back to Rules.

 

More information:
https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=services-configuring-iis-in-cognos-analytics

 

Product:
Cognos Analytics 11.1.7
Microsoft Windows 2016 server
Problem:
Can i store the Native SQL query the users send to the database for later troubleshooting?

Solution:
Activate logging of the SQL query to the AUDIT database.
Setup the AUDIT database logging in Cognos Configuration.
https://www.ibm.com/support/pages/how-configure-audit-reporting-cognos-analytics

Browse to Cognos Analytics (ibmcognos) and go to Manage > Administrative Console > Configuration > Dispatchers and Services. Click on the Set Properties – Configuration icon.
Click the Settings tab, choose logging as category.

Go down to Generate comments in native SQL and mark it.
Click OK.
Run some reports to test the system.
Open SQL Developer and go to your AUDIT database and check the

COGIPF_NATIVEQUERY table.

 

 

More Information:

https://www.ibm.com/support/pages/how-see-which-user-generating-query-cognos-analytics

https://www.envisn.com/envisn-cognos-blog/bid/102418/What-s-Wrong-with-IBM-Cognos-Audit-Data

https://www.ibm.com/support/pages/cognos-ipf-audit-table-descriptions

https://cognoshub.blogspot.com/2015/02/cognos-sql-native-sql-and-pass-through.html

https://www.wisdomjobs.com/e-university/ibm-cognos-tutorial-196/auditing-4398.html

Product:
Cognos Analytics 11.1.7
Product_version=11.1 R7 (LTS)
install_type=Custom
selected_features=FTR_LCM
IBM-JRE_version=8.0.6.15
LCM_BUILD_version=11.1.7-2009181443
LICENSE_version=11.1.7-2009291107
Microsoft Windows 2019 server

Problem:
When starting the Cognos® LifeCycle Manager (LCM) on the same server as CA11 is installed, they use the same port 9300.

Suggested Solution:

Start notepad++ as administrator
Change port on LCM by open D:\Program Files\ibm\cognos\lcm\wlp\usr\servers\lcm\server.xml in notepad++

Change the row <httpEndpoint host=”*” httpPort=”9305″ httpsPort=”-1″ id=”defaultHttpEndpoint”/> to use 9305 instead of 9300
Save the file.
Start a CMD as administrator.
Go to folder D:\Program Files\ibm\cognos\lcm
Run lcm_start.bat to start the service

You need to update the C:\ProgramData\Microsoft\Windows\Start Menu\Programs\IBM Cognos LCM\LCM URI internet shortcut to reflect the change of port

Then after that it should work to start and use it

More information:

https://www.ibm.com/support/knowledgecenter/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_upgr_mngr.doc/upgr_mngr.pdf

https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_upgr_mngr.doc/t_upgr_mngr_create_project.html#upgr_mngr_create_project

Product:
Cognos Analytics 11.1.7 fix pack 2
Microsoft Windows 2019 server

Problem:

When run some reports after a upgrade from previous version you get a error like this:

XQEGEN0002 An unexpected exception occurred: java.lang.StackOverflowError.

Report validation is OK but when you try to run it as HTML report you get a error.

Possible solution:

Check the report specification for any item or function that is common for the reports that does not work.

Can be that you have a filter in the Query parameters that you need to remove from the report to make it work.

You need to remove one part of the report at a time to find what part of the report specification that can give you the issue.

More information:

https://www.njit.edu/finance/sites/njit.edu.finance/files/finance-innovations/Cognos%20Analytics%20Reporting.pdf

https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ug_ca_dshb.doc/ug_ca_dshb.pdf

https://reports.nycenet.edu/Cognos84sdk/documentation/en/ug_cr_rptstd.pdf

standard-deviation From the old Cognos 8
Returns the standard deviation of selected data items. The keyword “distinct” is available for
backward compatibility of expressions used in previous versions of the product.
Syntax
standard-deviation ( [ distinct ] expr [ auto ] )
standard-deviation ( [ distinct ] expr for [ all | any ] expr {
, expr } )
standard-deviation ( [ distinct ] expr for report )
Example
standard-deviation ( ProductCost )
Result: A value indicating the deviation between product costs and the average product cost.

Product:
Cognos Analytics 11.1.7
Microsoft Windows 2019 server

Problem:
Can not add url or use link to tm1web or other web site.

When you try to add a URL you get below error:

Your URL uses an invalid domain. Click ‘View acceptable domains’ to see a list of domains you can use.

Solution:
On all Cognos BI (CA11) servers, go into Cognos Configuration
Expand Security
Select IBM Cognos Application Firewall
Under “Enable CAF Validation?” = True, click on pen icon to add more servernames for Valid domains and hosts.

Enter the company domain name with a wildcard as *.company.com
Enter all the server names for your Cognos Analytics servers
Enter all the TM1 servers you have in your environment
Enter the TM1WEB servers with port number like tm1webservername:9510 (you must enter the port number for all servers Cognos will communicate with if it is not port 80)

Click OK
Click SAVE in Cognos Configuration.
You need then click on RESTART to make this changes take effect.
Restart the primary content manager service first, when that is up again, then restart the other CA11 servers in your environment.

How add a URL link in CA11:

Click on + in the folder where you want the link to be and select URL

Enter a name and enter the URL to your server including port number 9510 for tm1web. Recommended is to use full names includeing domain part.

Click OK.

Test the link.

“Cannot open URL” can also be becouse the website does not exist.

More information:

https://www.ibm.com/support/knowledgecenter/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.inst_cr_winux.10.2.2.doc/t_inst_cnfg_caf_steps.html%23inst_cnfg_caf_steps

https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.inst_cr_winux.doc/t_inst_cnfg_caf_steps.html