List the Last Login Date for all Users in Crowd

Still need help?

The Atlassian Community is here for you.

Ask the community

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.


  • MySQL:
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'
  • Postgres:
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';
  • SQL Server
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';


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.

Last modified on Feb 15, 2019

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.