tm1py: count and list TM1 users

Post Reply
Wim Gielis
MVP
Posts: 2549
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

tm1py: count and list TM1 users

Post by Wim Gielis » Thu Oct 01, 2020 10:32 pm

Hi all,

For some time now I use a TI process to count TM1 users, in the following ways:
- Number of Admin users
- Number of Write users
- Number of Read users
- Number of Disabled users
- Number of non-admin users
- Number of read only users

I decided to rewrite the process using tm1py. I succeeded but if someone can have a look at the coding and suggest improvements, that would be awesome. Nice to have's will be to output user friendly names and also output to a text file in the TM1 data directory. Also, breaking out of the nested loops near the end of the code, that would be more efficient.

Questions for Marius Wirtz or others:
- is there an easy way to return the Admin users in a TM1 model ?
- is tm1.cells.get_value the easiest and best way to retrieve 1 (or only a handful) cube values similar to CellGetN/S in TI ?
- as I noted elsewhere, being part of the Admin group overrides the IsDisabled client property. You might want to take that into account.

Code: Select all

from TM1py import TM1Service
from TM1py.Objects import User

ADDRESS = "localhost"
PORT = 8001
USER = "Wim"
PWD = "my_password"
SSL = False
tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD, ssl=SSL)

admin_users = []
non_admin_users = []
write_users = []
read_users = []
read_only_users = []
disabled_users = []

# get read-only users
cubeContent = tm1.cells.execute_mdx_rows_and_values('SELECT {[}ClientProperties].[ReadOnlyUser]} ON COLUMNS, {[}Clients].MEMBERS} ON ROWS FROM [}ClientProperties]')

for user in cubeContent:
    if str(cubeContent[user][0]) != 'None':
        username = str(user[0]).replace('[}Clients].[}Clients].[','').replace(']','')
        read_only_users.append(str(username))

# get admin users, non-admin users, disabled users
# also, write and read users
users = tm1.security.get_all_users()
cubes = tm1.cubes.get_model_cubes()

for user in users:
    if str(user._user_type) == 'Admin':
        admin_users.append(str(user._name))
    else:
        non_admin_users.append(str(user._name))
        if user._enabled == False:
            disabled_users.append(str(user._name))
        else:
            vClient_Access = ''
            if str(user._name) not in read_only_users:
                # loop over all application cubes
                groups = tm1.security.get_groups(user._name)
                for cube in cubes:
                    for group in groups:
                        sAccess = tm1.cells.get_value('}CubeSecurity', cube.name + ',' + str(group), ["}Cubes", "}Groups"])
                        if not sAccess in ['','None','Read']:
                            vClient_Access = 'W'
            if vClient_Access == 'W':
                write_users.append(str(user._name))
            else:
                read_users.append(str(user._name))

# output
print('Number of users: ' + str(len(users)))
print('\tNumber of admin users: ' + str(len(admin_users)))
print('\tNumber of write users: ' + str(len(write_users)))
print('\tNumber of read users: ' + str(len(read_users)))
print('\tNumber of disabled users: ' + str(len(disabled_users)))
print('')
print('\t[Number of non-admin users: ' + str(len(non_admin_users)) + ']')
print('\t[Number of read-only: ' + str(len(read_only_users)) + ']')
print('')

if admin_users:
    print("Admin users:\n\t" + "\n\t".join(admin_users) + "\n")

if write_users:
    print("Write users:\n\t" + "\n\t".join(write_users) + "\n")

if read_users:
    print("Read users:\n\t" + "\n\t".join(read_users) + "\n")

if disabled_users:
    print("Disabled users:\n\t" + "\n\t".join(disabled_users) + "\n")

if non_admin_users:
    print("Non-admin users:\n\t" + "\n\t".join(non_admin_users) + "\n")

if read_only_users:
    print("Read-only users:\n\t" + "\n\t".join(read_only_users) + "\n")
Last edited by Wim Gielis on Fri Oct 02, 2020 10:43 pm, edited 2 times in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

scrumthing
Posts: 56
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: too many

Re: tm1py: count and list TM1 users

Post by scrumthing » Fri Oct 02, 2020 8:27 am

Hi Wim,
I presented something similar at the Cubewise TM1 Horizon a couple of weeks ago. You can find my example on github:
https://github.com/scrumthing/tm1py_tm1 ... ase..ipynb

That is a summary of how we do it at Deutsche Bahn with our 70+ instances.

My remarks on your code:
- If I understand your code correctly you are only checking for Admins but not for OperationsAdmin, etc.
- to get the read-only users querying the only client properties cube is the only way. IBM should add that flag to the users-requests.
- I personally do not query all security cubes for read or write. If the read-only flag is not set I count them as WRITE.
- I put the data into a pandas dataframe because I find them easier to handle.
- I know that getting the user name is harder if you are using CAM, for me only the friendly name worked
- My current obstacle is to get a list of employess using tm1 (some employess have multiple users (wim and wim_admin for example). That is a tedious undertaking.
- Next idea is to check the active directory and other sources for validating the users automatically
There is no OLAP database besides TM1!

Wim Gielis
MVP
Posts: 2549
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: tm1py: count and list TM1 users

Post by Wim Gielis » Fri Oct 02, 2020 9:35 am

Hi Christoph,

Thanks. I forgot to mension that I used a snippet of your code regarding the ReadOnly property.

I will comment later on the other feedback, thanks.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Wim Gielis
MVP
Posts: 2549
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: tm1py: count and list TM1 users

Post by Wim Gielis » Fri Oct 02, 2020 11:25 am

scrumthing wrote:
Fri Oct 02, 2020 8:27 am
- If I understand your code correctly you are only checking for Admins but not for OperationsAdmin, etc.
Exactly. I am extending the code.
scrumthing wrote:
Fri Oct 02, 2020 8:27 am
- to get the read-only users querying the only client properties cube is the only way. IBM should add that flag to the users-requests.
Agreed
scrumthing wrote:
Fri Oct 02, 2020 8:27 am
- I personally do not query all security cubes for read or write. If the read-only flag is not set I count them as WRITE.
Then I think my option is a better one, but YMMV.
scrumthing wrote:
Fri Oct 02, 2020 8:27 am
- I put the data into a pandas dataframe because I find them easier to handle.
That's beyond my knowledge at this moment.
scrumthing wrote:
Fri Oct 02, 2020 8:27 am
- I know that getting the user name is harder if you are using CAM, for me only the friendly name worked
I will want to extend the code with friendly names too.
scrumthing wrote:
Fri Oct 02, 2020 8:27 am
- My current obstacle is to get a list of employess using tm1 (some employess have multiple users (wim and wim_admin for example). That is a tedious undertaking.
- Next idea is to check the active directory and other sources for validating the users automatically
I bet it is ! I am happy to go with TM1 users for now.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Wim Gielis
MVP
Posts: 2549
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: tm1py: count and list TM1 users

Post by Wim Gielis » Fri Oct 02, 2020 11:25 am

Updated coding.

Code: Select all

from TM1py import TM1Service
from TM1py.Objects import User

ADDRESS = "localhost"
PORT = 8001
USER = "Wim"
PWD = ""
SSL = False
tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD, ssl=SSL)

admin_users = []

full_admin_users = []
security_admin_users = []
data_admin_users = []
operations_admin_users = []

non_admin_users = []

write_users = []
read_users = []
read_only_users = []
disabled_users = []

# get read-only users
cubeContent = tm1.cells.execute_mdx_rows_and_values('SELECT {[}ClientProperties].[ReadOnlyUser]} ON COLUMNS, {[}Clients].MEMBERS} ON ROWS FROM [}ClientProperties]')

for user in cubeContent:
    if str(cubeContent[user][0]) != 'None':
        username = str(user[0]).replace('[}Clients].[}Clients].[','').replace(']','')
        read_only_users.append(str(username))

# get admin users, non-admin users, disabled users
# also, write and read users
users = tm1.security.get_all_users()
cubes = tm1.cubes.get_model_cubes()

for user in users:
    if str(user._user_type) == 'Admin':
        admin_users.append(str(user._name))
        full_admin_users.append(str(user._name))
    elif str(user._user_type) == 'SecurityAdmin':
        admin_users.append(str(user._name))
        security_admin_users.append(str(user._name))
    elif str(user._user_type) == 'DataAdmin':
        admin_users.append(str(user._name))
        data_admin_users.append(str(user._name))
    elif str(user._user_type) == 'OperationsAdmin':
        admin_users.append(str(user._name))
        operations_admin_users.append(str(user._name))
    else:
        non_admin_users.append(str(user._name))
        if user._enabled == False:
            disabled_users.append(str(user._name))
        else:
            vClient_Access = ''
            if str(user._name) not in read_only_users:
                # loop over all application cubes
                groups = tm1.security.get_groups(user._name)
                for cube in cubes:
                    for group in groups:
                        sAccess = tm1.cells.get_value('}CubeSecurity', cube.name + ',' + str(group), ["}Cubes", "}Groups"])
                        if not sAccess in ['','None','Read']:
                            vClient_Access = 'W'
            if vClient_Access == 'W':
                write_users.append(str(user._name))
            else:
                read_users.append(str(user._name))

# output
print('Number of users: ' + str(len(users)))
print('\tNumber of non-admin users: ' + str(len(non_admin_users)))
print('\tNumber of admin users: ' + str(len(admin_users)))
print('\t\tNumber of full admin users: ' + str(len(full_admin_users)))
print('\t\tNumber of security admin users: ' + str(len(security_admin_users)))
print('\t\tNumber of data admin users: ' + str(len(data_admin_users)))
print('\t\tNumber of operations admin users: ' + str(len(operations_admin_users)))
print('')
print('\tNumber of write users: ' + str(len(write_users)))
print('\tNumber of read users: ' + str(len(read_users)))
print('\tNumber of read-only: ' + str(len(read_only_users)))
print('\tNumber of disabled users: ' + str(len(disabled_users)))
print('')
print('')

if admin_users:
    print("Admin users:\n\t" + "\n\t".join(admin_users) + "\n")

if full_admin_users:
    print("Full admin users:\n\t" + "\n\t".join(full_admin_users) + "\n")

if security_admin_users:
    print("Security admin users:\n\t" + "\n\t".join(security_admin_users) + "\n")

if data_admin_users:
    print("Data admin users:\n\t" + "\n\t".join(data_admin_users) + "\n")

if operations_admin_users:
    print("Operations admin users:\n\t" + "\n\t".join(operations_admin_users) + "\n")

if non_admin_users:
    print("Non-admin users:\n\t" + "\n\t".join(non_admin_users) + "\n")

if write_users:
    print("Write users:\n\t" + "\n\t".join(write_users) + "\n")

if read_users:
    print("Read users:\n\t" + "\n\t".join(read_users) + "\n")

if disabled_users:
    print("Disabled users:\n\t" + "\n\t".join(disabled_users) + "\n")

if read_only_users:
    print("Read-only users:\n\t" + "\n\t".join(read_only_users) + "\n")
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Wim Gielis
MVP
Posts: 2549
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: tm1py: count and list TM1 users

Post by Wim Gielis » Tue Oct 06, 2020 4:34 pm

If you have several TM1 models, you could loop over ports. Here I assume the same username, password, SSL.

Code: Select all

from TM1py import TM1Service
from TM1py.Objects import User

ADDRESS = "localhost"
USER = "Admin"
PWD = "..."
SSL = True

ports = [8001, 8009, 8015, 8016]
for port in ports:
    tm1 = TM1Service(address=ADDRESS, port=port, user=USER, password=PWD, ssl=SSL)
    active_configuration = tm1.server.get_active_configuration()

    print('')
    print('')
    print('=============  ' + active_configuration["ServerName"] + '  =============')
    print('')
    print('')
    
    admin_users = []
    full_admin_users = []
    security_admin_users = []
    data_admin_users = []
    operations_admin_users = []
    non_admin_users = []
    write_users = []
    read_users = []
    read_only_users = []
    disabled_users = []

    # get read-only users
    cubeContent = tm1.cells.execute_mdx_rows_and_values('SELECT {[}ClientProperties].[ReadOnlyUser]} ON COLUMNS, {[}Clients].MEMBERS} ON ROWS FROM [}ClientProperties]')

    for user in cubeContent:
        if str(cubeContent[user][0]) != 'None':
            username = str(user[0]).replace('[}Clients].[}Clients].[','').replace(']','')
            read_only_users.append(str(username))

    # get admin users, non-admin users, disabled users
    # also, write and read users
    users = tm1.security.get_all_users()
    cubes = tm1.cubes.get_model_cubes()

    for user in users:
        if str(user._user_type) == 'Admin':
            admin_users.append(str(user._name))
            full_admin_users.append(str(user._name))
        elif str(user._user_type) == 'SecurityAdmin':
            admin_users.append(str(user._name))
            security_admin_users.append(str(user._name))
        elif str(user._user_type) == 'DataAdmin':
            admin_users.append(str(user._name))
            data_admin_users.append(str(user._name))
        elif str(user._user_type) == 'OperationsAdmin':
            admin_users.append(str(user._name))
            operations_admin_users.append(str(user._name))
        else:
            non_admin_users.append(str(user._name))
            if user._enabled == False:
                disabled_users.append(str(user._name))
            else:
                vClient_Access = ''
                if str(user._name) not in read_only_users:
                    # loop over all application cubes
                    groups = tm1.security.get_groups(user._name)
                    for cube in cubes:
                        for group in groups:
                            sAccess = tm1.cells.get_value('}CubeSecurity', cube.name + ',' + str(group))
                            if not sAccess in ['','None','Read']:
                                vClient_Access = 'W'
                if vClient_Access == 'W':
                    write_users.append(str(user._name))
                else:
                    read_users.append(str(user._name))

    # output
    print('Number of users: ' + str(len(users)))
    print('\tNumber of non-admin users: ' + str(len(non_admin_users)))
    print('\tNumber of admin users: ' + str(len(admin_users)))
    print('\t\tNumber of full admin users: ' + str(len(full_admin_users)))
    print('\t\tNumber of security admin users: ' + str(len(security_admin_users)))
    print('\t\tNumber of data admin users: ' + str(len(data_admin_users)))
    print('\t\tNumber of operations admin users: ' + str(len(operations_admin_users)))
    print('')
    print('\tNumber of write users: ' + str(len(write_users)))
    print('\tNumber of read users: ' + str(len(read_users)))
    print('\tNumber of read-only: ' + str(len(read_only_users)))
    print('\tNumber of disabled users: ' + str(len(disabled_users)))
    print('')
    print('')

    if admin_users:
        print("Admin users:\n\t" + "\n\t".join(admin_users) + "\n")

    if full_admin_users:
        print("Full admin users:\n\t" + "\n\t".join(full_admin_users) + "\n")

    if security_admin_users:
        print("Security admin users:\n\t" + "\n\t".join(security_admin_users) + "\n")

    if data_admin_users:
        print("Data admin users:\n\t" + "\n\t".join(data_admin_users) + "\n")

    if operations_admin_users:
        print("Operations admin users:\n\t" + "\n\t".join(operations_admin_users) + "\n")

    if non_admin_users:
        print("Non-admin users:\n\t" + "\n\t".join(non_admin_users) + "\n")

    if write_users:
        print("Write users:\n\t" + "\n\t".join(write_users) + "\n")

    if read_users:
        print("Read users:\n\t" + "\n\t".join(read_users) + "\n")

    if disabled_users:
        print("Disabled users:\n\t" + "\n\t".join(disabled_users) + "\n")

    if read_only_users:
        print("Read-only users:\n\t" + "\n\t".join(read_only_users) + "\n")


Input("...")
Last edited by Wim Gielis on Tue Oct 06, 2020 8:43 pm, edited 1 time in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Wim Gielis
MVP
Posts: 2549
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: tm1py: count and list TM1 users

Post by Wim Gielis » Tue Oct 06, 2020 8:39 pm

If I want to retrieve the }TM1_DefaultDisplayValue, is it already coded as one of the attributes that we can retrieve through tm1py ?
I thought that I would use the Name property, giving me the client ID (element name in }Clients), and Friendly name would give me this alias.
But this does not appear to be case, when I test it. Do we need to query the }ElementAttributes_}Clients cube or has it been exposed in tm1py ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

scrumthing
Posts: 56
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: too many

Re: tm1py: count and list TM1 users

Post by scrumthing » Wed Oct 07, 2020 7:22 am

Wim Gielis wrote:
Tue Oct 06, 2020 4:34 pm
If you have several TM1 models, you could loop over ports. Here I assume the same username, password, SSL.
You could get the instances listed at the admin server with a useful function and retrieve ssl and port from there.

Code: Select all

# get modules
from TM1py.Utils import get_all_servers_from_adminhost
from TM1py.Services import TM1Service

# ip adress or dns of your server
serverIpAdress = 'localhost'
userName = 'admin'
userPassword = 'apple'

# =============================================================================================================
# Get instance information from admin server
# =============================================================================================================
try:
    tm1InstancesOnServer = get_all_servers_from_adminhost(serverIpAdress)

    for tm1Instance in tm1InstancesOnServer:
        #get information from admin server
        httpPortNumber = tm1Instance.http_port_number
        SSL = tm1Instance.using_ssl
        instanceName = tm1Instance.name

        print('Connecting to ' + instanceName )
        with TM1Service(address=serverIpAdress,port=httpPortNumber,user=userName,password=userPassword,namespace='',gateway='',ssl=SSL) as tm1:
            print(tm1.server.get_product_version())

except Exception as e:
    print(e)
There is no OLAP database besides TM1!

Wim Gielis
MVP
Posts: 2549
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: tm1py: count and list TM1 users

Post by Wim Gielis » Wed Oct 07, 2020 8:05 am

Thanks, I will have a look at that one.
The use case for my script is an investigation at one of our customers, where they have 5 TM1 models, 4 of which are actively used.
So I entered the port numbers in a list to know which ones to query.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Wim Gielis
MVP
Posts: 2549
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: tm1py: count and list TM1 users

Post by Wim Gielis » Thu Oct 08, 2020 9:29 am

Newest version, it's becoming an invaluable tool ;-)
Next step is to incorporate the code of Scrumthing to avoid hardcoded port numbers.
Also, get the }TM1_DefaultDisplayValue instead of }Clients principal element names.

Feel free to join this and turn it into an even more useful script !

Code: Select all

from TM1py import TM1Service
from TM1py.Objects import User
from datetime import datetime

logline = []

def inspect_users():

    ADDRESS = '...'
    USER = 'Admin'
    PWD = '...'
    SSL = True
    ports = [8001, 8005, ...]
    include_users_in_lists = "Y"

    filehandle = open(r"D:\tm1_users.txt","w")

    for port in ports:
        tm1 = TM1Service(address=ADDRESS, port=port, user=USER, password=PWD, ssl=SSL)
        active_configuration = tm1.server.get_active_configuration()

        logline.append('\n\n=============  ' + active_configuration["ServerName"] + '  =============\n')

        logline.append("Time: " + datetime.now().strftime("%x %X"))
        logline.append('http port number: ' + str(port))
        logline.append('TM1 data directory: ' + tm1.server.get_data_directory())
        logline.append('TM1 logging directory: ' + active_configuration["Administration"]["DebugLog"]["LoggingDirectory"])
        logline.append('')

        admin_users = []
        full_admin_users = []
        security_admin_users = []
        data_admin_users = []
        operations_admin_users = []
        non_admin_users = []
        authorized_users = []
        write_users = []
        read_users = []
        read_only_users = []
        disabled_users = []
        
        # custom security groups in TM1
        custom_groups = tm1.security.get_all_groups()
        try:
            custom_groups.remove('ADMIN')
            custom_groups.remove('DataAdmin')
            custom_groups.remove('SecurityAdmin')
            custom_groups.remove('OperationsAdmin')
            custom_groups.remove('}tp_Everyone')
        except ValueError:
            pass
        
        # get read-only users
        cubeContent = tm1.cells.execute_mdx_rows_and_values('SELECT {[}ClientProperties].[ReadOnlyUser]} ON COLUMNS, {[}Clients].MEMBERS} ON ROWS FROM [}ClientProperties]')

        for user in cubeContent:
            if str(cubeContent[user][0]) != 'None':
                username = str(user[0]).replace('[}Clients].[}Clients].[','').replace(']','')
                read_only_users.append(str(username))

        # get user types
        users = tm1.security.get_all_users()
        cubes = tm1.cubes.get_model_cubes()

        for user in users:
            if str(user._user_type) == 'Admin':
                admin_users.append(str(user._friendly_name))
                full_admin_users.append(str(user._friendly_name))
            elif str(user._user_type) == 'SecurityAdmin':
                admin_users.append(str(user._friendly_name))
                security_admin_users.append(str(user._friendly_name))
            elif str(user._user_type) == 'DataAdmin':
                admin_users.append(str(user._friendly_name))
                data_admin_users.append(str(user._friendly_name))
            elif str(user._user_type) == 'OperationsAdmin':
                admin_users.append(str(user._friendly_name))
                operations_admin_users.append(str(user._friendly_name))
            else:
                non_admin_users.append(str(user._friendly_name))
                if user._enabled == False:
                    disabled_users.append(str(user._friendly_name))
                else:
                    authorized_users.append(str(user._friendly_name))
                    vClient_Access = ''
                    if str(user._friendly_name) not in read_only_users:
                        # loop over all application cubes
                        groups = tm1.security.get_groups(user._friendly_name)
                        for cube in cubes:
                            for group in groups:
                                sAccess = tm1.cells.get_value('}CubeSecurity', cube.name + ',' + str(group))
                                if not sAccess in ['','None','Read']:
                                    vClient_Access = 'W'
                    if vClient_Access == 'W':
                        write_users.append(str(user._friendly_name))
                    else:
                        read_users.append(str(user._friendly_name))

        # output
        logline.append('Number of users: ' + str(len(users)))
        logline.append('\tNumber of non-admin users: ' + str(len(non_admin_users)))
        logline.append('\tNumber of admin users: ' + str(len(admin_users)))
        logline.append('\t\tNumber of full admin users (\'Administrator\'): ' + str(len(full_admin_users)))
        logline.append('\t\tNumber of security admin users: ' + str(len(security_admin_users)))
        logline.append('\t\tNumber of data admin users: ' + str(len(data_admin_users)))
        logline.append('\t\tNumber of operations admin users: ' + str(len(operations_admin_users)))
        logline.append('')
        logline.append('\tNumber of Read/Write users (\'Authorized Users\'): ' + str(len(authorized_users)))
        logline.append('\t\tNumber of write users: ' + str(len(write_users)))
        logline.append('\t\tNumber of read users: ' + str(len(read_users)))
        logline.append('\tNumber of read-only users (\'Explorers\'): ' + str(len(read_only_users)))
        logline.append('\tNumber of disabled users: ' + str(len(disabled_users)))
        logline.append('')
        logline.append('\tNumber of custom TM1 security groups: ' + str(len(custom_groups)))
        logline.append('\n')

        if include_users_in_lists == "Y":
            if len(security_admin_users) > 0 or len(data_admin_users) > 0 or len(operations_admin_users) > 0:
                output("Admin users", admin_users)
            output_list("Full admin users", full_admin_users)
            output_list("Security admin users", security_admin_users)
            output_list("Data admin users", data_admin_users)
            output_list("Operations admin users", operations_admin_users)
            output_list("Non-admin users", non_admin_users)
            output_list("Authorized users", authorized_users)
            output_list("Write users", write_users)
            output_list("Read users", read_users)
            output_list("Disabled users", disabled_users)
            output_list("Read-only users", read_only_users)

        output_list("Custom TM1 security groups", custom_groups)

    filehandle.write("\n".join(logline))
    filehandle.close()
    return

def output_list(sText, lList):

    if lList:
        lList.sort
        logline.append(sText + " (" + str(len(lList)) + "):\n\t" + "\n\t".join(lList) + "\n" )
        return

inspect_users()
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

scrumthing
Posts: 56
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: too many

Re: tm1py: count and list TM1 users

Post by scrumthing » Thu Oct 08, 2020 7:04 pm

Thanks for sharing Wim! I have one idea which could increase performance, guess instead of checking all cubes for all groups of every user you could generate a list of groups with WRITE-access and then checking if a user is in one of those groups on the list. Had no time to try that out.
There is no OLAP database besides TM1!

MariusWirtz
Posts: 14
Joined: Sat Apr 08, 2017 8:40 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: 2016

Re: tm1py: count and list TM1 users

Post by MariusWirtz » Sun Oct 11, 2020 4:23 pm

Hi Wim,

nice work. Perhaps you could post it on GitHub to simplify future cooperation.

Regarding your questions,
is there an easy way to return the Admin users in a TM1 model?
You can use the execute_mdx_rows_and_values function against a zero suppressed MDX query in the }ClientGroups cube.

Code: Select all

from TM1py import TM1Service

mdx = """
SELECT
NON EMPTY {Tm1SubsetAll([}Clients])} ON ROWS,
{[}Groups].[ADMIN]} ON COLUMNS
FROM [}ClientGroups]
"""

with TM1Service(address='localhost', port=12354, ssl=True, user="admin", password="apple") as tm1:
    rows_and_values = tm1.cells.execute_mdx_rows_and_values(mdx=mdx, element_unique_names=False)
    
    for row_elements, values in rows_and_values.items():
        print(row_elements[0])

Do you think it would be convenient to have this as a built-in function in the SecurityService to return all admin users in a model?

is tm1.cells.get_value the easiest and best way to retrieve 1 (or only a handful) cube values similar to CellGetN/S in TI?
There is no equivalent to CellGetN in TM1py.
In fact, it's best to avoid doing individual lookups (e.g. inside a loop) with TM1py.

Because there is overhead associated with every "roundtrip" to the server and back, it is best to retrieve everything you need from TM1 right at the beginning of your script. Then you can store it in structures (e.g. the default Dictionary or TM1py's CaseAndSpaceInsensitiveDict data structure) that make it easy to deal with the data in your main logic.

To retrieve a slice of a cube I use MDX almost every time. Depending on the shape, that you need the data to be in, TM1py provides different functions (e.g. execute_mdx_values, execute_mdx_rows_and_values, execute_mdx_dataframe)
as I noted elsewhere, being part of the Admin group overrides the IsDisabled client property. You might want to take that into account.
TM1py is just the messenger. It merely forwards the information from TM1 and its REST API. I think it's a user's responsibility to deal with this ambiguity.

Wim Gielis
MVP
Posts: 2549
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: tm1py: count and list TM1 users

Post by Wim Gielis » Thu Oct 15, 2020 12:18 pm

Thanks for the feedback above, I will take it into account.

For now, the latest code is here on Github: https://github.com/cubewise-code/tm1py/issues/393

Marius will have a look at it and add to tm1py what belongs there instead of being written in custom scripts.

Please try it out and comment or contribute to make the script even more useful.


Features that were added in the meantime:
- a summary output to just have the count of users the way IBM classifies users
- TM1 software version information of the installed version
- the data directory and logging directory folder locations are shown
- more harmonized naming of the different user types
- a descriptive attribute for }Clients element names, like }TM1_DefaultDisplayValue (or any other attribute)
- the possibility to exclude TM1 models related to certain port numbers
- looping over all TM1 servers on an admin host, rather than using hardcoded http port numbers
- alphabetically sorted output
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Wim Gielis
MVP
Posts: 2549
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: tm1py: count and list TM1 users

Post by Wim Gielis » Fri Oct 16, 2020 1:48 pm

A separate Github repository has been created: https://github.com/wimgielis/Count-and-list-TM1-users
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Post Reply