Query for inactive or idle users

'How Do I...' and 'How to...' Guide to Stash

On this page

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 Stash 3.7, we implement this functionality on the UI and via the users API endpoint. For more details, see  STASH-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.

 

How to query for pre-3.7 release?

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:

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 = 'lastAuthenticated'
AND from_unixtime(b.attribute_value/1000) between '2013-11-05 00:00:00' and NOW();

The above query is for MySQL and may need to be modified for different databases.

Stash 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 Stash to keep track of this column:

Query for Postgres
SELECT a.user_name, to_timestamp(b.attribute_value::bigint/1000)
FROM public.cwd_user a, public.cwd_user_attribute b
WHERE a.id = b.user_id and b.attribute_name = 'lastAuthenticated'
AND to_timestamp(b.attribute_value::bigint/1000) < '2015-05-01 00:00:00-00';

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.

 

Last modified on May 21, 2015

Was this helpful?

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