Monday, August 30, 2010

Reporting on deleted retained items with EWS on Exchange 2007

On Exchange servers pre Exchange 2010 when someone deletes an Item in Exchange it goes via the dumpster 1.0. Which is explained in http://msexchangeteam.com/archive/2009/09/25/452632.aspx as "essentially a view stored per folder. Items in the dumpster (henceforth known as Dumpster 1.0) stay in the folder where they were soft-deleted (shift-delete or delete from Deleted Items) and are stamped with the ptagDeletedOnFlag flag." . Being able to report on the items that are stored in these views can be useful for a number of auditing and admin reasons. With Exchange Web Services you query the items stored in these views using a SoftDeleted traversal of the folder in question. This works well for items that are stored in the dumpster of a normal mailbox folder but there is a problem when you have a hierarchy of folders that gets deleted. When this happens you can only query the first of the deleted hierarchy using a soft deleted traversal and the findfolders operation. While this is a little disappointing what you can do is still useful and worth putting to use. The following script makes use of this by going through ever folder in a mailbox and use the following properties to determine if more investigation is needed.

ptagDeletedOnFlag which details when a Item was deleted

PR_DELETED_MSG_COUNT which is the count of the delete Items with the dumpster view.

PR_DELETED_MESSAGE_SIZE_EXTENDED is the size of the Items in the dumpster view.

Using these properties when going through the folder hierarchy you can tell which folders currently have items of interest in the deleted Items view and is something that you should run a softdeleted traversal on. That's it the rest of the script uses some objects and html to email a report of the mailbox you run it against.

To use this script you need to have delegate access to the mailbox your reporting on or configure the script to use EWS Impersonation. The script has a few variables that need to be configured

$MailboxName = "mailbox@domain.com" Mailbox you want to audit

and

$sendAlertTo = "sendto@domain.com"
$sendAlertFrom = "report@domain.com"
$SMTPServer = "smtpservername"

I've put a download of this script here

$MailboxName = "mailbox@domain.com"

$sendAlertTo = "sendto@domain.com"
$sendAlertFrom = "report@domain.com"
$SMTPServer = "smtpservername"


$dllpath = "C:\Program Files\Microsoft\Exchange\Web Services\1.0\Microsoft.Exchange.WebServices.dll"
[void][Reflection.Assembly]::LoadFile($dllpath)

$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2007_SP1)

$windowsIdentity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
$sidbind = "LDAP://<SID=" + $windowsIdentity.user.Value.ToString() + ">"
$aceuser = [ADSI]$sidbind

$service.AutodiscoverUrl($aceuser.mail.ToString())


$rptCollection = @()


## Define Extended Properties

$PR_DELETED_ON = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(26255, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::SystemTime)
$PR_DELETED_MSG_COUNT = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(26176, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Integer)
$PR_DELETED_MESSAGE_SIZE_EXTENDED = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(26267, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Long)
$PR_DELETED_FOLDER_COUNT = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(26177, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Integer)
$PR_Sender_Name = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(26177, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::String)

## End Define Extended Properties
## Define Property Sets
## Folder Set

$fpsFolderPropertySet = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)
$fpsFolderPropertySet.add($PR_DELETED_ON)
$fpsFolderPropertySet.add($PR_DELETED_MSG_COUNT)
$fpsFolderPropertySet.add($PR_DELETED_MESSAGE_SIZE_EXTENDED)
$fpsFolderPropertySet.add($PR_DELETED_FOLDER_COUNT)

## Item Set

$ipsItemPropertySet = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::IdOnly)
$ipsItemPropertySet.add($PR_DELETED_ON)
$ipsItemPropertySet.Add([Microsoft.Exchange.WebServices.Data.ItemSchema]::Size)
$ipsItemPropertySet.Add([Microsoft.Exchange.WebServices.Data.ItemSchema]::Subject)
$ipsItemPropertySet.Add([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::From)
# End Set

$rfRootFolderID = new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::MsgFolderRoot,$MailboxName)
$rfRootFolder = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$rfRootFolderID)
$fvFolderView = New-Object Microsoft.Exchange.WebServices.Data.FolderView(10000);
$fvFolderView.Traversal = [Microsoft.Exchange.WebServices.Data.FolderTraversal]::Deep
$fvFolderView.PropertySet = $fpsFolderPropertySet
# $service.traceenabled = $true
$ffResponse = $rfRootFolder.FindFolders($fvFolderView);
foreach ($ffFolder in $ffResponse.Folders){
$dcDeleteItemCount = $null
$fptProptest = $ffFolder.TryGetProperty($PR_DELETED_MSG_COUNT, [ref]$dcDeleteItemCount)
if($fptProptest){
if ($dcDeleteItemCount -ne 0){
$ffFolder.DisplayName + " - Number Items Deleted :" + $dcDeleteItemCount
$bcBatchCount = 0;
$bcBatchSize = 1000
$ivItemView = new-object Microsoft.Exchange.WebServices.Data.ItemView($bcBatchSize, $bcBatchCount)
$ivItemView.Traversal = [Microsoft.Exchange.WebServices.Data.ItemTraversal]::SoftDeleted
$ivItemView.PropertySet = $ipsItemPropertySet
$service.traceenabled = $false
while (($fiFindItems = $ffFolder.FindItems($ivItemView)).Items.Count -gt 0)
{
foreach ($item in $fiFindItems.Items)
{
$lnum ++
write-progress "Processing message" $lnum
$delon = $null
$ptProptest = $item.TryGetProperty($PR_DELETED_ON, [ref]$delon)
$Itemobj = "" | select Type,DeletedOn,From,Subject,Size
$Itemobj.DeletedOn = $delon
$Itemobj.From = $item.From.Name
$Itemobj.Subject = $item.Subject
$Itemobj.Size = $item.Size
$Itemobj.Type = "Item"
$rptCollection += $Itemobj
}
$bcBatchCount += $fiFindItems.Items.Count
$ivItemView = new-object Microsoft.Exchange.WebServices.Data.ItemView($bcBatchSize, $bcBatchCount)
$ivItemView.Traversal = [Microsoft.Exchange.WebServices.Data.ItemTraversal]::SoftDeleted
$ivItemView.PropertySet = $ipsItemPropertySet
}
}
}
$dcDeletedFolderCount = $null
$fptProptest = $ffFolder.TryGetProperty($PR_DELETED_FOLDER_COUNT, [ref]$dcDeletedFolderCount)
if($fptProptest){
if ($dcDeletedFolderCount -ne 0){
$ffFolder.DisplayName + " - Number folders Deleted :" + $dcDeletedFolderCount
$fvFolderView1 = New-Object Microsoft.Exchange.WebServices.Data.FolderView(10000);
$fvFolderView1.Traversal = [Microsoft.Exchange.WebServices.Data.FolderTraversal]::SoftDeleted
$fvFolderView1.PropertySet = $fpsFolderPropertySet
$ffResponse2 = $ffFolder.FindFolders($fvFolderView1)

foreach ($ffDelFolder in $ffResponse2.Folders){
$dcDeletedSize = $null
$fptProptest = $ffDelFolder.TryGetProperty($PR_DELETED_MESSAGE_SIZE_EXTENDED, [ref]$dcDeletedSize)
$Deletedon = $null
$ptProptest = $ffDelFolder.TryGetProperty($PR_DELETED_ON, [ref]$Deletedon)
$Itemobj = "" | select Type,DeletedOn,From,Subject,Size
$Itemobj.DeletedOn = $Deletedon
$Itemobj.Subject = $ffDelFolder.DisplayName
$Itemobj.Size = $dcDeletedSize
$Itemobj.Type = "Folder"
$rptCollection += $Itemobj

}
}
}

}

$tableStyle = @"
<style>
BODY{background-color:white;}
TABLE{border-width: 1px;
border-style: solid;
border-color: black;
border-collapse: collapse;
}
TH{border-width: 1px;
padding: 10px;
border-style: solid;
border-color: black;
background-color:#66CCCC
}
TD{border-width: 1px;
padding: 2px;
border-style: solid;
border-color: black;
background-color:white
}
</style>
"@

$body = @"
<p style="font-size:25px;family:calibri;color:#ff9100">
$TableHeader
</p>
"@



$SmtpClient = new-object system.net.mail.smtpClient
$SmtpClient.host = $SMTPServer
$MailMessage = new-object System.Net.Mail.MailMessage
$MailMessage.To.Add($sendAlertTo)
$MailMessage.From = $sendAlertFrom
$MailMessage.Subject = "Dumpster Report for " + $MailboxName
$MailMessage.IsBodyHtml = $TRUE
$MailMessage.body = $rptCollection | ConvertTo-HTML -head $tableStyle –body $body
$SMTPClient.Send($MailMessage)

Friday, August 20, 2010

Anaylising the content of a PST file and reporting on the age and type of content using Powershell and WPF

As time passes we all receive more and more email, this is one of the irrefutable facts of life for any mail system or anybody with a mailbox. Even the most fastidious of deleters still can't avoid this so at some point in the future you may need to consider achieving. The fact is your users maybe already self archiving using PST files which like self medicating can lead to serious problems down the track when their laptop goes under a bus. Exchange 2010 introduced native achieving and SP1 when its released will build on these capabilities, for other versions of exchange there are a bunch of other fine products you can look at to do this. If your looking to ingest PST's into an online archive you may want to first review what content your going to be importing and look at things like how many attachments and what type are they and how old is the content to give you a feel for what your going to be storing and potentially having to backup.

What this boils down to is if you want to include attachment reporting then you will need a script that will do a pass on every item within a mailbox. This means in real terms that this script is going to be slow to very slow to run on a very large PST file. If you can wait you can get some useful information so there is a trade off their somewhere.

To loop through every message within a PST file using powershell is pretty easy if you use Dmitry's redemption library http://www.dimastr.com/redemption/. RDO gives us an easy to use wrapper around exMapi and the Outlooks PST provider. So now we have the ability to go through every item in a pst file the next thing to decide is how you want to group and classify each item. I've chosen to do this by date and content age this gives the most flexibility when it comes to aggregating the data later on. To add extra functionality in this script I've created separate hash's for attachment sizes and types this is something that you can build on yourself all you need to do is think about what it is you want to report and aggregate and then just add in a few lines of your own code.

When it comes time to report on the data that was collected in the PST sweep this is when Powershell comes into its own with the Group-Object and Measure-Object cmdlets. Because during the sweep i classified each message group depending on the age of the content i can then further re-aggregate this data using some pipeline magic eg
$Datehash.Values | group-object {$_.Folder} | Sort-Object @{expression={(($_.Group | Measure-Object SizeofItems -sum).sum/1MB)}} -Descending | foreach-object{

$Charthash2["1 to 3 years"] = $Charthash2["1 to 3 years"] + ($_.Group | Where-Object {$_.ContentAge -eq 36} | Measure-Object SizeofItems -sum).sum/1MB

}
Actually explaining what this line does would take a separate post but when it comes to quantifying numerical information without using a database this is extremely useful. In real words it first groups the data by Folder Name and then allows me to aggregate within the grouped data.

Displaying the result

I decided to try something new with this script instead of using the normal winform GUI I've used a lot in the past i went down the WPF path. WPF (Windows Presentation Framework) first appeared in .NET 3.0 so to use this script you must have the .NET 3.5 framework installed as well as the wpftoolkit which contains both datagrid and chart control I've used in this script. As far as a comparison between Winforms and WPF from powershell WPF is by far easier to use when building a GUI script because you define all the element in XML then just manipulate the data providers with code. You can use something like Visual studio to build the graphical look for your GUI and then cut and past the code more or less straight into your script with a few small changes. The result of what you get is also visually more pleasing eg these are a few of the screen-shots from this script.



Using this script

There are a few pre-requisites for using this script first you need redemption http://www.dimastr.com/redemption/download.htm

If you want to run this on Windows 7 64bit I found i had to use the following launcher script to start a 32bit session to run the script

&$env:windir\syswow64\windowspowershell\v1.0\powershell.exe -noninteractive -STA

This also ensures that you have a STA session of powershell which is important for the WPF code to work correctly. The last things that's required is the WPFtoolkit which you can downloaded from http://wpf.codeplex.com/releases/view/40535. Note if this gets installed to anywhere other then C:\Program Files (x86)\WPF Toolkit" + "\v3.5.50211.1\ you will need to change the path.

To run the script you need to pass it the path to the PST file as a cmdline argument eg ./pstanlv1.ps1 "c:\mail\outlook.pst"

I've put a download of this script here the code itself looks like

$fnFileName = $args[0]

$Datehash = new-object "System.Collections.Generic.Dictionary[System.string, System.object]"
$AttachmentTypehash = @{ }
$ItemTypehash = @{ }
Add-Type -Assembly PresentationFramework
$dataVisualization = "C:\Program Files (x86)\WPF Toolkit" + "\v3.5.50211.1\System.Windows.Controls.DataVisualization.Toolkit.dll"
$wpfToolkit = "C:\Program Files (x86)\WPF Toolkit" + "\v3.5.50211.1\WPFToolkit.dll"
Add-Type -Path $dataVisualization
Add-Type -Path $wpfToolkit

[xml]$xaml = @"
<Window
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:dg="http://schemas.microsoft.com/wpf/2008/toolkit"
xmlns:chartingToolkit="clr-namespace:System.Windows.Controls.DataVisualization.Charting;assembly=System.Windows.Controls.DataVisualization.Toolkit"
Title="MainWindow" Height="auto" Width="auto">
<Grid>
<TabControl Height="auto" HorizontalAlignment="Stretch" VerticalAlignment="Stretch" Margin="0,0,0,0" Name="Home" Width="auto">
<TabItem Header="OverView" Name="OverView">
<Grid>
<Canvas Height="Auto" HorizontalAlignment="Left" Margin="0,0,0,0" Name="canvas1" VerticalAlignment="Top" Width="Auto"></Canvas>
<dg:DataGrid AutoGenerateColumns="True" Height="Auto" HorizontalAlignment="Left" Margin="0,0,0,0" Name="dataGrid1" VerticalAlignment="Top" Width="600" />
<chartingToolkit:Chart x:Name="PieChart1" Margin="600,0,0,250">
<chartingToolkit:Chart.Series>
<chartingToolkit:PieSeries ItemsSource="{Binding}"
DependentValuePath="Value"
IndependentValuePath="Key" />
</chartingToolkit:Chart.Series>
</chartingToolkit:Chart>
<chartingToolkit:Chart x:Name="PieChart2" Margin="600,250,0,00">
<chartingToolkit:Chart.Series>
<chartingToolkit:PieSeries ItemsSource="{Binding}"
DependentValuePath="Value.SizeofAttachments"
IndependentValuePath="Key" />
</chartingToolkit:Chart.Series>
</chartingToolkit:Chart>
</Grid>
</TabItem>
<TabItem Header="Content Age" Name="Cage">
<Grid>
<Canvas Height="Auto" HorizontalAlignment="Left" Margin="0,0,0,0" Name="canvas2" VerticalAlignment="Top" Width="Auto"></Canvas>
<DataGrid AutoGenerateColumns="True" Height="Auto" HorizontalAlignment="Left" Margin="0,250,0,0" Name="dataGrid2" VerticalAlignment="Top" Width="auto" />
<chartingToolkit:Chart x:Name="PieChart3" Margin="0,0,0,250">
<chartingToolkit:Chart.Series>
<chartingToolkit:PieSeries ItemsSource="{Binding}"
DependentValuePath="Value"
IndependentValuePath="Key" />
</chartingToolkit:Chart.Series>
</chartingToolkit:Chart>
</Grid>
</TabItem>
<TabItem Header="Item Type" Name="Itype">
<Grid>
<Canvas Height="Auto" HorizontalAlignment="Left" Margin="0,0,0,0" Name="canvas3" VerticalAlignment="Top" Width="Auto"></Canvas>
<dg:DataGrid AutoGenerateColumns="True" Height="Auto" HorizontalAlignment="Left" Margin="0,0,0,0" Name="dataGrid3" VerticalAlignment="Top" Width="400" />
<chartingToolkit:Chart x:Name="PieChart4" Margin="400,0,0,250" Title="Item type by Item Count">
<chartingToolkit:Chart.Series>
<chartingToolkit:PieSeries ItemsSource="{Binding}"
DependentValuePath="Value.NumberofItems"
IndependentValuePath="Key" />
</chartingToolkit:Chart.Series>
</chartingToolkit:Chart>
<chartingToolkit:Chart x:Name="PieChart5" Margin="400,250,0,00" Title="Item type by Item Size">
<chartingToolkit:Chart.Series>
<chartingToolkit:PieSeries ItemsSource="{Binding}"
DependentValuePath="Value.SizeofItems"
IndependentValuePath="Key" />
</chartingToolkit:Chart.Series>
</chartingToolkit:Chart>
</Grid>
</TabItem>
</TabControl>
</Grid>
</Window>
"@


Function Enumfolders($cnCurrentFolder){
foreach($folder in $cnCurrentFolder.Folders){
"Processing : " + $folder.Name
ProcessItems($folder)
If($folder.Folders.Count -ne 0){Enumfolders($folder)}
}
}

Function ProcessItems($wfWorkingFolder){
$lnum = 0
foreach($Item in $wfWorkingFolder.Items){
$lnum ++
write-progress "Processing message" $lnum
if ($ItemTypehash.ContainsKey($Item.MessageClass)){
$ItemTypehash[$Item.MessageClass].NumberofItems = $ItemTypehash[$Item.MessageClass].NumberofItems + 1
$ItemTypehash[$Item.MessageClass].SizeofItems = $ItemTypehash[$Item.MessageClass].SizeofItems + $Item.Size
}
else{
$iaItemAgobject = "" | select NumberofItems,SizeofItems
$iaItemAgobject.NumberofItems = 1
$iaItemAgobject.SizeofItems = $Item.Size
$ItemTypehash.add($Item.MessageClass,$iaItemAgobject)
}
$ItemAttachedNumber = 0
$ItemAttachedSize = 0
if ($Item.Attachments.Count -ne 0){
foreach($attachment in $Item.Attachments){
$ItemAttachedNumber = $ItemAttachedNumber +1
$ItemAttachedSize = $ItemAttachedSize + $attachment.Size
if ($Attachment.FileName -eq $null){
$attachext = "Embeeded"
}
else{
if ($Attachment.FileName.Substring($Attachment.FileName.Length-4,1) -eq ".")
{
$attachext = $Attachment.FileName.Substring($Attachment.FileName.Length-3,3)
}
else {
if ($Attachment.FileName.Substring($Attachment.FileName.Length-5,1) -eq "."){
$attachext = $Attachment.FileName.Substring($Attachment.FileName.Length-4,4)
}
else{
$attachext = "unkonwn"
}
}
}
if ($AttachmentTypehash.ContainsKey($attachext)){
$AttachmentTypehash[$attachext].NumberofAttachments = $AttachmentTypehash[$attachext].NumberofAttachments + 1
$AttachmentTypehash[$attachext].SizeofAttachments = $AttachmentTypehash[$attachext].SizeofAttachments + $Attachment.Size
}
else{
$iaAttachmentAgobject = "" | select NumberofAttachments,SizeofAttachments
$iaAttachmentAgobject.NumberofAttachments = 1
$iaAttachmentAgobject.SizeofAttachments = $Attachment.Size
$AttachmentTypehash.add($attachext,$iaAttachmentAgobject)
}
}
}
$caContentAge = New-TimeSpan $Item.ReceivedTime $(Get-Date)
if($caContentAge.days -le 183){$ca = 6}
if($caContentAge.days -gt 183 -band $caContentAge.days -le 365){$ca = 12}
if($caContentAge.days -gt 365 -band $caContentAge.days -le 1095){$ca = 36}
if($caContentAge.days -gt 1095 -band $caContentAge.days -le 1825){$ca = 60}
if($caContentAge.days -gt 1825){$ca = 100}
$agkey = $Item.ReceivedTime.ToString("yyyyMMdd") + "-" + $wfWorkingFolder.Name
if ($Datehash.ContainsKey($agkey)){
$Datehash[$agkey].NumberofItems = $Datehash[$agkey].NumberofItems + 1
$Datehash[$agkey].SizeofItems = $Datehash[$agkey].SizeofItems + $Item.Size
$Datehash[$agkey].NumberofAttachments = $Datehash[$agkey].NumberofAttachments + $ItemAttachedNumber
$Datehash[$agkey].AttachmentSize = $Datehash[$agkey].AttachmentSize + $ItemAttachedSize
}
Else{
$daDateAgregationobject = "" | select Date,Folder,ContentAge,NumberofItems,SizeofItems,NumberofAttachments,AttachmentSize
$daDateAgregationobject.Date = $Item.ReceivedTime.ToString("yyyyMMdd")
$daDateAgregationobject.Folder = $wfWorkingFolder.Name
$daDateAgregationobject.ContentAge = $ca
$daDateAgregationobject.NumberofItems = 1
$daDateAgregationobject.SizeofItems = $Item.Size
$daDateAgregationobject.NumberofAttachments = $ItemAttachedNumber
$daDateAgregationobject.AttachmentSize = $ItemAttachedSize
$Datehash.add($agkey,$daDateAgregationobject)
}
}
}

$RDOSession = new-object -com Redemption.RDOsession

$PSTfile = $RDOSession.LogonPSTStore($fnFileName, 1)
$PSTRoot = $RDOSession.GetFolderFromID($PSTfile.IPMRootFolder.EntryID, $PSTfile.EntryID)
Enumfolders($PSTRoot)

$byDateTable = New-Object System.Data.Datatable
$byDateTable.columns.add("Folder")
$byDateTable.columns.add("#Items",[INT64])
$byDateTable.columns.add("Items Size(MB)",[INT64])
$byDateTable.columns.add("#Attachments",[INT64])
$byDateTable.columns.add("Attachments Size(MB)",[INT64])
$byAgeTable = New-Object System.Data.Datatable
$byAgeTable.columns.add("Folder")
$byAgeTable.columns.add("6>#Items",[INT64])
$byAgeTable.columns.add("6>#(MB)",[INT64])
$byAgeTable.columns.add("6>#Atch",[INT64])
$byAgeTable.columns.add("6>#Atch(MB)",[INT64])
$byAgeTable.columns.add("6to12#Items",[INT64])
$byAgeTable.columns.add("6to12#(MB)",[INT64])
$byAgeTable.columns.add("6to12#Atch",[INT64])
$byAgeTable.columns.add("6to12#Atch(MB)",[INT64])
$byAgeTable.columns.add("1to3years#Items",[INT64])
$byAgeTable.columns.add("1to3years#(MB)",[INT64])
$byAgeTable.columns.add("1to3years#Atch",[INT64])
$byAgeTable.columns.add("1to3years#Atch(MB)",[INT64])
$byAgeTable.columns.add("3to5years#Items",[INT64])
$byAgeTable.columns.add("3to5years#(MB)",[INT64])
$byAgeTable.columns.add("3to5years#Atch",[INT64])
$byAgeTable.columns.add("3to5years#Atch(MB)",[INT64])
$byAgeTable.columns.add("5+years#Items",[INT64])
$byAgeTable.columns.add("5+years#(MB)",[INT64])
$byAgeTable.columns.add("5+years#Atch",[INT64])
$byAgeTable.columns.add("5+years#Atch(MB)",[INT64])
$Charthash = @{ }
$cCount1 = 0
$Datehash.Values | group-object {$_.Folder} | Sort-Object @{expression={(($_.Group | Measure-Object SizeofItems -sum).sum/1MB)}} -Descending | foreach-object{
if ((($_.Group | Measure-Object SizeofItems -sum).sum/1MB) -gt 1 -band $cCount1 -le 10){
if ($_.Name.Length -gt 10){$chartname = $_.Name.Substring(0,10)}
else{$chartname = $_.Name}
$Charthash.add($chartname,(($_.Group | Measure-Object SizeofItems -sum).sum/1MB))
}
$cCount1++
[VOID]$byDateTable.rows.add($_.Name,($_.Group | Measure-Object NumberofItems -sum).sum/1,(($_.Group | Measure-Object SizeofItems -sum).sum/1MB),($_.Group | Measure-Object NumberofAttachments -sum).sum/1,(($_.Group | Measure-Object AttachmentSize -sum).sum/1MB))
}
$Charthash2 = @{ }
$Charthash2.Add("Under 6 Months",0)
$Charthash2.Add("6 to 12 Months",0)
$Charthash2.Add("1 to 3 years",0)
$Charthash2.Add("3 to 5 years",0)
$Charthash2.Add("Over 5 years",0)
$Datehash.Values | group-object {$_.Folder} | Sort-Object @{expression={(($_.Group | Measure-Object SizeofItems -sum).sum/1MB)}} -Descending | foreach-object{
$Charthash2["Under 6 Months"] = $Charthash2["Under 6 Months"] + ($_.Group | Where-Object {$_.ContentAge -eq 6} | Measure-Object SizeofItems -sum).sum/1MB
$Charthash2["6 to 12 Months"] = $Charthash2["6 to 12 Months"] + ($_.Group | Where-Object {$_.ContentAge -eq 12} | Measure-Object SizeofItems -sum).sum/1MB
$Charthash2["1 to 3 years"] = $Charthash2["1 to 3 years"] + ($_.Group | Where-Object {$_.ContentAge -eq 36} | Measure-Object SizeofItems -sum).sum/1MB
$Charthash2["3 to 5 years"] = $Charthash2["3 to 5 years"] + ($_.Group | Where-Object {$_.ContentAge -eq 60} | Measure-Object SizeofItems -sum).sum/1MB
$Charthash2["Over 5 years"] = $Charthash2["Over 5 years"] + ($_.Group | Where-Object {$_.ContentAge -eq 100} | Measure-Object SizeofItems -sum).sum/1MB
[VOID]$byAgeTable.rows.add($_.Name,($_.Group | Where-Object {$_.ContentAge -eq 6} | Measure-Object NumberofItems -sum).sum/1,(($_.Group | Where-Object {$_.ContentAge -eq 6} | Measure-Object SizeofItems -sum).sum/1MB),($_.Group | Where-Object {$_.ContentAge -eq 6} | Measure-Object NumberofAttachments -sum).sum/1,(($_.Group | Where-Object {$_.ContentAge -eq 6} | Measure-Object AttachmentSize -sum).sum/1MB),($_.Group | Where-Object {$_.ContentAge -eq 12} | Measure-Object NumberofItems -sum).sum/1,(($_.Group | Where-Object {$_.ContentAge -eq 12} | Measure-Object SizeofItems -sum).sum/1MB),($_.Group | Where-Object {$_.ContentAge -eq 12} | Measure-Object NumberofAttachments -sum).sum/1,(($_.Group | Where-Object {$_.ContentAge -eq 12} | Measure-Object AttachmentSize -sum).sum/1MB),($_.Group | Where-Object {$_.ContentAge -eq 36} | Measure-Object NumberofItems -sum).sum/1,(($_.Group | Where-Object {$_.ContentAge -eq 36} | Measure-Object SizeofItems -sum).sum/1MB),($_.Group | Where-Object {$_.ContentAge -eq 36} | Measure-Object NumberofAttachments -sum).sum/1,(($_.Group | Where-Object {$_.ContentAge -eq 36} | Measure-Object AttachmentSize -sum).sum/1MB),($_.Group | Where-Object {$_.ContentAge -eq 60} | Measure-Object NumberofItems -sum).sum/1,(($_.Group | Where-Object {$_.ContentAge -eq 60} | Measure-Object SizeofItems -sum).sum/1MB),($_.Group | Where-Object {$_.ContentAge -eq 60} | Measure-Object NumberofAttachments -sum).sum/1,(($_.Group | Where-Object {$_.ContentAge -eq 60} | Measure-Object AttachmentSize -sum).sum/1MB),($_.Group | Where-Object {$_.ContentAge -eq 100} | Measure-Object NumberofItems -sum).sum/1,(($_.Group | Where-Object {$_.ContentAge -eq 100} | Measure-Object SizeofItems -sum).sum/1MB),($_.Group | Where-Object {$_.ContentAge -eq 100} | Measure-Object NumberofAttachments -sum).sum/1,(($_.Group | Where-Object {$_.ContentAge -eq 100} | Measure-Object AttachmentSize -sum).sum/1MB))
}
$XMLreader = New-Object System.Xml.XmlNodeReader $xaml
$XAMLreader = [Windows.Markup.XamlReader]::Load($XMLreader)
$tc = $XAMLreader.FindName("PieChart1")
$tc.DataContext = $Charthash
$tc = $XAMLreader.FindName("PieChart2")
$tc.DataContext = ($AttachmentTypehash.GetEnumerator() | Sort-Object Value.SizeofAttachments | select-object -First 10)
$tc = $XAMLreader.FindName("PieChart3")
$tc.DataContext = $Charthash2
$tc = $XAMLreader.FindName("PieChart4")
$tc.DataContext = ($ItemTypehash.GetEnumerator() | Sort-Object Value.SizeofItems | select-object -First 10)
$tc = $XAMLreader.FindName("PieChart5")
$tc.DataContext = ($ItemTypehash.GetEnumerator() | Sort-Object Value.SizeofItems | select-object -First 10)
$datagrid = $XAMLreader.FindName("dataGrid1")
$datagrid.ItemsSource = $byDateTable.defaultview
$datagrid2 = $XAMLreader.FindName("dataGrid2")
$datagrid2.ItemsSource = $byAgeTable.defaultview

$byItemTable = New-Object System.Data.Datatable
$byItemTable.columns.add("ItemType")
$byItemTable.columns.add("#Items",[INT64])
$byItemTable.columns.add("Items Size(MB)",[INT64])
$ItemTypehash.GetEnumerator() | foreach-object {
[VOID]$byItemTable.rows.Add($_.key.ToString(),$_.value.NumberofItems,$_.value.SizeofItems)
$_.key.ToString()
}

$datagrid3 = $XAMLreader.FindName("dataGrid3")
$datagrid3.ItemsSource = $byItemTable.defaultview
$XAMLreader.ShowDialog()

Friday, August 13, 2010

Using Exchange Search and AQS with EWS on Exchange 2010

One of the great new features of Exchange Web Services on Exchange 2010 is the ability to use AQS (Advanced Query Syntax) when querying a mailbox folder. The reason this is helpful is that it provides an easily assessable entrypoint into the Exchange Search Service. The Exchange Search Service has been constantly improving with each new version of Exchange when you consider the size and Item counts of the modern mailbox this has now become a vital component of Exchange and something that those using EWS should consider taking advantage of. For the basics behind the difference between a Store Search and one use that utilizes the Exchange Search Service the Exchange Team blog has a great three part post on the subject.

The main points of interest are that index searches are considerable quicker and can be used to do things such as look at the content of an attachment which a normal store search can not do. Of course there are also a number of things a store search can do that a index search can't because a index search is always limited to searching the properties it indexes.

Using AQS also allows you the opportunity to have more flexible functions and methods when compared with creating search filters in EWS. For example most search filters are hard-coded for a particular property or you create a collection of search filters to apply additional logic. An Example of this would be

SearchFilter sf1 = new SearchFilter.IsEqualTo(ItemSchema.Subject, "Blah");
SearchFilter sf2 = new SearchFilter.IsGreaterThan(ItemSchema.DateTimeReceived, DateTime.Now.AddDays(-1));
SearchFilter sfcol = new SearchFilter.SearchFilterCollection(LogicalOperator.And, sf1, sf2);

While the equivalent AQS query that can be used is

FindItemsResults fiItems = service.FindItems(QueryFolder, "Received:yesterday AND subject:\"blah\"", iv);

Working Out the AQS Syntax

This is perhaps the hardest thing when it comes to using AQS the documentation can be a little confusing and sometimes doesn't show everything you need to know the two articles i would have a look at is firstly

http://msdn.microsoft.com/en-us/library/ee693615.aspx
http://technet.microsoft.com/en-us/library/bb232132.aspx

and

http://msdn.microsoft.com/en-us/library/bb266512(VS.85).aspx

The detail provided in the later is of importance because it provides good detail on what the conditional logic is that can be used with AQS when you need to constructed more advanced searches. For instance if you had to search all messages where a particular name is in the body but where only give part of the first or surname of the person. The COP_WORD_STARTSWITH help do this so you could find say any matches on "Microsoft Exchange" using "body:$<\"Micro Exc\"" this will find any a word starting with micro, followed by a word starting with exc.

Is this useful for SysAdmin's ?

ECP is a exceedingly powerful tool and if you where doing mailbox discoveries then this is what you should be using. However every problem is unique and having this ability availability at the command-line to do this can be very advantages. Using the EWS Managed API allows you to do this in Powershell relatively easily. Eg the following script will take the input of the Mailbox you want it to run against (as primary email address) and AQS query you want to use to search with an return the results to the console

$MailboxName = $args[0]

$dllpath = "C:\Program Files\Microsoft\Exchange\Web Services\1.0\Microsoft.Exchange.WebServices.dll"
[void][Reflection.Assembly]::LoadFile($dllpath)

$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2007_SP1)

$windowsIdentity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
$sidbind = "LDAP://"
$aceuser = [ADSI]$sidbind

$service.AutodiscoverUrl($aceuser.mail.ToString())


$folderid = new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox,$MailboxName)

$iv = new-object Microsoft.Exchange.WebServices.Data.ItemView(2000)
$fiItems = $service.FindItems($folderid, $args[1], $iv)
foreach ($Item in $fiItems.Items)
{
$Item.Subject
}

With this you can then make queries like

./aqssearch.ps1 gscales@mailbox.com "sent:today"

./aqssearch.ps1 gscales@mailbox.com "sent:thismonth"

find attachment content with

./aqssearch.ps1 gscales@mailbox.com "attachment:Good Movies"

There are a large number of problems you can solve relatively easily just by learning some simple AQS syntax so for both the Admin and developer if your using Exchange 2010 this is something to examine and start using.

I've put a download of the script here.

Thursday, August 12, 2010

Parsing IIS Log ActiveSync Traffic for Information about Iphone IOS Versions Exchange 2003,2007,2010

Last month i posted a script for getting the Iphone IOS version information for all users on a server using the Get-ActiveSyncDeviceStatistics cmdlet which worked okay but had one major flaw in that it doesn't pick up the updates to the O/S as pointed out in http://www.hedonists.ca/2010/07/22/blocking-the-iphone-part-i. So the only 100% way would be to parse the IIS logs where you can grab the same information about the devices as long as the cs-useragent is being include in the logs (if not you need to change this). This script is still useful for somethings and hopefully i should be able to re-purpose this to do something useful.

There are good and bad things about parsing logs, the good is that this technique should work on any version of Exchange including 2003 the bad is that if your log files are large then there are much better ways then using Powershell to parse them (such as logparser). But using PS does have a number of advantages and if your whiling to wait while the script runs then this one if for you. As i don't have a lot of access to Production Exchange servers at the moment the testing this script has had is very little but seems to work okay on a few old logs i had lying around if you have any problems please let me know. The base of this parser if from one of my other post which is a pretty reliable parser I've used all the time. Its been adapted to only look at specific ActiveSync traffic which should contain the useragent information. The rest of the script is from my first ActiveSync script along with a little custom object code to get all the information compiled into a report. This script also reports on Non-Iphone as well (although doesn't give you the IOS details for these).

I've included two different versions in the download the first script offers a little file selection gui to let you browse and select the log file your want this to run it against the second can be run unattended and would look for last file modified in the last 6 hours in the log directory you configured it to look in eg

get-childitem c:\inetpub\logs\logfiles\w3svc1\*.* | where-object{$_.LastWriteTime -gt (get-date).addhours(-6)} | foreach-object{
$fname = $_.FullName
}

* Note it will only run on one log file in the above example if you want to batch process a number of files then the rest of the code needs to be functionized.

I've put a download of this code here the code itself looks like.

[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
$exFileName = new-object System.Windows.Forms.openFileDialog
$exFileName.ShowHelp = $true
$exFileName.ShowDialog()

$sendAlertTo = "mailbox@example.com"
$sendAlertFrom = "mailbox@example.com"
$SMTPServer = "smtp.example.com"

$appleCollection = @()
$appleverhash = @{ }
$hndSethash = @{ }

function addtoIOShash($inputvar){
$inparray = $inputvar.Split(',')
$v1 = $inparray[1].Substring(0,1)
$v2 = $inparray[1].Substring(1,1)
$v3 = $inparray[1].Substring(2,($inparray[1].Length-2))
$useragent = "{0:D2}" -f (([int][char]$v2)-64)
$apverobj = "" | select IOSVersion,IOSReleaseDate,ActiveSyncUserAgent,AppleBuildCode
$apverobj.IOSVersion = $inparray[0]
$apverobj.IOSReleaseDate = $inparray[2]
$apverobj.AppleBuildCode = $inparray[1]
$apverobj.ActiveSyncUserAgent = $v1 + $useragent + "." + $v3
$appleverhash.add($apverobj.ActiveSyncUserAgent,$apverobj)

}



#IOSVersion,Deviceid,ReleaseDate
addtoIOShash("1,1A543,Jun-07")
addtoIOShash("1.0.1,1C25,Jul-07")
addtoIOShash("1.0.2,1C28,Aug-07")
addtoIOShash("1.1,3A100,Sep-07")
addtoIOShash("1.1,3A101,Sep-07")
addtoIOShash("1.1.1,3A109,Sep-07")
addtoIOShash("1.1.1,3A110,Sep-07")
addtoIOShash("1.1.2 ,3B48,Nov-07")
addtoIOShash("1.1.3,4A93,Jan-08")
addtoIOShash("1.1.4,4A102,Feb-08")
addtoIOShash("1.1.5,4B1,Jul-08")
addtoIOShash("2,5A347,Jul-08")
addtoIOShash("2.0.1,5B108,Aug-08")
addtoIOShash("2.0.2,5C1,Aug-08")
addtoIOShash("2.1,5F136,Sep-08")
addtoIOShash("2.1,5F137,Sep-08")
addtoIOShash("2.1,5F138,Sep-08")
addtoIOShash("2.2,5G77,Nov-08")
addtoIOShash("2.2.1,5H11,Jan-09")
addtoIOShash("3,7A341,Jun-09")
addtoIOShash("3.0.1,7A400,Jul-09")
addtoIOShash("3.1,7C144,Sep-09")
addtoIOShash("3.1,7C145,Sep-09")
addtoIOShash("3.1,7C146,Sep-09")
addtoIOShash("3.1.2,7D11,Oct-09")
addtoIOShash("3.1.3,7E18,Feb-09")
addtoIOShash("3.2,7B367,Apr-10")
addtoIOShash("3.2.1,7B405,Jul-10")
addtoIOShash("4.0,8A293,Jun-10")
addtoIOShash("4.0.1,8A306,Jul-10")

$hndSethash.add("Apple-iPhone","IPhone")
$hndSethash.add("Apple-iPhone1C2","IPhone 3G")
$hndSethash.add("Apple-iPhone2C1","IPhone 3GS")
$hndSethash.add("Apple-iPhone3C1","IPhone 4")
$hndSethash.add("Apple-iPad","IPad")
$hndSethash.add("Apple-iPod","IPod Touch")

$CurrentDate = Get-Date




$fname = $exFileName.FileName
$mbcombCollection = @()
$FldHash = @{}
$usHash = @{}
$fieldsline = (Get-Content $fname)[3]
$fldarray = $fieldsline.Split(" ")
$fnum = -1
foreach ($fld in $fldarray){
$FldHash.add($fld,$fnum)
$fnum++
}

get-content $fname | Where-Object -FilterScript { $_ -ilike “*&DeviceType=*” } | %{
$lnum ++
write-progress "Scanning Line" $lnum
if ($lnum -eq $rnma){ Write-Progress -Activity "Read Lines" -Status $lnum
$rnma = $rnma + 1000
}
$linarr = $_.split(" ")
$uid = $linarr[$FldHash["cs-username"]] + $linarr[$FldHash["cs(User-Agent)"]]
if ($linarr[$FldHash["cs-username"]].length -gt 2){
if ($usHash.Containskey($uid) -eq $false){
$usrobj = "" | select UserName,UserAgent,Iphone,IphoneType,IOSVersion,IOSReleaseDate,AppleBuildCode
$usrobj.UserName = $linarr[$FldHash["cs-username"]]
$usrobj.UserAgent = $linarr[$FldHash["cs(User-Agent)"]]
if ($usrobj.UserAgent -match "apple"){
$apcodearray = $usrobj.UserAgent.split("/")
$usrobj.Iphone = "Yes"
$usrobj.IphoneType = $hndSethash[$apcodearray[0]]
$usrobj.IOSVersion = $appleverhash[$apcodearray[1]].IOSVersion
$usrobj.IOSReleaseDate = $appleverhash[$apcodearray[1]].IOSReleaseDate
$usrobj.AppleBuildCode = $appleverhash[$apcodearray[1]].AppleBuildCode
}
else{
$usrobj.Iphone = "No"
}
$usHash.add($uid,$usrobj)
$mbcombCollection += $usrobj

}
}
}


$tableStyle = @"
<style>
BODY{background-color:white;}
TABLE{border-width: 1px;
border-style: solid;
border-color: black;
border-collapse: collapse;
}
TH{border-width: 1px;
padding: 10px;
border-style: solid;
border-color: black;
background-color:#66CCCC
}
TD{border-width: 1px;
padding: 2px;
border-style: solid;
border-color: black;
background-color:white
}
</style>
"@

$body = @"
<p style="font-size:25px;family:calibri;color:#ff9100">
$TableHeader
</p>
"@



$SmtpClient = new-object system.net.mail.smtpClient
$SmtpClient.host = $SMTPServer
$MailMessage = new-object System.Net.Mail.MailMessage
$MailMessage.To.Add($sendAlertTo)
$MailMessage.From = $sendAlertFrom
$MailMessage.Subject = "iPhone Registrration Report"
$MailMessage.IsBodyHtml = $TRUE
$MailMessage.body = $mbcombCollection | ConvertTo-HTML -head $tableStyle –body $body
$SMTPClient.Send($MailMessage)

Thursday, August 05, 2010

OT: Looking for work

After working for my last employer for a very long time (over a decade in relatively the same job and location) a couple of weeks okay I finally finished up there in the hope of establishing a better work/life balance.

So if your in need a of a overly creative Exchange Engineer/Developer/Scripter send me an email glenscales@yahoo.com no job too big or small :) .

Cheers
Glen

Importing a Outlook Express dbx file into Exchange using the EWS managed API and powershell

I recently switched to a new Laptop which had Windows7 which left me in a little bit of a hole as to how i could get access to some old email which i needed rather urgently that was located in a dbx file from a previous laptop where I had been using Outlook Express for a number of years. I found http://www.slipstick.com/outlook/convert_oe.htm which had a acceptable method of doing this but I thought I'd like to push this email into a Exchange Mailbox anyway so i decided to see if i could do this programmaticly.

Turns out there is no real API that makes access t0 DBX files easy however a few people over the years have come up with methods to access these files, there is a PHP dbxparser which someone has also ported to C# on code-project here this was enough information to allow me to build a simple DLL that i could then use in Powershell to extract email and then using some simple code to upload the email into Exchange using the MIMEContent upload technique with EWS. In the end the code worked fairly well i had to include some code to ignore NDR's which where causing some errors and because its not batched its a little slow but it did do the trick. I've put a download of the code I used and the DLL here for anybody that interested the script itself looks like this.

$dbxfile = "c:\temp\inbox.dbx"
$dllpath = "C:\temp\psdbxparser.dll"
$MailboxName = "mailbox@domain.com"

$casserverName = "exserver"
$userName = "username"
$password = "password"
$domain = "domain"

## Code From http://poshcode.org/624
## Create a compilation environment
$Provider=New-Object Microsoft.CSharp.CSharpCodeProvider
$Compiler=$Provider.CreateCompiler()
$Params=New-Object System.CodeDom.Compiler.CompilerParameters
$Params.GenerateExecutable=$False
$Params.GenerateInMemory=$True
$Params.IncludeDebugInformation=$False
$Params.ReferencedAssemblies.Add("System.DLL") | Out-Null

$TASource=@'
namespace Local.ToolkitExtensions.Net.CertificatePolicy{
public class TrustAll : System.Net.ICertificatePolicy {
public TrustAll() {
}
public bool CheckValidationResult(System.Net.ServicePoint sp,
System.Security.Cryptography.X509Certificates.X509Certificate cert,
System.Net.WebRequest req, int problem) {
return true;
}
}
}
'@
$TAResults=$Provider.CompileAssemblyFromSource($Params,$TASource)
$TAAssembly=$TAResults.CompiledAssembly

## We now create an instance of the TrustAll and attach it to the ServicePointManager
$TrustAll=$TAAssembly.CreateInstance("Local.ToolkitExtensions.Net.CertificatePolicy.TrustAll")
[System.Net.ServicePointManager]::CertificatePolicy=$TrustAll

## end code from http://poshcode.org/624

$dllpath = "C:\Program Files\Microsoft\Exchange\Web Services\1.0\Microsoft.Exchange.WebServices.dll"
[void][Reflection.Assembly]::LoadFile($dllpath)
$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2007_SP1)
$uri=[system.URI] ("https://" + $casserverName + "/ews/exchange.asmx")
$service.Url = $uri
$service.Credentials = New-Object System.Net.NetworkCredential($username,$password,$domain)


$casuri = "https://" + $casserverName + "/ews/exchange.asmx"
$uri=[system.URI] $casuri
$service.Url = $uri

$folderid = new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox,$MailboxName)
$TargetFolder = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$folderid)


[Reflection.Assembly]::LoadFile($dllpath)
$dbx = new-object psdbxparser.DBX
$mcount = $dbx.Parse($dbxfile)
if ($mcount -gt 0){
for($iloop=0;$iloop -lt $mcount;$iloop++){
$msgString = $dbx.Extract($iloop)
$emUploadEmail = new-object Microsoft.Exchange.WebServices.Data.EmailMessage($service)
[byte[]]$bdBinaryData1 = [System.Text.Encoding]::ASCII.GetBytes($msgString)
$emUploadEmail.MimeContent = new-object Microsoft.Exchange.WebServices.Data.MimeContent("us-ascii", $bdBinaryData1);
$PR_Flags = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(3591, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Integer);
$emUploadEmail.SetExtendedProperty($PR_Flags,"1")
if ($msgString.Indexof("message/delivery-status") -eq -1){
$emUploadEmail.Save($TargetFolder.id)
"Uploaded : " + $iloop
}


}

}