How to find and delete the inactive users in Bitbucket

[Reddeppa]

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.

[Daniel Eads]

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

[Reddeppa]

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) :(