Help with custom query to extract email text?

Hello Elisabeth, 

This is an interesting request, thanks for sharing it. 

Before building the query, the first step is confirming where the communication content is actually stored.
In Sugar, emails can originate from multiple places (inbound email, email-to-case, direct replies, etc.), so we want to be sure the messages you want to extract are indeed stored in the emails module and not, for example, in Case Notes.

Assuming everything is stored in the Emails module, the tables you want to look in the database are: 

• emails – main email metadata (ID, subject, dates, direction, etc.)

• emails_text – this stores the actual email body and header fields
  
  

With this in mind, you can build a query to retrieve all emails where your support representative’s email address appears as the sender, and then filter them by date (e.g., the last 30 days). Once you have that dataset, you can further remove internal communications by filtering the to_addrs field to include only customer domains.

This could be an example query to get you started: 

SELECT 
    e.id,
    e.name AS subject,
    e.date_sent,
    et.from_addr,
    et.to_addrs,
    et.description,
    et.description_html
FROM emails e
INNER JOIN emails_text et 
    ON e.id = et.email_id
WHERE 
    et.from_addr LIKE '%support_person@yourcompany.com%'
    AND e.date_sent >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
    AND e.deleted = 0
ORDER BY e.date_sent DESC;

Let me know if this helps. 

Cheers, 

André 

Hello Elisabeth Hunt , 

While testing this locally I was using older demo data and overlooked an important detail.

In newer versions of Sugar, the fields that store the email body (description and description_html) are compressed and encoded to reduce database storage.

As a result, when you export the CSV from the query, those two columns will not appear in readable form.

They need to be decoded before the data can be used normally.

To make this easy, I prepared a small Python script you can run on the .csv file after downloading it. 

It reads your export and adds two new columns containing the decoded text for description and description_html.

Here is the script:

import base64
import zlib
import csv
import sys
import os

def decode_field(value):
    """Decode Base64 + raw DEFLATE fields. Return original value if decoding fails."""
    if not isinstance(value, str) or value.strip() == "":
        return value
    try:
        raw = base64.b64decode(value)
        # -15 = raw DEFLATE (no headers)
        text = zlib.decompress(raw, -15).decode("utf-8", errors="replace")
        return text
    except Exception:
        return value

def main():
    # --- Handle missing argument ---
    if len(sys.argv) < 2:
        print("Usage: python3 decode_custom_queries.py <input_file.csv>")
        sys.exit(1)

    input_file = sys.argv[1]

    # --- Check if the file exists ---
    if not os.path.isfile(input_file):
        print(f"Error: File not found: {input_file}")
        sys.exit(1)

    # --- Create output filename ---
    base, ext = os.path.splitext(input_file)
    output_file = f"{base}_decoded{ext}"

    # --- Process CSV ---
    with open(input_file, newline="", encoding="utf-8") as f_in:
        reader = csv.DictReader(f_in)
        fieldnames = reader.fieldnames + ["Description_decoded", "description_html_decoded"]
        rows = []

        for row in reader:
            row["Description_decoded"] = decode_field(row.get("Description"))
            row["description_html_decoded"] = decode_field(row.get("description_html"))
            rows.append(row)

    # --- Write decoded file ---
    with open(output_file, "w", newline="", encoding="utf-8") as f_out:
        writer = csv.DictWriter(f_out, fieldnames=fieldnames)
        writer.writeheader()
        writer.writerows(rows)

    print(f"Done! Decoded file saved as: {output_file}")

if __name__ == "__main__":
    main()

To use the script, simply create a .py file with the content provided above.

Once the file is saved, you can run it from the command line with:

% python3 decoder.py exportedFileFromSugar.csv

After it runs, you’ll see a message like:

Done! Decoded file saved as:exportedFileFromSugar_decoded.csv

This new file will contain the same data as the original export, but with the email text fields fully decoded and human-readable.

Please let me know if you’re able to use it successfully or if you’d like any help.

Cheers,

André

WOW, André, thank you!!  Let me give this a shot!