I need users access information into the system(last Login / logout time).
When I execute the sql query is below, the results aren't reasonable. Because I see the inactive user but it logged in the system. They can't login the system.
SELECT ls.USER_ID, csu.user_name , csu.first_name+' '+csu.last_name Full_Name , CONVERT(date,ls.created_date) Date_Of_LoginFROM log_sessions ls INNER JOIN cmn_sec_users csu ON csu.id = ls.user_id INNER JOIN SRM_RESOURCES res on csu.ID = res.ID
group by csu.user_name , csu.first_name+' '+csu.last_name ,ls.USER_ID ,CONVERT(date,ls.created_date) ,res.FULL_NAME order by CONVERT(date,ls.created_date) desc
I check this subject "Clarity PPM Login History " . I executed the query. I can't give any result.
How can I reach the users login informations?
When you say you cannot get any results, is the query erroring out or it returns 0 rows?
It returns 0 rows.
Gamze wrote: It returns 0 rows.
That SQL in the other thread relies on setting up some jobs first, have you done that?
CMN_SEC_USERS.LAST_LOGGED_IN_DATE is really the only reliable thing I think.
The problem with looking for "session information" (such as when did a user log out) is that its always guesswork since the user may just close their browser and never actually log out, the session might ultimately expire of course if you have set a timeout but you are starting to "guess" at things by trying to analyse that info.
I've checked "CMN_SEC_USERS.LAST_LOGGED_IN_DATE" results. It's quite detailed. But results are still senseless. Some inactive users seem logged in today.
Have you executed or schdeuled the "Tomcat access log import/analyze job" ?
No I don't. If I executed now, is it be useful? I need this list for internal audit. So I need history.
Are those inactive users running jobs or processes?
Yes, they could be on the processes which are canceled or waiting on the process' step.
Gamze Tomcat access log import/analyze job populates the data in the log_sessions and log_details tables. The data retention is governed by the 'Session token retention in days' and 'Log retention in days' parameters set in the Delete Log Analysis Data job. Make sure the parameters are set to days you need to go back in time and also run the tomcat access log import/analyze job if its not run and try to execute the query again.