steampipe plugin install imap

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:
    1. A where mailbox = 'INBOX' qualifier in the query.
    2. The mailbox config setting in imap.spc.
    3. Default is INBOX.

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_message
where
mailbox = '[Gmail]/Starred';
select
*
from
imap_message
where
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,
size
from
imap_message
where
size > 1000000
order by
size desc;
select
timestamp,
from_email,
subject,
size
from
imap_message
where
size > 1000000
order 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,
subject
from
imap_message
where
timestamp > current_timestamp - interval '14 days'
and timestamp < current_timestamp - interval '7 days'
order by
timestamp;
select
timestamp,
from_email,
subject
from
imap_message
where
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,
subject
from
imap_message
where
from_email = 'jim@dundermifflin.com';
select
timestamp,
from_email,
subject
from
imap_message
where
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,
subject
from
imap_message
where
mailbox = '[Gmail]/Drafts'
and query = 'keyword';
select
timestamp,
from_email,
subject
from
imap_message
where
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_type
from
imap_message as m,
jsonb_array_elements(attachments) as a
where
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_type
from
imap_message as m,
json_each(m.attachments) as a
where
mailbox = '[Gmail]/Starred';

Schema for imap_message

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
attachmentsjsonbAll parts having a Content-Disposition of attachment.
bcc_addressesjsonbArray of BCC addresses.
body_htmltextHTML body of the message.
body_texttextText body of the message.
cc_addressesjsonbArray of CC addresses.
embedded_filesjsonbAll parts having a Content-Disposition of inline.
errorsjsonbErrors returned while parsing the email.
flagsjsonbFlags set on the message.
from_addressesjsonbArray of From addresses.
from_emailtext=Email address, in lower case, of the first (and usually only) mailbox in the From header.
headersjsonbFull set of headers defined in the message.
in_reply_tojsonbArray of message IDs that this message is a reply to.
mailboxtext=Mailbox queried for messages.
message_idtext=Unique message identifier that refers to a particular version of a particular message.
querytext=Search query to match messages.
seq_numbigint>, >=, =, <, <=Sequence number of the message.
sizebigint>, >=, =, <, <=Size in bytes of the message.
subjecttext=Subject of the message.
timestamptimestamp with time zone>, >=, =, <, <=Time when the message was sent.
to_addressesjsonbArray 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