Clarity

  • 1.  Users Login/Logout History

    Posted Nov 07, 2017 08:43 AM

    Dears,

     

    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_Login
    FROM 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?

     

    Thanks.



  • 2.  Re: Users Login/Logout History

    Posted Nov 07, 2017 08:57 AM

    Hi 

    When you say you cannot get any results, is the query erroring out or it returns 0 rows?

     

    Regards,

    Srikanth G



  • 3.  Re: Users Login/Logout History

    Posted Nov 07, 2017 09:09 AM

    It returns 0 rows.



  • 4.  Re: Users Login/Logout History

    Posted Nov 07, 2017 09:18 AM

    Gamze wrote:

     

    It returns 0 rows.

    That SQL in the other thread relies on setting up some jobs first, have you done that?



  • 5.  Re: Users Login/Logout History

    Posted Nov 07, 2017 09:05 AM

    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.



  • 6.  Re: Users Login/Logout History

    Posted Nov 07, 2017 09:46 AM

    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. 



  • 7.  Re: Users Login/Logout History

    Posted Nov 07, 2017 09:22 AM

    Have you executed or schdeuled the "Tomcat access log import/analyze job" ?



  • 8.  Re: Users Login/Logout History

    Posted Nov 07, 2017 09:53 AM

    No I don't. If I executed now, is it be useful? I need this list for internal audit. So I need history. 



  • 9.  Re: Users Login/Logout History

    Posted Nov 07, 2017 09:53 AM

    Are those inactive users running jobs or processes?



  • 10.  Re: Users Login/Logout History

    Posted Nov 07, 2017 10:01 AM

    Yes, they could be on the processes which are canceled or waiting on the process' step.



  • 11.  Re: Users Login/Logout History
    Best Answer

    Posted Nov 07, 2017 10:12 AM

    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.