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 details (which include the username, display name, 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 months 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 database.
When using Bitbucket Server / Data Center with a different database, some adjustments to these queries may be required.