How to list all the users in a Role from a particular project

[Sateesh Chandra]

Hi

Could someone help with a query to extract all the users in a particular role in a Jira project. Also help me with the extract of all the users in a group. Can I get the Full Name and the user name in the output.

Regards

Sateesh

[Sakthi Balaji · Accepted solution]

Try like this,

SELECT U.* FROM projectroleactor P

INNER JOIN cwd_user U

ON U.USER_NAME = P.ROLETYPEPARAMETER

where pid = 'UR PROJECTID'

and projectroleid = 'ROLEID';

[Sakthi Balaji]

Hi sateesh,

can you try this Database query

select u.user_name , u.display_name, u.email_address from

cwd_user u inner join cwd_membership g

on u.id = g.child_id

where g.parent_id = XXXX

order by u.user_name;

Note:

XXXX - replace with the group id you wanted to list, you can get the id of the group from the below (cwd_group) table.

cwd_group will contain the Group Id and info about the group,

cwd_membership table will contain the User and group mapping.

cwd_user will contain the list of users created in jira

[jeffc]

Thanks! Worked great for me!

[Sateesh Chandra]

Thanks Sakthi for the wonderful queries.

[Sateesh Chandra]

It works but does not display FullName and Username though

[Sakthi Balaji]

You can map the roletypeparameter value in the "projectroleactor" table with the user name in the "cwd_user" table.

[Sakthi Balaji]

Hi sateesh,

use this query for that,

SELECT * FROM `projectroleactor`

where pid = 'XXX'

and projectroleid = 'yyy';

u can get the pid (project id) from the below table.

SELECT * FROM `project`;

projectroleid u can get from

select * from projectrole;

Regards,

Sakthi

[Sateesh Chandra]

Hi Sakthi,

It worked perfect. Can this be used to get the list of users in a particular role in a single Jira project.

Sateesh

[Sateesh Chandra]

Database query is fine.

[Nic Brough -Adaptavist-]

What type of query? Plugin? Database?