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. 

[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

[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

[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

[RAJESH]

Hi Ravi,

Could you please provide full script here.

Thank you,

Rajesh

[kdickason]

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

[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

[KGrandhi]

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

[mogavenasan]

Hi @lester Kacprzak,

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

Thanks,
Moga

[Dave Liao]

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

[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

[lester Kacprzak]

SQL for JIRA Users