Clarity

Hi,     We are  using Clarity 7.5.3 FP 06 and MS SQL server 2000.  I like to find the associates who logged into the Clarity application for past two months.       When I checked the database table of CMN_SEC_USERS, the column name LAST_LOGGED_IN_DATE has been defined to track the associate who logged in to Clarity. But the values populated as NULL into these columns. Also the technical document shows that this column is a deprecated column and its no longer in use.       Please let me know is there is any way to find the resource who login to Clarity in past two months.     Thanks,  Vimalraj R.

There are session tables which are cleaned when the clean user session job is run. So you cannot get the data you need afterwards. But you can start tracking that daily if you need it. There is going to be a fair amount of data since a user can have moren than one session open at the same time.  Martti K.Xinify

Unfortunately I do NOT think that there is a simple way to get this (apparently simple) information.  The "audit sessions" are cleared down too often and the last logon on date field is depreciated (as noted above).  What we use is the CMN_SEC_USERS.LAST_PWD_CHANGE date - we force users to change password every 60 days, so if a user has NOT changed their password in the last 60 days then this column will tell us who has NOT logged on in that period.   (We then LOCK those users).  Hope that helps some!  David MortonCapgemini

The field that was "depreciated" in 753 is actually populated in 8.1 (+?).  So you can build a simple portlet yourself based on CMN_SEC_USERS.LAST_LOGGED_IN_DATE.    David Morton
Capgemini

I'm actually running into this problem right now and I see absolutely no way to accomplish this.  Your logic here also makes no sense:   Dave wrote:
What we use is the CMN_SEC_USERS.LAST_PWD_CHANGE date - we force users to change password every 60 days, so if a user has NOT changed their password in the last 60 days then this column will tell us who has NOT logged on in that period.   (We then LOCK those users).   Let's say all accounts are set to require a password change every 60 days.  They changed their password on Day 1 and today is day 59.  The user logs into today on day 59 and they are allowed to login.  Tomorrow will be the first day they will be prompted to change their password and instead of seeing that screen they will be locked out because your logic has locked them out.  I just don't want anyone to think that there is a workaround for this issue...because there isn't!

In 7.5.3 we used that logic - the users we locked were ones that had not changed their password in the last 90 days (with the system forcing users to change their passwords every 60 days).  Since we moved from 7.5.3 we can just use the CMN_SEC_USERS.LAST_LOGGED_IN_DATE like has been mentioned a few times in this thread.    

When you get to r8.1+ with license information portlets you can see it there. See
TEC541831
How to Determine When a User Last Logged in to Clarity?

Martti K.

You can try the SQL below.  SELECT a.user_id,
 a.session_start_date,
 a.session_end_date,
 u.user_name
FROM cmn_session_audits a,
 cmn_sec_users u
WHERE   a.user_id = u.id AND
  a.session_start_date >= SYSDATE - 60
ORDER BY u.user_name  This assumes that you are using Oracle as it uses the SYSDATE function

FYI, I have submitted an enhancement request for something similar a year ago...   a portlet to display active, logged in users.  I have written my own sql to show such data but it isn't perfect.   As far as the last two months...   you'd have to go through app logs or sysout logs if you use WebSphere.  You can enable auditing on the resource side I think which could provide you more specifics o nyour resources as well.  

Here is the sql to show who has logged in for the past 60 days.   Note it has nothing to do with sessions.   Just simply the fact that they logged in.  SELECT LAST_NAME, FIRST_NAME, USER_NAME, LAST_LOGGED_IN_DATE
FROM niku.CMN_SEC_USERS
where LAST_LOGGED_IN_DATE >= DATEADD(day, -60, CURRENT_TIMESTAMP)

Hi,  we are still in 7.5.2 ,authentication is done via  LDAP and also running the clean user session job weekly to avoid million of records in Audit trail.As u know we can't use LAST_LOGGED_IN_DATEas it is allways null so put an custom trigger to cpature that Last Login Date.Now happy to hear that LAST_LOGGED_IN_DATE is working from 8.1.  cheers,sundar