Query for inactive or idle users

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.

Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

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.

Important Note on DB Queries

  • This Database Query / Functionality is not officially supported!

  • Atlassian is not responsible for translating queries for different databases.
    • This query should only be used as an example. Any desired adjustments should be checked with your local Database Administrator, including queries related to different DBMS that are not present/listed in this KB article, like Oracle Database. 
  • Atlassian will not be providing support for changes, adjustments, or inquiries regarding the below queries, as this is not part of our Atlassian Support Offerings.
    • Atlassian does not provide any support to sample database queries provided in our Knowledge Base documents. Extraction of data from Bitbucket should be performed using our REST API Documentation because we do not keep all data relationships in the DB. Many connections to data are handled in the code. The REST API can go through the code paths to get the data. A SQL would have to do that manually or, in some cases, it can’t be done at all.

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.

    • If the "lastAuthenticationTimestamp" is not returned for a user, it means the user never logged in to the system. Check the "user2" in the example below:
    • Click here to expand... 
      {
    "size": 2,
    "limit": 25,
    "isLastPage": true,
    "values":
    [
        {
            "name": "admin",
            "emailAddress": "admin@admin.com",
            "id": 2,
            "displayName": "XXXXX Admin",
            "active": true,
            "slug": "admin",
            "type": "NORMAL",
            "directoryName": "Bitbucket Internal Directory",
            "deletable": true,
            "lastAuthenticationTimestamp": 1697101324129,
            "mutableDetails": true,
            "mutableGroups": true,
            "links":
            {
                "self":
                [
                    {
                        "href": "htXXXXXXXXX/users/admin"
                    }
                ]
            }
        },
        {
            "name": "user2",
            "emailAddress": "XXXXXX@XXXXXX.com",
            "id": 3,
            "displayName": "user2",
            "active": true,
            "slug": "user2",
            "type": "NORMAL",
            "directoryName": "Bitbucket Internal Directory",
            "deletable": true,
            "mutableDetails": true,
            "mutableGroups": true,
            "links":
            {
                "self":
                [
                    {
                        "href": "hXXXXXXX/users/user2"
                    }
                ]
            }
        }
    ],
    "start": 0
}


  • 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 epoch 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 SQLServer ...
Query for Microsoft SQLServer 
SELECT a.user_name, dateadd(s, convert(bigint, b.attribute_value) / 1000, convert(datetime, '1-1-1970 00:00:00'))
FROM cwd_user a, cwd_user_attribute b
WHERE a.id = b.user_id and b.attribute_name = 'lastAuthenticationTimestamp'
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 
	cwd_user usr, 
	cwd_user_attribute attr
WHERE 
	usr.id = attr.user_id 
	AND attr.attribute_name = 'lastAuthenticationTimestamp' 
ORDER BY 
	2 ASC;

Note: Unlike other database types, in Oracle the table aliases are given immediately after the table name without using "AS" between the table name and the alias

Last modified on Jun 4, 2024

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.