Purpose

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:

JSD-1685 - Getting issue details... STATUS

JSD-1762 - Getting issue details... STATUS

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

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:

   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:

   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:

   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:

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

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