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

[Amol Dongare]

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

[DPKJ · Accepted solution]

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/

[Amol Dongare]

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;

[DPKJ]

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;

[Viktor Pfafenrot]

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

Regards

[Makareswar Rout]

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;

[Anton]

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

[Makareswar Rout]

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;

[Pinakin Bhatt]

Hi..

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

Regards,
pinakin

[Makareswar Rout]

@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;

[Jorge Quintanilla]

@DPKJ - 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. 

[Jorge Quintanilla]

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

[Mahesh Kallepalli]

@Jorge Quintanilla Have you achieved this I need to extract only Inactive users from database.