Product:

Planning Analytics 2.0.9.13

Microsoft Windows 2019 server

Issue:

If i get a text file with the AD usernames that should be inside my TM1 application, how do i remove the users from TM1 that is not in the list?

This for a TM1 application that uses CAM security, and is connected to CA11 for login.

If you have a AD group called “Tm1people” you can with this command create a list:

IN CMD

dsquery group -name "Tm1people" | dsget group -members > D:\user_handling\keepusers.txt

IN POWERSHELL

Get-ADGroupMember -identity "Tm1people" | select SamAccountName

 

Suggested Solution:

Create two TI processes, one that load the list of user in a temp dimension, and a second process, that check every user in the TM1 application against that temp dimension list.  Login to TM1 Architect as administrator.  Create a new TI process.

In process ONE, go to data source and select text and select the file D:\user_handling\keepusers.txt

Click preview, and you should get a list of the users Active Directory account names.

Change the variable name to vUser and set the content to other. This to get the variable to be accessible in the Advanced tab.

In Parameters tab, create a pSure parameter, that you need to answear JA to make the process run.

In advanced prolog tab, enter this code:

 

# only execute if parameter pSure is JA
if (pSure @<> 'JA');
ProcessQuit;
endif;


#--- variables section ---
sCube = '}ClientGroups';
sDimName1 = '}Clients';

#-- enter the domain name of your company
sDomainName = 'adname/' ;
sDimName2 = 'TempUsersList';

#--- file name setup for debug text file
sFileName= 'debugfile1.txt';
sFilePath = 'd:\temp\';
sDEBUGFILE = sFilePath | sFileName ;

# create a temporary dimension to hold all users to keep
if (DimensionExists( sDimName2 ) =1);
DimensionDestroy(sDimName2);
endif;
DimensionCreate( sDimName2 );

 

In the Metadata tab, enter this code:

 

#-- add domain name to username
sFullUser = sDomainName | vUser;
# add users from list to the temp dimension
DimensionElementInsertDirect( sDimName2, '' , sFullUser, 'S' );

 

In the Epilog tab, enter this command to start the second process:

# call the other process to clean out users
ExecuteProcess( 'second processname');

 

Save the process, and create a new process, the second process TWO.

In Advanced – prolog tab, enter this code:

 

#--- variables section ---
sCube = '}ClientGroups';
sDimName1 = '}Clients';
#-- enter the domain name of your company
sDomainName = 'adname/' ;
sDimName2 = 'TempUsersList';

#--- file name setup for debug text file
sFileName= 'debugfile2.txt';
sFilePath = 'd:\temp\';
sDEBUGFILE = sFilePath | sFileName ;


#-- in a while loop check if the user clients exist in the dimension list --

iElm = 1;
#-- get number of users in clients dimension
ElmCount = DIMSIZ(sDimName1);
#-- do this for each user in client dimension
WHILE(iElm <= ElmCount);
#-- get the user alias from the list
sElment = DIMNM( sDimName1, iElm );
sElmentAlias = ATTRS( sDimName1, sElment, '}TM1_DefaultDisplayValue' );

#-- check if he is not in the list from the file
nResult = (DIMIX (sDimName2, sElmentAlias) );
if( nResult=0 );
#-- get if the user is admin
vGroupAdmin = CellGetS( sCube , sElment , 'ADMIN' );
If (vGroupAdmin @<> 'ADMIN' );
sPrincipalName = DimensionElementPrincipalName( sDimName1, sElmentAlias );

# check that name is not blank
if (sPrincipalName @<> '');
##--- remove user from security cube in tm1
##--- only if user is not part of ADMIN group

#--- only debug, write out data to file
# ASCIIOUTPUT ( sDEBUGFILE, sPrincipalName, sElment, sElmentAlias );

DeleteClient ( sPrincipalName );
endif;

endif;

endif;

iElm = iElm + 1;
END;

 

In the epilog tab, enter this code:

#-- Remove the temp list of users to prevent accidental runs
DimensionDestroy(sDimName2);

 

Save the process.

Create the keepusers.txt file and test run the process in a sample application with many users.

You may need to have below command in the Epilog:

SECURITYREFRESH;

More Information:

https://blog.technotesdesk.com/export-a-list-of-members-from-an-active-directory-group-to-a-text-file 

https://everanalytics.wordpress.com/2015/09/15/manage-user-sessions-on-your-ibm-cognos-tm1-server/ 

DIMNM TM1 Function: Use and Syntax

https://exploringtm1.com/dimsiz-tm1-function-use-and-syntax/ 

https://www.wimgielis.com/tm1_clientsgroups_EN.htm

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=stf-deleteclient