Query for inactive or idle users
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:
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:
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.