Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,300,028
Community Members
 
Community Events
165
Community Groups

find last login date for bitbucket users

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.  

3 answers

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

 

 

I was able to do this with just going to the website as an admin and running a simple python script.  The script and instructions are below:


# 1: Assemble Bitbucket users.txt
  # Go to https://<bitbucket>/admin/users
  # Hold "Page Down" until you reach the whole bottom of the page (will take a while)
  # copy the contents (ctrl-a, ctrl-c) to a blank text document
  # remove header and footer
  # run this regular expression to replace double names:
  # find:  (.*)\n\1\n
  # replace: $1\t
  # save as Bitbucket users.txt
# 2: Assemble Bitbucket permissions.txt
  # got to https://<bitbucket>/admin/permissions
  # do the same processing as for the above
  # remove all trailing tabs and spaces
  # save as Bitbucket permissions.txt
# 3: filter and merge together results
  # Run this analysis on the document

#%%
import pandas as pd
import numpy as np
import os

bitbucket_user_filename = r"C:\<path>\Bitbucket users.txt"
bitbucket_permissions_filename = os.path.join(
    os.path.dirname(bitbucket_user_filename), "Bitbucket permissions.txt"
)
bitbucket_users_filtered_filename = os.path.join(
    os.path.dirname(bitbucket_user_filename), "Bitbucket_logged_on.csv"
)

df_users = pd.read_csv(bitbucket_user_filename, delimiter="\t")
df_users.fillna("", inplace=True)
logged_in_df: pd.DataFrame = df_users[
    ~df_users["Last authenticated"].isin(("Unknown", ""))
]

df_perms = pd.read_csv(bitbucket_permissions_filename, delimiter="\t")
df_perms.columns = ["Name"]
logged_in_df = logged_in_df.merge(df_perms, on="Name", how="outer")
logged_in_df.fillna("", inplace=True)

logged_in_df.to_csv(bitbucket_users_filtered_filename)

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.

Suggest an answer

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

Git push size limits are coming to Bitbucket Cloud starting April 4th, 2022

Beginning on April 4th, we will be implementing push limits. This means that your push cannot be completed if it is over 3.5 GB. If you do attempt to complete a push that is over 3.5 GB, it will fail...

2,255 views 2 9
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