nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Accepted answers

All comments

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

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

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_xv6czu

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.

soumyajit_xxqx06

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?

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira