ActiveSync Device and User Report for Office 365 D, MT, and Exchange 2010+


Today, I had a customer ask me for an ActiveSync device report.  Normally, this is a somewhat simple task (Get-Mailbox | Get-ActiveSyncDevice), but in a large environment with hundreds of domains representing different agencies or business units, that is kind of an unwieldy report to run (as well as including a lot of data for out-of-scope users).

So, here's a new script that hopefully solves that problem for someone else besides me.

The first problem, of course, was creating a dynamic filter that I could pass to a Get-Mailbox or Get-CASMailbox command.  In an earlier blog post, I talk about how I solved that problem.  So, using the [scriptblock]::Create() method to create a my filter gave me a big chunk of the solution.  Instead of having to manually execute a command, I could pass a parameter value to my script that would then be passed to Get-Mailbox -Filter and use server-side filtering to dramatically cut down the number of mailboxes that I would query.

Fun fact: the -Filter parameter in Get-Mailbox and Get-CASMailbox doesn't let you use the -match operator, so you're stuck with "like" and "eq" (and their counterparts).

# begin snip
param (
    [Parameter(Mandatory=$false)]
        [string]$Domain
        )
If ($Domain)
    {
    If ($Domain.StartsWith("*")) { # Value already starts with an asterisk }
    Else { $Domain = "*" + $Domain}
    $Filter = [scriptblock]::Create("{EmailAddresses -like `"$Domain`" -and HasActiveSyncDevicePartnership -eq `$True}")
    }
If ($Domain)
    {
    $cmd = "Get-CASMailbox -ResultSize $ResultSize -Filter $Filter -WarningAction SilentlyContinue"
    $EASMailboxes = Invoke-Expression $cmd
    }
#end snip

With those two filtering parameters (EmailAddresses -like $Domain and HasActiveSyncDevicePartnerShip -eq $true), I was able to cut a query down--even for a domain that has 7,000 mailboxes in an Office 365 tenant with 120,000+ mailboxes to just a minute or two.

From there, building the report was a little more than looping through the $EASMailboxes param and then adding a Get-Mailbox to each object in the $EASMailboxes pipeline to retreive DisplayName and PrimarySmtpAddress (since Get-CASMailbox frustratingly doesn't surface those attributes).

Foreach ($Mailbox in $EASMailboxes)
    {
    $EASDeviceStatistics = Get-ActiveSyncDeviceStatistics -Mailbox $Mailbox.Identity -WarningAction SilentlyContinue
    $MailboxStatistics = Get-Mailbox $Mailbox.Identity | Select DisplayName,PrimarySmtpAddress
    Write-Host -NoNewLine "Processing mailbox "; Write-Host -NoNewLine -ForegroundColor Green "[ $($i) / $($TotalEASMailboxes) ]"; Write-Host ", $($MailboxStatistics.DisplayName)"
    $j = 1
    $TotalEASDevices = $EASDeviceStatistics.Count
    If (!($TotalEASDevices)) { $TotalEASDevices = "1" }
    Foreach ($EASDevice in $EASDeviceStatistics)
        {
        Write-Host -NoNewLine "     Processing device [ $($j) / $($TotalEASDevices) ] ";Write-Host -NoNewLine -ForegroundColor Green "$($EASDevice.DeviceID)"; Write-Host 
        $line = New-Object PSObject
        Add-Member -InputObject $line -MemberType NoteProperty -Name "DisplayName" -Value $MailboxStatistics.DisplayName
        Add-Member -InputObject $line -MemberType NoteProperty -Name "PrimarySmtpAddress" -Value $MailboxStatistics.PrimarySmtpAddress
        Foreach ($Column in $EASColumns)
            {
            Add-Member -InputObject $line -MemberType NoteProperty -Name $Column -Value $EASDevice.$Column
            }
        Foreach ($Column in $CASColumns)
            {
            Add-Member -InputObject $line -MemberType NoteProperty -Name $Column -Value $Mailbox.$Column
            }
        Foreach ($Column in $CASArrayColumns)
            {
            $ColumnData = $Mailbox.$Column -join ";"
            Add-Member -InputObject $line -MemberType NoteProperty -Name $Column -Value $ColumnData
            }
            $Report += $line
        $j++
        }
    $i++
    }

One of the things that I discovered, though, was that I was retrieving data for mailboxes whose PrimarySmtpAddress didn't line up with the domain I had supplied in the -Domain parameter.  I was curious about this and decided to investigate--and discovered that those users had a proxyAddress that matched the domain in the filter, but had a different PrimarySmtpAddress (I'm guessing it was to cover reply-ability issues when people moved between agencies or departments).

As to why these objects got included, this would be due to the fun fact from earlier--you can't use the -match operator in the -Filter parameter, and since the PrimarySmtpAddress property of a mailbox isn't returned in Get-CASMailbox, the only one available to use is EmailAddresses.

The final step was to create a switch parameter to determine if the final output should exclude those mailboxes.  Fortunately, I can use RegEx here.  I just have to take off the pre-pended asterisk in the $Domain variable and run a -match against the PrimarySmtpAddress column of the report object.

If ($ExcludeSecondaryDomains -and $Domain)
    {
    $PrimarySmtpDomain = $Domain.Substring(1)
    $TempReport = $Report | ? { $_.PrimarySmtpAddress -match $PrimarySmtpDomain }
    $TempReport | Export-Csv -NoTypeInformation $ReportName
    }
Else
    {
    $Report | Export-Csv -NoTypeInformation $ReportName
    }

You can grab the completed report script over on the TechNet Gallery.

Comments (6)

  1. Turbomcp says:

    very interesting
    and i thought this cut my time on large environments
    https://blogs.technet.microsoft.com/exchange/2015/11/02/running-powershell-cmdlets-for-large-numbers-of-users-in-office-365/
    this is on a whole different level:)
    Thanks again

    1. The larger the tenant, the more valuable server-side filtering becomes. A get-mailbox |? client-side filter on a tenant with 100,000 mailboxes that selects data for 1,000 users may take 4 hours or longer to run. Being able to select those same 1,000 users with a server-side filter might only take 5 or 6 minutes.

  2. Turbomcp says:

    Trying to run the first snippet
    i get a missing } somewhere
    (i also added this [string]$ResultSize = "Unlimited")
    but then i get this:
    Cannot bind parameter 'Filter' to the target. Exception setting "Filter": "Invalid filter syntax. For a description of the filter parameter syntax see the command help.
    "EmailAddresses -like 'kuku.com' -and HasActiveSyncDevicePartnership -eq True" at position 76."

    1. Go grab the completed script and give it a whirl, since I just posted a few little tidbits and there are a number of params and other vars left out.

      1. Turbomcp says:

        Thanks
        i broke it down to the filter alone and then get-casmailbox(just to see where problem is)
        i had typo:)
        sorry about that.
        it is very fast, impressive
        i got 30k tenant with few different email addresses
        i filtered to only specific ones(around 2500) and got results in blistering less than 30 seconds:)
        amazing
        Thanks again, as always very interesting and eye opening techniques

        1. Great! And thanks! 🙂

          Working with large customers, I'm always trying to find the fastest and most efficient way to get data sets. There are plenty of people with much better scripting skills than mine--but I'm glad to be able to share things I've learned. I work with an amazing team of consultants and always feel like there's so much to learn.

Skip to main content