Clarity

We are in clarity V13.3. 

There are few sessions in the table for users, who i am sure are not logged in. i also verified that they dont have any reports or jobs scheduled in their name. i tried running the 'clear sessions job' even though the session does not clear out in cmn_sessions table. 

ex: there is a inactive user who has a session in cmn_sessions table and has a last_logged_in_date matching in cmn_sec_users table

what else could i check and remove to stop the inactive user logging/consuming a license? 

Thanks,

Aish

It could be possible of a bug in 13.3 and cmn_sessions has nothing to do with license. For licence you can check the licence portlet.

Regards

Suman Pramanik 

Ok, my wrong wordings. I do not care about license here as we have more than required.

How do i make sure the inactive user do not have a session?

i want to know who are logged in & few inactive and locked users session are in session table which causes confusion whom i am sure are not logged in. There is a session for Active user who claims she cannot login. i am clueless..

Thanks,

Aish

CMN_SESSIONS is actually not that reliable and it had certain issues, so we designed a Job called Tomcat Analyse job which stores the data and that is available from 14.2, I would request you to plan for upgrade.

Just wondering if jobs and processes once initiated by that/those users can create such sessions.

When there is a job running, it will create a session i believe. but there are no jobs scheduled by these users.

Hi

You can try to use that NSQL portlet to audit sessions. It will exclude all BG sessions:

SELECT @SELECT:DIM:USER_DEF:IMPLIED:USER_LOGGINS:SQLWRAP.DIM_KEY:DIM_KEY@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:USER_LOGGINS:SQLWRAP.user_name:user_name@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:USER_LOGGINS:SQLWRAP.full_name:full_name@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:USER_LOGGINS:SQLWRAP.date_of_login:date_of_login@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:USER_LOGGINS:SQLWRAP.times_logged_in:times_logged_in@

 

FROM
(
select
rownum dim_key,
q.user_name user_name,
q.full_name full_name,
q.date_of_login date_of_login,
q.times_logged_in times_logged_in

from (

select
csu.user_name user_name,
csu.first_name||' '||csu.last_name full_name,
trunc(ls.created_date) date_of_login,
count(*) times_logged_in
from log_sessions ls, cmn_sec_users csu
where csu.id = ls.user_id
and ls.token in (select distinct session_cookie from log_details) ----- to exclude all the bg sessions
group by csu.user_name, csu.first_name||' '||csu.last_name, trunc(ls.created_date)
order by trunc(ls.created_date) desc, count(*) desc

) q

) SQLWRAP

WHERE @FILTER@

When you are 14.2 there are other more accurate ways to find out.

and then just by running job to clean up sessions or restarting services will "kill" session if user is not logged in anymore.

Thanks for sharing. As we are in 13.3 i cannot use the above portlet.

i did run the job which cleared an inactive user but the same appears in session table the next day.

Oopps.. apologies, I copied the wrong NSQL (for 14.2 or higher).

I meant this one for  13.3

SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:S.id:SessionID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:u.id:UserID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:u.user_name:UserName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:u.last_name:LastName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:u.first_name:FirstName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:s.created_date:LoggedInSince@

FROM CMN_SESSIONS S,CMN_SEC_USERS U
WHERE U.ID=S.USER_ID
AND S.ID= (SELECT MAX(ID) FROM CMN_SESSIONS WHERE USER_ID = S.USER_ID)

AND @FILTER@

Hello Aurora_Gaimon,
Thank you very much for sharing.
Could you tell me if it is possible to extract from the database the time that each user has been with the active session?
Thank you very much and greetings.

CMN_SESSION_AUDITS.session_start_date holds the start time of a current session.

Thanks Dave,

The problem is that this table does not show reliable data because for my user it shows logins during the whole night every 5 minutes or so and I have not logged in during the night.

A greeting.

If you have scheduled a recurring job (for example time slicing, but could be anything) which runs often, then each instance of that run would generate a session.

Then I understand that there is no way to know the time that each user has dedicated in the application. True?

Thank you, Dave.

CarlosBirlainSelfa82333808 wrote:

 

Then I understand that there is no way to know the time that each user has dedicated in the application. True?

 

yes there is a way, as I said "CMN_SESSION_AUDITS.session_start_date holds the start time of a current session"

If you are getting confused about which "session" is the online login and which session is a batch job, then that is a different issue, schedule batch jobs as an administration user rather than an application user to help that.

For just "last logged in time" for a user there is a last_logged_in_date on CMN_SEC_USERS which will tell you that but that is at user level not session level (so a user with multiple online sessions this will only tell you the latest one)

The problem is that in the "session_end_date" field it shows the null value in many of the registers and I can not calculate the time that the connected user has remained.

Yes its only showing you active sessions - when the session gets cleanly shut down then the record is no longer there. When a user does not cleanly exit (for example just closes their browser without logging off) then the record remains.

The log files contain information too, you should be able to spot logins and logouts in the activity log file, and session information in the application log file - depending on your version and logging level settings.

Hi Dave.

I do not see a clear solution to this problem. What I need is a portlet that shows by user and by date the time it has been with the session started. With these tables in the database, those records would not be reliable.

I appreciate your help, Dave. I do not like to waste your time.

OK, all I have been saying is that you can do that ^ easily using CMN_SESSION_AUDITS.session_start_date, but only for currently active sessions.