turbot/microsoft365
steampipe plugin install microsoft365

Table: microsoft365_my_mail_message - Query Microsoft365 Mail Messages using SQL

Microsoft365 Mail is a service within the Microsoft365 suite that provides users with robust and secure mail services. It offers features such as spam filtering, malware protection, and customizable mail rules. Mail Messages in Microsoft365 represent individual emails that users send, receive, and store in their mailboxes.

Table Usage Guide

The microsoft365_my_mail_message table provides insights into Mail Messages within Microsoft365. As a security analyst, you can explore message-specific details through this table, including sender, recipient, subject, and body. Utilize it to uncover information about messages, such as those with specific keywords, the interactions between users, and the verification of communication compliance.

Important Notes

  • If not authenticating with the Azure CLI, this table requires the user_id argument to be configured in the connection config.

Examples

Basic info

Explore your recent email activity to understand the context and timeline of your communications. This could be useful to review your most recent email subjects and previews, helping you to stay organized and up-to-date.

select
subject,
created_date_time,
body_preview
from
microsoft365_my_mail_message
order by
created_date_time
limit
10;
select
subject,
created_date_time,
body_preview
from
microsoft365_my_mail_message
order by
created_date_time
limit
10;

List unread messages

Discover the segments that contain unread messages in your Microsoft 365 mail, allowing you to prioritize your responses and manage your inbox more efficiently. This is particularly useful in busy work environments where it's crucial to stay on top of important communications.

select
subject,
created_date_time,
body_preview
from
microsoft365_my_mail_message
where
not is_read
order by
created_date_time;
select
subject,
created_date_time,
body_preview
from
microsoft365_my_mail_message
where
is_read = 0
order by
created_date_time;

List high important messages

Discover the segments that contain high importance messages in your Microsoft 365 mail. This can be particularly useful for prioritizing your responses and managing your time effectively.

select
subject,
created_date_time,
body_preview
from
microsoft365_my_mail_message
where
filter = 'importance eq ''high'''
order by
created_date_time;
select
subject,
created_date_time,
body_preview
from
microsoft365_my_mail_message
where
filter = 'importance eq ''high'''
order by
created_date_time;

List messages from a specific user

Discover the segments that contain messages from a specific user in order to gain insights into their communication habits and content. This can be particularly useful for monitoring employee communication or analyzing customer feedback.

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

List draft messages

Explore which emails are still in draft status, allowing you to review and complete them in order of their creation dates. This can help manage your workflow by ensuring no important communications are left unfinished.

select
subject,
created_date_time,
body_preview
from
microsoft365_my_mail_message
where
is_draft
order by
created_date_time;
select
subject,
created_date_time,
body_preview
from
microsoft365_my_mail_message
where
is_draft
order by
created_date_time;

Schema for microsoft365_my_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_idtextID 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_my_mail_message