User login,logout and Session Info

Hello,
i am able to fetch the active users info by system.util.getSessionInfo(),
I Created Audit Profile in my project, Below Query :Select “EVENT_TIMESTAMP”,“ACTOR”,“ACTION” from audit_events where “ACTOR” = ‘Shanmukha’ and “ACTION” like ‘log%’ . i am Fetching the User LOG Details in a Single column but
I would like to see the Specific Users Login, Logout, Session Time with respect to Time in individual columns respective table.
please help me on this.
Thank you.
image

What sql database are you using, and what version?

Try the answer in the OP from here: https://stackoverflow.com/questions/42655443/login-logout-time-in-sql-server

Hi Nminchin,
i am using
Database:PostgreSQL 10.10 on x86_64-pc-linux-gnu and Ignition Version: 8.0.15

Try something like this:

Select *, "NEXT_TSTAMP" - "EVENT_TIMESTAMP" As "DURATION"
From (
  Select *, lead("ACTION") OVER (Partition By "ACTOR" Order By "EVENT_TIMESTAMP") As "NEXT_TSTAMP"
  From audit_events
  Where .......
) subq
Where "ACTION"='login'
Order By EVENT_TIMESTAMP

The last login, if not yet logged out, will have nulls for NEXT_TSTAMP and DURATION.

2 Likes