How to list all Boards with a certain status in Jira

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server と Data Center のみ - この記事は、サーバーおよびデータセンター プラットフォームのアトラシアン製品にのみ適用されます。

    

要約

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.