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 a.user_name, from_unixtime(b.attribute_value/1000) 
FROM cwd_user a, cwd_user_attribute b 
WHERE a.id = b.user_id and b.attribute_name = 'lastAuthenticationTimestamp'
AND from_unixtime(b.attribute_value/1000) between '2013-11-05 00:00:00' and NOW();



Click here to expand query for PostgreSQL ...
Query for Postgres
SELECT cwd_user.user_name, to_timestamp(CAST(cwd_user_attribute.attribute_value as double precision)/1000) 
FROM cwd_user_attribute, cwd_user 
WHERE cwd_user_attribute.user_id = cwd_user.id 
AND cwd_user_attribute.attribute_name = 'lastAuthenticationTimestamp'
order by to_timestamp asc;

Postgres doesn't include the from_unixtime function so you need to use the to_timestamp and convert the attribute_value to bigint and divide by 1000.  Then you would change the date to your cut-off date.  This query returns all users who have not logged in since the date specified. 

This example assumes that you are using the default schema "public", if you are not using the public schema you will need to adjust the query to the schema that your database is using.



Click here to expand query for Oracle
Query for Oracle
select a.user_name, to_date('1970-01-01', 'YYYY-MM-DD') + (b.attribute_value/ 86400000) from bitbucket.cwd_user a, bitbucket.cwd_user_attribute b where a.id = b.user_id and b.attribute_name = 'lastAuthenticationTimestamp' and to_date('1970-01-01', 'YYYY-MM-DD') + (b.attribute_value/ 86400000) < to_date('2016-09-01','YYYY-MM-DD');



Last modified on Aug 13, 2019

Was this helpful?

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