How to identify inactive Bitbucket Server users
Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.
Administrators may wish to monitor the usage of Bitbucket Server or Data Center in order to optimise license usage.
However, out-of-the-box, the web interface does not help list the users that haven't logged in for a while.
You can run the following SQL queries against your database to list the users who:
Have never logged in:
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');
Have logged in (at least once), but not in the last 6 months:
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;
The static value of
15778800000used in this query represents the 6 months interval, expressed in the number of milliseconds.
This value may be adjusted to represent other intervals, as required.
These SQL queries have been designed and tested against Oracle database.
When using Bitbucket Server / Data Center with a different database, some adjustments to these queries may be required.