Retrieve a list of users assigned to project roles in Jira server

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server と Data Center のみ - この記事は、サーバーおよびデータセンター プラットフォームのアトラシアン製品にのみ適用されます。

The following query will give you a list of projects, the roles within that project, and the users assigned to that role.

This was written in MySQL syntax, so you may need to tweak it depending on the database you are using.

This was written for JIRA schemas above v6.0

SELECT p.pname, pr.NAME, u.display_name 
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;

The output should be similar to the following:

pname           name               display_name  
--------------  -----------------  ------------  
SCRUM           Developers         admin         
KANBAN          Developers         admin         
Justice League  Administrators     admin         
Terraria        Administrators     admin         
Justice League  Service Desk Team  Thor          
Terraria        Service Desk Team  Loki          
KANBAN          Developers         Loki        

(warning) This is a pretty basic query, so you will probably have to alter it a bit to get the exact results you want. For example, you can add some criteria to limit the results by Project (WHERE p.pname = "My Project") or by role (pr.NAME = "Developers")

Further Examples:

Filtering by Role name:

SELECT p.pname, pr.NAME, u.display_name 
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
WHERE name = 'Service Desk Team';

Filtering by Project Name:

SELECT p.pname, pr.NAME, u.display_name 
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
WHERE pname = 'SCRUM';


Filtering by Jira Service Management projects only(To search for Jira Software, just change to Software):

SELECT p.pname, pr.NAME, u.display_name 
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
WHERE projecttype = 'service_desk';
List of users belonging to groups assigned to project roles for all projects:
SELECT p.pname as ProjN, pr.NAME as roleN, pra.roletype, pra.roletypeparameter, cmem.child_name, u.display_name
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.child_name
INNER JOIN cwd_user u ON u.lower_user_name = au.lower_user_name
WHERE pra.roletype = 'atlassian-group-role-actor' order by p.pname;


List the admin and project rules and when a particular user was added to the Project role (by joining the audit_log and audit_item tables):

SELECT al.created as Created_Date,al.author_key as Change_Author, ai.object_name as Project_Name, al.object_name as Project_Role,acv.delta_from, acv.delta_to 
FROM audit_log al 
INNER JOIN audit_item ai on al.id = ai.log_id and al.summary = 'Project roles changed' 
INNER JOIN audit_changed_value acv on acv.log_id = al.id 
WHERE al.created > '2019-05-31' 
ORDER BY al.created,ai.object_name, al.object_name;



最終更新日 2021 年 7 月 30 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.