Product:

Microsoft SQL server 2019

Issue:

How do i log if a table is deleted or truncated in SQL server databases?

Solution:

Create a audit log file with this SQL command:

USE master

CREATE SERVER AUDIT [Truncate_Audit]
TO FILE
(FILEPATH = N'c:\temp\logs'
,MAXSIZE = 1 GB
,MAX_ROLLOVER_FILES = 20
,RESERVE_DISK_SPACE = OFF
)
WITH
(QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)

ALTER SERVER AUDIT [Truncate_Audit] WITH (STATE = ON)

Then to make all database that start with letter A to be monitor run below SQL command:

DECLARE @command varchar(500)
SELECT @command = 'IF ''?'' LIKE ''A%'' BEGIN USE ?
EXEC(''Create database audit specification [DatabaseAuditSpecification-Truncate]
FOR SERVER AUDIT [Truncate_Audit]
ADD (Delete on database::[?] by [dbo])
WITH (STATE=ON)'')END'

EXEC sp_MSforeachDB @command

Now, do some add and delete of a table in your database to create some action to log.

Then to see the log enter this SQL command:

SELECT event_time, succeeded, object_id, object_name,session_server_principal_name,server_principal_name,server_instance_name,database_name,statement,file_name,audit_file_offset
FROM fn_get_audit_File ('c:\temp\logs\Truncate_Audit_*.sqlaudit',null,null) order by event_time DESC

You have to adjust the SQL code for your needs.

More Information:

SQL Server auditing with Server and Database audit specifications

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-ver15  

https://www.sqlshack.com/various-techniques-to-audit-sql-server-databases/

https://sqlship.wordpress.com/2011/06/30/how-to-capture-delete-and-truncate-t-sql-using-database-audit-specification/

Product:

Microsoft SQL server 2016

Issue:
How do i add a AD group to all databases on the SQL server as read only access?

Solution:

Adjust below script – replace AD\DBAteam with your domain\group and run it in SQL query.

Use master
GO

DECLARE @dbname VARCHAR(50) 
DECLARE @statement NVARCHAR(max)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR 
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution','SSISDB') 
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @dbname 
WHILE @@FETCH_STATUS = 0 
BEGIN 

SELECT @statement = 'use '+@dbname +';'+ ' CREATE USER [AD\DBATeam] 
FOR LOGIN [AD\DBATeam] ; EXEC sp_addrolemember N''db_datareader'', 
[AB\DBATeam] '

exec sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname 
END 
CLOSE db_cursor 
DEALLOCATE db_cursor

 

The script will work until it reaches a database that is offline – there it will stop and not process any databases below that.

The users login must first be in the database;

CREATE LOGIN [AD\DBATeam] FROM WINDOWS

More information:

https://www.mssqltips.com/sqlservertip/3541/grant-user-access-to-all-sql-server-databases/

https://www.guru99.com/sql-server-create-user.html

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql?view=sql-server-ver15

Move or copy SQL Logins by assigning roles and permissions

Product:

Cognos Controller 10.4.2
Microsoft Excel 2013
Microsoft Windows 2019 server

Issue:

Data entry does not work in Cognos Controller Client for a few users on the Citrix platform.

Error message:

ControllerForms12 … Object variable or With block variable Object reference not set … ActivateDataEntryForm…

Error launching menu item “Controller Link”

Unable to get the FullName property of the addin class at System.Runtime.Type.ForwardCallToInvokeMember .. a WrapperTypes.

Suggested solution:

Open Excel that is used with Cognos Controller for the user who have issues.

Go to Options – Add-ins, click on the Go button at the bottom for Excel Add-Ins.

Uncheck the link for “Cognos Controller Link for Microsoft Excel” add-in, press OK, and exit excel.
Log off Citrix session and log off Citrix Connection Center.
Log out from your computer and log in again.
Start Excel in Citrix as before.

Go to Options – Add-ins – Go button for Excel Add-ins.

Click on Browse

Find C:\Program Files\ibm\IBM Cognos Controller Local Client\adxloader.Controller.ExcelLink.dll
Click Open.
On question that “a file named C:\Program Files\ibm\IBM Cognos Controller Local Client\adxloader.Controller.ExcelLink.dll  already exists in this location. Do you want to replace it?” Click YES.
Remove any other add in, that can interfere, like Thinkcell.
Click OK
Exit Excel.

Start Cognos Controller client and try again.

Ensure the user have the same version of Excel on Citrix as on other Citrix servers or on her laptop.

More Information:

https://www.ibm.com/support/pages/apar/PI49914

https://www.ibm.com/support/pages/unable-get-fullname-property-addin-class-when-attempting-log-controller-link-within-controller 

https://www.ibm.com/support/pages/standard-error-controllerforms-openworkbook-object-reference-not-set-when-opening-or-switching-between-data-entry-forms

https://www.ibm.com/support/pages/standard-error-number-5-server-was-unable-process-request-different-menu-items

Product:
Cognos Analytics 11.1.7 if9
kit_version=11.1.7-2204120500
Manifest=casrv-manifest-11.1.7-2204120500-winx64h.json
Microsoft Windows 2016 Server

Issue:
You have downloaded the interim fix of CA11 and put it on a file share to install from.

The user you install with have only READ-ONLY access to the file share shared folder.

When run the installation you get strange errors, like:

The installation is unable to continue. Please make sure all product services have been stopped and applications have been closed.

Solution:

Ensure the user that you install with have full rights to the windows share where the cognos media files are.

Connect to \\servername\d$ if needed, to get the correct rights.

The installation program need write access to the folder where the media is stored.

More Information:

https://www.ibm.com/support/pages/node/6525664

https://www.ibm.com/support/pages/ibm-cognos-analytics-1117-fix-pack-4

https://www.ibm.com/support/pages/ibm-cognos-analytics-11x-fix-lists

https://www.ibm.com/support/pages/how-upgrade-your-version-cognos-analytics

Product:
Cognos Controller Web 10.4.2
Microsoft Windows 2019 server

Issue:

After applied a DNS alias to the Cognos Controller server, the users can not login to the Controller Web.

They get a error like Server is not reachable or still initializing, please refresh the page in a few seconds

Solution:

You must update the D:\Program\ibm\cognos\ccr_64\frontend\config.js file with the URL the end user will use to access Cognos Controller Web.

If you change it from a server-name to a DNS alias, then open config.js in notepad++

 //Details for Controller UI service 
"expressJs": {
"host": "cognoscontroller.domain.com", //interface used by Controller Web UI Service
"port": "9080", //port used by Controller Web UI Service
"options": {

Change the interface value to your DNS alias. The controller web backend connection at line 14 can be left with the server host name.

Save the file.

Restart the 3 Controller web services.

More Information:

https://www.ibm.com/support/pages/troubleshooting-server-not-reachable-or-still-initializing-please-refresh-page-few-seconds-error-launching-controller-web

Product:
Planning Analytics Workspace 74
Microsoft Windows 2019 server

Issue:
To install PAW74 or later on Windows 2019 server, you need to first install the docker runtime.

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=ipawl-install-planning-analytics-workspace-local-windows-server-2019

Solution:

From a workstation with internet connection.
Create a folder like c:\install\docker

Go to the folder in powershell as administrator

Enter this command to get the installation script

Invoke-WebRequest -Uri https://get.mirantis.com/install.ps1 -o install.ps1

Enter this command to download the media only

.\install.ps1 -DownloadOnly

Then copy this files over to the server, and place them in some folder like d:\install\docker

On the new server start powershell as administrator and go to the folder to start installation with command

.\install.ps1 -Offline

When installation is done, it is common to change the drive for docker. Update the file c:\ProgramData\docker\config\daemon.json

The value data-root point to where you want all the docker images for PAW. In windows you must enter the path with double slash. Save the file.

To start the docker service enter:

Start-Service docker

If you get error;

fatal: unable to configure the Docker daemon with file C:\ProgramData\docker\config\daemon.json: invalid character ‘P’ in string escape code

then you have missed the \\ in the file.

To check version of docker enter:

docker version

Should give response: Client: Mirantis Container Runtime

Version: 20.10.11

API version: 1.41

More information:

https://www.mirantis.com/blog/getting-started-with-mirantis-container-runtime-on-windows-server/

https://store.mirantis.com/product/basic-for-mirantis-container-runtime-formerly-docker-engine-enterprise-for-compatible-linux-and-windows-server/

 

https://techcrunch.com/2022/02/09/mirantis-on-run-rate-over-100m-two-years-after-buying-docker-enterprise-assets/

Product:
Cognos Controller 10.4.2
Microsoft Windows 2019 server

Issue:
After upgrade of Cognos Controller to a new version, the users get a error message when they start Cognos Controller Client program.

“The version of Controller requires database version 992.”

Solution:

Wait for the IT department to upgrade the Cognos Controller Client installation on the Citrix server you are using. Cognos Controller 10.4.2 use database version 1010.

More Information:

https://www.ibm.com/support/pages/ibm-cognos-controller-builds-ccr-name-and-database-version

https://www.ibm.com/support/pages/how-install-controller-local-client

Product:
Planning Analytics Workspace 74

TM1_version=TM1-AW64-ML-RTM-11.0.912.9-0
TM1_name=IBM Cognos TM1

Microsoft Windows 2019 server

Issue:

How much disk space do i need for PAW on my new server?

Solution:

The install folder (where you downloaded the software) takes around 15 GB.

The PAW folder (where log files are) takes 10 GB.

The DOCKER folder (where the images are) takes around 52 GB.

The program folder for planning analytics takes 7 GB.

The folder for your TM1 instance – is very depending on your solution and how much data you have in TM1.

The Windows folder take around 18 GB.

Normally you have windows on C: and all the rest on your D: drive.

Recommendation is to have above 100 GB free on the C: and D: drive before you start.

 

More information:

https://static1.squarespace.com/static/5268c662e4b0269256614e9a/t/5ccb9e48ea82dc000156eadc/1556848201313/TM1+Planning+Analytics+-+Cheat+Sheet+-+2019-05.pdf

https://na.eventscloud.com/file_uploads/b16ad176a58e3a9a360d66f1a4009c4e_Plan_IBMPlanningAnalyticsLocal_SoufianeAzizi.pdf

Product:
Cognos Controller 10.4.2
Microsoft Windows 2019 server

Issue:
When you login to cognos controller client, you get a error message of this kind;

Number: 76
Source: mscorlib
Description: Could not find a part of the path ‘C:\Users\nnnnn\AppData\Roaming\Cognos\CCR\ADMdb_cognoscontroller.local,1433CognosControllerdatabase’.
at Microsoft.VisualBasic.ErrObject.Raise(Int32 Number, Object Source, Object Description, Object HelpFile, Object HelpContext)
at Cognos.Controller.Common.RaiseErrCli.RaiseError(Int32 lErrNo, String sErrSource, String sErrDesc, String sErrHelpFile, Int32 lErrHelpContext)
at Cognos.Controller.Common.RaiseErrCli.RaiseError(Int32 lErrNo, String sErrSource, String sErrDesc)

Suggested Solution:

Click OK to get past the error message, and still login to Cognos Controller Client program.

Clear the local cache by going to Maintain – Special Utilities – Clear Local Cache.

Close Cognos Controller program and try to login again. Did the error go away?

Can be garbage in the cache of files in folder C:\Users\nnnn\AppData\Roaming\Cognos\ccr, that are cleared by this action.

More Information:

https://www.ibm.com/support/pages/standard-error-76-could-not-find-part-path-when-launching-controller

Product:
Cognos Controller 10.4.2
Microsoft Windows 2019 server

Issue:
When open FAP.UDL file with DBCONV.exe program to upgrade the database you get a error.

ERROR: java.lang.NullPointerException

Possible solution:

The UDL file does not contain a password.

Copy the fap.uld file to folder D:\Program files\ibm\cognos\ccr_64\Data

Open the file in Cognos Controller configuration and add a password.

Click on test connection icon.

Click on arrow to start the Database conversion program.

Select FAP DB.

Click Connect.

Check if the numbers are different, then you need to click on Upgrade button.

Close the program.

Remove the fap.udl file from the \ibm\cognos\ccr_64\Data folder.

Also, ensure the SQLJDBC42.JAR file is in the folder D:\Program files\ibm\cognos\ccr_64

More Information:

https://www.ibm.com/support/pages/javalangunsupportedoperationexception-jre-version-16-error-when-using-database-conversion-utility-dbconv

https://www.ibm.com/support/pages/could-not-create-view-could-not-create-controller-type-failed-launch-javalangnullpointerexception-launching-java-related-functionality-caused-invalid-ccrjavahome-variable