List the Last Login Date for all Users in Crowd
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Please note the queries on this page only shows users who logged in to Confluence at least once, to see the list of never logged in users please check How to check for users who never logged into Confluence.
Symptoms
Get a list of the last login date for all users in Crowd.
Cause
Unfortunately, this information is not available in Crowd via the UI. Querying the respective fields in the Crowd database will help fetch these details.
Resolution
Connect to your Crowd database and please run this query to get a list of usernames and last login time for each of these users.
Please note the Crowd database may not accurately report the last login date to other applications authenticating to Crowd if the Remember Me cookie is used.
For example: If a user logs into Jira and checks the Remember Me option when logging in, subsequent requests for authentication that are granted by the cookie will only be recognized by Jira and not seen by the Crowd database.
Select the appropriate query suited for your database language from the options below.
PostgreSQL
PostgreSQL
1
2
3
select cwd_user.user_name, to_timestamp(CAST(cwd_user_attribute.attribute_value as double precision)/1000)
from cwd_user_attribute, cwd_user
where cwd_user_attribute.user_id = cwd_user.id AND cwd_user_attribute.attribute_name = 'lastAuthenticated';MSSQL
MSSQL
1
2
3
select cwd_user.user_name,cwd_directory.directory_name, DATEADD(ss, CAST(cwd_user_attribute.attribute_value as BIGINT)/1000, '19700101') AS 'Last_Login'
from cwd_user_attribute, cwd_user, cwd_directory
where cwd_user_attribute.user_id = cwd_user.id [cwd_user.id] AND cwd_user.directory_id = cwd_directory.id [cwd_directory.id] AND cwd_user_attribute.attribute_name = 'lastAuthenticated';MySQL
MySQL
1
2
3
SELECT cwd_user.user_name, from_unixtime(cwd_user_attribute.attribute_value/1000)
FROM cwd_user, cwd_user_attribute
WHERE cwd_user_attribute.user_id = cwd_user.id AND cwd_user_attribute.attribute_name = 'lastAuthenticated'NB: If you use another database you may need to tweak the SQL above to match the required syntax.
Crowd stores the last login time based on epoch time. The MySQL from_unixtimestamp returns a Unix timestamp in seconds. Hence the above value in 'cwd_user_attribute.attribute_value' is divided by 1000 to discard the milliseconds.
đź’ˇ You can also further extend the queries above to only list Active and/or Inactive Users, example for PostgreSQL database below
List of Active Users with Last Login Date
1
select cwd_user.user_name, cwd_user.active, to_timestamp(CAST(cwd_user_attribute.attribute_value as double precision)/1000) from cwd_user_attribute, cwd_user where cwd_user_attribute.user_id = cwd_user.id AND cwd_user_attribute.attribute_name = 'lastAuthenticated' AND cwd_user.active = 'T';List of Inactive Users with Last Login Date
1
select cwd_user.user_name, cwd_user.active, to_timestamp(CAST(cwd_user_attribute.attribute_value as double precision)/1000) from cwd_user_attribute, cwd_user where cwd_user_attribute.user_id = cwd_user.id AND cwd_user_attribute.attribute_name = 'lastAuthenticated' AND cwd_user.active = 'F';Was this helpful?