You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
Next: Root
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
The Atlassian Community can help you and your team get more value out of Atlassian products and practices.
I need to create an extract all the JIRA users and their last access date.
@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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You can do that easily using ScriptRunner for Jira. You can LoginManager to get this information.
ComponentAccessor.getComponent(LoginManager)
Ravi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I would love to see this script too. I'm trying to do this in both Jira and Confluence @Ravi Sagar _Sparxsys_ .
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I would love to see this script as well if you could please share it that would help a lot
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @lester Kacprzak,
This is a free app and I have used this quite often on different occasions; Jira User Export.
Thanks,
Moga
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Unfortunately this app isn't free (at least as of January 2023).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.