Table: imap_message - Query IMAP Messages using SQL
Internet Message Access Protocol (IMAP) is an Internet standard protocol used by email clients to retrieve messages from a mail server over a TCP/IP connection. It is a method of accessing electronic mail or bulletin board messages that are kept on a (possibly shared) mail server. IMAP allows for managing and manipulating the email on the server without downloading the messages to the local device.
Table Usage Guide
The imap_message
table provides insights into messages within an IMAP server. As a system administrator, explore message-specific details through this table, including sender, recipient, subject, and associated metadata. Utilize it to uncover information about messages, such as those with specific subjects, from certain senders, or sent at particular dates.
Important Notes
- All queries are against a single mailbox, chosen in this order of precedence:
- A
where mailbox = 'INBOX'
qualifier in the query. - The
mailbox
config setting inimap.spc
. - Default is
INBOX
.
- A
Examples
List messages from the default mailbox (e.g. INBOX)
Explore the contents of your default mailbox, such as the INBOX, to gain insights into all your email messages. This can be particularly useful for analyzing overall email activity or identifying specific messages.
select *from imap_message;
select *from imap_message;
List messages from a specific mailbox
Determine the areas in which starred emails reside within a specific Gmail mailbox. This can be useful to quickly pinpoint important communications that have been marked for follow-up.
select *from imap_messagewhere mailbox = '[Gmail]/Starred';
select *from imap_messagewhere mailbox = '[Gmail]/Starred';
Find messages greater than 1MB in size
Explore which emails have a large size, potentially indicating attachments or extensive content. This can help manage storage space and identify important communications that may require more attention due to their size.
select timestamp, from_email, subject, sizefrom imap_messagewhere size > 1000000order by size desc;
select timestamp, from_email, subject, sizefrom imap_messagewhere size > 1000000order by size desc;
List messages received between 7 and 14 days ago
Discover the segments of your email inbox that contain messages received between a week and two weeks ago. This can help you manage your inbox by focusing on specific time frames.
select timestamp, from_email, subjectfrom imap_messagewhere timestamp > current_timestamp - interval '14 days' and timestamp < current_timestamp - interval '7 days'order by timestamp;
select timestamp, from_email, subjectfrom imap_messagewhere timestamp > datetime('now', '-14 days') and timestamp < datetime('now', '-7 days')order by timestamp;
Find messages from a given address
Discover the segments that contain emails sent from a specific address. This is useful for tracking communication history and identifying the content of the messages from a particular sender.
select timestamp, from_email, subjectfrom imap_messagewhere from_email = 'jim@dundermifflin.com';
select timestamp, from_email, subjectfrom imap_messagewhere from_email = 'jim@dundermifflin.com';
Search drafts for messages with a keyword
Explore drafts for messages containing a specific keyword. This can help in quickly identifying and reviewing relevant draft messages without having to manually search through each one.
select timestamp, from_email, subjectfrom imap_messagewhere mailbox = '[Gmail]/Drafts' and query = 'keyword';
select timestamp, from_email, subjectfrom imap_messagewhere mailbox = '[Gmail]/Drafts' and query = 'keyword';
List all attachments on Starred messages
Explore the attachments linked to your most important emails. This query is useful for identifying and reviewing all attachments connected to your starred messages, providing a quick way to assess important documents or files.
select m.timestamp, m.from_email, m.subject, a ->> 'file_name' as attachment_filename, a ->> 'content_type' as attachment_content_typefrom imap_message as m, jsonb_array_elements(attachments) as awhere mailbox = '[Gmail]/Starred';
select m.timestamp, m.from_email, m.subject, json_extract(a.value, '$.file_name') as attachment_filename, json_extract(a.value, '$.content_type') as attachment_content_typefrom imap_message as m, json_each(m.attachments) as awhere mailbox = '[Gmail]/Starred';
Schema for imap_message
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
attachments | jsonb | All parts having a Content-Disposition of attachment. | |
bcc_addresses | jsonb | Array of BCC addresses. | |
body_html | text | HTML body of the message. | |
body_text | text | Text body of the message. | |
cc_addresses | jsonb | Array of CC addresses. | |
embedded_files | jsonb | All parts having a Content-Disposition of inline. | |
errors | jsonb | Errors returned while parsing the email. | |
flags | jsonb | Flags set on the message. | |
from_addresses | jsonb | Array of From addresses. | |
from_email | text | = | Email address, in lower case, of the first (and usually only) mailbox in the From header. |
headers | jsonb | Full set of headers defined in the message. | |
in_reply_to | jsonb | Array of message IDs that this message is a reply to. | |
mailbox | text | = | Mailbox queried for messages. |
message_id | text | = | Unique message identifier that refers to a particular version of a particular message. |
query | text | = | Search query to match messages. |
seq_num | bigint | >, >=, =, <, <= | Sequence number of the message. |
size | bigint | >, >=, =, <, <= | Size in bytes of the message. |
subject | text | = | Subject of the message. |
timestamp | timestamp with time zone | >, >=, =, <, <= | Time when the message was sent. |
to_addresses | jsonb | Array of To addresses. |
Export
This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.
You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh
script:
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- imap
You can pass the configuration to the command with the --config
argument:
steampipe_export_imap --config '<your_config>' imap_message