Client last log-in date

Suggest and discuss enhancements for TM1
Post Reply
User avatar
wilsonric
Posts: 15
Joined: Mon Jan 10, 2011 4:36 am
OLAP Product: TM1
Version: 2.0.9
Excel Version: 365 MSO 16-0-13127
Location: Sydney, Australia

Client last log-in date

Post by wilsonric »

The ability to record the last date a client logged in to TM1 would be very useful from a client/licence management perspective.
If this information was recorded in the }ClientProperties cube it would be easy to see who is under utilising (or not using at all) their TM1 account. We could then utilise this licence for someone else, and provide the redundant or very casual user with an alternative data source - ie. a monthly report from Finance.

Anyone else interested?
Regards,
Richard
AnthonyT
Posts: 42
Joined: Mon May 19, 2008 10:25 am
OLAP Product: TM1, EV
Version: 9.0 9.1 9.4 9.5 10.1 10.2
Excel Version: 2003 2007 2010 2015
Location: London, UK

Re: Client last log-in date

Post by AnthonyT »

You can find this out by importing the Audit log file (provided that you have them enabled), and you can see what has happened by day

With some TI manipulation you see when a client logged in last, changed passwords, changed groups, as well as server shutdowns / restarts, and process / dim / cube changes

(Or I've done something wrong !)
Anthony

That's no moon - that's a space station
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Client last log-in date

Post by lotsaram »

wilsonric wrote:The ability to record the last date a client logged in to TM1 would be very useful from a client/licence management perspective.
If this information was recorded in the }ClientProperties cube it would be easy to see who is under utilising (or not using at all) their TM1 account. We could then utilise this licence for someone else, and provide the redundant or very casual user with an alternative data source - ie. a monthly report from Finance.

Anyone else interested?
Provided logging is on for }ClientProperties and ClientPropertiesSyncInterval parameter is used then this is a quite straightforward piece of coding to DIY.
Bedrock.Server.UserLogins.Caller.pro
(7.2 KiB) Downloaded 3416 times
Bedrock.Server.UserLogins.pro
(3.7 KiB) Downloaded 2220 times
(Note I don't know if this has been officially added to bedrock, as I have adopted bedrock as a standard set of utilities I just adopted the naming and coding conventions ...)

All you need to do is run the caller process, it will automatically create the logging cube (need to run it 2x to initially set up the metadata). Then ongoing schedule the caller process in a chore to run daily and process the last 1 or 2 days only of tm1s logging output (assuming there is at least a daily SaveDataAll.)

If there is no change to naming conventions vs. the default param values then after the process has run you can get clients ordered by last login date with the following MDX.

Code: Select all

{ORDER(
  {TM1SUBSETALL( [}Clients] )},
  [SYS_UserProperties].([SYS_UserProperties_M].[Last Login Ordinal]),
  BDESC
)}
Obviously the last login is only as current as 1/ the last time the process was run and 2/ the last time a save data was done. However for the purposes of compliance and tracking which users haven't accessed the system it is quite adequate.
mnaithan
Posts: 28
Joined: Fri Mar 06, 2015 10:07 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2007

Re: Client last log-in date

Post by mnaithan »

Hello

I ran these bedrock processes for last login date, but i am gettting blank cube automatically created by process (SYS_UserProperties),I have changed logging 'Yes' for }ClientProperties and added parameter ClientPropertiesSyncInterval in cfg file with value 180 (sec) and did SaveData before runnning, what could I might be missing,has anybody else tried this

Thanks

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

Re: Client last log-in date

Post by lotsaram »

mnaithan wrote:Hello
I ran these bedrock processes for last login date, but i am gettting blank cube automatically created by process (SYS_UserProperties),I have changed logging 'Yes' for }ClientProperties and added parameter ClientPropertiesSyncInterval in cfg file with value 180 (sec) and did SaveData before runnning, what could I might be missing,has anybody else tried this
Thanks
Micky
ClientPropertiesSyncInterval is a dynamic parameter in that you can change the value while the server is in session and it will be picked up and applied. However the parameter isn't dynamic in the sense that if it is added while the server is up then it won't register but requires a server restart to switch it "on". As long as ACTIVE Status is being recorded in }ClientProperties by having ClientPropertiesSyncInterval activated and transaction logging is enabled in }CubeProperties for }ClientProperties then there should be no problems. I have used this process or a derivative of it with every system I have built or maintained since 2011 (which is quite a lot) and never had any issues with it.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
mnaithan
Posts: 28
Joined: Fri Mar 06, 2015 10:07 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2007

Re: Client last log-in date

Post by mnaithan »

Thanks for the reply, I did restart the Server

just to confirm ClientPropertiesSyncInterval has to be manually added in cfg file as its not there ..?, and

if I add ClientPropertiesSyncInterval parameter and restart the Server then its going to start loging from that day ( I mean suppose I did it today so and ran the process to show past 90 day clients ,then it wont show any one logged before today ..?, and what should be the idle value for ClientPropertiesSyncInterval
mnaithan
Posts: 28
Joined: Fri Mar 06, 2015 10:07 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2007

Re: Client last log-in date

Post by mnaithan »

Its working now, may be it was some Server issue
mnaithan
Posts: 28
Joined: Fri Mar 06, 2015 10:07 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2007

Re: Client last log-in date

Post by mnaithan »

Hello,

have to come back on this one, actually after how much time the user login details should be reflected in the 'SYS_UserProperties' Cube, my understanding is that is should depend on ClientPropertiesSyncInterval value in cfg file, but in my case I have value 1800 for ClientPropertiesSyncInterval (1800secs = 30 min) so it should refresh in 30 mins time but I am not able to see the change in SYS_UserProperties' Cube
and does it shows current time or server time in last login date , and where Sever time can be found in TM1 , thanks
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Client last log-in date

Post by lotsaram »

mnaithan wrote:Hello,

have to come back on this one, actually after how much time the user login details should be reflected in the 'SYS_UserProperties' Cube, my understanding is that is should depend on ClientPropertiesSyncInterval value in cfg file, but in my case I have value 1800 for ClientPropertiesSyncInterval (1800secs = 30 min) so it should refresh in 30 mins time but I am not able to see the change in SYS_UserProperties' Cube
and does it shows current time or server time in last login date , and where Sever time can be found in TM1 , thanks
Really, really I suggest reading the manuals and getting familiar with the software on a basic level. The TI process does contain this comment in the header which should be sufficient explanation.
# This process processes tm1sYYYYMMDDHHMMSS.log transaction files in the server logging directory and loads the date stamp
# of changes to the ACTIVE field of the ClientProperties cube to a tracking cube.
# All tm1s.log files from a given start date are processed. The active tm1s.log file is not processed. To ensure all logs are processed
# perform a SaveData prior to running.

# NOTE: requires the ClientPropertiesSyncInterval tm1s.cfg parameter AND logging switched on in the ClientProperties cu be in order to function
Setting ClientPropertiesSyncInterval=1800 will update the }CLientProperties cube every 30 minutes. (a user could easily log in and out within this time, I would set it to less). But how do you expect the SYS_UserProperties cube; which you created to be updated every 30 minutes, by some kind of magic? This cube is only going to be updated by the TI process which reads from the saved tm1s.log files. So the values in the SYS_UserProperties cube are only going to be as current as
- the last time the process was run
AND
- the last time a SaveDataAll was performed

This cube is designed to support a compliance requirement to track when users last accessed the system with the primary aim to identify users with no record of access for >x days (usually x=90 for SOx) to then remove access rights for such users. Usually the process to update the cube would be scheduled to run 1x per day, typically directly following a SaveData.

The values in the cube are only going to update more frequently if the process is scheduled to run more frequently and there is a frequent SaveDataAll (which is not typically recommended).

If you want a more immediate solution to track who is logged on I suggest the following approaches.

If you just want to know who is logged on right now shorten ClientPropertiesSyncInterval to something like 1 - 5 minutes and just build a report off the }ClientProperties cube itself.

Or for an alternate (& probably better) solution to tracking the last login/out stamp for a user you could do the following...
Add the following lines of code to the tm1s-log.properties file

Code: Select all

log4j.logger.TM1.Login = DEBUG, LOGIN
log4j.appender.LOGIN = org.apache.log4j.SharedMemoryAppender
log4j.appender.LOGIN.MemorySize = 1MB
log4j.appender.LOGIN.File = tm1login.log
log4j.appender.LOGIN.MaxFileSize = 2MB
log4j.appender.LOGIN.MaxBackupIndex = 5
log4j.appender.LOGIN.TimeZone = Local
Then simply write a new TI process to read the new tm1login.log file and write the timestamps to the SYS_UserProperties cube. For all intents and purposes this TI could be scheduled as often as you like with no ill effect on the TM1 server.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
mnaithan
Posts: 28
Joined: Fri Mar 06, 2015 10:07 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2007

Re: Client last log-in date

Post by mnaithan »

Thanks for the reply, actually I did run SaveData and TI ,apologies if I forgot to mention that, appreciate your explaination
Eighty3
Posts: 5
Joined: Wed Dec 12, 2018 5:12 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Re: Client last log-in date

Post by Eighty3 »

lotsaram wrote: Thu Jan 19, 2012 8:17 am
wilsonric wrote:The ability to record the last date a client logged in to TM1 would be very useful from a client/licence management perspective.
If this information was recorded in the }ClientProperties cube it would be easy to see who is under utilising (or not using at all) their TM1 account. We could then utilise this licence for someone else, and provide the redundant or very casual user with an alternative data source - ie. a monthly report from Finance.

Anyone else interested?
Provided logging is on for }ClientProperties and ClientPropertiesSyncInterval parameter is used then this is a quite straightforward piece of coding to DIY. Bedrock.Server.UserLogins.Caller.pro
Bedrock.Server.UserLogins.pro
(Note I don't know if this has been officially added to bedrock, as I have adopted bedrock as a standard set of utilities I just adopted the naming and coding conventions ...)

All you need to do is run the caller process, it will automatically create the logging cube (need to run it 2x to initially set up the metadata). Then ongoing schedule the caller process in a chore to run daily and process the last 1 or 2 days only of tm1s logging output (assuming there is at least a daily SaveDataAll.)

If there is no change to naming conventions vs. the default param values then after the process has run you can get clients ordered by last login date with the following MDX.

Code: Select all

{ORDER(
  {TM1SUBSETALL( [}Clients] )},
  [SYS_UserProperties].([SYS_UserProperties_M].[Last Login Ordinal]),
  BDESC
)}
Obviously the last login is only as current as 1/ the last time the process was run and 2/ the last time a save data was done. However for the purposes of compliance and tracking which users haven't accessed the system it is quite adequate.
Thank you for the code. You made my life easy.

I noticed that the log timestamp is GMT (as per IBM this cannot be changed within the file. Please correct me if I am wrong.
NOTE: Changing the log4j.appender.S1.TimeZone to LOCAL will not change what's in the actual log file on disk. This is always recorded in GMT. It does change the times to local time when you view the log file via Architect.
Source: https://www-01.ibm.com/support/docview. ... wg21459102 ).


I modified the code in Bedrock.Server.UserLogins.pro with following changes and I noticed that when I ASCIIOUTPUT the data, only the first line get the timezone conversion applied

Code: Select all

sTimeStamp =SubSt(vTimeStamp, 1, 4)  |'-'|   SubSt(vTimeStamp, 5, 2)  |'-'|  SubSt(vTimeStamp, 7, 2)    |' '| SubSt(vTimeStamp, 9, 2) |':'| SubSt(vTimeStamp, 11, 2) |':' | SubSt(vTimeStamp, 13, 2)  ;

nDateTime = ParseDate ( sTimeStamp, 'yyyy-MM-dd h:m:s' ) ;
NewDateFormatter ( 'en_au', 'Australia/Sydney', 'serial', 'medium', 'datetime'  ) ;
sDateTimeAU = FormatDate ( nDateTime, 0 ) ;
ASCIIOUTPUT ( 'date.txt',sTimeStamp, NumberToString ( nDateTime ) ,sDateTimeAU ) ;
Source: https://exploringtm1.com/date-time-functions-tm1-10-2/

ASCIIOUTPUT

Code: Select all

"2019-12-04 03:37:10","21887.150810185","04/12/2019 2:37:10 PM"
"2019-12-04 03:37:10","21886.692476852","2019-12-04 3:37:10"
Sourcefile: tm1s*.log file I am loading has 2 login records as below.

Code: Select all

"","20191204033710","20191204033710","*","S","ACTIVE","","}ClientProperties","CAMID(""TMB:u:b98a4f20c97a33429de8405f91fc5948"")","STATUS",""
"","20191204033710","20191204033710","*","S","","ACTIVE","}ClientProperties","CAMID(""TMB:u:b98a4f20c97a33429de8405f91fc5948"")","STATUS",""
The SYS_Userproperties cube has "2019-12-04 3:37:10" stamped against the user. Any idea what I am doing wrong.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Client last log-in date

Post by Wim Gielis »

Eighty3 wrote: Wed Dec 04, 2019 5:50 amAny idea what I am doing wrong.
Sure. I bet you did not consult the documentation on the functions FormatDate, ParseDate, NewDateFormatter.

In fact, FormatDate and ParseDate have 3 arguments each. Where do you use those 3 in your code ?
That Index argument is the result of a call to the NewDateFormatter function.
How do you expect your FormatDate function to use the result of a/the NewDateFormatter function, if you don't use its outcome ?

You linked to an article on exploringtm1, yet you did not use the syntax from that article.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply