Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mountaindude
Partner Ambassador
Partner Ambassador

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

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?




Please mark the post as a solution if it provided you with a solution to the topic at hand. Thanks!
6 Replies
kevincase
Creator II
Creator II

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
Partner Ambassador
Partner Ambassador
Author

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

Please mark the post as a solution if it provided you with a solution to the topic at hand. Thanks!
kevincase
Creator II
Creator II

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
Contributor
Contributor

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
Creator II
Creator II

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
Creator
Creator

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?