How to identify inactive users that haven't logged in for a long time?

Summary

Administrators may wish to monitor the usage of Bitbucket Data Center in order to optimise license usage. By default, the web interface does not provide a feature to display users who have not logged in for a certain period.

Solution

You can run the following SQL queries against your database to list the details (which include the username, display name, and email address) of the users who:

1. Have never logged in:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 SELECT user_name, display_name, email_address FROM cwd_user WHERE user_name NOT IN (SELECT user_name FROM cwd_user cu, cwd_user_attribute cua WHERE cua.user_id = cu.id AND cua.attribute_name = 'lastAuthenticationTimestamp') AND user_name NOT IN (SELECT user_name FROM cwd_user cu, cwd_user_attribute cua WHERE cua.user_id = cu.id AND cua.attribute_name = 'lastAuthenticated')

2. Have logged in (at least once), but not in the last 6 months:

   Oracle

   1 2 3 4 5 6 7 8 9 10 11 12 SELECT u.user_name, u.display_name, u.email_address FROM cwd_user u join cwd_user_attribute a ON u.id = a.user_id WHERE ( a.attribute_name = 'lastAuthenticationTimestamp' OR a.attribute_name = 'lastAuthenticated' ) AND a.attribute_value < ( Cast(Sys_extract_utc(systimestamp) AS DATE) - DATE '1970-01-01' ) * 86400000 - 15778800000;

   Note:

   • The static value of 15778800000 used in this query represents the 6 month interval, expressed in the number of milliseconds.

   • This value may be adjusted to represent other intervals, as required.

   PostgreSQL

   1 2 3 4 5 6 7 8 9 10 SELECT usr.user_name, usr.display_name, usr.email_address FROM cwd_user AS usr, cwd_user_attribute AS attr WHERE usr.id = attr.user_id AND ( attr.attribute_name = 'lastAuthenticationTimestamp' OR attr.attribute_name = 'lastAuthenticated') AND To_timestamp(Cast(attr.attribute_value AS DOUBLE PRECISION) / 1000) < current_timestamp - interval '180 day';

REST API and PAT Users

Users accessing the Bitbucket REST API using a personal access token will have an updated value in thelastAuthenticationTimestamp column in the Database. So the SQL query above can also be used for last logged-in status of users using the personal access token.