How to identify inactive Bitbucket Server users

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

Purpose

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.

Solution

  • You can run the following SQL queries against your database to list the users who:

    1. 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');
    2. 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;

      Note:

      • The static value of 15778800000 used 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.


DescriptionHow to identify inactive users that haven't logged in for a long time?
ProductBitbucket Server, Stash
Last modified on Apr 2, 2019

Was this helpful?

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