disable users who have not logged in

Christian Roestel Community Champion Apr 17, 2015

is it possible to query the users in JIRA to determine who has not logged in (created an account but never logged in) so that we can disable them using bob swift CLI for JIRA? 

My goal is to create a script to run that would disable any use that has never logged in or has not logged in in the past 120 days. .

 

Thanks!

2 answers

2 votes

Hi Christian,

You can achieve that by checking the cwd_user_attribute table in JIRA and checking if that user id is located there with the lastAuthenitcated row.

In case some user does not have the attribute lastAuthenticated, then it means he/she never logged to the app.

This query should work for you. I've tested it for Confluence database but JIRA user tables are very similar and should work as well, if not at least you have the idea on how to retrieve this data. laugh

The query below will show you all users that have logged to the app so it means the users that are not returned have not logged to the app yet.

select cu.id, cu.lower_user_name, cua.attribute_name, cua.attribute_value
from cwd_user cu
join cwd_user_attribute cua on cu.id = cua.user_id
where cua.attribute_name = 'lastAuthenticated';

EDIT.

Improving the query, it now shows the users who didn't logged as results.

select cu.id, cu.lower_user_name, cua.attribute_name, cua.attribute_value
from cwd_user cu, cwd_user_attribute cua
where cu.id not in
(select cu.id
from cwd_user cu
join cwd_user_attribute cua on cu.id = cua.user_id)
and cua.attribute_name = 'lastAuthenticated';

I hope this helps!

Cheers,

Rodrigo

Christian Roestel Community Champion May 28, 2015

does anyone know how to do this with Script Runner?

0 vote
Christian Roestel Community Champion May 28, 2015

PS>>>> with Script Runner

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,317 views 14 20
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot