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