Query for inactive or idle users

Still need help?

The Atlassian Community is here for you.

Ask the community

History

There are certain scenarios when you wish to know which users have used their account in the past period. Since Bitbucket Server 3.7, we implement this functionality on the UI and via the users API endpoint. For more details, see  BSERV-4117 - Getting issue details... STATUS

This is what you can see from now on:


As this information can't be gleaned retroactively, it will be maintained from the upgrade to 3.7 on. Until a user first logs in after the upgrade their last timestamp will show as "Unknown" in the UI.

Each time a user authenticates via HTTP or SSH (including accessing a Git repository over HTTP or SSH, push or pull), or via the login page, their timestamp will be updated.

How to query for post-3.7 version?

  • The last authentication timestamp is stored in the database
  • The best way to fetch this data is by using the /rest/api/1.0/admin/users (GET) REST API. On the JSON response 200 example on the previous link, you will be able to see the value "lastAuthenticationTimestamp":. We encourage you to retrieve this information using the REST endpoint as opposed to directly querying the database. This is an example of how you can practically obtain this information:


    Click here to expand cURL call sample ...
    $ curl -H "Content-Type:application/json" --user admin:admin -H "Accept:application/json" -X GET http://localhost:7990/bitbucket/rest/api/1.0/admin/users
    {  
       "size":3,
       "limit":25,
       "isLastPage":true,
       "values":[  
          {  
             "name":"admin",
             "emailAddress":"admin@example.com",
             "id":1,
             "displayName":"Administrator",
             "active":true,
             "slug":"admin",
             "type":"NORMAL",
             "directoryName":"Bitbucket Internal Directory",
             "deletable":true,
             "lastAuthenticationTimestamp":1450221817844,
             "mutableDetails":true,
             "mutableGroups":true,
             "links":{  
                "self":[  
                   {  
                      "href":"http://localhost:7990/bitbucket/users/admin"
                   }
                ]
             }
          },
          {  
             "name":"user",
             "emailAddress":"user@example.com",
             "id":2,
             "displayName":"User",
             "active":true,
             "slug":"user",
             "type":"NORMAL",
             "directoryName":"Bitbucket Internal Directory",
             "deletable":true,
             "mutableDetails":true,
             "mutableGroups":true,
             "links":{  
                "self":[  
                   {  
                      "href":"http://localhost:7990/bitbucket/users/user"
                   }
                ]
             }
          },
          {  
             "name":"user2",
             "emailAddress":"user@2.com",
             "id":51,
             "displayName":"User 2",
             "active":true,
             "slug":"user2",
             "type":"NORMAL",
             "directoryName":"Bitbucket Internal Directory",
             "deletable":true,
             "lastAuthenticationTimestamp":1449467319385,
             "mutableDetails":true,
             "mutableGroups":true,
             "links":{  
                "self":[  
                   {  
                      "href":"http://localhost:7990/bitbucket/users/user2"
                   }
                ]
             }
          }
       ],
       "start":0
    }
  • The information will be stored on a different table and the script that could be used for Bitbucket Server pre-3.7 will not be working anymore.

How to query for pre-3.7 version?

Bitbucket Server stores the last login time based on epoc time. The MySQL from_unixtimestamp returns a Unix timestamp in seconds. Hence the above value in 'b.attribute_value' is divided by 1000 to discard the milliseconds.

If you have Delegated LDAP Authentication, be aware that a current bug on Crowd doesn't allow Bitbucket Server to keep track of this column:



To query for users that have been logging in since 2013-11-05 00:00:00, for instance, you will need to query the database. The example below uses a syntax compatible with MySQL:


Click here to expand query for MySQL ...
Query for MySQL
SELECT 
	usr.user_name, 
	FROM_UNIXTIME(attr.attribute_value / 1000) AS last_authentication
FROM 
	cwd_user AS usr, 
	cwd_user_attribute AS attr 
WHERE 
	usr.id = attr.user_id 
	AND attr.attribute_name = 'lastAuthenticationTimestamp'
ORDER BY 
	2 ASC;



Click here to expand query for PostgreSQL ...
Query for Postgres
SELECT 
	usr.user_name, 
	TO_TIMESTAMP(CAST(attr.attribute_value AS DOUBLE PRECISION) / 1000) AS last_authentication
FROM
	cwd_user AS usr,  
	cwd_user_attribute AS attr
WHERE 
	usr.id = attr.user_id
	AND attr.attribute_name = 'lastAuthenticationTimestamp'
ORDER BY 
	2 ASC;



Click here to expand query for Oracle
Query for Oracle
SELECT 
	usr.user_name, 
	TO_DATE('1970-01-01', 'YYYY-MM-DD') + (attr.attribute_value / 86400000) AS last_authentication
FROM 
	bitbucket.cwd_user AS usr, 
	bitbucket.cwd_user_attribute AS attr
WHERE 
	usr.id = attr.user_id 
	AND attr.attribute_name = 'lastAuthenticationTimestamp' 
ORDER BY 
	2 ASC;
Last modified on Sep 21, 2020

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.