Export Exchange Online mailbox and archive stats to CSV

If you’re like me, you’ve written variations of this script a hundred times. I decided to finally blog it and put it on git so that I could refer back. This script will get all exchange online mailboxes, check if an archive exists then dump all relevant information to CSV. It helps to know the quota of a mailbox and archive (dependent on your 365 license type), as well as the current usage, item count and if auto expanding archive is enabled. The script will report progress to the console as it goes letting you know what mailbox it’s currently processing and how many are left to process. Enjoy.

# Establish output path
$OutputPath = "$env:TEMP\$(Get-date -Format 'yyyyMMddhhmmss')_mailbox_report.csv"

# Establish result array variable

# Get all mailboxes
$mailboxes = Get-Mailbox -ResultSize Unlimited

# Get total mailboxes and establish counter variable
$totalmbx = $mailboxes.Count
$i = 0 

# Loop through each mailbox and perform actions
$mailboxes | ForEach-Object {
    # Increment counter
    # Add current mailbox to $mbx variable
    $mbx = $_
    # Reset variables for next loop
    $mba = $null
    $mbs = $null
    $mbasize = $null
    $mbssize = $null
    $MailboxAllocationInGB = $null
    $ArchiveAllocationInGB = $null
    # Write progress to host
    Write-Host "Processing $mbx" "$i out of $totalmbx completed"
    # Check if archive enabled, if so, get archive stats
    if ($mbx.ArchiveName){
        $mba = Get-MailboxStatistics -Archive $mbx.UserPrincipalName
        # Format archive size to GB with 2 decimal places
        if ($mba.TotalItemSize -ne $null){
            $mbasize = [math]::Round(($mba.TotalItemSize.ToString().Split('(')[1].Split(' ')[0].Replace(',','')/1GB),2)
            $mbasize = 0 

    # Get mailbox stats
    $mbs = Get-MailboxStatistics $mbx.UserPrincipalName
        # Format mailbox size to GB with 2 decimal places
        if ($mbs.TotalItemSize -ne $null){
            $mbssize = [math]::Round(($mbs.TotalItemSize.ToString().Split('(')[1].Split(' ')[0].Replace(',','')/1GB),2)
            $mbssize = 0 

    # Get archive allocation (quota) and trim everything but the size in GB
    if ($mbx.ArchiveName){
        $ArchiveAllocationInGB = $mbx.ArchiveQuota.Split('G')
        $ArchiveAllocationInGB = $ArchiveAllocationInGB[0]
    # Get mailbox allocation (quota) and trim everything but the size in GB
    $MailboxAllocationInGB = $mbx.ProhibitSendReceiveQuota.Split('G')
    $MailboxAllocationInGB = $MailboxAllocationInGB[0]

    # Create PSObject and store all relevant information for export
    $Result += New-Object -TypeName PSObject -Property $([ordered]@{ 
        UserName = $mbx.DisplayName
        UserPrincipalName = $mbx.UserPrincipalName
        MailboxType = $mbx.RecipientTypeDetails
        MailboxAllocationInGB = $MailboxAllocationInGB
        MailboxSizeInGB = $mbssize
        MailboxItemCount = if ($mbs.ItemCount) {$mbs.ItemCount} Else { $null}
        ArchiveEnabled = if ($mbx.ArchiveName) {"Enabled"} Else { "Disabled"}
        ArchiveName = $mbx.ArchiveName
        ArchiveAllocationInGB = if ($mbx.ArchiveName) {$ArchiveAllocationInGB} Else { $null} 
        ArchiveSizeInGB = $mbasize
        ArchiveItemCount = if ($mba.ItemCount) {$mba.ItemCount} Else { $null}
        AutoExpandingArchiveEnabled = $mbx.AutoExpandingArchiveEnabled
# Export results to CSV
$Result | Export-CSV $OutputPath -NoTypeInformation -Encoding UTF8
Write-Host "Output csv file is located here: `n `n $OutputPath `n" -ForegroundColor Yellow

Code also on my github here.

Thanks for reading – Jesse

Exchange Online message trace with more detail

Recently I needed to dig through some email using the Exchange Online PowerShell module and I found the default cmdlets a bit lacking in detail. Get-MessageTrace and Get-MessageTraceDetail show you enough, but sometimes you want to know more about the flow of an email from when it was received until it was ultimately delivered, marked as spam or quarantined. The graphical view at https://security.microsoft.com/quarantine is good and does give you pretty much everything you need, but I wanted to be able to see the spam scoring metrics and any additional details.

Get-MessageTrace gives us information about when the message was received, the sender and recipient addresses, the subject and what ultimately happened to the email. See the following screen shot where I’ve used a spam email that was captured in quarantine to demonstrate:

Get-MessageTrace -StartDate (get-date).AddDays(-10) -EndDate (get-date) -SenderAddress ds043_buh@edu.klgd.ru

Get-MessageTraceDetail give us more specifics about each event that occurred from when the message was received, to in this case, when it was quarantined.

Get-MessageTrace -StartDate (get-date).AddDays(-10) -EndDate (get-date) -SenderAddress ds043_buh@edu.klgd.ru | Get-MessageTraceDetail

But what happens if you want to know more about each event and scrutinise further? The answer is in the ‘data’ property of each event, which is an xml string. If I store the message trace detail in a variable, select an event and look the data property, I see something like the following:

$messageTraceDetail = Get-MessageTrace -StartDate (get-date).AddDays(-10) -EndDate (get-date) -SenderAddress ds043_buh@edu.klgd.ru | Get-MessageTraceDetail


As we can see, this data is not very useful. To make it more useful, we can create an xml object from this xml string so that we can work with each property and do something with it.

$xml = [xml]$messageTraceDetail[0].Data

The data we are after lives under the root.MEP node of the xml object. Pathing to this exposes the properties in the event we have selected, which in this example is the receive event.

My goal was to expose the details of each event into a single object and have it output to the console for inspection. To do this, I needed to get the message, get message detail, loop through each event, convert the data property to xml, select the properties that were relevant and build a custom object.

As I started writing the script, I came across a few hurdles that needed addressing:

  • When adding properties to my custom object, some event properties were not showing any values. I realised that some event property values were strings and some integers.
  • Some property names were the same between events, so I needed a way to make these unique if they were going to be members of the same custom object.
  • Some properties legitimately had no data in their values since not all delivered mail is the same. For example, a spam email may populate a spam list property value, but a clean email would not. I needed to remove blank properties dynamically each time the script ran.
  • The [datetime] objects returned in some properties were set to UTC +0 and I wanted to see these properties in local time.
  • The ‘RecipientReference’ property was always blank (in all the emails I’ve checked), so I wanted to exclude this all together.
  • Not all data was available in the Get-MessageTraceDetail events data property, some of it was in the detail property and some was in the output from Get-MessageTrace.
  • I wanted to be prompted to enter a recipient or subject and filter on these if required.

The below script is the result and the output looks something like this:

function Get-MessageTraceWithMoreDetail
   Trace mail messages with more detail
   This script traces mail messages and provides more detail exposing each event and outputs a single object for review.
   Start date and end date values are set to 10 days old and current date respectively, but you can overide if required.
   Get-MessageTraceWithMoreDetail -startDate (Get-Date).AddDays(-3) -endDate $endDate = (Get-Date).AddDays(-1)
        [datetime]$startDate = (Get-Date).AddDays(-10),

        [datetime]$endDate = (Get-Date)

try {

        $senderAddress = Read-Host -Prompt "What is the sender address or domain? eg. @domain.com or user@domain.com"

        Clear-Variable -Name recipientCheck,subjectCheck -ErrorAction SilentlyContinue

        while ($recipientCheck -notmatch "Y|N")
            $recipientCheck = Read-Host -Prompt "Do you want to search using a recipient filter? [ Y | N ]"

        while ($subjectCheck -notmatch "Y|N")
            $subjectCheck = Read-Host -Prompt "Do you want to search using a subject filter? [ Y | N ]"

        if ($subjectCheck -eq "Y" -and $recipientCheck -eq "Y") {
                $recipientFilter = Read-Host -Prompt "What is the recipients email address? eg. user@domain.com"
                $subjectFilter = Read-Host -Prompt "What words does the subject contain?"

                $messagesToReview = Get-MessageTrace -StartDate $startDate -EndDate $endDate -SenderAddress $senderAddress -RecipientAddress $recipientFilter | Where-Object -FilterScript {$_.Subject -like "*$subjectFilter*"}

        elseif ($subjectCheck -eq "Y" -and $recipientCheck -eq "N") {

                $subjectFilter = Read-Host -Prompt "What words does the subject contain?"

                $messagesToReview = Get-MessageTrace -StartDate $startDate -EndDate $endDate -SenderAddress $senderAddress | Where-Object -FilterScript {$_.Subject -like "*$subjectFilter*"}

        elseif ($subjectCheck -eq "N" -and $recipientCheck -eq "Y") {

                $recipientFilter = Read-Host -Prompt "What is the recipients email address? eg. user@domain.com"

                $messagesToReview = Get-MessageTrace -StartDate $startDate -EndDate $endDate -SenderAddress $senderAddress -RecipientAddress $recipientFilter

        else {

               $messagesToReview = Get-MessageTrace -StartDate $startDate -EndDate $endDate -SenderAddress $senderAddress


    Write-Host ""
    Write-Host ""
    Write-Host "============ Message Report $(get-date) ============" -ForegroundColor Cyan

    foreach ($message in $messagesToReview) {

                # Convert to local time
                $messageReceivedDate = Get-LocalTime -UTCTime $message.Received

                $customMessageObjectProps = [ordered]@{
                    'Step 0 : Sender IP' = $message.FromIP ;
                    'Step 0 : From address' = $message.SenderAddress ;
                    'Step 0 : Date received' = $messageReceivedDate ;
                    'Step 0 : To address' = $message.RecipientAddress ;
                    'Step 0 : Message subject' = $message.Subject ;
                    'Step 0 : Message status' = $message.Status ;
                    'Step 0 : Message size (KB)' = $([math]::Round(($message.Size / 1KB),2)) ;
                    'Step 0 : Message ID' = $message.MessageId}

                $customMessageObject = ""

                $customMessageObject = New-Object -TypeName PSObject -Property $customMessageObjectProps

                $messageDetail = $message | Get-MessageTraceDetail

                [int]$c = ""

                foreach ($event in $messageDetail) {

                    $xml = [xml]$event.Data
                    $eventReport = $xml.root.MEP

                    # Convert to local time
                    $adjustedDate = Get-LocalTime -UTCTime $event.Date
                    $customMessageObject | Add-Member -NotePropertyName "Step $($c) : Action taken:" -NotePropertyValue $($event.Detail)
                    $customMessageObject | Add-Member -NotePropertyName "Step $($c) : Action time:" -NotePropertyValue $($adjustedDate.ToString())

                        foreach ($xmlProp in $eventReport) {

                            if ( 'string' -in (($eventReport | Get-Member).Name)) {
                                   if ($xmlProp.String -ne $null) {

                                        if ($xmlProp.Name -ne "RecipientReference") {
                                        $customMessageObject | Add-Member -NotePropertyName "Step $($c) : $($xmlProp.Name)" -NotePropertyValue $xmlProp.string -Force

                            elseif ( 'integer' -in (($eventReport | Get-Member).Name)) {
                                   if ($xmlProp.integer -ne $null) {
                                        $customMessageObject | Add-Member -NotePropertyName "Step $($c) : $($xmlProp.Name)" -NotePropertyValue $xmlProp.integer -Force

        Write-Output $customMessageObject


    catch {

    Write-Host "Failed to perform message trace with more detail"
    Write-Host "$($_)"
    Write-Host "Line Number: $($_.InvocationInfo.ScriptLineNumber)"
    Write-Host "Offset: $($_.InvocationInfo.OffsetInLine)"
    Write-Host "Line: $($_.InvocationInfo.Line)"



function Get-LocalTime($UTCTime)
$strCurrentTimeZone = (Get-WmiObject win32_timezone).StandardName
$TZ = [System.TimeZoneInfo]::FindSystemTimeZoneById($strCurrentTimeZone)
$LocalTime = [System.TimeZoneInfo]::ConvertTimeFromUtc($UTCTime, $TZ)
Return $LocalTime

Code is also on my GitHub.