How to Export The Email From Jira Email Processor Plugin (JEPP)

Summary

At times, there are some emails that might contribute to a halt of email creation from Service Management. The reason behind is JEPP (Jira Email Processor Plugin) runs into and error state and might require a deletion on the problematic email to resolve it.

Example:

JSDSERVER-1685 - Issue creation via email failed if email subject exceeds 255 characters (fixed in JSM 2.3.5)

JSDSERVER-1762 - Issue Creation via Email failed if Email contain Base64 attachments (fixed in JSM 2.4.1)

So this how-to is targeted to assist on exporting the email back to the original format so that the information is not lost.

Solution

Steps

1. Identify the email you wanted to export based on the "AO_2C4E5C_MAILITEMAUDIT" table. Find any particular MAIL_ITEM_ID you want to export.

2. Identify how many rows the email is by running the following SQL query:

   1 select count(*) from "AO_2C4E5C_MAILITEMCHUNK" where "MAIL_ITEM_ID" = <MAIL_ITEM_ID from Step 1>;

   ℹ️ Replace the <MAIL_ITEM_ID from Step 1> with the MAIL_ITEM_ID of the email to be exported.

3. Export the mail content to CSV:

   1 copy (select "MIME_MSG_CHUNK" from "AO_2C4E5C_MAILITEMCHUNK" where "MAIL_ITEM_ID" = <MAIL_ITEM_ID from Step 1>) to '/tmp/mail.csv' with CSV;

   ℹ️ The above tested working fine for PostgreSQL, for other database variants, please perform the modification accordingly.

4. If Step 2 above return value larger than 1, use the following command to reconstruct the email:

   1 cat mail.csv | sed 's/\n//g' | base64 --decode > test.eml

   ℹ️ Command above tested working fine for Mac OSX. For other OS variant, please alter the command accordingly

5. If Step 2 return value as only 1, use the following command instead:

   1 cat mail.csv | base64 --decode > test.eml

6. You can then open the .eml file using email clients.