List the Last Login Date for all Users in Crowd
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 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';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';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
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';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';