Friday, 25 November 2011

Reporting on Apple IOS ActiveSync Devices

UPDATE - I've posted a new, much faster, version of this script in a new post.  The original post is still below for reference.

 - Ben

Devices running Apple IOS make up the largest proportion of devices connecting to the Exchange ActiveSync service I run.  Unfortunately these devices - when running older versions of IOS - can cause problems for their users.

To combat this we regularly report on the versions of IOS users are using, and suggest that those running older versions upgrade.

There are several ways to get information about ActiveSync devices from Exchange.  One method is to use the Get-ActiveSyncDeviceStatistics cmdlet to retrieve information about all the devices, but unfortunately the device OS version this reports is the version that the device was running when it was initially set up, not the version it might be running today.  You could use the Export-ActiveSyncLog cmdlet to analyse the IIS log files from your Exchange Client Access servers, but while this will provide a list of user agent versions, it doesn't provide a way to cross reference them with user names.

To solve this problem I wrote a script which parses IIS log files, finds Apple ActiveSync devices, converts the user agent data to more meaningful IOS versions, and outputs the results to a CSV file.


# Hash table of hardware types
$HWVersions = @{
 "iPad" = "iPad";
 "iPad1C1" = "iPad";
 "iPad2C1" = "iPad 2";
 "iPad2C2" = "iPad 2";
 "iPad2C3" = "iPad 2";
 "iPad3C1" = "iPad 3";
 "iPad3C2" = "iPad 3";
 "iPad3C3" = "iPad 3"; 
 "iPhone" = "iPhone";
 "iPhone1C2" = "iPhone 3G";
 "iPhone2C1" = "iPhone 3GS";
 "iPhone3C1" = "iPhone 4";
 "iPhone3C3" = "iPhone 4";
 "iPhone4C1" = "iPhone 4S";
 "iPod" = "iPod Touch";
 "iPod2C1" = "iPod Touch 2";
 "iPod3C1" = "iPod Touch 3";
 "iPod4C1" = "iPod Touch 4";
}

# Hash table of IOS versions
$IOSVersions = @{
 "508.11" = "2.2.1";
 "701.341" = "3.0";
 "701.400" = "3.0.1";
 "702.367" = "3.2";
 "702.405" = "3.2.1";
 "702.500" = "3.2.2";
 "703.144" = "3.1";
 "704.11" = "3.1.2";
 "705.18" = "3.1.3";
 "801.293" = "4.0";
 "801.306" = "4.0.1";
 "801.400" = "4.0.2";
 "802.117" = "4.1";
 "802.118" = "4.1";
 "803.148" = "4.2.1";
 "803.14800001" = "4.2.1";
 "805.128" = "4.2.5";
 "805.200" = "4.2.6";
 "805.303" = "4.2.7";
 "805.401" = "4.2.8";
 "805.501" = "4.2.9";
 "805.600" = "4.2.10";
 "806.190" = "4.3";
 "806.191" = "4.3";
 "807.4" = "4.3.1";
 "808.7" = "4.3.2";
 "808.8" = "4.3.2";
 "810.2" = "4.3.3";
 "810.3" = "4.3.3";
 "811.2" = "4.3.4";
 "812.1" = "4.3.5";
 "901.334" = "5.0";
 "901.405" = "5.0.1"
 "901.406" = "5.0.1"
 "902.176" = "5.1"
 "902.179" = "5.1"
 "902.206" = "5.1.1"
}

# CSV Headers
$CSVHeader = "date","time","s-ip","cs-method","cs-uri-stem","cs-uri-query","s-port","cs-username","c-ip","cs(User-Agent)","sc-status","sc-substatus","sc-win32-status","time-taken"

# Logfile paths
$Logpaths  = ("\\exchange01\LogFiles\W3SVC1","\\exchange02\LogFiles\W3SVC1")

$Resultset = @()

ForEach ($Path in $Logpaths) {
 Write-Host $Path
 $Files = Get-ChildItem $Path
 $Logfile = ($Files | Sort-Object -Property LastWriteTime -Descending)[1].FullName
 Write-Host "Importing log file $Logfile"

 $Counter = 1
 Import-Csv $Logfile -Delimiter " " -Header $CSVHeader | % {
  Write-Progress -Activity "Parsing log file $Logfile" -Status "Line $Counter"
  If ($_."cs(User-Agent)" -like "Apple-*") {
   $Resultset = $Resultset + ($_."cs-username" + "," + $_."cs(User-Agent)")
  }
  $Counter ++
 }
 Write-Host "$Logfile parsed"
}
$Resultset = $Resultset | Sort-Object | Get-Unique

Write-Host "Aggregating data"
$Output = @()
ForEach ($Line in $Resultset) {
 $Object = "" | Select-Object Username,Useragent,Device,Version
 ($Object.Username,$Object.Useragent) = $Line.Split(",")
 ($null,$Object.Device,$Object.Version) = $Object.Useragent.Split("-/")
 $Object.Device = $HWVersions.Get_Item($Object.Device)
 $Object.Version = $IOSVersions.Get_Item($Object.Version)

 $Output = $Output + $Object
}

$Output | Export-Csv -Path .\Apple-Device-Versions.csv -Force -NoTypeInformation

 
Download Script

To use the script you will need to change the $Logpaths variable on line 61 to point to the UNC paths of the log files you want to analyse.

Beware that depending on the size of your logfiles the script may take a long time to run.

The resulting output file will be similar to this:
CSV File Output


The tables of devices and IOS versions will need to be kept up to date over time - the versions listed represent versions I've seen in my Exchange environment.  Another fairly well-maintained list can be found here.

- Ben

11 comments:

  1. This script is exactly what I need right now to diagnose some issues. However, I can't get it to run successfully. I am getting the error:

    Import-Csv : A parameter cannot be found that matches parameter name 'Delimiter
    '.
    At C:\iOS.ps1:72 char:32
    + Import-Csv $Logfile -Delimiter <<<< "," -Header $CSVHeader | % {

    Followed by it telling me the first log is parsed, then:

    You cannot call a method on a null-valued expression.
    At C:\iOS.ps1:87 char:52
    + ($Object.Username,$Object.Useragent) = $Line.Split( <<<< ",")
    You cannot call a method on a null-valued expression.
    At C:\iOS.ps1:88 char:66
    + ($null,$Object.Device,$Object.Version) = $Object.Useragent.Split( <<<< "-/")
    Exception calling "get_Item" with "1" argument(s): "Key cannot be null.
    Parameter name: key"
    At C:\iOS.ps1:89 char:39
    + $Object.Device = $HWVersions.Get_Item( <<<< $Object.Device)
    Exception calling "get_Item" with "1" argument(s): "Key cannot be null.
    Parameter name: key"
    At C:\iOS.ps1:90 char:41
    + $Object.Version = $IOSVersions.Get_Item( <<<< $Object.Version)

    ReplyDelete
  2. Sounds like you need to check the line with the Import-CSV command, and make sure that the -Delimeter parameter is followed by a single space enclosed in double-quotes, followed by the -Header paramter.

    The entire line should look like this:
    Import-Csv $Logfile -Delimiter " " -Header $CSVHeader | % {

    - Ben

    ReplyDelete
  3. I've added a link to download the script as a .ps1 file, which may help avoid copy/paste issues.

    - Ben

    ReplyDelete
  4. Thanks for posting this! It worked great until the end when I got all the null errors:

    Aggregating data
    You cannot call a method on a null-valued expression.
    At C:\iphoneversion.ps1:87 char:52
    + ($Object.Username,$Object.Useragent) = $Line.Split <<<< (",")
    + CategoryInfo : InvalidOperation: (Split:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\iphoneversion.ps1:88 char:66
    + ($null,$Object.Device,$Object.Version) = $Object.Useragent.Split <<<< ("-/")
    + CategoryInfo : InvalidOperation: (Split:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Exception calling "get_Item" with "1" argument(s): "Key cannot be null.
    Parameter name: key"
    At C:\iphoneversion.ps1:89 char:39
    + $Object.Device = $HWVersions.Get_Item <<<< ($Object.Device)
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    Exception calling "get_Item" with "1" argument(s): "Key cannot be null.
    Parameter name: key"
    At C:\iphoneversion.ps1:90 char:41
    + $Object.Version = $IOSVersions.Get_Item <<<< ($Object.Version)
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    ReplyDelete
  5. Hi Jason,

    Looks like the data set is empty after the log file has been parsed. One thing you can try is pasting the script lines directly into an Exchange Management Shell window. Paste everything up to the line "$Resultset = $Resultset | Sort-Object | Get-Unique", and then see if $ResultSet contains any data by typing "$ResultSet | Format-Table".

    I'll try to add some error checking to the script to give more useful errors when something like that happens.

    - Ben

    ReplyDelete
  6. Hi Ben

    I was successful to get the script working, but the script only parses the first logfile. I copied the logfiles to a local folder.
    Do the logfiles have to be on a UNC path or do you have any other idea how to get the script to parse all logfiles?

    Regards,
    Stefan

    ReplyDelete
  7. Hi Stefan,

    The script is written to parse the previous days logfile in the logs directory of each UNC path specified (in order to parse files from multiple CAS servers).

    In order to parse all the files in one (or more) paths you'd need to change the script.

    You should be able to do what you want if you replace lines 65-80 of the original script with this code, which will parse every file in the paths sepcified.

    (Sorry about the formatting - I can't use formatting tags in comments.)

    - Ben

    ForEach ($Path in $Logpaths) {
    Write-Host $Path
    $Files = Get-ChildItem $Path

    ForEach ($File in $Files) {
    $Logfile = $File.FullName
    Write-Host "Importing log file $Logfile"

    $Counter = 1
    Import-Csv $Logfile -Delimiter " " -Header $CSVHeader | % {
    Write-Progress -Activity "Parsing log file $Logfile" -Status "Line $Counter"
    If ($_."cs(User-Agent)" -like "Apple-*") {
    $Resultset = $Resultset + ($_."cs-username" + "," + $_."cs(User-Agent)")
    }
    $Counter ++
    }
    }
    Write-Host "$Logfile parsed"
    }

    ReplyDelete
  8. Updated to support IOS 5.1 and iPad 3.

    - Ben

    ReplyDelete
  9. Ben thanks for the great script I have not run it yet but will this give me only iOS devices? What if I want the iOs info just like in your script but I also want to know the other devices? Dont need the same detail I need on iOs but need it nonetheless...

    ReplyDelete
  10. Unfortunately none of the other types of devices give up as much information in the logs as Apple devices do, making it next to impossible to get the same level of information.

    There are other ways to get device information (the Get-ActiveSyncDeviceStatistics cmdlet), but they also struggle to get good information for non-Apple devices, as not all other types of devices give the server much information. Android is particularly bad / inconsistent.

    The device information stored on the server is also typically from when the ActiveSync device partnership was created, so if an OS update was done later it probably won't be reflected in the stats unless the device partnership was removed and recreated.

    - Ben

    ReplyDelete
  11. Updated for IOS 5.1.1

    - Ben

    ReplyDelete