Export Exchange Online Message Trace to Database

Prerequisites

• Entra ID Application Registration

• Azure Automation Account

• Understanding of PowerShell

• Self-Signed Certificate for Certificate Based Authentication, in PFX format

Enable the Resource Provider

The subscription where this is being setup may not have the Resource Provider enabled. To do this:

• Go to the Subscription

• Under Settings go to Resource providers

• Search for Microsoft.Automation

• Select the row, then select the Register Button at the top of the list

Create Entra ID App Registration

Inside the tenant where we are pulling the data from

• In Entra ID Portal, create App Registration

• Note the Tenant ID, Client ID

• Create a Client Secret

• Assign Mail.Read and User.Read in API Permissions

• Grant Admin Consent

• Set https://login.microsoftonline.com/common/oauth2/nativeclient as the Redirect URI

Create Automation Account

• Create an Automation Account

• Once created, go to Modules under Shared Resources confirm that ExchangeOnlineManagement and SQLServer has been added

• Go to Certificates under Shared Resources and Import the PFX file

• Create a Runbook , using PowerShell for the language and version 5.1, and use the following script block;

# Import Statements
Import-Module ExchangeOnlineManagement

# Variables
$Thumbprint = "<THUMBPRINT>" # This thumbprint is looked up against Certificates imported to the Automation Account
$AppId = "guid" # The Application ID from the Source Tenant where the authentication will take place
$Organization = "tenant.onmicrosoft.com" # The onmicrosoft.com address for the organisation

# Start
# Connect to Exchange Online and then determine the timeframes for start and finish
Connect-ExchangeOnline -AppId $AppId -CertificateThumbprint $Thumbprint -Organization $Organization -ShowBanner:$false
$Now = Get-Date
$StartDate = (Get-Date $Now).AddMinutes(-90)
$EndDate = $Now

# Execute the Message Trace
$MessageTrace = Get-MessageTrace -StartDate $StartDate -EndDate $EndDate

# Display the Message Trace
$MessageTrace

Setup SQL Table

Run the following query in the Database to create an SQL Table:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExchangeOnlineLogs]') AND type in (N'U'))
DROP TABLE [dbo].[ExchangeOnlineLogs]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ExchangeOnlineLogs](
[MessageId] [varchar](450),
[Received] [varchar](450) NULL,
[SenderAddress] [varchar](450) NULL,
[RecipientAddress] [varchar](450) NULL,
[Subject] [varchar](450) NULL,
[Status] [varchar](450) NULL,
[ToIp] [varchar](450) NULL,
[FromIp] [varchar](450) NULL,
[Size] [varchar](450) NULL,
[MessageTraceId] [varchar](450) NULL,
[StartDate] [varchar](450) NULL,
[EndDate] [varchar](450) NULL
)
GO

/* Add the Constraint */
ALTER TABLE ExchangeOnlineLogs ADD CONSTRAINT UQ_Message UNIQUE (MessageId,SenderAddress,RecipientAddress)

/* Create the User */
CREATE USER [databaseuser]
FOR LOGIN [databaseuser]
WITH DEFAULT_SCHEMA = dbo
GO

/* Add the Role */
ALTER ROLE RESTRICT_TO_EXCHANGE_LOGS_TABLE DROP MEMBER [databaseuser]

DROP ROLE RESTRICT_TO_EXCHANGE_LOGS_TABLE
CREATE ROLE RESTRICT_TO_EXCHANGE_LOGS_TABLE
GO
GRANT ALL ON ExchangeOnlineLogs TO RESTRICT_TO_EXCHANGE_LOGS_TABLE
GO
SP_ADDROLEMEMBER RESTRICT_TO_EXCHANGE_LOGS_TABLE, databaseuser

How are failures communicated?

TBA

What to do if we notice a failure

TBA