How to fetch from the Jira database all the JSM Email Requests and their corresponding projects and request types
プラットフォームについて: Data Center - この記事は、Data Center プラットフォームのアトラシアン製品に適用されます。
このナレッジベース記事は製品の Data Center バージョン用に作成されています。Data Center 固有ではない機能の Data Center ナレッジベースは、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。
*Fisheye および Crucible は除く
要約
The purpose of this article is to provide an SQL query that will fetch all the Jira Service Management (JSM) Mail Channels along with the JSM projects and Request Types they are associated to.
Note that this article only applies to the JSM Email Request / Mail Channel functionality which is documented in Receiving requests by email. If you are trying to fetch the list of Jira Mail Handlers (configured in ⚙ > System > Incoming Mail) from the Database, please refer to the other KB article Fetch Jira incoming mail handler associated projects.
環境
JSM (Jira Service Management) Data Center/Server on any version from 5.0.0.
ソリューション
SQL クエリ
The SQL query mentioned below was written and tested on a PostgreSQL database. For other types of database, the SQL query might need to be slightly modified.
SELECT es."EMAIL_ADDRESS" as "Mail Server", p."pname" as "Project Name",
p."pkey" as "Project Key", vpf."NAME" as "Request Type"
FROM "AO_54307E_EMAILCHANNELSETTING" es
INNER JOIN "AO_54307E_VIEWPORT" vp ON vp."ID" = es."SERVICE_DESK_ID"
INNER JOIN project p ON p.id = vp."PROJECT_ID"
INNER JOIN "AO_54307E_VIEWPORTFORM" vpf ON es."REQUEST_TYPE_ID" = vpf."ID";Example of output
You can find an example of output below, in a case where 2 JSM projects are each configured with 1 mail channel:
|Mail Server |Project Name|Project Key|Request Type |
|--------------------------------------|------------|-----------|--------------|
|someserver1@test.com |IT Project |ITSD |GET IT Help |
|someserver2@test.com |SD Project |SD |Travel request|