turbot/microsoft365
steampipe plugin install microsoft365

Table: microsoft365_mail_message - Query Microsoft 365 Mail Messages using SQL

Microsoft 365 Mail Messages are individual pieces of electronic mail delivered through the Microsoft 365 platform. They are a fundamental component of the Microsoft 365 suite, used for communication and information exchange within an organization. These messages can contain text, files, images, and other types of data.

Table Usage Guide

The microsoft365_mail_message table provides insights into the details of each mail message in a user's mailbox within Microsoft 365. As an IT administrator or security analyst, explore message-specific details through this table, including sender, recipient, subject, and associated metadata. Utilize it to uncover information about mail messages, such as those with specific keywords, the communication patterns within your organization, and the verification of compliance with communication policies.

Important Notes

  • You must specify the user_id in the where or join clause (where user_id=, join microsoft365_mail_message m on m.user_id=) to query this table.

Examples

Basic info

Explore the most recent emails received by a specific user in your Microsoft365 organization to gain insights into their communication patterns and topics of interest. This can be particularly useful for auditing purposes or to understand the context of a user's interactions.

select
subject,
created_date_time,
body_preview
from
microsoft365_mail_message
where
user_id = 'test@org.onmicrosoft.com'
order by
created_date_time
limit
10;
select
subject,
created_date_time,
body_preview
from
microsoft365_mail_message
where
user_id = 'test@org.onmicrosoft.com'
order by
created_date_time
limit
10;

List unread messages

Discover the segments that contain unread emails in a specific Microsoft 365 account. This can be useful for prioritizing and managing your inbox more effectively.

select
subject,
created_date_time,
body_preview
from
microsoft365_mail_message
where
user_id = 'test@org.onmicrosoft.com'
and not is_read
order by
created_date_time;
select
subject,
created_date_time,
body_preview
from
microsoft365_mail_message
where
user_id = 'test@org.onmicrosoft.com'
and not is_read
order by
created_date_time;

List high important messages

Explore your high priority emails in Microsoft 365 to better manage your tasks and prioritize your actions. This query helps you focus on the emails marked as 'high importance', allowing you to respond to critical issues promptly.

select
subject,
created_date_time,
body_preview
from
microsoft365_mail_message
where
user_id = 'test@org.onmicrosoft.com'
and filter = 'importance eq ''high'''
order by
created_date_time;
select
subject,
created_date_time,
body_preview
from
microsoft365_mail_message
where
user_id = 'test@org.onmicrosoft.com'
and filter = 'importance eq ''high'''
order by
created_date_time;

List messages from a specific user

Explore the content of messages sent from a specific user in your organization to gain insights into communication patterns and content. This could be particularly useful for audit purposes, or to understand the context of a user's communications over time.

select
subject,
created_date_time,
body_preview
from
microsoft365_mail_message
where
user_id = 'test@org.onmicrosoft.com'
and filter = '(from/emailAddress/address) eq ''test@domain.com'''
order by
created_date_time;
select
subject,
created_date_time,
body_preview
from
microsoft365_mail_message
where
user_id = 'test@org.onmicrosoft.com'
and filter = '(from/emailAddress/address) eq ''test@domain.com'''
order by
created_date_time;

List draft messages

Explore which emails are currently in draft status to understand what communication is pending or unfinished. This can help organize and prioritize your email responses.

select
subject,
created_date_time,
body_preview
from
microsoft365_mail_message
where
user_id = 'test@org.onmicrosoft.com'
and is_draft
order by
created_date_time;
select
subject,
created_date_time,
body_preview
from
microsoft365_mail_message
where
user_id = 'test@org.onmicrosoft.com'
and is_draft = 1
order by
created_date_time;

Schema for microsoft365_mail_message

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
attachmentsjsonbThe attachments of the message.
bcc_recipientsjsonbThe Bcc: recipients for the message.
bodyjsonbThe body of the message. It can be in HTML or text format.
body_previewtextThe first 255 characters of the message body in text format.
categoriesjsonbThe categories associated with the message.
cc_recipientsjsonbThe Cc: recipients for the message.
change_keytextThe version of the message.
conversation_idtextThe ID of the conversation the email belongs to.
created_date_timetimestamp with time zone
filtertext=Odata query to search for resources.
fromjsonbThe owner of the mailbox from which the message is sent.
has_attachmentsboolean!=, =Indicates whether the message has attachments.
idtext=Unique identifier for the message.
importancetextThe importance of the message. The possible values are: low, normal, and high.
inference_classificationtextThe classification of the message for the user, based on inferred relevance or importance, or on an explicit override. The possible values are: focused or other.
internet_message_idtextThe message ID in the format specified by RFC2822.
is_delivery_receipt_requestedbooleanIndicates whether a read receipt is requested for the message.
is_draftboolean!=, =Indicates whether the message is a draft. A message is a draft if it hasn't been sent yet.
is_readboolean!=, =Indicates whether the message has been read.
is_read_receipt_requestedbooleanIndicates whether a read receipt is requested for the message.
last_modified_date_timetimestamp with time zone
parent_folder_idtextThe unique identifier for the message's parent mailFolder.
received_date_timetimestamp with time zoneThe date and time the message was received.
reply_tojsonbThe email addresses to use when replying.
senderjsonbThe date and time the message was created.
sent_date_timetimestamp with time zoneThe date and time the message was sent.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
subjecttext=The subject of the message.
tenant_idtext=, !=, ~~, ~~*, !~~, !~~*The Azure Tenant ID where the resource is located.
titletextTitle of the resource.
to_recipientsjsonbThe To: recipients for the message.
user_idtext=ID or email of the user.
web_linktext

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)" -- microsoft365

You can pass the configuration to the command with the --config argument:

steampipe_export_microsoft365 --config '<your_config>' microsoft365_mail_message