How to get a list of the Project's Leads and Default assignees via Database queries.
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
How to get a list of the Project's default assignees via Database Queries.
As an Admin, you may want a report on users assigned as Project Leads to determine the workload or Project Distribution among users.
As an Admin, you may want a report on Projects with or without a Default Assignee
Jira doesn't provide UI functionality for this, so this article presents an alternative: querying the database directly.
đź’ˇWhile you're on this, you may also be interested in these other how-to articles on listing users. Depending on your specific need, you may need to run multiple DB queries or work on joining them together. You are more than welcome to ask the Community if you've any questions on these or share your own queries!
Environment
Jira 8+
Solution
Project Leads
1— Project Lead: The List of ProjectLeads can be found in the Project Table of the Jira database under the Lead column and we can use the cwd_user table as a join to get the username. . You can filter by projects using other columns like project Type, pkey, pname or use the cwd_user table to filter by users.
1
2
3
4
5
6
7
8
9
10
SELECT
project.pname AS "Project Name",
cwd_user.user_name AS "Project Lead",
project.projecttype AS "Project Type"
FROM
project
JOIN
cwd_user ON project.lead = cwd_user.user_name
WHERE
cwd_user.user_name = '<username>'; -- Replace <username> with the actual usernameDefault Assignees:
2— Default Assignee: The Default Assignee can be found on the same Project Table of the Jira database under the assigneetype column, but works a bit differently since the default Default Assignee can only be set Project Lead or Unassigned. These two values are represented in the database, with a 2 for Project Lead and a 3 for Unassigned.
Default Assignee = Unassigned
1
2
3
4
5
6
7
8
9
10
11
SELECT
project.pname AS "Project Name",
cwd_user.user_name AS "Project Lead",
project.projecttype AS "Project Type"
FROM
project
JOIN
cwd_user ON project.lead = cwd_user.user_name
WHERE
cwd_user.user_name = '<username>' -- Replace <username> with the actual username
AND project.assigneetype = 3;Default Assignee = Project Lead (The Lead Column will have the Project Lead user)
1
2
3
4
5
6
7
8
9
10
11
SELECT
project.pname AS "Project Name",
cwd_user.user_name AS "Project Lead",
project.projecttype AS "Project Type"
FROM
project
JOIN
cwd_user ON project.lead = cwd_user.user_name
WHERE
cwd_user.user_name = '<username>' -- Replace <username> with the actual username
AND project.assigneetype = 2;Was this helpful?