steampipe plugin install imapsteampipe 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

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