find last login date for bitbucket users

[Rodney Sawyer]

I am trying to find inactive users in Bitbucket.  People request access and then look at it once or never end up using it, but now that license is tied up.  So I want to get a list all licensed users and the last time they logged into Bitbucket.  

[G__Sylvie_Davies__bit-booster_com_ · Accepted solution]

Click on the "Users" link on the "Administration" screen (upper-left).   There is a "Last Authenticated" column there.  I'm not sure when it showed up, but it's there for Bitbucket 4.11:

users.png

Another approach would be to use the REST api.  The regular "rest/api/1.0/users" API does not have the "last authenticated" data, but the "rest/api/1.0/admin/users" variant does!   Feel free to try the following example using my demo server (login against http://vm.bit-booster.com/bitbucket beforehand as "test" with pw "test" first).  Here's an example GET call against "rest/api/1.0/admin/users":  http://vm.bit-booster.com/bitbucket/rest/api/1.0/admin/users?limit=1000

p.s.  I invite you to try my Bitbucket add-on:  Bit-Booster for Bitbucket Server

[Scott Kimber]

To pull this directly from the postgres database and get one list for all users that haven't logged in since before a certain date, you can run a variant of this query:

select u.id, u.lower_user_name, u.first_name, a.attribute_value AS "Last login timestamp", CAST(a.attribute_value AS DOUBLE PRECISION) AS "INTTS"
FROM public.cwd_user_attribute a JOIN public.cwd_user u ON a.user_id = u.id
WHERE a.attribute_name = 'lastAuthenticationTimestamp'
AND u.is_active = 'T'
AND CAST(a.attribute_value AS double precision) < 1615055452000
ORDER BY a.attribute_value;

This will list the username, first, name, and the timestamp in epoch format (number of milliseconds since January 1, 1070.  Simply adjust the number in the second to last line to match the date you want to use as the cut off ( this site https://www.epochconverter.com is very helpful in that endeavor).  You can also specify the directory)id or email, etc as needed to track down users that may no longer be needing a license.

[johnml1135]

I was able to do this with a simple python script (also posted here: https://stackoverflow.com/a/73938604/2140414):

---

#%%

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:8443"

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 = "username"

password = "password"

#%%

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)