Product:
Cognos Controller 10.4.2
Microsoft Windows 2019 server
Issue:
How find events for cognos controller in windows event log?
Suggested solution:
Start windows event viewer on the controller server.
Create a custom view of the application log.
Enter the event to zero and press OK
Enter the name to: events 0 and press OK
Then you have a custom list of event to look at.
More information:
How to Create Custom Views in Windows Server 2012 R2 Event Viewer
Product:
Cognos Controller 10.4.2
Microsoft Windows 2019 server
Issue:
After apply HTTPS and TLS 1.2 users can not login to Cognos Controller Client.
They get a error like:
an error occurred while trying to access the server.
Solution:
4. Navigate to the following path: [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319]
More information:
https://www.ibm.com/support/pages/how-configure-force-controller-use-tls-12
Product:
Cognos Controller 10.4.2
Microsoft Windows 2019 server
Issue:
When setup HTTPS for Cognos Controller web, need to convert the jks file to keyfile.key and cert.cer
https://www.ibm.com/support/pages/how-install-controller-web
https://www.ibm.com/support/pages/how-configure-controller-web-use-ssl-https
Solution:
If you setup HTTPS/TLS/SSL for Cognos Analytics on the same server as you have Cognos Controller installed, then you may want to use that certificate for the Controller Web installation.
(This is a summary of some of the steps – you may need to do additional steps to get HTTPS to work for you).
CA11 is setup with commands like this:
ThirdPartyCertificateTool.bat -c -e -p NoPassWordSet -a RSA -r "request.csr" -d "CN=servername.domain.com,OU=department,O=company,L=place,C=SE" -H "servername.domain.com"
Above to create a certificate request, that you can send to your company CA. This will update the CAMKeystore file in the install_location\configuration\certs directory.
The pem file you get from the company CA, needs to be separated up into 4 files with a certificate in each. You can use Notepad++ for this.
Then import the pem files with below command.
ThirdPartyCertificateTool.bat -i -T -r root.pem -p NoPassWordSet ThirdPartyCertificateTool.bat -i -T -r issuing.pem -p NoPassWordSet ThirdPartyCertificateTool.bat -i -e -r server.pem -t chain.pem -p NoPassWordSet
Update Cognos Configuration to HTTPS/443 and under Certificate Authority settings, click the Use third party CA property, and set its Value to True.
Save and restart CA11 service.
Import the p7b cert into IIS, and configure its bindings to use HTTPS.
Update the IIS settings with the file CA_IIS_CONFIG.BAT.
Change Cognos Controller Configuration to support HTTPS.
https://www.ibm.com/support/pages/how-enable-ssl-https-cognos-controller
To get a key.jks file from the CAMkeystore, start IKEYMAN.EXE as administrator.
Open the D:\Program Files\ibm\cognos\analytics\configuration\certs\CAMkeystore file.
Select personal certificate: encryption and click on export button.
Select Export Key.
Select the folder where you want to store the key.jks file.
Press OK and set a new password: changeit
Then you have a key.jks file with the content of the CAMkeystore.
Put the *.pem files you created before in c:\cert\ folder and use below commands to import them in a key.jks file for controller web.
Start a DOS prompt and change to controller web folder:
cd d:\Program Files\ibm\cognos\ccr_64\fcmweb\jre\bin keytool -keystore key.jks -importcert -file C:\Cert\issuing.pem -alias issuing1 keytool -import -trustcacerts -alias root1 -file C:\Cert\root.pem -keystore key.jks keytool -importkeystore -destkeystore key.jks -srckeystore C:\Cert\key.jks -srcstorepass changeit
Restart the Controller Web service. Copy key.jks file in folder D:\Program Files\ibm\cognos\ccr_64\fcmweb\jre\bin to folder ..\lib\security.
Open d:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\server.xml in Notepad++
Remove brackets around “<feature>ssl-1.0</feature>”
Add below line last
</webApplication> <keyStore id="defaultKeyStore" password="changeit" sslProtocol="SSL_TLS" /> </server>
Save the file and restart the Controller Web services.
Copy the file …jre\bin\key.jks to D:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\resources\security folder.
Edit the file d:\Program Files\IBM\cognos\ccr_64\analytics\templates\ps\portal\variables_CCRWeb.xml to use correct port number 9081.
Edit the file com.ibm.cognos.fcm.web.properties in Notepad++ to have https instead of http.
Reboot the Cognos Controller Web server.
For the Cognos Controller Web frontend you need to extract the key and certificate from your jks file.
To extract the certificate for the server use this command:
keytool -export -alias encryption -file C:\Cert\cert.crt -keystore C:\Cert\key.jks
(The alias was encryption in the CAMkeystore file, therefor is it the same in the key.jks file)
To find if a certificate for a server exist in a jks file use this command:
keytool -list -v -srckeystore C:\Cert\key.jks | findstr "server001"
To extract the key from a jks file, you must first convert it to a pck12 file with this command:
keytool -importkeystore -srckeystore C:\Cert\key.jks -srcstorepass changeit -srckeypass changeit -srcalias encryption -destalias encryption -destkeystore C:\Cert\identity.p12 -deststoretype PKCS12 -deststorepass changeit -destkeypass changeit
Now you have the file identity.p12 that you can copy to a server where you have openssl installed, so you can export the key with command:
"D:\Program Files\Git\mingw64\bin\openssl.exe" pkcs12 -in D:\install\cert\prod\identity.p12 -nocerts -out D:\install\cert\prod\keyfile2.key
Copy both of the above resulting files (cert.crt , keyfile2.key) to the following folder D:\Program Files\ibm\cognos\ccr_64\frontend.
Open config.js from folder D:\Program Files\ibm\cognos\ccr_64\frontend
Edit to
Save the file and and restart the Controller web service.
Check for errors in folder D:\Program Files\ibm\cognos\ccr_64\frontend\logs and D:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\logs
Error like: Error: error:0906D06C:PEM routines:PEM_read_bio:no start line
Then you have to do the steps again – as one of the files are wrong.
In case the above jks does not work – do it again but with a pfx-file.
You can export a p12 file from IKEYMAN, then only rename the p12 file to pfx. Then you have a pfx file.
cd d:\Program Files\ibm\cognos\ccr_64\fcmweb\jre\bin keytool -keystore key.jks -importcert -file C:\Cert\fromcamkey\issuecert.arm -alias issuing2 keytool -import -trustcacerts -alias root2 -file C:\Cert\fromcamkey\rootcert.arm -keystore key.jks keytool -importkeystore -destkeystore key.jks -srckeystore C:\Cert\fromcamkey\camkey.pfx -srcstoretype PKCS12 -srcstorepass changeit
Then copy the pfx to your computer where OPENSSL is installed, and do this commands to get out the files to frontend folder.
"D:\Program Files\Git\mingw64\bin\openssl.exe" pkcs12 -in D:\install\cert\prod\camkey.pfx -clcerts -nokeys -out D:\install\cert\prod\cert3.crt "D:\Program Files\Git\mingw64\bin\openssl.exe" pkcs12 -in D:\install\cert\prod\camkey.pfx -nocerts -out D:\install\cert\prod\keyfile3.key
Copy the cert3.crt and keyfile3.key to the folder D:\Program Files\ibm\cognos\ccr_64\frontend on controller web server.
Update the config.js file for above new file names.
Reboot the cognos controller web server.
More information:
https://security.stackexchange.com/questions/98282/difference-between-openssl-and-keytool
Keytool is a tool that comes with Java that works with KeyStores – it can create KeyStores and manipulate keys and certificates inside them. It can also create keys and sign certificates. So it is both a key generation and a KeyStore-file-administration tool.
OpenSSL works with standard formats (PEM/CER/CRT/PKCS/etc) but does not manipulate KeyStore files. It is possible to generate a key and/or certificate with OpenSSL, and then import that key/cert into a KeyStore using keytool, but you can’t put the key/cert into the KeyStore directly using OpenSSL.
Both OpenSSL and keytool have the same purpose: generating/storing keys and certificate(s) (chaines). The thing is that Java can only work with certificates/keys contained within its keystore (JKS). Those certificates and keys are generated using the keytool library, not by using openssl.
Java keytool will always need a keystore in order to store the certificates and keys it has generated, where this is not the case for openssl. Do note that OpenSSL can also be used to create a similar container, namely PKCS12 (.p12). This is a password protected container containing keys and certificates (just like Java’s keystore). However, it’s not compatible with Java… You’d need to convert the .p12 container to .jks before your Java application will be able to work on the certificates.
https://www.ibm.com/support/pages/how-export-private-key-and-public-key-keystore
https://dzone.com/articles/extracting-a-private-key-from-java-keystore-jks
https://www.sslshopper.com/article-most-common-java-keytool-keystore-commands.html
https://docs.oracle.com/javase/7/docs/technotes/tools/solaris/keytool.html
Product:
Cognos Controller 10.4.2
Microsoft Windows 2019 server
Issue:
When testing the access to controllerserver application in iis you get an error (after you active HTTPS for the IIS website):
This when you test by browse to address: http://controllerservername.domain.com/ibmcognos/controllerserver/ccrws.asmx
Suggested solution:
Restart the Cognos Controller server or
Change the IIS manager to only use HTTPS bindings and not both port 80 and 443 on the default web site.
Clear the cache in your web browser before you test again.
More Information:
https://www.ibm.com/support/pages/how-enable-ssl-https-cognos-controller
Product:
Cognos Controller 10.4.2
Microsoft Windows 2019 server
Issue:
When login to Cognos Controller client you get a error:
System.Web.Services.Protocols.SoapException: Server was unable to process request. —> System.ArgumentNullException: Value cannot be null.
Parameter name: uriString
at System.Uri..ctor(String uriString)
at System.Web.Services.Protocols.WebClientProtocol.set_Url(String value)
at Cognos.Controller.Common.CRNBridge.CRNBridge.set_EndPointURL(String sURL)
at Cognos.Controller.Proxy.CCRWS.GetUserInfo(String sGuid, String sUser, String passportId)
— End of inner exception stack trace —
at Cognos.Controller.Forms.Common.Main.DoLoginCognos8(Form& frm)
at CCR.AppContext.DoLogin()
at CCR.AppContext.Login()
Solution:
Check the IIS settings, you can be missing the ibmcognos or controllerserver application folder.
Check if you can browse from your Web browser to the server at https://controllerservername/ibmcognos/controllerserver/ccrws.asmx
If missing, reinstall them with ca_iis_config.bat or in IIS manager like;
Add the controllerserver application
• Right click on the ibmcognos entry, select Add Application from the popup menu
• Enter controllerserver in the Alias edit field
• Change the ‘Application Pool’ to be “.NET v4.5”
• Browse to the (CCR_installdir)\ControllerProxyServer path (typically d:\Program
Files\ibm\cognos\ccr_64\ControllerProxyServer) and click OK
More information:
https://www.ibm.com/docs/en/cognos-controller/10.4.2?topic=controller-configure-cognos
Product:
Planning Analytics 2.0.9.11
Microsoft Windows 2019 server
Issue:
The TM1 Admin service is not starting. This program is not found running in task manager d:\program files\ibm\cognos\tm1_64\bin64\tm1admsd.exe.
There are no error in the log file at d:\program files\ibm\cognos\tm1_64\bin64\tm1admsrv.log.
You get this error when you start the service:
“The IBM Cognos TM1 Admin Server x64 service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.”
Solution:
Check if a other application is using the port needed by the TM1 admin service (tm1admsdx64).
TM1 admin service uses the ports:
| 5495 | Tm1 architect contact with TM1 Admin service |
| 5498 | Tm1 architect contact with TM1 Admin service (ssl) |
| 5895 | TM1 Admin Server (REST API) |
| 5898 | TM1 Admin Server (REST API) |
netstat -aon | findstr '[port_number]'. Replace the [port_number] with the actual port number that you want to check and hit enter.tasklist | findstr '[PID]'. Replace the [PID] with the number from the above step and hit enter.
You can use DOS command NETSTAT -ANO to get a list of used ports as shown above.
Then you can check what service is using that process id (pid) with above command.
If you can, stop the other application/process and then try to start TM1 admin service again.
But in most cases, you need to reboot the full windows server, to clear any runaway processes.
More information:
https://veerasundar.com/blog/how-to-check-which-application-is-using-which-port/
If the tm1admsrv.exe is blocking the start of TM1 admin service, it is because the TM1 has itself started the TM1ADMSRV as a background process – to keep the Tm1 system up, after a crash of the TM1 admin service.
Product:
Planning Analytics 2.0.9.3
Microsoft Windows 2019 server
Issue:
Login to TM1 client give error, like cafe or pax or pafe.
The PMHub service parameter was not specified or is not one of the configured locations.
Solution:
Update the pmhub.html file with the server name of the PAW server to the line var pmhubURLs =.
Open pmhub.html from folder D:\Program Files\ibm\cognos\analytics\webcontent\bi
In later version of PAL (TM1) the pmhub.html file that are in use is in the webcontent\bi folder instead of the webcontent folder.
Add the url to your PAW server without portnumbers.
var pmhubURLs = [“http://tm1webserver.company.com:9510″,”http://tm1webserver.company.com”,”http://pawserver.company.com”,”http://pawserver”];
Save the file after you added the paw server name.
More Information:
Product:
Cognos Controller 10.4.2
Microsoft Windows 2019 server
Issue:
How does the data flow in a FAP solution?
https://www.ibm.com/docs/en/cognos-controller/10.4.2?topic=format-fap-overview
Explanation:
The FAP database is the central point of the FAP process, of copying data from a cognos controller database to a tm1 cube.
Step 1) The Cognos Controller users enter data for the company, and it is stored in the Cognos Controller database on the SQL server. Inside the Cognos Controller database is data about the companies, accounts and values. The Cognos Controller users use the Cognos Controller client software on there laptop or a citrix server to interact with the system.
Step 2) It is the Cognos Controller server that communicate with the Cognos Controller database, and update and reads all the tables in there.
Step 3) The FAP windows service monitor the cognos controller database for any updates of data, if there is a update of data, then the FAP windows service copy that value over to the FAP database. It also update a reference number, so it know what values have already been copied over before from cognos controller database to the FAP database.
This reference number can come out of sync when you restart the TM1 instance, and then you need to reset it.
Step 4) The FAP windows service writes the controller data to the FAP database, and at regular intervals it calls to the TM1 instance to tell it to get more data from the FAP database. The FAP windows service create and active a TI process in the TM1 instance to collect data from the FAP database.
Step 5) The TM1 instance run a TI process to read the controller data from the FAP database tables, via a hard coded ODBC connection called: FAP. The ODBC FAP connection must point to the FAP database. The controller data is stored in a cube inside the TM1 instance. The name of the FAP cube is defined in the FAP configuration.
Step 6) in most solution, a TM1 consultant, have added extra TM1 TI process, that do additionally handling of the controller data in the TM1 cube. This TI process normally start with the name: ccr_ip_after_
https://www.ibm.com/docs/en/cognos-controller/10.4.2?topic=1042-fap-extension-points
Step 7) The FAP manager client only talks to the FAP database. It configure all tables that tell the FAP windows service what to do and when.
The source tab in FAP client, should point to the cognos controller database you want to read data from.
The data mart tab in FAP client, should point to the TM1 instance, where you want the cognos controller data to be published.
The FAP client settings, tells how often the data will be updated, and when the FAP service should clean out old log values from the log tables, and clean the temporary tables.
On the TM1 server it must exist a odbc connection named FAP, that point to the FAP database.
In many installations, there is also a odbc connection named controller, that should point to the cognos controller database.
More Information:
https://www.ibm.com/docs/en/cognos-controller/10.4.2?topic=ucfap1-introduction
https://www.element61.be/en/resource/why-engage-ibm-tm1-fap-top-your-consolidation
https://www.aramar.co.uk/ibm-analytics-suite/ibm-cognos-controller/
Many IBM partners have develop there own extension to the FAP solution.
https://addedo.com/wp-content/uploads/2020/05/addedo.Express.pdf
Product:
Planning Analytics 2.0.9.3
Microsoft Windows 2019 server
Issue:
You have SSO with Active Directory through use of Cognos Analytics on the same server.
After some time you are logged out of TM1 Architect, and get below error message:
Error -2 occurred pinging your Cognos Password
You will now be disconnected from name.
Solution:
Update your Internet Options settings by adding the server name to the trusted internet zone.
More information:
https://code.cubewise.com/blog/determine-the-version-of-ibm-planning-analytics
Product:
Microsoft SQL server 2016
Problem:
How is my Index used?
Solution:
List the index in the database:
SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name ,IX.name AS Index_Name ,IX.type_desc Index_Type ,SUM(PS.[used_page_count]) * 8 IndexSizeKB ,IXUS.user_seeks AS NumOfSeeks ,IXUS.user_scans AS NumOfScans ,IXUS.user_lookups AS NumOfLookups ,IXUS.user_updates AS NumOfUpdates ,IXUS.last_user_seek AS LastSeek ,IXUS.last_user_scan AS LastScan ,IXUS.last_user_lookup AS LastLookup ,IXUS.last_user_update AS LastUpdate
Show if index are fragmented:
SELECT OBJECT_NAME(IDX.OBJECT_ID) AS Table_Name, IDX.name AS Index_Name, IDXPS.index_type_desc AS Index_Type, IDXPS.avg_fragmentation_in_percent Fragmentation_Percentage FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) IDXPS INNER JOIN sys.indexes IDX ON IDX.object_id = IDXPS.object_id AND IDX.index_id = IDXPS.index_id ORDER BY Fragmentation_Percentage DESC
List index usage:
SELECT * FROM sys.dm_db_index_usage_stats INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID WHERE indexes.is_primary_key = 0 --This line excludes primary key constarint AND indexes. is_unique = 0 --This line excludes unique key constarint AND dm_db_index_usage_stats.user_updates <> 0 -- This line excludes indexes SQL Server hasn’t done any work with AND dm_db_index_usage_stats. user_lookups = 0 AND dm_db_index_usage_stats.user_seeks = 0 AND dm_db_index_usage_stats.user_scans = 0 ORDER BY dm_db_index_usage_stats.user_updates DESC
More Information:
Gathering SQL Server indexes statistics and usage information
https://codefibershq.com/blog/useful-sqlserver-queries-commands-and-snippets