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

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
attachments	jsonb		The attachments of the message.
bcc_recipients	jsonb		The Bcc: recipients for the message.
body	jsonb		The body of the message. It can be in HTML or text format.
body_preview	text		The first 255 characters of the message body in text format.
categories	jsonb		The categories associated with the message.
cc_recipients	jsonb		The Cc: recipients for the message.
change_key	text		The version of the message.
conversation_id	text		The ID of the conversation the email belongs to.
created_date_time	timestamp with time zone
filter	text	=	Odata query to search for resources.
from	jsonb		The owner of the mailbox from which the message is sent.
has_attachments	boolean	!=, =	Indicates whether the message has attachments.
id	text	=	Unique identifier for the message.
importance	text		The importance of the message. The possible values are: low, normal, and high.
inference_classification	text		The 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_id	text		The message ID in the format specified by RFC2822.
is_delivery_receipt_requested	boolean		Indicates whether a read receipt is requested for the message.
is_draft	boolean	!=, =	Indicates whether the message is a draft. A message is a draft if it hasn't been sent yet.
is_read	boolean	!=, =	Indicates whether the message has been read.
is_read_receipt_requested	boolean		Indicates whether a read receipt is requested for the message.
last_modified_date_time	timestamp with time zone
parent_folder_id	text		The unique identifier for the message's parent mailFolder.
received_date_time	timestamp with time zone		The date and time the message was received.
reply_to	jsonb		The email addresses to use when replying.
sender	jsonb		The date and time the message was created.
sent_date_time	timestamp with time zone		The date and time the message was sent.
subject	text	=	The subject of the message.
tenant_id	text		The Azure Tenant ID where the resource is located.
title	text		Title of the resource.
to_recipients	jsonb		The To: recipients for the message.
user_id	text		ID or email of the user.
web_link	text

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