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://everanalytics.wordpress.com/2015/09/15/manage-user-sessions-on-your-ibm-cognos-tm1-server/
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
