Share via

Exchange Online Active Sync Device Report Using PowerShell

  • Article

Exchange Online Active Sync Device Report Using PowerShell

Requirement

We need to know Active Sync Device Report Using in excel sheet to accomplish the below

  • Identify the device which is not in use for more than x Days
  • To send communication to users before removing the device
  • To validate the usage and to align organization security policy

Environment

  • PowerShell 4.0
  • Windows 7 64 bit Enterprise Edition
  • Exchange Online - Office 365

Challenges

  • Organization has enabled license for Shared Mailbox as well for signing in Outlook and for configuring Active SYNC. Example Alert monitoring.
  • Filtering user mailbox will end up in non compliance report
  • We can't even leverage Workflows for larger queries because it's impossible to establish session using Inline script - It failed at least for me.
  • Active Directory alignment was other challenge - We thought of querying based on office so triggering communication will be easy. But no luck - most of the information are in correct.
  • Unable to check Usage Location - Because not all users updated this in OWA
  • When end users reports issue about active sync connectivity many tried reconfiguring Active Sync.
  • Worst Case: End user has active device with different run space ID - 24 in numbers.
  • If a user tries multiple attempts to configure active sync unique ID will be generated for the same device and all shows active.

Solution

Not really an easy way of doing but we have to deliver the report. Come let's explore Get-MobileDeviceStatistics! Many have written a blog using Get-ActiveSyncDeviceStatistics - it works but displays a warning about deprecation and advice to use Get-MobileDeviceStatistics. After many attempts we planned to go step by step

  • Query all mailbox
  • Since client environment has loads of non standards for Alias and Identity - We decided to query with Primary SMTP Address as KEY
  • Avoid Filtering - Don't check LastSuccessSYNC status - The ultimate goal is to fetch devices which are not in use for x Days.
  • We need to know both active used device and old devices as well.
  • Keep a tab about users in vacation - so 30 days is not good option.
  • Queried Shared and User Mailbox - Approximately 10,000 in numbers.
  • Luckily we had few mailbox which are HiddenFromAddressListsEnabled set to true - Filtered 2000 mail box

Skeleton Structure of PowerShell Code

Import-Csv C:\Temp\ExchangeOnline\SharedMailBox.csv | %{$PrimarySmtpAddress=$_.PrimarySmtpAddress; Get-MobileDeviceStatistics -Mailbox $_.PrimarySmtpAddress | 
Select-Object @{Name="PrimarySmtpAddress";Expression={$PrimarySmtpAddress}}, DeviceType, DeviceOS, DeviceMobileOperator, LastSyncAttemptTime} | 
Export-CSV "C:\Temp\SharedMailBox-ActiveSync.csv" -Encoding utf8 -NoTypeInformation 


Import-Csv C:\Temp\ExchangeOnline\UserMailBox.csv | %{$PrimarySmtpAddress=$_.PrimarySmtpAddress; Get-MobileDeviceStatistics -Mailbox $_.PrimarySmtpAddress | 
Select-Object @{Name="PrimarySmtpAddress";Expression={$PrimarySmtpAddress}}, DeviceType, DeviceOS, DeviceMobileOperator, LastSyncAttemptTime} | 
Export-CSV "C:\Temp\ShareedMailBox-ActiveSync.csv" -Encoding utf8 -NoTypeInformation

Conclusion

  • Align Active Directory and use custom fields appropriately to avoid confusion and challenges.
  • Check Active SYNC Device report every month not once in a YEAR!