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

Orit Nachshon August 15, 2018

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

1 vote
Yassin June 6, 2019

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

Peter Horvath February 25, 2020

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

Like Yassin likes this
Anoop K Baby March 12, 2020

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.

Anoop K Baby March 12, 2020

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

Like Yassin likes this
Deleted user April 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
Yassin December 28, 2020

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

AKBAR June 28, 2021

@YassinWhat about the cloud compatible version for this?

Mohan Sundar March 24, 2022

@Yassin The plugin is not free anymore :( 

Mir O_ November 29, 2023

And, there is no version for Cloud.

1 vote
Kurt Klinner
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 15, 2018
Orit Nachshon August 16, 2018

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

soumyajit September 25, 2020

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;

ViswanathanR December 8, 2020

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

0 votes
Kurt Klinner
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 16, 2018

@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