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
4,365,234
Community Members
 
Community Events
168
Community Groups

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

Hi,

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?

Thanks!

Orit.

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.

https://marketplace.atlassian.com/apps/1220581/my-user-manager-for-jira?hosting=server

It is for free :) Enjoy it

Kind Regards

Yassin

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,

u.user_name,

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

FROM cwd_user u

JOIN (

SELECT DISTINCT child_name

FROM cwd_membership m

JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID

) AS m ON m.child_name = u.user_name

JOIN (

SELECT *

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','dd.mm.yyyy') + to_number(a.attribute_value)/1000/60/60/24, 'yyyy-mm-dd') as last_login
FROM cwd_user u, cwd_user_attributes a
WHERE u.id = a.user_id
AND a.attribute_name ='login.lastLoginMillis'
and u.DIRECTORY_ID=3
and u.active=1
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

 

Cheers

Kurt

Suggest an answer

Log in or Sign up to answer