How to get list of currently active sessions and/or users?

[mountaindude]

I am looking into ways to get a list of currently active sessions. Or even better, a list of users with active sessions.

I could listen to session start and end events, and from that keep track of who has an open session at any given time, but surely there is an API to get the same information…? Can't seem to find it though.

Ideas?

[kevincase]

Goran,

The way I do it is by connecting to the postgres database directly. I run the following query:

select "Users"."Name", "LicenseUserAccessUsages"."ModifiedByUserName" as "Login", "LicenseUserAccessUsages"."UseStartTime" - Interval '4 hours' as "SessionStart", "LicenseUserAccessUsages"."LatestActivity" - Interval '4 hours' as "LastActivity", 'User Access' as "AccessType"

from "LicenseUserAccessUsages", "LicenseUserAccessTypes", "Users"

where "LicenseUserAccessUsages"."Deleted" = 'f'

and "LicenseUserAccessUsages"."LatestActivity" >= Now() + '3:30'

and "LicenseUserAccessTypes"."ID" = "LicenseUserAccessUsages"."UserAccessType_ID"

and "Users"."ID" = "LicenseUserAccessTypes"."User_ID"

union

Select "Users"."Name", "LicenseLoginAccessUsageSessions"."ModifiedByUserName" as "Login", "LicenseLoginAccessUsageSessions"."CreatedDate" - Interval '4 hours' as "SessionStart", "LicenseLoginAccessUsageSessions"."LatestActivity" - Interval '4 hours' as "LastActivity", 'Login Access' as "AccessType"

From "LicenseLoginAccessUsageSessions", "LicenseLoginAccessUsages", "Users"

where "LicenseLoginAccessUsageSessions"."Deleted" = 'f'

and "LicenseLoginAccessUsageSessions"."LatestActivity" >= Now() + '3:30'

and "LicenseLoginAccessUsages"."ID" = "LicenseLoginAccessUsageSessions"."LicenseLoginAccessUsage_ID"

and "Users"."ID" = "LicenseLoginAccessUsages"."User_ID"

It's a close representation but may be off some on the User access.  I just look at the last activity to see if they are truly active.

Hope this helps.

Kevin

[mountaindude]

Very cool, kevincase‌!

Will certainly give that a try, meanwhile I ended up building some node.js code that listens for session begin/end events from the Sense logging framework, and then keeps a dict of what users are currently in "active" state. By no means fool proof, but should be good enough for our purposes.

One concern with these kinds of hacks is how future proof they are... If Qlik changes the schema of the Postgres db, or the format of the logging events, we're toast... Oh well - that's for another day

[kevincase]

Goran,

You are correct.  The query that I originally wrote for 2.1 did not work in 2.2.  I had to make a slight modification as the data was recorded a little differently.

This definitely won't work in 3.0 as the postgres database has been replaced with casandra.

Back to the drawing board!

I wrote this query as a quick solution and it works for me for the most part. 

[vmahmomo]

My be the answer is little late, but you can use Session Monitor app to find out active user in Qlik Sense environment.

I don't see that you can also kill their session but at least you can see who logged in currently. ( remember to run the session monitor app to get the latest information)

[kevincase]

Mohsin,

Yes, that will work but someone could connect between the time the application was re-loaded and the time you are viewing the information.

For 3.2 I run the following:

--Need to adjust time interval for daylight saving time -> Spring Interval = 4 Hours, Fall Interval = 5 Hours

select "Users"."Name", "LicenseUserAccessUsages"."ModifiedByUserName" as "Login", "LicenseUserAccessUsages"."UseStartTime" - Interval '4 hours' as "SessionStart", "LicenseUserAccessUsages"."LatestActivity" - Interval '4 hours' as "LastActivity", 'User Access' as "AccessType"

from "LicenseUserAccessUsages", "LicenseUserAccessTypes", "Users"

where "LicenseUserAccessUsages"."Deleted" = 'f'

and "LicenseUserAccessUsages"."LatestActivity" >= Now() + '3:30'

and "LicenseUserAccessTypes"."ID" = "LicenseUserAccessUsages"."UserAccessType_ID"

and "Users"."ID" = "LicenseUserAccessTypes"."User_ID"

union

Select "Users"."Name", "LicenseLoginAccessUsageSessions"."ModifiedByUserName" as "Login", "LicenseLoginAccessUsageSessions"."CreatedDate" - Interval '4 hours' as "SessionStart", "LicenseLoginAccessUsageSessions"."LatestActivity" - Interval '4 hours' as "LastActivity", 'Login Access' as "AccessType"

From "LicenseLoginAccessUsageSessions", "LicenseLoginAccessUsages", "Users"

where "LicenseLoginAccessUsageSessions"."Deleted" = 'f'

and "LicenseLoginAccessUsageSessions"."LatestActivity" >= Now() + '3:30'

and "LicenseLoginAccessUsages"."ID" = "LicenseLoginAccessUsageSessions"."LicenseLoginAccessUsage_ID"

and "Users"."ID" = "LicenseLoginAccessUsages"."User_ID"

For June 2018 release, this will work:

--Need to adjust time interval for daylight saving time -> Spring Interval = 4 Hours, Fall Interval = 5 Hours

select "Users"."Name", "LicenseUserAccessUsages"."ModifiedByUserName" as "Login", "LicenseUserAccessUsages"."UseStartTime" - Interval '4 hours' as "SessionStart", "LicenseUserAccessUsages"."LatestActivity" - Interval '4 hours' as "LastActivity", 'User Access' as "AccessType"

from "LicenseUserAccessUsages", "LicenseUserAccessTypes", "Users"

where "LicenseUserAccessUsages"."LatestActivity" >= Now() + '3:30'

and "LicenseUserAccessTypes"."ID" = "LicenseUserAccessUsages"."UserAccessType_ID"

and "Users"."ID" = "LicenseUserAccessTypes"."User_ID"

union

Select "Users"."Name", "LicenseLoginAccessUsageSessions"."ModifiedByUserName" as "Login", "LicenseLoginAccessUsageSessions"."CreatedDate" - Interval '4 hours' as "SessionStart", "LicenseLoginAccessUsageSessions"."LatestActivity" - Interval '4 hours' as "LastActivity", 'Login Access' as "AccessType"

From "LicenseLoginAccessUsageSessions", "LicenseLoginAccessUsages", "Users"

where "LicenseLoginAccessUsageSessions"."LatestActivity" >= Now() + '3:30'

and "LicenseLoginAccessUsages"."ID" = "LicenseLoginAccessUsageSessions"."LicenseLoginAccessUsage_ID"

and "Users"."ID" = "LicenseLoginAccessUsages"."User_ID"

[ken4runner]

Is this still accurate in current versions? I do not see any rows in two of the tables mentioned:

LicenseLoginAccessUsageSessions and LicenseLoginAccessUsages

are these still used, and if not, what tables currently have the session related info?