I received a couple of nice comments about last month article about retrieving Office 365 mailboxes statistics. Apparently, I am not alone trying to get information from Office 365 in an automated way!

This month, I will show you 2 more tricks built using PowerShell to get some more information from the Office 365 tenant. First, I will show you how to get the list of mobile devices that connects to the mailboxes. Second, I will show you how to list not-delivered emails.

Downloadable code

Obviously, there won’t be any .Net code to download this month. Instead, the PowerShell scripts are made available so you can start from them and modify to satisfy your own requirements.

This script was tested using the version 5 of PowerShell.

Review last month scripts

I will not repeat the sections related to the editor, to the credentials and to sending emails. They were covered last month and reusable as-is for this article. If needs be, revisit PowerShell – Get O365 mailboxes statistics.

Getting the list of connected mobile devices

To ensure that only allowed devices are connecting to the mailboxes (for compliance reasons), we need to provide a list of mobile devices that connect to the mailboxes we are managing.

Figure 1: A sample of the mobile devices report

Much like last month, you need to start by retrieving a list of the various mailboxes by using the Get-Mailbox function:

$mailboxes = Get-Mailbox | 
            Select Identity, DisplayName, UserPrincipalName, RecipientTypeDetails |
            Sort DisplayName 

Once you have the list of mailboxes, you can then query which devices have been used to connect to it using the Get-MobileDeviceStatistics function for each of the mailboxes. You can then loop through the devices (because there can be more than one for a single mailbox), to build your array of devices. The script to collect the data looks like this:

foreach ($mailbox in $mailboxes) 
{ 
    $mailboxCount ++
    Write-Output ("Processing $mailboxCount - $mailbox")

    #Get other details from the account
    $mobiles = Get-MobileDeviceStatistics -Mailbox $mailbox.Identity | 
        Select DeviceFriendlyName, DeviceOS, DeviceType, LastSuccessSync, DevicePhoneNumber, DeviceMobileOperator, Status, 
                StatusNote, DeviceAccessState, DeviceAccessStateReason, IsValid

    foreach ($mobile in $mobiles) { 

        $days = ((Get-Date) - $mobile.LastSuccessSync).Days.tostring("####")  
        $newItem = New-Object System.Object
        $newItem | Add-Member -MemberType NoteProperty -Name "DisplayName" -Value $mailbox.DisplayName
        $newItem | Add-Member -MemberType NoteProperty -Name "DeviceFriendlyName" -Value $mobile.DeviceFriendlyName
        $newItem | Add-Member -MemberType NoteProperty -Name "DeviceOS" -Value $mobile.DeviceOS
        $newItem | Add-Member -MemberType NoteProperty -Name "DeviceType" -Value $mobile.DeviceType
        $newItem | Add-Member -MemberType NoteProperty -Name "LastSuccessSync" -Value $mobile.LastSuccessSync
        $newItem | Add-Member -MemberType NoteProperty -Name "DevicePhoneNumber" -Value $mobile.DevicePhoneNumber
        $newItem | Add-Member -MemberType NoteProperty -Name "DeviceMobileOperator" -Value $mobile.DeviceMobileOperator
        $newItem | Add-Member -MemberType NoteProperty -Name "Status" -Value $mobile.Status
        $newItem | Add-Member -MemberType NoteProperty -Name "StatusNote" -Value $mobile.StatusNote
        $newItem | Add-Member -MemberType NoteProperty -Name "DeviceAccessState" -Value $mobile.DeviceAccessState
        $newItem | Add-Member -MemberType NoteProperty -Name "DeviceAccessStateReason" -Value $mobile.DeviceAccessStateReason
        $newItem | Add-Member -MemberType NoteProperty -Name "IsValid" -Value $mobile.IsValid
        $newItem | Add-Member -MemberType NoteProperty -Name "Days" -Value $days

        $collectionWithItems.Add($newItem) | Out-Null
    } 
}  

Once you have your collection of devices, you can output it to HTML and send it by email like we did last month.

Notice that some carriers are not sharing as much information (like the phone number or the operator name) but if your main quest is to get which devices are connecting to which mailboxes, this script should be able to provide you that information.

Getting the list of emails not-delivered

For some reasons, some emails are not being delivered. If you are administering a mail server, it could be a good thing to monitor those emails so you can react before the users notice the issue.

Figure 2: Not delivered emails report sample

This time, we are not starting at the mailbox level like we did for the other scripts. We query the messages directly by using Get-MessageTrace. We can filter for a period of time using the StartDate and EndDate parameters and filter even more on the status using Where-Object clause.

One thing you need to be aware is that the Get-MessageTrace function is limited to return a maximum of 5000 message each time you query it. This is why you need to use the PageSize and Page parameters and loop while you have not gone through all the messages you are interested in.

My loop looks like this:

$Page = 0
do 
{ 
    $Page++ 
    Write-Output "Collecting Message Tracking - Page $Page..." 
    $CurrMessages = Get-MessageTrace -StartDate (Get-Date).AddDays(-2) -EndDate (Get-Date)  -PageSize 5000  -Page $Page | 
                    Where-Object {($_.status -ne 'delivered') -and ($_.status -ne 'Resolved') -and ($_.status -ne 'Expanded') } 
    if ($CurrMessages -ne $null)
    {
        foreach ($message in $CurrMessages) 
        { 
            $collectionWithItems.Add((CreateCollectionItem $message)) | Out-Null
        }
    }
} 
until ($CurrMessages -eq $null) 

This loop is calling the CreateCollectionItem function because the Get-MessageTrace function returns a list of messages but what we are really interested in are the failed messages and this function does not provide the reason. This information will be provided by the Get-MessageTraceDetail function. Be warned that if you go too far back (meaning more than a few days), the trace details might be missing. So it is important to query for that information regularly.

My CreateCollection function reads like this:

function CreateCollectionItem
{
    param($pMessage)

    #Get other details from the account
    $details = Get-MessageTraceDetail -MessageTraceId $pMessage.MessageTraceId -RecipientAddress $pMessage.RecipientAddress

    $newItem = New-Object System.Object

    $newItem | Add-Member -MemberType NoteProperty -Name "Received" -Value $pMessage.Received
    $newItem | Add-Member -MemberType NoteProperty -Name "SenderAddress" -Value $pMessage.SenderAddress
    $newItem | Add-Member -MemberType NoteProperty -Name "RecipientAddress" -Value $pMessage.RecipientAddress
    $newItem | Add-Member -MemberType NoteProperty -Name "Subject" -Value $pMessage.Subject
    $newItem | Add-Member -MemberType NoteProperty -Name "Status" -Value $pMessage.Status
    $newItem | Add-Member -MemberType NoteProperty -Name "FromIP" -Value $pMessage.FromIP
    $newItem | Add-Member -MemberType NoteProperty -Name "Size" -Value (SizeToString($pMessage.Size))
    $newItem | Add-Member -MemberType NoteProperty -Name "MessageTraceId" -Value $pMessage.MessageTraceId

    $StatusGroupingSort = 0
    $StatusGrouping = ""
    $detailMsg = ""
    $eventID = 0
    foreach ($detail in $details)
    {
        if ($detail.Detail -ne $null -AND $detail.Detail -ne '' -AND $detail.Event -ne 'Receive' -AND $detail.Event -ne 'Submit')
        { 
            $eventID ++
            $detailMsg += $eventID.ToString() + " - " + $detail.Event + ": " + $detail.Detail + "<br>"
        }

        if ($detail.Event -eq 'Malware')
        { 
            $StatusGrouping = 'Malware'
            $StatusGroupingSort = 30
        }
        if ($detail.Event -eq 'Badmail')
        { 
            $StatusGrouping = 'Badmail'
            $StatusGroupingSort = 20
        }
    }
    $newItem | Add-Member -MemberType NoteProperty -Name "Message" -Value $detailMsg

    #GROUPING = RestrictedToGroupPermission
    if ($StatusGrouping -eq '' -AND $detailMsg -like '*RESOLVER.RST*')
    {
        $StatusGrouping = 'Restricted To Group'
        $StatusGroupingSort = 50
    }
    #GROUPING = PENDING
    if ($StatusGrouping -eq '' -AND $pMessage.Status -eq 'Pending')
    {
        $StatusGrouping = 'Pending'
        $StatusGroupingSort = 40
    }
    #GROUPING = RecipientNotFound
    if ($StatusGrouping -eq '' -AND $detailMsg -like '*RESOLVER.ADR.RecipientNotFound*')
    {
        $StatusGrouping = 'Recipient Not Found'
        $StatusGroupingSort = 60
    }
    #GROUPING = OOF addressed to external recipient
    if ($StatusGrouping -eq '' -AND $detailMsg -like '*RESOLVER.OOF*')
    {
        $StatusGrouping = 'Out of Office'
        $StatusGroupingSort = 62
    }

    $newItem | Add-Member -MemberType NoteProperty -Name "StatusGrouping" -Value $StatusGrouping
    $newItem | Add-Member -MemberType NoteProperty -Name "StatusGroupingSort" -Value $StatusGroupingSort

    return $newItem
} 

Because the list of failed messages can be rather long and painful to go through, I am adding a bit of custom process to this function to be able to group the alike messages together when outputted to HTML (all malware together, all Recipient not found together) which make the review a bit easier and faster. Look for the StatusGrouping (used to provide a short description) and StatusGroupingSort (used to sort the results) fields.

Conclusion

I would like to have more of these features already implanted as schedulable tasks in Office 365 so I could reuse their report but so far, I have mostly found ad-hoc reports and/or incomplete reports.

In those occasions, I pull out my little PowerShell skills and roll up my sleeves to try to fit it samples I find here and there together until it fits my requirements.

This month, I have provided 2 hopefully useful scripts, the first one to get the list of mobile devices that connects to the mailboxes and a second to list not-delivered emails.