Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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?

@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

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. ;)  

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you