CODEWITHSUNDEEP
bitbucketbitbucket-server
Sandeep Anand
Sandeep Anand

Reputation: 159

Find the list of inactive users in Bitbucket?

How can I find the list of those users who never logged in to their Bitbucket account? Is there any query or macro, or will I have to do it manually ?

Upvotes: 3

Views: 5470

Answers (5)

Joao Vitorino
Joao Vitorino

Reputation: 3266

In the bitbucket cloud, there is no rest API to get this information but is possible to download a CSV file with this information.

curl https://bitbucket.org/!api/internal/workspaces/{workspace_name}/user-list -o users.csv

Upvotes: 0

johnml1135
johnml1135

Reputation: 4997

Here is some python code that did the trick for me. I had many users (10k+) but <1000 users with permissions. I also have basic authentication (username/passoword) enabled for my bitbucket server. By querying both "users" and "permissions" and merging the results using pandas, I got a pretty good result. I can open it in excel and filter on only users that have not been logged on for many days, or not at all.

import pandas as pd
import numpy as np
import json
import requests
import datetime
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

bitbucket_url = "https://my-bitbucket-server" 
users_url = bitbucket_url + "/rest/api/1.0/admin/users?limit=1000&start="
permissions_url = bitbucket_url + "/rest/api/1.0/admin/permissions/users?limit=1000&start="
out_filename = r"C:\mypath\authenticatedUsers.csv"

username = "myusername"
password = "mypassword"

def get_responses(url,username,password):
    json_responses = []
    with requests.Session() as s:
        s.auth = (username,password)
        start = 0
        while(True):
            response = s.get(url + str(start),verify=False)
            json_response = json.loads(response.text)
            if "values" not in json_response:
                print("Error:" + response.text)
            json_responses += json_response["values"]
            if(len(json_response["values"]) < 1000):
                break
            start += 1000
    return json_responses

users = pd.DataFrame(get_responses(users_url,username=username,password=password))
users["DaysSinceAuth"] = np.floor((datetime.datetime.now().timestamp() - users.lastAuthenticationTimestamp/1000)/3600/24)
permissions = get_responses(permissions_url,username=username,password=password)
permissions = pd.DataFrame([{"id":p["user"]["id"],"permission":p["permission"]} for p in permissions])
usersmerged = users.merge(permissions,how="inner",on="id")
usersmerged.to_csv(out_filename)

Upvotes: 0

Dantel35
Dantel35

Reputation: 135

Here is a solution for Postgresql, notice that you may want to consider the creation date of a user to exclude those that got their access yesterday and did not mange to login yet.

Users who have not logged in for one year or never, but who exist for at least one year:

select * from (SELECT distinct cu.lower_user_name
      ,to_timestamp(CAST(attribute_value AS BIGINT)/1000) AS last_login
      ,cu.created_date
FROM cwd_user As cu
      LEFT JOIN 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 cwd_user_attribute As cua
          ON cu.ID = cua.user_id and
          cua.attribute_name='lastAuthenticationTimestamp'
      WHERE is_active = 'T'AND (cm.lower_parent_name='stash-users' OR
          cm.lower_parent_name='bitbucket-users'
      OR cm.lower_parent_name='stash' OR
          cm.lower_parent_name='bitbucket') ) q1
      where  (last_login < current_date - (365) or last_login is null)
      and created_date < current_date - (365);

Upvotes: 1

Marek S.
Marek S.

Reputation: 3

See also the Atlassian KB on the topic: https://confluence.atlassian.com/bitbucketserverkb/query-for-inactive-or-idle-users-779171719.html In essence, Bitbucket Server (and Data Center):

  • shows Last authenticated column in the list of users (under Administration > Users)
  • offers REST API to query the last authenticated date for any given user

Upvotes: 0

JOST
JOST

Reputation: 51

You can do this with SQL query to database:

    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'

Upvotes: 2

Related Questions