Client last log-in date
- 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
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?
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
Richard
-
- 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
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 !)
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
That's no moon - that's a space station
-
- MVP
- Posts: 3685
- 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
Provided logging is on for }ClientProperties and ClientPropertiesSyncInterval parameter is used then this is a quite straightforward piece of coding to DIY. (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 ...)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?
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
)}
-
- 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
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
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
-
- MVP
- Posts: 3685
- 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
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.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
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- 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
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
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
-
- 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
Its working now, may be it was some Server issue
-
- 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
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
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
-
- MVP
- Posts: 3685
- 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
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.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
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# 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
- 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
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- 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
Thanks for the reply, actually I did run SaveData and TI ,apologies if I forgot to mention that, appreciate your explaination
-
- 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
Thank you for the code. You made my life easy.lotsaram wrote: ↑Thu Jan 19, 2012 8:17 amProvided 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.prowilsonric 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?
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.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.Code: Select all
{ORDER( {TM1SUBSETALL( [}Clients] )}, [SYS_UserProperties].([SYS_UserProperties_M].[Last Login Ordinal]), BDESC )}
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.
Source: https://www-01.ibm.com/support/docview. ... wg21459102 ).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.
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 ) ;
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"
Code: Select all
"","20191204033710","20191204033710","*","S","ACTIVE","","}ClientProperties","CAMID(""TMB:u:b98a4f20c97a33429de8405f91fc5948"")","STATUS",""
"","20191204033710","20191204033710","*","S","","ACTIVE","}ClientProperties","CAMID(""TMB:u:b98a4f20c97a33429de8405f91fc5948"")","STATUS",""
-
- MVP
- Posts: 3184
- 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
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
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