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

[Orit Nachshon]

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.

[Yassin]

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]

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

[Anoop K Baby]

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]

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

[Deleted user]

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!!

[Yassin]

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

[AKBAR]

@YassinWhat about the cloud compatible version for this?

[Mohan Sundar]

@Yassin The plugin is not free anymore :( 

[Mir O_]

And, there is no version for Cloud.

[Kurt Klinner]

@Orit Nachshon

Hi Orit

have a look to

https://confluence.atlassian.com/jirakb/retrieve-last-login-dates-for-users-from-the-database-363364638.html

Cheers

Kurt

[Orit Nachshon]

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]

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]

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

[Kurt Klinner]

@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