How to fetch from the Jira database all the JSM Email Requests and their corresponding projects and request types
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
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.
Environment
JSM (Jira Service Management) Data Center/Server on any version from 5.0.0.
Solution
SQL query
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.
1
2
3
4
5
6
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:
1
2
3
4
|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|Was this helpful?