Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
Level
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

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

0 votes
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 any interview participants for Bitbucket Data Center

Hi everyone,  We are looking to learn more about development teams’ workflows and pain points, especially around DevOps, integrations, administration, scale, security, and the related challeng...

493 views 5 4
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