Reporting on Users’ Last Login Date

A frequently asked question by Connect users or admins is ‘How do I get a report that includes when the last time a user logged into the system?’.  This information is referred to as ‘last-login-date’ in Adobe Connect…  This is possible to get 2 different ways, although with 1 of them, you need an ON PREMISE (licensed) system because you need to get to this information via a direct database query.  This obviously is not an option on our hosted platform.

Option 1: (Hosted and On-Premise):

The report for Last Login Date is in the system when you go to: Reports > System Usage > Group Usage > {select a group} > Next > (no filter) >Create Report

It will give you all the users in that GROUP you selected, and their “Last Login Date”.

HOWEVER, you have to do by group to get the results and certainly you don’t want to have to run this report on one group at a time if you have a lot of groups…One thing you could do is have a group that by default, everyone in the system is a member of.  That way, you can report on that one group (if you add all existing groups into that one group).  For the sake of this article, I am just running this report on my ‘Administrators’ group in the screenshot below (where there are only two users in that group, both named Jim Johnson).

Another option (and better in my opinion) is to bypass this report in the product and go the SQL route, which is much faster and will allow you to get at exactly what data you want… Here’s the SQL query:

-List of all users in an account and last time they logged in –

SELECT p.PRINCIPAL_ID
,p.NAME
,p.LOGIN
,max(us.DATE_CREATED) AS LastLogin
FROM pps_principals p
LEFT OUTER JOIN PPS_GROUP_MEMBERS g ON p.principal_id = g.principal_id
LEFT OUTER JOIN PPS_USER_SESSIONS us ON p.principal_id = us.user_id
WHERE g.GROUP_ID =’xxxxxx’
AND p.account_id = 7
AND p.DISABLED IS NULL
GROUP BY p.PRINCIPAL_ID
,p.NAME
,p.LOGIN

Substitute the xxxxxx value with the ID of your {everyone} group.  You can find this in your PPS_PRINCIPALS table.   It’s a hidden group that all principals belong to by default in Adobe Connect.

Select the ‘{everyone}’ group with a corresponding principal-id that is NOT the top negative value. The Account_ID for the right {everyone} group should = 7.

In the query, the account_id will = 7 for all on-premise installations of Connect, so leave that alone.

An example below of what this looks like (where my system’s {everyone} group is ‘11019’. It may differ on your system.  The output only has 2 users listed, as the system is just a test system.  But for the sake of showing how the results look, it will show the user’s Principal-ID, Name (first+last), Login, and most importantly, the ‘LastLogin’ value (the name derived from the query itself, which can be changed). Of course you can also just run this query on other individual groups depending on your requirements.