Exporting Exchange 2010 ActiveSync statistics for iOS Devices

/ December 5, 2011

image

Update 22/10/2012: For the most up to date version of this script, see my post “Exporting iOS 6 and earlier device information”

When exporting ActiveSync statistics from your Exchange Server 2010 environment, you’ve got a number of options, including using the Export-ActiveSyncLogs cmdlet to parse IIS log files and produce a number of CSV reports to help understand the way your ActiveSync devices are being used, the use of Device troubleshooting logs to retrieve client-side logs on a user-by-user basis to help diagnose issues, and use of the Get-ActiveSyncDeviceStatistics cmdlet to interrogate the information stored by Exchange and Active Directory about each ActiveSync device partnership.

The final option is what provides the foundation for this script, however when just exporting information on it’s own the built in cmdlet doesn’t interpret the information encoded in the User Agent string that helps understand what versions of iOS are in use across your business. Therefore as well as exporting the data from Exchange, this script maps the information stored in Exchange to iOS versions.

Usage:

1
.\Export-MessageTrackingLogsForRecipient.ps1 -OutputCSVFile C:\output.csv

Example Output:

1482-09-08

Script (downloadable below)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
# .SYNOPSIS
# Generates a CSV file containing ActiveSync Device Statistics with iOS Specific Information.
#
# .PARAMETER OutputCSVFile
# Filename to save the output CSV file as
#
# .EXAMPLE
# .\Export-MessageTrackingLogsForRecipient.ps1 -OutputCSVFile C:\output.csv
param(
[parameter(Position=1,Mandatory=$true,ValueFromPipeline=$false,HelpMessage="Output CSV File Name")][string]$OutputCSVFile
)
# The following is a Hash Table containing information used to
# map the Apple Device User Agent to it's corresponding iOS version
$iOSVersions=@{"508.11"="2.2.1";
"701.341"="3.0.0";
"701.400"="3.0.1";
"702.367"="3.2";
"702.405"="3.21";
"702.5"="3.3";
"703.144"="3.1";
"704.11"="3.1.2";
"705.18"="3.1.3";
"801.293"="4.0.0";
"801.306"="4.0.1";
"801.400"="4.0.2";
"802.117"="4.1";
"803.148"="4.2.1";
"806.190"="4.3";
"806.191"="4.3";
"807.4"="4.3.1";
"808.7"="4.3.2";
"810.2"="4.3.3";
"811.2"="4.3.4";
"812.1"="4.3.5";
"901.334"="5";
"901.403"="5.0.1";
"901.405"="5.0.1"
"902.176"="5.1";
"902.179″=”5.1"}
# Retrieve mailboxes of users who have a connected ActiveSync Device
$CASMailboxes = Get-CASMailbox -Filter {hasactivesyncdevicepartnership -eq $true -and -not displayname -like "CAS_{*"} -ResultSize Unlimited;
[array]$Mailboxes = $CASMailboxes | Get-Mailbox;
# Create an array to store the output
$Output=@();
# Perform a set of actions against each mailbox retrieved
foreach ($Mailbox in $Mailboxes)
{
# Retrieve the ActiveSync Device Statistics for the associated user mailbox
[array]$ActiveSyncDeviceStatistics = Get-ActiveSyncDeviceStatistics -Mailbox $Mailbox;
# Use the information retrieved above to store information one by one about each ActiveSync Device
foreach ($Device in $ActiveSyncDeviceStatistics)
{
# Where possible use the information stored in the Device User Agent to understand the iOS device version in use
$iOSVersion = "N/A";
if ($Device.DeviceUserAgent -like "*/*") {
$rawiOSVersion = ($Device.DeviceUserAgent).Substring(($Device.DeviceUserAgent).IndexOf("/")+1);
if ($iOSVersions[$rawiOSVersion])
{
$iOSVersion = $iOSVersions[$rawiOSVersion];
}
}
# Create a new object to store this ActiveSync device information in our CSV file
$OutputItem = New-Object Object;
# Add information to the object
$OutputItem | Add-Member NoteProperty Username $Mailbox.SamAccountName;
$OutputItem | Add-Member NoteProperty "Display Name" $Mailbox.DisplayName;
$OutputItem | Add-Member NoteProperty "Device Type" $Device.DeviceType;
$OutputItem | Add-Member NoteProperty "Device Model" $Device.DeviceModel;
$OutputItem | Add-Member NoteProperty "iOS Version" $iOSVersion;
$OutputItem | Add-Member NoteProperty "Device ID" $Device.DeviceID
$OutputItem | Add-Member NoteProperty "Status" $Device.Status
$OutputItem | Add-Member NoteProperty "ActiveSync Policy" $Device.DevicePolicyApplied
$OutputItem | Add-Member NoteProperty "ActiveSync Policy Status" $Device.DevicePolicyApplicationStatus
$OutputItem | Add-Member NoteProperty "Last Sync" $Device.LastSuccessSync
$OutputItem | Add-Member NoteProperty "Last Sync Attempt" $Device.LastSyncAttemptTime
$OutputItem | Add-Member NoteProperty "Last Policy Update" $Device.LastPolicyUpdateTime
$OutputItem | Add-Member NoteProperty "First Sync" $Device.FirstSyncTime
# Add the object to our array of output objects
$Output += $OutputItem;
}
}
# Print the output object to the screen in a table format with a subset of details for ease of reading
$Output | Format-Table Username,"Device Type","Device ID","Last Sync"
# Export the full set of data to the specified CSV file
$Output | Export-CSV -Path $OutputCSVFile -NoTypeInformation

Version 1.0, 5th December 2011

Download ExportActiveSyncDeviceStatistics.zip

Related posts

15 thoughts on “Exporting Exchange 2010 ActiveSync statistics for iOS Devices”

  2. Pingback: How to Export Exchange 2010 ActiveSync statistics for IOS Devices. - Nagle Ki Duniya

  3. Pingback: Home – ExchangeServerInfo » Exchange – ActiveSync Device Reporting

  5. Hi Steve,

    Thank you for the script. I’m a complete novice to powershell and would love to export IOS devices to migrate users to an MDM solutions. When running your script in Exchange 2007 management shell on a Windows 2008 server, i receive the following script error:

    [PS] C:\TEMP>.\iphonestat.ps1 -OutputCSVFile .\iphonestat.csv Unable to find type parameter(Position=1,Mandatory=$true,ValueFromPipeline=$fa
    lse,HelpMessage=”Output CSV File Name”)]: make sure that the assembly containin
    g this type is loaded.
    At C:\TEMP\Iphonestat.ps1:10 char:104 +
    [parameter(Position=1,Mandatory=$true,ValueFromPipeline=$false,HelpMessag
    e=”Output CSV File Name”)][ <<<< string]$OutputCSVFile

    How do i correct this error?

    Thanks again
    John

  7. Cool script, where did you find the match-up values? I still get alot of N/A in my environment, even though the device is iPad/iPhone.. So I think we need more match-up values…

  9. Works well
    Of course the problem is keeping it up to date. I found two entries for iOS 5.1:
    “902.176”=”5.1″
    “902.179”=”5.1″

  10. Steve, thank you very much for script. It works great…question for you? would running this powershell script cause sync issues for users(outlook on the iphone for example) while the script was running or right after it ran? It was really weird today but right around the time i ran the script people received prompts to re-enter their info on their iphones for Microsoft outlook. Thoughts?

  11. Pingback: How to Export Exchange 2010 ActiveSync statistics for IOS Devices. « msexchangeanywhere

Comments are closed.