Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How to find and delete the inactive users in Bitbucket

Hi,

Can any one please help me with query or some other way to find and delete the inactive users in Bitbucket to save Bitbucket licences.

Thank you.

1 answer

1 vote
Daniel Eads Atlassian Team Aug 05, 2020

Greetings,

Dates of the last login of a user are available in Bitbucket Server's Users administration page. But it's also possible to query the same information from the REST API or even directly from the database. Based on the results, you could then deactivate users via a script or through Bitbucket's web interface. Details on retrieving the dates through the API or from SQL are available on this article . Since the exact query depends on your database type (each has a different way of casting dates), I'll recommend referencing the article rather than copying the specific steps here.

There are also a couple apps on the Marketplace that look to simplify deactivating users automatically. Depending on your instance size, that might be a useful and quick way forward as well.

Cheers,
Daniel

Hi Daniel,

Thanks for the response.

I have seen dates of the last login of a user are available in Bitbucket  Server's Users administration page, but I could see "last authenticated" is "Unknown". Does this mean users never logged in to Bitbucket ? or Are these users are using Bitbucket using ssh-key authentication (for source code management), instead of login to Bitbucket via web interface ?

My database is Oracle DB, so I tried the below query, but it does not work.

SELECT cu.lower_user_name
      ,cu.display_name
      ,cu.lower_display_name
      ,cu.lower_email_address
      ,cu.is_active
      ,dateadd(second,cast(cast(cua.attribute_value AS nvarchar(255)) AS bigint)/1000,'19700101 00:00:00:000') AS LAST_LOGIN
  FROM [BitBucketDB].[dbo].[cwd_user] As cu
      LEFT JOIN [BitBucketDB].[dbo].cwd_membership AS cm
        ON cu.directory_id=cm.directory_id
        AND cu.lower_user_name=cm.lower_child_name
        AND cm.membership_type='GROUP_USER'

      LEFT JOIN [BitBucketDB].[dbo].cwd_user_attribute As cua
      ON cu.ID = cua.user_id and cua.attribute_name='lastAuthenticationTimestamp' 
      WHERE cm.lower_parent_name='stash-users' 

When I run the above query in Oracle DB, I got the below error:

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause:
*Action: Error at Line: 6 Column: 64

missing right parenthesis in oracle (railroad diagrams) :(

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Bitbucket

⭐ Calling all Bitbucket and DevOps experts: Special showcase opportunity ⭐

Hi, Bitbucket community! Are you a DevOps practitioner (or know one in your network)? Do you have DevOps tips, tricks, or learnings you'd like to share with the community? If so, we'd love to hea...

1,453 views 4 7
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you