Database query to retrieve Licensed Users with their last login time timestamp in Bitbucket Server and Datacenter
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Summary
During the license cleanup process, customers may need details of licensed users, including their most recent login time. This information aids in the identification of inactive users, thereby facilitating informed decisions regarding their retention or removal within the Bitbucket server/Datacenter.
Environment
Bitbucket 7.x,8.x
Solution
Important note on database query
This Database Query / Functionality is not officially supported!
Atlassian is not responsible for translating queries for different databases.
This query should only be used as an example. Any desired adjustments should be checked with your local Database Administrator, including queries related to different DBMS that are not present/listed in this KB article, like Oracle Database.
Atlassian will not be providing support for changes, adjustments, or inquiries regarding the below queries, as this is not part of our Atlassian Support Offerings.
Atlassian does not provide any support to sample database queries provided in our Knowledge Base documents. Extraction of data from Bitbucket should be performed using our REST API Documentation because we do not keep all data relationships in the DB. Many connections to data are handled in the code. The REST API can go through the code paths to get the data. A SQL would have to do that manually or, in some cases, it can’t be done at all.
The following query can be utilized to identify all licensed users who have logged into Bitbucket at least once. Please note, this query is designed for PostgreSQL and may require alterations for compatibility with other database engines.", this query is based on PostgreSQL and may need to be modified for alternate database engines.
Users who have logged in atleast once:
1
2
3
4
5
6
7
8
9
10
select cu.id as user_id,
cu.user_name,
cu.display_name,
cu.email_address,
to_timestamp(cua.attribute_value::bigint / 1000) as lastauthenticated
from cwd_user cu
join cwd_user_attribute cua
on cu.id = cua.user_id
where cua.attribute_name = 'lastAuthenticationTimestamp'
order by lastauthenticated desc Users who have never logged in to bitbucket:
1
2
3
4
5
6
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');API method :
The suggested method for querying this data is to utilize the Bitbucket REST API. The following API call can also be employed to retrieve this information.
You can check the "lastAuthenticationTimestamp" field for the last login time of the user from the below API response
1
curl --user <Bitbucket-admin-username>:<password> -H "Accept:application/json" -X GET "<Bitbucket-base-url>/rest/api/1.0/admin/users?limit=1000" | jq If you have more than 1000 users, please follow How to apply the limit filter in Bitbucket Server and Datacenter ReST API and query more than the max limit of 1000 results.
Additional details:
From Bitbucket 8.12.x, you can search for and filter out licensed users on the Users page.
Check the Licensed column to find out if a user is licensed. You can also use filters to find users by their license statuses, last authentication time, and directory. In the Export menu, apart from the users and user permissions options, you can now select Users (Filtered results) to export the data on the users you've filtered out.
Was this helpful?