How to list all Boards with a certain status in Jira
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 provides a way to list all agile boards in Jira that make use of a certain status.
This may be useful when managing statuses and you want to assess the impact of certain changes to them.
Environment
All versions of Jira Software 7.x and 8.x.
Solution
The queries below list the boards and columns which relate to the status "STATUS-NAME".
Columns names starting with "gh..." are default columns which names weren't changed since their creation.
Boards with the status in any column
select distinct
board."ID" as "Board Id",
board."NAME" as "Board name",
col."NAME" as "Column name",
col."POS" as "Column position",
status.pname as "Status name"
from "AO_60DB71_RAPIDVIEW" as board
join "AO_60DB71_COLUMN" as col on col."RAPID_VIEW_ID" = board."ID"
join "AO_60DB71_COLUMNSTATUS" as colstat on colstat."COLUMN_ID" = col."ID"
join issuestatus as status on status.id = colstat."STATUS_ID"
and status.pname = 'STATUS-NAME'; -- Replace status name here Board Id | Board name | Column name | Column position | Status name
----------+---------------+-------------------------------+-----------------+-------------
1 | DEV board | gh.workflow.preset.inprogress | 1 | In Progress
2 | SCRUM board | gh.workflow.preset.inprogress | 1 | In Progress
3 | Scrum and Dev | gh.boards.inprog | 1 | In Progress
4 | Dev and Scrum | gh.boards.inprog | 1 | In Progress
5 | DEV 2 | gh.boards.inprog | 2 | In Progress
(5 rows)Boards with the status only in the last column ("done")
select distinct
board."ID" as "Board Id",
board."NAME" as "Board name",
col."NAME" as "Column name",
col."POS" as "Column position",
status.pname as "Status name"
from "AO_60DB71_RAPIDVIEW" as board
join "AO_60DB71_COLUMN" as col on col."RAPID_VIEW_ID" = board."ID"
and col."ID" = (
select innercol."ID"
from "AO_60DB71_COLUMN" as innercol
where innercol."RAPID_VIEW_ID" = board."ID"
order by "POS" desc limit 1
)
join "AO_60DB71_COLUMNSTATUS" as colstat on colstat."COLUMN_ID" = col."ID"
join issuestatus as status on status.id = colstat."STATUS_ID"
and status.pname = 'STATUS-NAME'; -- Replace status name here Board Id | Board name | Column name | Column position | Status name
----------+---------------+-------------------------+-----------------+-------------
1 | DEV board | gh.workflow.preset.done | 2 | Done
2 | SCRUM board | gh.workflow.preset.done | 2 | Done
3 | Scrum and Dev | gh.boards.done | 2 | Done
4 | Dev and Scrum | gh.boards.done | 2 | Done
5 | DEV 2 | gh.boards.done | 3 | Done
(5 rows)