How do i get a list of inactive users in Attlasian stash

[Rahul Mohan]

IS there a way to list all the inactive users for the past X number of days ?

[Svante Gustafsson Björkegren]

Hi Rahul,

You could try the following SQL query on your database. (I have tested it against Postgres)

select su.name, max(sa.created_timestamp) from stash_user su
join sta_activity sa
on sa.user_id = su.id
where su.id not in (
select s.id from sta_activity a 
join stash_user s
on a.user_id = s.id
where a.created_timestamp > CURRENT_DATE - integer '30' 
order by s.name desc
)
group by su.name

It will give you a list of all "stash-users" i.e. users counting toward your license, that haven't any activity registered the last 30 days. It takes ALL activities into consideration. I haven't found out yet what the different activity_types represent so I look for them all in this query.

I tried this on my instance and it seems to work as expected. Please play around with it and let me know how it works for you!

Cheers,

// Svante