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,299,328
Community Members
 
Community Events
165
Community Groups

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

2 answers

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

SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
u.display_name AS "Full_Name",
u.lower_email_address AS "Email_Address",
m.parent_name as group_membership,
u.active AS "Active",
to_timestamp(CAST(attribute_value AS BIGINT)/1000) AS "Last Login"
FROM cwd_user u
JOIN (
SELECT DISTINCT child_name,parent_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;

Sorry for question, but how add in ths script project (projectrole)? I need get username, project, role on this project, it`s poseble? TY in advance. (if who can do this whith description - super) UDP: Decided

SELECT distinct p.pkey as "Project key",p.PNAME as "Project Name",PC.CNAME AS "Project Category",p.LEAD AS "Project Lead", pr.name as "Role name", u.lower_user_name as "Username",u.active, u.display_name as "Display name",u.lower_email_address as "e-Mail", 'individual_role_no_group' as "Group name",TO_TIMESTAMP(CAST(ub.ATTRIBUTE_VALUE AS bigint) / 1000) AS "Last_loggedIN"

FROM projectroleactor pra

INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID

INNER JOIN project p ON p.ID = pra.PID

INNER JOIN app_user au ON au.user_key = pra.ROLETYPEPARAMETER

INNER JOIN cwd_user u ON u.lower_user_name = au.lower_user_name

INNER JOIN CWD_USER_ATTRIBUTES ub ON ub.USER_ID = u.ID

LEFT JOIN NODEASSOCIATION NA ON NA.SOURCE_NODE_ID = p.ID

LEFT JOIN PROJECTCATEGORY PC ON NA.SINK_NODE_ID = PC.ID

WHERE pra.roletype = 'atlassian-user-role-actor'  AND ub.ATTRIBUTE_NAME = 'login.lastLoginMillis'

UNION

SELECT distinct p.pkey as "Project key",p.PNAME as "Project Name",PC.CNAME AS "Project Category",p.LEAD AS "Project Lead", pr.name as "Role name", cmem.lower_child_name as "Username",u.active, u.display_name as "Display name",u.lower_email_address as "e-Mail", cmem.lower_parent_name as "Group name",TO_TIMESTAMP(CAST(ub.ATTRIBUTE_VALUE AS bigint) / 1000) AS "Last_loggedIN"

FROM projectroleactor pra

INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID

INNER JOIN project p ON p.ID = pra.PID

INNER JOIN cwd_membership cmem ON cmem.parent_name = pra.roletypeparameter

INNER JOIN app_user au ON au.lower_user_name = cmem.lower_child_name

INNER JOIN cwd_user u ON u.lower_user_name = au.lower_user_name

INNER JOIN CWD_USER_ATTRIBUTES ub ON ub.USER_ID = u.ID

LEFT JOIN NODEASSOCIATION NA ON NA.SOURCE_NODE_ID = p.ID

LEFT JOIN PROJECTCATEGORY PC ON NA.SINK_NODE_ID = PC.ID

WHERE pra.roletype = 'atlassian-group-role-actor'  AND ub.ATTRIBUTE_NAME = 'login.lastLoginMillis'

order by 1, 2, 3;

Hi..

How to save list of Jira active user with associated groups in CSV/excel/pdf  file ?

Regards,
pinakin

@Pinakin Bhatt 

You can use  as :

 

\copy ( select ******your query****** ) to '/tmp/user_list.csv' with CSV;

 

Example:

 

\copy (SELECT distinct A.user_name as "User",prl.name as "User_Role",A.email_address as "Email",pr.pname as "Project" ,pr.pkey as "Key" ,pr.LEAD as "Lead",cu.email_address as "Lead_Email",pc.cname as "Category",to_timestamp(CAST(B.attribute_value AS bigint)/1000) as "Last_loggedIN"FROM cwd_user A , cwd_user_attributes B, projectroleactor P , project pr,nodeassociation na,projectcategory pc,cwd_user cu,projectrole prl WHERE B.user_id = A.id AND B.attribute_name = 'login.lastLoginMillis' AND A.USER_NAME = P.ROLETYPEPARAMETER AND P.pid = pr.id AND na.SOURCE_NODE_ID = pr.id AND na.SINK_NODE_ID = pc.ID AND pr.LEAD=cu.user_name AND prl.id = P.projectroleID ) to '/tmp/user_list_with_role.csv' with CSV;

@DPK J - One question I am running your query: 

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;

But for some reason, It doesn't list all the users because it doesn't list from all the directories. Do you have an idea of why this is happening? 

Thanks, in advance. 

@DPK J - I think it is also not listing all of the users from the listed directories

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Software

Upcoming changes to epic fields in company-managed projects

👋 Hi there Jira Community! A few months ago we shared with you plans around renaming epics in your company-managed projects. As part of these changes, we highlighted upcoming changes to epics on...

14,959 views 38 49
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