How to export a list of projects and project admins for a specific system field from database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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.
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 which are using the Time Tracking system field and also retrieves 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.
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
"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-actor