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

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How to get the List of all active & inactive users with last login from Jira DB ?

Hi Community!

 

How to get a List of all users from my Jira PostgreSQL DB which shows the columns in result as,

1. username

2. Full name

3. email id

4. Is active or not

5. Last login date

 

Thanks

1 answer

1 accepted

1 vote
Answer accepted
DPK J Community Leader Mar 29, 2020

In Jira Database there is a table called 'cwd_user' which contain information you are looking for.

You can easily find details like username, display name, active, email etc from this. But last login is not stored in this table.

For last login follow this link - https://confluence.atlassian.com/jirakb/find-the-last-login-date-for-a-user-in-jira-server-363364638.html

PS - Jira database design is can be found here - https://developer.atlassian.com/server/jira/platform/database-schema/

Thanks for the Answer,
Could you please help me to modify the below query to add 3 new columns like Active, full name, email address? 

 

SELECT d.directory_name AS "Directory", 
    u.user_name AS "Username",
    to_timestamp(CAST(attribute_value AS BIGINT)/1000) AS "Last Login" 
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'
    ) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
ORDER BY "Last Login" DESC;
DPK J Community Leader Mar 30, 2020

Sure @Amol Dongare 

 

SELECT d.directory_name AS "Directory", 
    u.user_name AS "Username",
u.display_name AS "Full_Name",
u.lower_email_address AS "Email_Address",
u.active AS "Active",
to_timestamp(CAST(attribute_value AS BIGINT)/1000) AS "Last Login" 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' ) AS a ON a.user_id = u.id JOIN cwd_directory d ON u.directory_id = d.id ORDER BY "Last Login" DESC;
Like Amol Dongare likes this

Thanks for the snippet. Is it possible to also display group membership?

 

Regards

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

👁‍🗨 Confluence Team planning dashboard_Atlympics 🌈

Hi Atlassian's, How is your journey with #Atlympics 2021 so far....excited! Me too, same excitement. Here's my typical team planning and vision dashboard which I used to share to my management and ...

36 views 1 3
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you