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

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,463,996
Community Members
 
Community Events
176
Community Groups

To extract all JIRA users and their last access date

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

5 answers

@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/60as 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

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

1 vote

Hi @lester Kacprzak 

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

ComponentAccessor.getComponent(LoginManager)

Ravi

Hi Ravi,

Could you please provide full script here.

Thank you,

Rajesh

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

Like Dave Liao likes this

@Ravi Sagar _Sparxsys_

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

Thanks

Like Dave Liao likes this

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

0 votes

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 Community Leader Jan 08, 2023

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

0 votes

@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

SQL for JIRA Users

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
TAGS

Atlassian Community Events