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.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I was able to do this with a simple python script (also posted here: https://stackoverflow.com/a/73938604/2140414):
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.