Saturday, March 01, 2008

ActiveSync IIS Log Tool / Powershell GUI Version 1

Anyone trying to administer and manage Windows Mobile Devices when active-syncing against an Exchange 2007 server will know the unique set of challenges keeping an eye on these devices presents. Because all the communication a mobile device makes is via https (well lets hope it is) all this activity is recorded in the IIS Log files. On the Exchange Team blog there are some great scripts that have been published for doing reporting on Exchange Active Sync via the logparser which works really well and is a really fast and functional method if you have large log files. http://msexchangeteam.com/archive/2006/02/14/419562.aspx .

Because some of the servers I work on tend to be smaller and greater in number I wanted a better method to do some quick reporting on ActiveSync that was quick, easy to setup and practical to do on a day to day basis. So I thought I put together a little powershell GUI to be able to pull information from an IIS log file about Active-Sync devices and then summarize and present the result in a DataGrid.

To read the log file in Powershell I’ve used Get-Content, now anyone who has tried to use Get-Content to read a large log file is probably right now going “oh”. To put it simply reading a large log file with Get-content is like watching the grass grow. If you have large log files to read unless you have a lot of time to maybe crochet a nice hat than this script isn’t for you. If your log files are under 50 mb then you should be able to get some decent performance (around a minute for a 50MB file).

The script works by presenting a nice shiny winform for you to select a log file to look at. It then uses Get-Content to read in the content from the IIS log file you select. To filter only ActiveSync data it uses the following filter Where-Object -FilterScript { $_ -ilike “*Microsoft-Server-ActiveSync*” }

It then does a summary of the information it gets back, to help you get a handle on what mobile versions and devices you have out in the field this script has a function to parse and then work out from the information in the log what version of window mobile the device is running. This is version 1 so it may not do the device conversion properly but from the rather small amount of testing I did it seemed to work okay.

The other thing it does is summarizes the sent and received Bytes from the Log file to let you see how much data your mobile device has sent and received. And also does summaries for each device to see how many ping,syncs,replys etc each device is doing. The script populates 2 ADO.NET tables there is a raw log view to let you see the raw data and a summary view which reports on all the nice summary stuff I just talked about. To do all the summaries it uses a number of nested hash tables.

Because by default Log files are in UTC (yes you can change this) the script will convert the times it reads in the log file into local time where the script is running. This behavior is configurable with a checkbox you can also view a certain number of lines in the log file and refresh it to read the latest information. Although using Get-Content is slow the advantage of using it is that it doesn’t lock the log file when it reading it (yeh!)

I’ve put a downloadable version of the script here the script itself looks like.

· Warning this script may cause Global Warming if you leave the light on in the bathroom

[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")

function openLog{
$exFileName = new-object System.Windows.Forms.openFileDialog
$exFileName.ShowDialog()
$fnFileNamelableBox.Text = $exFileName.FileName
Populatetable
}

#
function Populatetable{

$logTable.clear()
$asyncsumTable.clear()
$unHash.clear()
$lfLogFieldHash.clear()
$tcountline = -1
$lnum = 0
$rnma = 1000
if ($rbVeiwAllOnlyRadioButton.Checked -eq $true){$tcountline = $lnLogfileLineNum.value}
get-content $fnFileNamelableBox.Text | Where-Object -FilterScript { $_ -ilike “*Microsoft-Server-ActiveSync*” } | %{
$lnum ++
if ($lnum -eq $rnma){ Write-Progress -Activity "Read Lines" -Status $lnum
$rnma = $rnma + 1000
}
$linarr = $_.split(" ")
$lfDate = ""
$async = 0
$lfTime = ""
$lfSourceIP = ""
$lfMethod = ""
$lfAction = ""
$lfUserName = ""
$devid = ""
$lfPPCversion = ""
if ($linarr[0].substring(0, 1) -ne "#"){
if ($linarr.Length -gt 0){$lfDate = $linarr[0]}
if ($linarr.Length -gt 1){$lfTime = $linarr[1]}
if ($linarr.Length -gt 8){$lfSourceIP= $linarr[9]}
if ($linarr.Length -gt 3){$lfMethod = $linarr[4]}
if ($linarr.Length -gt 5){$lfAction = $linarr[6]}
if ($linarr.Length -gt 7){$lfUserName = $linarr[8]}
if ($linarr.Length -gt 10){$lfPPCversion = $linarr[11]}
if ($linarr.Length -gt 15){$bcSent = $linarr[16]}
if ($linarr.Length -gt 16){$bcRecieved = $linarr[17]}
if ($lfMethod -ne "Options"){
$cmdint = $lfAction.IndexOf("&Cmd=")+5
$aysnccmd = $lfAction.Substring($cmdint,($lfAction.IndexOfAny("&",$cmdint)-$cmdint))
}
else {$aysnccmd = "Options"}
$ldLogDatecombine = $lfDate + " " + $lfTime
if ($ltimeconv.Checked -eq $true){
$LogDate = Get-Date($ldLogDatecombine)
$LocalLogDate = $LogDate.tolocaltime()
$ldLogDatecombine = $LocalLogDate.ToString("yyyy-MM-dd HH:mm:ss")
}
$devidint = $lfAction.indexof("&DeviceId")+10
$devid = $lfAction.Substring($devidint,($lfAction.IndexOfAny("&",$devidint)-$devidint))
if ($lfLogFieldHash.ContainsKey -eq $false){$lfLogFieldHash.Add($aysnccmd,$lfLogFieldHashnum)
$lfLogFieldHashnum = $lfLogFieldHashnum + 1 }
if ($unHash.ContainsKey($devid)){
$usrHash = $unHash[$devid]
$usrHash["BytesSent"] = [Int]$usrHash["BytesSent"] + [Int]$bcSent
$usrHash["BytesRecieved"] = [Int]$usrHash["BytesRecieved"] + [Int]$bcRecieved
$usrHash["LastSeen"] = $ldLogDatecombine
$lfPPCversion = $usrHash["MobileVersion"]
if ($usrHash.ContainsKey($aysnccmd)){
$usrHash[$aysnccmd] = $usrHash[$aysnccmd] +1
}
else{
$usrHash.Add($aysnccmd,1)
}
}
else{
$lfPPCversion = getppcversion($lfPPCversion)
$usrHash = @{ }
$usrHash.Add("UserName",$lfUserName)
$usrHash.Add("BytesSent",[Int]$bcSent)
$usrHash.Add("BytesRecieved",[Int]$bcRecieved)
$usrHash.Add("MobileVersion",$lfPPCversion)
$usrHash.Add("LastSeen",$ldLogDatecombine)
$usrHash.Add($aysnccmd,1)
$unHash.Add($devid,$usrHash)
}
$logTable.Rows.Add($ldLogDatecombine,$lfSourceIP,$lfMethod,$lfAction,$aysnccmd,$devid,$lfUserName,$lfPPCversion,$bcSent,$bcRecieved)

}
}


foreach($devkey in $unHash.keys){
$devid = $devkey
$devhash = $unHash[$devkey]
$userName = $devhash["UserName"]
$MobileVersion = $devhash["MobileVersion"]
$LastSeen = $devhash["LastSeen"]
$pings = 0
if ($devhash.Contains("Ping")){
$pings = $devhash["Ping"]
}
$Sync = 0
if ($devhash.Contains("Sync")){
$Sync = $devhash["Sync"]
}
$FolderSync = 0
if ($devhash.Contains("FolderSync")){
$FolderSync = $devhash["FolderSync"]
}
$SendMail = 0
if ($devhash.Contains("SendMail")){
$SendMail = $devhash["SendMail"]
}
$SmartReply = 0
if ($devhash.Contains("SmartReply")){
$SmartReply = $devhash["SmartReply"]
}
$SmartForward = 0
if ($devhash.Contains("SmartForward")){
$SmartForward = $devhash["SmartForward"]
}
$gattach = 0
if ($devhash.Contains("GetAttachment")){
$gattach= $devhash["GetAttachment"]
}
$bsent = 0
if ($devhash.Contains("BytesSent")){
$bsent = [math]::round($devhash["BytesSent"]/1024,2)
}
$brecv = 0
if ($devhash.Contains("BytesSent")){
$brecv = [math]::round($devhash["BytesRecieved"]/1024,2)
}
$asyncsumTable.Rows.Add($devid,$userName,$pings,$Sync,$FolderSync,$SendMail,$SmartReply,$SmartForward,$gattach,$MobileVersion,$bsent,$brecv,$LastSeen)

}

if ($SsumBox.Checked -eq $true){
$dgDataGrid.DataSource = $asyncsumTable
}
else {
$dgDataGrid.DataSource = $logTable
}
write-progress "complete" "100" -completed
}

function getppcversion($ppcVersion){
$ppcret = $ppcVersion
$verarry = $ppcVersion.split("/")
$versegarry = $verarry[1].split(".")
if ($verarry[0] -eq "Microsoft-PocketPC"){
if ($versegarry[0] -eq "3"){
$ppcret = "PocketPC 2003"
}
if ($versegarry[0] -eq "2"){
$ppcret = "PocketPC 2002"
}
}
else {
if ($versegarry[0] -eq "5"){
if ($versegarry[1] -eq "1"){
if ([Int]$versegarry[2] -ge 2300) {$ppcret = "SmartPhone 2003"}
else {$ppcret = "Mobile 5"}

}
if ($versegarry[1] -eq "2"){
$ppcret = "Mobile 6"
}

}
}
if ($ppcVersion -eq "Microsoft-PocketPC/3.0") {}
return $ppcret
}


$unHash = @{ }
$lfLogFieldHash = @{ }
$lfLogFieldHashnum = 2
$form = new-object System.Windows.Forms.form
$form.Text = "ActiveSync Log Tool"
$Dataset = New-Object System.Data.DataSet
$logTable = New-Object System.Data.DataTable
$logTable.TableName = "ActiveSyncLogs"
$logTable.Columns.Add("Time");
$logTable.Columns.Add("SourceIPAddress");
$logTable.Columns.Add("Method");
$logTable.Columns.Add("Action");
$logTable.Columns.Add("ActiveSyncCommand");
$logTable.Columns.Add("DeviceID");
$logTable.Columns.Add("UserName");
$logTable.Columns.Add("PPCVersion");
$logTable.Columns.Add("Sent");
$logTable.Columns.Add("Recieved");

$asyncsumTable = New-Object System.Data.DataTable
$asyncsumTable.TableName = "ActiveSyncSummary"
$asyncsumTable.Columns.Add("DeviceID")
$asyncsumTable.Columns.Add("UserName")
$asyncsumTable.Columns.Add("Ping")
$asyncsumTable.Columns.Add("Sync")
$asyncsumTable.Columns.Add("FolderSync")
$asyncsumTable.Columns.Add("SendMail")
$asyncsumTable.Columns.Add("SmartReply")
$asyncsumTable.Columns.Add("SmartForward")
$asyncsumTable.Columns.Add("GetAttachment")
$asyncsumTable.Columns.Add("MobileVersion")
$asyncsumTable.Columns.Add("BytesSent(KB)")
$asyncsumTable.Columns.Add("BytesRecieved(KB)")
$asyncsumTable.Columns.Add("LastSeen")





# Content
$cmClickMenu = new-object System.Windows.Forms.ContextMenuStrip
$cmClickMenu.Items.add("test122")

# Add Open Log file Button

$olButton = new-object System.Windows.Forms.Button
$olButton.Location = new-object System.Drawing.Size(20,19)
$olButton.Size = new-object System.Drawing.Size(75,23)
$olButton.Text = "Select file"
$olButton.Add_Click({openLog})
$form.Controls.Add($olButton)



# Add FileName Lable
$fnFileNamelableBox = new-object System.Windows.Forms.Label
$fnFileNamelableBox.Location = new-object System.Drawing.Size(110,25)
$fnFileNamelableBox.forecolor = "MenuHighlight"
$fnFileNamelableBox.size = new-object System.Drawing.Size(200,20)
$form.Controls.Add($fnFileNamelableBox)

# Add Refresh Log file Button

$refreshButton = new-object System.Windows.Forms.Button
$refreshButton.Location = new-object System.Drawing.Size(390,29)
$refreshButton.Size = new-object System.Drawing.Size(75,23)
$refreshButton.Text = "Refresh"
$refreshButton.Add_Click({Populatetable})
$form.Controls.Add($refreshButton)

# Add Veiw RadioButtons
$rbVeiwAllRadioButton = new-object System.Windows.Forms.RadioButton
$rbVeiwAllRadioButton.Location = new-object System.Drawing.Size(20,52)
$rbVeiwAllRadioButton.size = new-object System.Drawing.Size(69,17)
$rbVeiwAllRadioButton.Checked = $true
$rbVeiwAllRadioButton.Text = "View All"
$rbVeiwAllRadioButton.Add_Click({if ($rbVeiwAllRadioButton.Checked -eq $true){$lnLogfileLineNum.Enabled = $false}})
$form.Controls.Add($rbVeiwAllRadioButton)

$rbVeiwAllOnlyRadioButton = new-object System.Windows.Forms.RadioButton
$rbVeiwAllOnlyRadioButton.Location = new-object System.Drawing.Size(110,52)
$rbVeiwAllOnlyRadioButton.size = new-object System.Drawing.Size(89,17)
$rbVeiwAllOnlyRadioButton.Text = "View Only #"
$rbVeiwAllOnlyRadioButton.Add_Click({if ($rbVeiwAllOnlyRadioButton.Checked -eq $true){$lnLogfileLineNum.Enabled = $true}})
$form.Controls.Add($rbVeiwAllOnlyRadioButton)

# Local Time Converstion CheckBox

$ltimeconv = new-object System.Windows.Forms.CheckBox
$ltimeconv.Location = new-object System.Drawing.Size(310,7)
$ltimeconv.Size = new-object System.Drawing.Size(150,20)
$ltimeconv.Checked = $true
$ltimeconv.Text = "Convert to Local Time"
$form.Controls.Add($ltimeconv)

# Show Summary CheckBox

$SsumBox = new-object System.Windows.Forms.CheckBox
$SsumBox.Location = new-object System.Drawing.Size(510,7)
$SsumBox.Size = new-object System.Drawing.Size(200,20)
$SsumBox.Checked = $true
$SsumBox.Add_Click({if ($SsumBox.Checked -eq $true){$dgDataGrid.DataSource = $asyncsumTable}
else {$dgDataGrid.DataSource = $logTable}})

$SsumBox.Text = "Show Summary"
$form.Controls.Add($SsumBox)


# Add Numeric log line number
$lnLogfileLineNum = new-object System.Windows.Forms.numericUpDown
$lnLogfileLineNum.Location = new-object System.Drawing.Size(201,52)
$lnLogfileLineNum.Size = new-object System.Drawing.Size(69,20)
$lnLogfileLineNum.Enabled = $false
$lnLogfileLineNum.Maximum = 10000000000
$form.Controls.Add($lnLogfileLineNum)

# File setting Group Box

$OfGbox = new-object System.Windows.Forms.GroupBox
$OfGbox.Location = new-object System.Drawing.Size(12,0)
$OfGbox.Size = new-object System.Drawing.Size(464,75)
$OfGbox.Text = "Log File Settings"
$form.Controls.Add($OfGbox)



# Add DataGrid View

$dgDataGrid = new-object System.windows.forms.DataGrid
$dgDataGrid.AllowSorting = $True
$dgDataGrid.Location = new-object System.Drawing.Size(12,81)
$dgDataGrid.size = new-object System.Drawing.Size(1024,750)
$form.Controls.Add($dgDataGrid)


$form.topmost = $true
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()


4 comments:

Chris said...

I cant seem to get this to work on Vista 64. It hangs when I click select file. Works fine on w2003 x64 though. Any idea why? I tend to do most of my "digging" from my workstation

Glen said...

I posted a fix for this in the download its a problem on Vista and 2008 you need to have

$exFileName.ShowHelp = $true

set before showdialouge

Cheers
Glen

Arun Mysore said...

I am trying to run this from WinXP using powershell, I get this error message, any suggestons would be appreciated !
> asynctoolv2.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details.

Thank you,
Arun.

Arun Mysore said...

I ran this command and I am all set - sorry for the post.
Set-ExecutionPolicy RemoteSigned