List the Last Login Date for all Users in Crowd


アトラシアン コミュニティをご利用ください。



Get a list of the last login date for all users in Crowd.


Unfortunately, this information is not available in Crowd via the UI. Querying the respective fields in the Crowd database will help fetch these details.


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 = 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 = 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 = [] AND cwd_user.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.

最終更新日: 2019 年 2 月 15 日


Powered by Confluence and Scroll Viewport.