Friday, April 25, 2014

Reporting and Filtering Magic for ActiveSync in a Large Environment

Hi folks,

I was working on the reporting of ActiveSync devices that are used by Exchange users.

Client has provided me a script which wouldn't work crushing with the error as on this screenshot.

I was working based on this article . This perfectly works for a small environment or when Get-CASMailbox command is submitted without -ResultSize Unlimited parameter which is needed in the environment with more than 1000 mailboxes. In their production environment with more than 30K mailboxes this was a disaster and creating a variable for client mailboxes was failing with the same kind of error.

According to some of the articles in the internet this error could be fixed by tweaking web.config file for PowerShell virtual directory on Exchange servers which I didn't really want to do as I prefer not to mess with default config unless really needed and also raising change applying it across all Exchange estate wasn't really attractive to me because of long change management process (and don't forget Microsoft's recommendation that all Exchange servers should be configured in standard fashion across all the estate).

I tried using pipelining results of Get-CASMailbox -ResultSize Unlimited to Where-Object command with the filter. So my command started looking like Get-CASMailbox -ResultSize Unlimited |Where-Object {$_.HasActiveSyncDevicePartnership -eq 'True'} |Get-Mailbox
However this also didn't do magic and the evil "Remote client exceeded allowed maximum" would still appear.

To resolve it I decided to experiment a little bit. First of all it's worthy to know that Get-CASMailbox returns name for a mailbox which can be a good which I used as a value for the  Mailbox parameter of the Get-ActiveSyncDevice. I still used Get-CASMailbox -ResultSize Unlimited |Where-Object {$_.HasActiveSyncDevicePartnership -eq 'True'}. However this time I didn't pipeline it, but rather feed into the $EASUser variable which I used as an input for the Get-ActiveSyncDeviceStatistics command. And since client needed particular information about ActiveSync device, namely when it last talked to server, its type and ID and whether it can be remotely wiped.

So I ended up with the code as below (you can customize second line

$EasUser=Get-CASMailbox -ResultSize Unlimited |Where-Object {$_.HasActiveSyncDevicePartnership-eq 'True'}
$EasUser | foreach {Get-ActiveSyncDeviceStatistics -Mailbox $_.Name | select identity,LastSuccessSync,devicetype,deviceid,isremotewipesupported} |Export-Csv D:\MessagingMetricsReports\ActiveSyncUsageReport.csv

It resulted in the nice Excel Spreadsheet like this which was later sent to the happy customer. However since my production environment has more than 30K mailboxes and some users have more than a single ActiveSync device the file size was about 4 MB, so don't be scared if you get your report that big.