How to list all Boards with a certain status in Jira
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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.
*Fisheye および Crucible は除く
要約
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.
環境
All versions of Jira Software 7.x and 8.x.
ソリューション
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
Example output
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
Example output
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)
最終更新日 2021 年 9 月 30 日
Powered by Confluence and Scroll Viewport.