To know Active Users & Inactive Users of TM1

Post Reply
habibullah.sayyed
Posts: 37
Joined: Mon Jul 29, 2019 12:01 pm
OLAP Product: TM1
Version: v10.x
Excel Version: excel 2016

To know Active Users & Inactive Users of TM1

Post by habibullah.sayyed » Thu Jan 16, 2020 11:59 am

Hello Community,

I have above 400 user for TM1, and I want to know which users are active, so that i want to delete the inactive users.
My friend suggested that delete all users from TM1 architect, this will not impact the active users, and whenever active user login, their ID will visible in the TM1 architect in "}ClientGroup" Cube. So can I do this or anyone has better solution from this?

Thank you.

ascheevel
Community Contributor
Posts: 159
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.8
Excel Version: 2016
Location: Minneapolis, USA

Re: To know Active Users & Inactive Users of TM1

Post by ascheevel » Thu Jan 16, 2020 2:23 pm


tomok
MVP
Posts: 2737
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: To know Active Users & Inactive Users of TM1

Post by tomok » Thu Jan 16, 2020 5:06 pm

habibullah.sayyed wrote:
Thu Jan 16, 2020 11:59 am
My friend suggested that delete all users from TM1 architect
A really, really bad idea. What about the people that were off sick that day, or had their computer turned off, etc. and were therefore not actively logged into TM1? Boom, there goes all there security group assignments.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

ascheevel
Community Contributor
Posts: 159
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.8
Excel Version: 2016
Location: Minneapolis, USA

Re: To know Active Users & Inactive Users of TM1

Post by ascheevel » Thu Jan 16, 2020 5:46 pm

tomok wrote:
Thu Jan 16, 2020 5:06 pm
Boom, there goes all there security group assignments.
habidullah didn't specify, but if security group assignments are done in Cognos, a user's group assignments would repopulate the next time they log in. Native group assignments would definitely be lost though, hopefully habidullah's friend doesn't suggest to delete the admin users too.

User avatar
paulsimon
MVP
Posts: 754
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: To know Active Users & Inactive Users of TM1

Post by paulsimon » Thu Jan 16, 2020 11:20 pm

Hi

Deleting users is a bad idea for many reasons.

You will potentially lose Private Views and Subsets stored against those users.

If there is anything else stored against the user it will also be lost, such as TM1 Web settings, Locks on Dimension Elements, etc.

If the user does not already exist, then their CAMID has to be inserted into the }Clients dimension, on login, which is a meta data modification and therefore has locking implications that can affect performance.

The audit log is one possibility but we generally find it creates too much overhead and it is hard to tune to give the right amount of information.

We use the an enhancement of the method based on looking for ACTIVE in the }ClientProperties cube

For this to work, you must add the following to the TM1S.CFG

ClientPropertiesSyncInterval=60

We then use a Chore that runs at regular intervals. This runs a process which reads from a view on }ClientProperties to get those users that are Active and puts the results into a cube for later analysis. You can tune the capturing cube and process so that you record eg at hourly level for the current day, daily level for the current month and monthly level thereafter. This gives a small system overhead as all you are wanting to find out is whether the user was logged in. If let's say your IdleConnectionTimeOut is set to 3600 ie 1 hour, then you only need to run the chore every 20 minutes or so to catch most users as most users will tend to sign in and then not bother to log out even if it only takes them 5 mins to update a forecast.

However, be wary of assuming that just because a user has not logged in for 3 months that they no longer want to use the system. For example, if the system supports both statutory and management accounts, then the statutory accounts users might only use the system twice a year for interims and year end, whereas management accounts users will probably use it monthly. Similarly some users might be back up users if the main user for their area is away on holiday. We certainly use the cube as a way to manage license usage. However, we do generally email the user first before deleting their account. We also try to tie in to HR's leavers notifications but this doesn't catch everything.

Regards

Paul Simon

User avatar
gtonkin
MVP
Posts: 803
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: To know Active Users & Inactive Users of TM1

Post by gtonkin » Fri Jan 17, 2020 6:31 am

habibullah.sayyed wrote:
Thu Jan 16, 2020 11:59 am
Hello Community,

I have above 400 user for TM1, and I want to know which users are active, so that i want to delete the inactive users.
My friend suggested that delete all users from TM1 architect, this will not impact the active users, and whenever active user login, their ID will visible in the TM1 architect in "}ClientGroup" Cube. So can I do this or anyone has better solution from this?

Thank you.
I am reading this differently but the habibullah.sayyed would need to confirm.
My take is that they are looking at cleaning up "dormant" users rather than "inactive" i.e. recently logged out etc.

habibullah.sayyed please confirm by explaining the rationale behind this e.g. you have more }Clients registered than you are licensed for and want to remove users who no longer need access/have not logged in for 30 days versus removing inactive users based on their status.
Last edited by gtonkin on Fri Jan 17, 2020 8:14 am, edited 1 time in total.

lotsaram
MVP
Posts: 3408
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: To know Active Users & Inactive Users of TM1

Post by lotsaram » Fri Jan 17, 2020 8:12 am

I interpreted he OP's request (albeit however poorly formulated) as how to determine and then remove dormant or inactive accounts, as opposed to simply users not currently logged in.

For SOx compliance of needing to remove accounts inactive for >90 days I used to employ the method described by paulsimon. However, I have since discovered a better and more convienient way. Just add the following lines of code to your tm1s-log.properties file in the configuration directory.

Code: Select all

log4j.appender.USR=org.apache.log4j.SharedMemoryAppender
log4j.appender.USR.File=usrcon.log
log4j.appender.USR.MaxFileSize=5 MB
log4j.appender.USR.MaxBackupIndex=10
log4j.appender.USR.TimeZone=Local
log4j.logger.TM1.Login=DEBUG, USR
This will create a separate log file "usrcon.log" in the logging directory which will have new records written to it each time any user logs in or out. It is then very easy to process the log file and determine the last activity timestamp for any user. If you are not interested in knowing when and for how long users were logged in but just when users last logged in this method is better as:
* no need to add ClientPropertiesSyncInterval to tm1s.cfg
* no need to have a regularly scheduled chore that looks at the }ClientProperties cube
* no need to ever trawl through transaction logs looking for changes in ACTIVE in }ClientProperties

Of course both methods are only effective from the moment the logon tracking solution is implemented. It you need retrospective analysis for before logon / logout was monitored then you are stuck, there's really nothing you can do. (Maybe unless audit logging is enabled, but I have found the RawStore logs to be quite unhelpful. I don't know of anyone who has actually found a good use for them.)
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

tomok
MVP
Posts: 2737
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: To know Active Users & Inactive Users of TM1

Post by tomok » Fri Jan 17, 2020 12:24 pm

It doesn't really matter if the OP is talking about users that aren't currently logged in or users that have not logged into TM1 within a certain time period when it comes to the idea of just deleting users and let the system add them back. This would only be a valid approach if they are using CAM security AND they have the user group assignments in AD. There are a lot of IBM customers that use native security or use CAM but only have local groups. That latter group would include every single Cloud customer since IBM's cloud offering does not currently support the integration of AD groups into TM1.

For our SOX compliance we use a setup similar to what lotsaram suggested except we take it a notch further. We have several different logs (thanks to the wonderful help from this user group) and we have a job that copies the contents of these into SQL tables every night. From there I can run a SQL query at any point that will give me a list of users that have not logged into TM1 in a set number of days. Once a quarter we run that using 90 days as the parameter and then delete all users that have not logged into TM1 during that time frame.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

User avatar
Elessar
Community Contributor
Posts: 194
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Location: Russia

Re: To know Active Users & Inactive Users of TM1

Post by Elessar » Fri Jan 17, 2020 1:30 pm

There is another rather extreme idea. Our client used it to detect which (of 2000) reports in Cognos BI are really in use: just removed access to all the reports and waited for the tickets like "Hey, where is my favorite report?" in their Service Desk. 2 weeks gave ~90% of active reports, The last ticket was after a year. ~1000 reports were deleted.

You can just remove all the users from all the groups and restore access if they ask.
Best regards, Alexander Dvoynev

User avatar
macsir
Community Contributor
Posts: 695
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365
Contact:

Re: To know Active Users & Inactive Users of TM1

Post by macsir » Tue Jan 21, 2020 8:46 pm

tomok wrote:
Fri Jan 17, 2020 12:24 pm
It doesn't really matter if the OP is talking about users that aren't currently logged in or users that have not logged into TM1 within a certain time period when it comes to the idea of just deleting users and let the system add them back. This would only be a valid approach if they are using CAM security AND they have the user group assignments in AD. There are a lot of IBM customers that use native security or use CAM but only have local groups. That latter group would include every single Cloud customer since IBM's cloud offering does not currently support the integration of AD groups into TM1.

For our SOX compliance we use a setup similar to what lotsaram suggested except we take it a notch further. We have several different logs (thanks to the wonderful help from this user group) and we have a job that copies the contents of these into SQL tables every night. From there I can run a SQL query at any point that will give me a list of users that have not logged into TM1 in a set number of days. Once a quarter we run that using 90 days as the parameter and then delete all users that have not logged into TM1 during that time frame.
Why not loading into TM1 back again? I use TI process to read that login file directly to cubes as the format is quite consistent. From the cube, you can have a easier user activity analysis. :lol:
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

luizg2019
Posts: 17
Joined: Thu Sep 12, 2019 11:02 pm
OLAP Product: TM1 - PAW & Architect
Version: PA 2.0.7 - PAW 2.0.44
Excel Version: office 2016

Re: To know Active Users & Inactive Users of TM1

Post by luizg2019 » Wed Jan 22, 2020 8:55 pm

lotsaram wrote:
Fri Jan 17, 2020 8:12 am
However, I have since discovered a better and more convienient way. Just add the following lines of code to your tm1s-log.properties file in the configuration directory.

Code: Select all

log4j.appender.USR=org.apache.log4j.SharedMemoryAppender
log4j.appender.USR.File=usrcon.log
log4j.appender.USR.MaxFileSize=5 MB
log4j.appender.USR.MaxBackupIndex=10
log4j.appender.USR.TimeZone=Local
log4j.logger.TM1.Login=DEBUG, USR
Hello, thanks for sharing this tip.
If users login via PAW, each time they write new data, or change the dimension element, load a new view, etc... a new login is added to the log. Is there any way where we can only log their main login and not each transaction?

Thank you!

lotsaram
MVP
Posts: 3408
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: To know Active Users & Inactive Users of TM1

Post by lotsaram » Thu Jan 23, 2020 11:10 am

luizg2019 wrote:
Wed Jan 22, 2020 8:55 pm
Hello, thanks for sharing this tip.
If users login via PAW, each time they write new data, or change the dimension element, load a new view, etc... a new login is added to the log. Is there any way where we can only log their main login and not each transaction?
This is an artifact of how the front end is written in PAW. From the server perspective a new thread and login request is established so it will be logged as a connection request. There's nothing you can do about it, it is what it is.
However from a use case perspective of tracking last activity by a user it doesn't really have an impact. If using a separate log then even with every request and several hundred users the log file is still going to be relatively small and manageable. So my 2c is just don't worry about it.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

moby91
MVP
Posts: 217
Joined: Fri Mar 11, 2011 2:18 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2003 2007

Re: To know Active Users & Inactive Users of TM1

Post by moby91 » Thu Jan 23, 2020 1:25 pm

luizg2019 wrote:
Wed Jan 22, 2020 8:55 pm
lotsaram wrote:
Fri Jan 17, 2020 8:12 am
However, I have since discovered a better and more convienient way. Just add the following lines of code to your tm1s-log.properties file in the configuration directory.

Code: Select all

log4j.appender.USR=org.apache.log4j.SharedMemoryAppender
log4j.appender.USR.File=usrcon.log
log4j.appender.USR.MaxFileSize=5 MB
log4j.appender.USR.MaxBackupIndex=10
log4j.appender.USR.TimeZone=Local
log4j.logger.TM1.Login=DEBUG, USR
Hello, thanks for sharing this tip.
If users login via PAW, each time they write new data, or change the dimension element, load a new view, etc... a new login is added to the log. Is there any way where we can only log their main login and not each transaction?

Thank you!
You may add the line
log4j.additivity.TM1.Login=false
so that logins are written to the usrcon.log file only.

Post Reply