How to export a list of projects and project admins for a specific system field in Jira Data Center
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
This article presents the option to list all projects which are using a specific system field, along with their project admins, by querying the database.
This can be especially useful for auditing, "house-cleaning" and reporting purposes.
Environment
Jira 8.x and 9.x.
Solution
The below sample query will list all projects that are using the Time Tracking system field and also retrieve project admin information:
This was written and tested using a PostgreSQL DB, for other database types you may need to tweak it depending on the database you are using.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select distinct p.pkey as "Project Key", p.pname, coalesce(cf.cfname, fsli.fieldidentifier) as "System Field Name", fs.name as "Screen Name",
coalesce(it.pname, 'DEFAULT') as "Issue Type", itss.name as "Scheme Name", pr.NAME as "Project Role", u.display_name as "User", pra.roletype
from project p
INNER JOIN projectroleactor pra ON pra.PID =p.ID
INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID
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
join nodeassociation na on na.source_node_id = p.id and na.sink_node_entity = 'IssueTypeScreenScheme'
join issuetypescreenscheme itss on itss.id = na.sink_node_id
join issuetypescreenschemeentity itsse on itsse.scheme = itss.id
join fieldscreenscheme fss on fss.id = itsse.fieldscreenscheme
join fieldscreenschemeitem fssi on fssi.fieldscreenscheme = fss.id
join fieldscreen fs on fs.id = fssi.fieldscreen
join fieldscreentab fst on fst.fieldscreen = fs.id
join fieldscreenlayoutitem fsli on fsli.fieldscreentab = fst.id
left join customfield cf on concat('customfield_', cf.id) = fsli.fieldidentifier
left join issuetype it on it.id = itsse.issuetype
where 1=1 and coalesce(cf.cfname, fsli.fieldidentifier) = 'timetracking' and pr.name = 'Administrators'Sample output
POSTGRES SAMPLE OUTPUT
1
2
3
"Project Key","pname","System Field Name","Screen Name","Issue Type","Scheme Name","Project Role","User","roletype"
TEST,TESTPROJ,timetracking,TEST: Scrum Bug Screen,Bug,TEST: Scrum Issue Type Screen Scheme,Administrators,admin,atlassian-user-role-actor
TEST,TESTPROJ,timetracking,TEST: Scrum Default Issue Screen,DEFAULT,TEST: Scrum Issue Type Screen Scheme,Administrators,admin,atlassian-user-role-actorWas this helpful?