Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
Community Members
Community Events
Community Groups

Get a list of Jira active users and their last login date


Can I have an Oracle query for getting all Jira licensed users and their last login date, including those who didn't login at all?



3 answers

Hi Everyone, 

I want to share my plugin with you. It will definetly help you filter out the inactive/active or never logged in users.

It is for free :) Enjoy it

Kind Regards


Thanks, it is a nice start! Could you please make the tables sortable?

Like Yassin likes this

Whether it support Jira Software? I just installed this plugin and restarted my Jira instance, but still I didn't see any option under user managment.

Please ignore my question. I was checking it on wrong place. I figured it out. 

Like Yassin likes this
Deleted user Apr 17, 2020

This is great!! Is it possible to search for the opposite of what this tool displays? In other words, it looks like, if I choose "Active" users and "Last Login Since", then say "90 days", this will display all the Active users who have logged in for the past 90 days.

In order to determine which users I should consider for de-activating, I'd want to know the opposite of that. In other words, which Active users have NOT logged in during the past 90 days. Knowing that, I would be better able to determine if the user should be de-activated or not.

Thanks for a great tool!!

Like Yassin likes this

Glad that you liked it. @peter: I will add the sort table feature to my list. Thanks for the proposal. ;)  

@Yassin What about the cloud compatible version for this?

@Yassin  The plugin is not free anymore :( 

Thanks Kurt!

My DBA tried this query from the article and getting an error. Any idea?


SELECT d.directory_name,


TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date

FROM cwd_user u



FROM cwd_membership m

JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID

) AS m ON m.child_name = u.user_name



FROM cwd_user_attributes ca

WHERE attribute_name = 'login.lastLoginMillis'

) a ON a.user_id = u.ID

 JOIN  cwd_directory d ON u.directory_id = d.ID

order by last_login_date desc;

) AS m ON m.child_name = u.user_name


ERROR at line 9:

ORA-00905: missing keyword

try this one:

select u.user_name, u.created_date, u.display_name, u.email_address,
to_char(to_date('01.01.1970','') + to_number(a.attribute_value)/1000/60/60/24, 'yyyy-mm-dd') as last_login
FROM cwd_user u, cwd_user_attributes a
WHERE = a.user_id
AND a.attribute_name ='login.lastLoginMillis'
and to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24') + to_number(a.attribute_value)/1000/60/60/24 , 'YYYY')<2019
order by last_login;

hi, is todate supported in SQL?, shouldnt we use cast ? or convert?

0 votes

@Orit Nachshon


The last line contains a 

) AS m ON m.child_name = u.user_name

that should not been there, can you pls. check




Suggest an answer

Log in or Sign up to answer