Qlik Community

Qlik Sense Integration, Extensions, & APIs

Discussion board where members can learn more about Integration, Extensions and API’s for Qlik Sense.

Luminary
Luminary

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?




5 Replies
kevincase
Contributor II

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

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

Luminary
Luminary

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

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

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

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

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

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

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

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"

Community Browser