turbot/imap

GitHub
steampipe plugin install imapsteampipe plugin install imap

Table: imap_message

Query messages from a given mailbox.

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)

select
*
from
imap_message

List messages from a specific mailbox

select
*
from
imap_message
where
mailbox = '[Gmail]/Starred'

Find messages greater than 1MB in size

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

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

Find messages from a given address

select
timestamp,
from_email,
subject
from
imap_message
where
from_email = 'jim@dundermifflin.com'

Search drafts for messages with a keyword

select
timestamp,
from_email,
subject
from
imap_message
where
mailbox = '[Gmail]/Drafts'
and query = 'keyword'

List all attachments on Starred messages

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'

.inspect imap_message

Messages in IMAP.

NameTypeDescription
_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_emailtextEmail 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.
mailboxtextMailbox queried for messages.
message_idtextUnique message identifier that refers to a particular version of a particular message.
querytextSearch query to match messages.
seq_numbigintSequence number of the message.
sizebigintSize in bytes of the message.
subjecttextSubject of the message.
timestamptimestamp with time zoneTime when the message was sent.
to_addressesjsonbArray of To addresses.