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

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.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

Purpose

Administrators may wish to monitor the usage of Bitbucket Server or 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:

    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
    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
    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';

These SQL queries have been designed and tested against Oracle and PostgreSQL databases.
When using a Bitbucket Server / Data Center with a different database, some adjustments to these queries may be required.





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.



DescriptionHow to identify inactive users that haven't logged in for a long time?
ProductBitbucket Server, Stash

Last modified on Jan 29, 2024

Was this helpful?

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