Summary

This article provides SQL query to fetch some more information about Incoming mail handler configuration, apart from this related article already provides. It can be used to find out mappings between handlers and projects using those handlers.

  Note that this article only applies to the Jira Incoming Mail Handler functionality (configured in ⚙ > System > Incoming Mail). If you are trying to fetch the list of Jira Service Management (JSM) Mail Channels (configured in Project Settings > Email Requests) from the Database, please refer to the other KB article How to fetch from the Jira database all the JSM Email Requests and their corresponding projects and request types.

Environment

Jira 8.x, 9.x

Solution

1. First, find the mail handler ID from the following query (note the "Handler ID" value):
   

   SELECT sc.id AS "Handler ID", sc.servicename AS "Handler Name", ms.id AS "Mail Server ID", ms.servername AS "Mail Server Name", ps.id AS "propertystring ID"
   FROM serviceconfig sc
   INNER JOIN propertyentry pe ON pe.property_key = 'popserver' AND pe.entity_id = sc.id
   INNER JOIN propertystring ps ON ps.id = pe.id
   INNER JOIN mailserver ms ON CAST(ps.propertyvalue AS integer) = ms.id
   WHERE sc.clazz = 'com.atlassian.jira.service.services.mail.MailFetcherService';

2. Then use this query to identify the project being used, and match "Handler ID" value to pe.entity_id (in this example, handler ID was 10300):
   

   SELECT *
   FROM propertyentry pe
   INNER JOIN propertystring ps ON pe.id = ps.id
   WHERE pe.entity_id IN (10300) and property_key = 'handler.params'

3. Column "propertyvalue" will contain project key and other configuration parameters: