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; } } }

To extract all JIRA users and their last access date

lester Kacprzak

I need to create an extract all the JIRA users and their last access date.

Accepted answers

All comments

Kurt Klinner

@lester Kacprzak

Hi Iester

if you have access to the used db you can use the sql query provided at https://confluence.atlassian.com/jirakb/find-the-last-login-date-for-a-user-in-jira-server-363364638.html

All the best

Kurt

Ravi Sagar _Sparxsys_

Hi @lester Kacprzak

You can do that easily using ScriptRunner for Jira. You can LoginManager to get this information.

ComponentAccessor.getComponent(LoginManager)

Ravi

mogavenasan

Hi @lester Kacprzak,

This is a free app and I have used this quite often on different occasions; Jira User Export.

Thanks,
Moga

RAJESH_nsjm88

Hi Ravi,

Could you please provide full script here.

Thank you,

Rajesh

akshay.kg

@lester Kacprzak One given by Atlassian have a join with cwd_membership will will remove non licensed users I have removed it in below query try this.

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

kdickason

I would love to see this script too. I'm trying to do this in both Jira and Confluence @Ravi Sagar _Sparxsys_ .

lester Kacprzak

SQL for JIRA Users

Dave LIAO

Unfortunately this app isn't free (at least as of January 2023).

KGrandhi

This worked. We use AD auth,

Was trying to get USER info along with AD groups associated to users. Following is my query

SELECT
u.user_name,
u.LOWER_DISPLAY_NAME,
m.LOWER_PARENT_NAME,
u.EMAIL_ADDRESS,
TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date
FROM JIRA.CWD_USER u
JOIN
(
SELECT *
FROM JIRA.cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) a ON a.user_id = u.ID
JOIN JIRA.CWD_MEMBERSHIP m ON m.child_id = u.ID
order by last_login_date desc

KGrandhi

Moses Ebrahimi

@Ravi Sagar _Sparxsys_

I would love to see this script as well if you could please share it that would help a lot

Thanks

Strong Tractor

@Connor Bartal _ worked great with additional filters and statements modified it gives the total no. of logged in users per today with threshold = 0

and this script covers lot of use cases.

Welcome!

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

Quick Links

Find more posts tagged with

server

jira

jira-software

jira-software-server