Wednesday, December 22, 2010

Using Remote Powershell and EWS on Office365

A big leap forward on Office365 compared with the current BPOS offering is the ability to use remote powershell and a subset of the Exchange cmdlets that are available in Exchange 2010. This makes it pretty easy to migrate your current onpremise scripts that use make use of EMS and EWS into something that uses remote powershell and EWS. To demonstrate this I've converted on my old scripts the FreeBusy board to use Remote powershell, Office365 and the EWS Managed API 1.1.

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

$UserName = "admin@domain.com"
$Password = "password1"
$secpassword = ConvertTo-SecureString $Password -AsPlainText -Force
$adminCredential = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $UserName,$secpassword

If(Get-PSSession | where-object {$_.ConfigurationName -eq "Microsoft.Exchange"}){
write-host "Session Exists"
}
else{
$rpRemotePowershell = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -credential $adminCredential -Authentication Basic -AllowRedirection
$importresults = Import-PSSession $rpRemotePowershell
}


$dllpath = "C:\Program Files\Microsoft\Exchange\Web Services\1.1\Microsoft.Exchange.WebServices.dll"
[void][Reflection.Assembly]::LoadFile($dllpath)
$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP1)

$windowsIdentity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
$sidbind = "LDAP://<SID=" + $windowsIdentity.user.Value.ToString() + ">"
$aceuser = [ADSI]$sidbind
$service.Credentials = New-Object System.Net.NetworkCredential($username,$password)
$service.AutodiscoverUrl($UserName ,{$true})

$mbHash = @{ }

$tmValHash = @{ }
$tidx = 0
for($vsStartTime=[DateTime]::Parse([DateTime]::Now.ToString("yyyy-MM-dd 0:00"));$vsStartTime -lt [DateTime]::Parse([DateTime]::Now.ToString("yyyy-MM-dd 0:00")).AddDays(1);$vsStartTime = $vsStartTime.AddMinutes(30)){
$tmValHash.add($vsStartTime.ToString("HH:mm"),$tidx)
$tidx++
}

get-mailbox -ResultSize unlimited | foreach-object{
if ($mbHash.ContainsKey($_.WindowsEmailAddress.ToString()) -eq $false){
$mbHash.Add($_.WindowsEmailAddress.ToString(),$_.DisplayName)
}
}
$Attendeesbatch = [activator]::createinstance(([type]'System.Collections.Generic.List`1').makegenerictype([Microsoft.Exchange.WebServices.Data.AttendeeInfo]))

$StartTime = [DateTime]::Parse([DateTime]::Now.ToString("yyyy-MM-dd 0:00"))
$EndTime = $StartTime.AddDays(1)


$displayStartTime = [DateTime]::Parse([DateTime]::Now.ToString("yyyy-MM-dd 08:30"))
$displayEndTime = [DateTime]::Parse([DateTime]::Now.ToString("yyyy-MM-dd 17:30"))

$drDuration = new-object Microsoft.Exchange.WebServices.Data.TimeWindow($StartTime,$EndTime)
$AvailabilityOptions = new-object Microsoft.Exchange.WebServices.Data.AvailabilityOptions
$AvailabilityOptions.RequestedFreeBusyView = [Microsoft.Exchange.WebServices.Data.FreeBusyViewType]::DetailedMerged

$batchsize = 100
$bcount = 0
$bresult = @()
if ($mbHash.Count -ne 0){
$mbHash.GetEnumerator() | Sort Value | foreach-object {
if ($bcount -ne $batchsize){
$Attendee1 = new-object Microsoft.Exchange.WebServices.Data.AttendeeInfo($_.Key)
$Attendeesbatch.add($Attendee1)
$bcount++
}
else{
$availresponse = $service.GetUserAvailability($Attendeesbatch,$drDuration,[Microsoft.Exchange.WebServices.Data.AvailabilityData]::FreeBusy,$AvailabilityOptions)
foreach($avail in $availresponse.AttendeesAvailability.OverallResult){$bresult += $avail}
$Attendeesbatch = [activator]::createinstance(([type]'System.Collections.Generic.List`1').makegenerictype([Microsoft.Exchange.WebServices.Data.AttendeeInfo]))
$bcount = 0
$Attendee1 = new-object Microsoft.Exchange.WebServices.Data.AttendeeInfo($_.Key)
$Attendeesbatch.add($Attendee1)
$bcount++
}
}
}
$availresponse = $service.GetUserAvailability($Attendeesbatch,$drDuration,[Microsoft.Exchange.WebServices.Data.AvailabilityData]::FreeBusy,$AvailabilityOptions)
$usrIdx = 0
$frow = $true
foreach($res in $availresponse.AttendeesAvailability){
if ($frow -eq $true){
$fbBoard = $fbBoard + "<table><tr bgcolor=`"#95aedc`">" +"`r`n"
$fbBoard = $fbBoard + "<td align=`"center`" style=`"width=200;`" ><b>User</b></td>" +"`r`n"
for($stime = $displayStartTime;$stime -lt $displayEndTime;$stime = $stime.AddMinutes(30)){
$fbBoard = $fbBoard + "<td align=`"center`" style=`"width=50;`" ><b>" + $stime.ToString("HH:mm") + "</b></td>" +"`r`n"
}
$fbBoard = $fbBoard + "</tr>" + "`r`n"
$frow = $false
}
for($stime = $displayStartTime;$stime -lt $displayEndTime;$stime = $stime.AddMinutes(30)){
if ($stime -eq $displayStartTime){
$fbBoard = $fbBoard + "<td bgcolor=`"#CFECEC`"><b>" + $mbHash[$Attendeesbatch[$usrIdx].SmtpAddress] + "</b></td>" + "`r`n"
}
$title = "title="
if ($res.MergedFreeBusyStatus[$tmValHash[$stime.ToString("HH:mm")]] -ne $null){
$gdet = $false
$FbValu = $res.MergedFreeBusyStatus[$tmValHash[$stime.ToString("HH:mm")]]
switch($FbValu.ToString()){
"Free" {$bgColour = "bgcolor=`"#41A317`""}
"Tentative" {$bgColour = "bgcolor=`"#52F3FF`""
$gdet = $true
}
"Busy" {$bgColour = "bgcolor=`"#153E7E`""
$gdet = $true
}
"OOF" {$bgColour = "bgcolor=`"#4E387E`""
$gdet = $true
}
"NoData" {$bgColour = "bgcolor=`"#98AFC7`""}
"N/A" {$bgColour = "bgcolor=`"#98AFC7`""}
}
if ($gdet -eq $true){
if ($res.CalendarEvents.Count -ne 0){
for($ci=0;$ci -lt $res.CalendarEvents.Count;$ci++){
if ($res.CalendarEvents[$ci].StartTime -ge $stime -band $stime -le $res.CalendarEvents[$ci].EndTime){
if($res.CalendarEvents[$ci].Details.IsPrivate -eq $False){
$subject = ""
$location = ""
if ($res.CalendarEvents[$ci].Details.Subject -ne $null){
$subject = $res.CalendarEvents[$ci].Details.Subject.ToString()
}
if ($res.CalendarEvents[$ci].Details.Location -ne $null){
$location = $res.CalendarEvents[$ci].Details.Location.ToString()
}
$title = $title + "`"" + $subject + " " + $location + "`" "
}
}
}
}
}

}
else{
$bgColour = "bgcolor=`"#98AFC7`""
}
if($title -ne "title="){
$fbBoard = $fbBoard + "<td " + $bgColour + " " + $title + "></td>" + "`r`n"
}
else{
$fbBoard = $fbBoard + "<td " + $bgColour + "></td>" + "`r`n"
}

}
$fbBoard = $fbBoard + "</tr>" + "`r`n"
$usrIdx++
}
$fbBoard = $fbBoard + "</table>" + " "
$fbBoard | out-file "c:\fbboard.htm"

Wednesday, December 01, 2010

Simple Exchange Powershell Client V2

This has taken me a little longer then expected to update but I've finally created a version 2 of my simple powershell client script i posted here.

Firstly if you missed it the new version the EWS Managed API 1.1 has been released which allows you to access all the new 2010 SP1 functionality like archive mailboxes, dumpster v2 etc you can download this from here the new script requires this library.

The new features I've added to the script is firstly there is a Entry Point box that allows you to select what entry point you want.



The Non IPM Root is usefull if you want to look at the dumpster folders or other things in the Non_IPM_Root, Archive root takes you into the Archive mailbox if its exists for that user.

Another new edition is the ItemType box which allows you the select between being able to view the Normal Mailbox Items, FAI (Folder Associated Items) or Deleted Items (not really applicable for Dumpster v2).


Lastly because the new script is built for use with Windows 7.0 and Powershell V2 the right click menu for the datagird will now work okay so I've added the ability to once you select the Items in the datagrid you can rightclick and then Delete, Copy or Move the selected Items to another folder (the deletes are soft deletes).

I've put a download of the new script here


Tuesday, November 16, 2010

Breadcrumb auditing in Exchange 2010 with EWS and Powershell

Great strides have been made since Exchange 2007 SP2 with the introduction of more advanced audit levels into Exchange which have again improved in Exchange 2010 and again with 2010 SP1. Auditing however relies on a few things that after the fact of an event you may have wished to audit (or more likely a Superior wanting to know what mailboxes where accessed) . But like footprints in the sand when you walk along the beach many of the actions we do in Outlook do leave marks that the majority of people may not realize. One such mark in the sand that happens in your mailbox when using Outlook 2010 or OWA 2010 is when you access another users folder eg calendar, contacts etc is a wunderbar link gets added to the Common Views Root folder.

What you can do with this little bit of information is deduce what other mailboxes a particular user has been access by enumerating the wunderbar links in the users common view folder. These wunderbar links are FAI (folder associated Items) so live in the FAI collection of that particular folder.

To do this with EWS requires the use of some of the nice new features of EWS in Exchange 2010 such as the ability to access the associated Folder Items collection where these WunderBar Shortcuts are stored.

To access the associated folder Items collection in EWS you just need to change the Traversal type to Associated (note this wont work on 2007).

The Wunderbar Items themselves have a bunch of extended properties that contain information that is important to see what mailbox and folder this users was accessing. A full list of the shortcut properties can be found in the following Exchange protocol document http://msdn.microsoft.com/en-us/library/cc463899%28EXCHG.80%29.aspx. The Ones I've used in this script are

PidTagWlinkEntryId EntryID the ShortCut points to
PidTagWlinkStoreEntryId StoreEntryID the ShortCut points to
PidTagWlinkAddressBookEID Like the above but contains just the users info
PidTagWlinkFolderType Contains the GUID to let you know what type of folder it is
PidTagWlinkGroupName The name of the group

I've put this altogether is a powershell script that use the new EWS 1.1 Managed API beta (which allows you to access the associated folders collection). To use this script you need to customize the the following variable the output of this script is a html report.

$casserverName = "casservername"
$userName = "username"
$password = "password"
$domain = "domain"
$Mailboxname = "user@domain.com"
$fileName = "c:\report.htm"

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

$casserverName = "casservername"
$userName = "username"
$password = "password"
$domain = "domain"
$Mailboxname = "user@domain.com"
$fileName = "c:\report.htm"

$rptCollection = @()

function ReturnFolderType($guid){
switch($guid){
"0C78060000000000C000000000000046" { return "MailFolder"}
"0278060000000000C000000000000046" { return "Calendar" }
"0178060000000000C000000000000046" { return "Contacts" }
"0378060000000000C000000000000046" { return "Tasks"}
"0478060000000000C000000000000046" { return "Notes"}
"0878060000000000C000000000000046" { return "Journal"}
}

}



## 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.1\Microsoft.Exchange.WebServices.dll"
[void][Reflection.Assembly]::LoadFile($dllpath)
$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010)
$uri=[system.URI] ("https://" + $casserverName + "/ews/exchange.asmx")
$service.Url = $uri
$service.Credentials = New-Object System.Net.NetworkCredential($username,$password,$domain)
$rfFolderid = new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Root,$MailboxName)
$fview = New-Object Microsoft.Exchange.WebServices.Data.FolderView(1000)
$Sfir = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.FolderSchema]::DisplayName, "Common Views")
$findfldResults = $Service.FindFolders($rfFolderid,$Sfir,$fview)
if ($findfldResults.Folders.Count -eq 1) {
$findfldResults.Folders[0].DisplayName
$PidTagWlinkEntryId = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(26700, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Binary)
$PidTagWlinkStoreEntryId = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(26702, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Binary)
$PidTagWlinkAddressBookEID = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(26708, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Binary)
$PidTagWlinkFolderType = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(26703, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Binary)
$PidTagWlinkGroupName = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(26705, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::String)
$Propset = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)
$Propset.add($PidTagWlinkEntryId)
$Propset.add($PidTagWlinkStoreEntryId)
$Propset.add($PidTagWlinkAddressBookEID)
$Propset.add($PidTagWlinkFolderType)
$Propset.add($PidTagWlinkGroupName)
$iv = New-Object Microsoft.Exchange.WebServices.Data.ItemView(10000)
$iv.PropertySet = $Propset
$iv.Traversal = [Microsoft.Exchange.WebServices.Data.ItemTraversal]::Associated
$fiResults = $findfldResults.Folders[0].FindItems($iv)
foreach($itItem in $fiResults.Items){
$itItem.Subject
$WlinkStoreEntryId = $null
$rptobj = "" | select MailboxName,LinkName,GroupName,FolderType,DateCreated,LastModified
$lnLegDN = ""
if ($itItem.TryGetProperty($PidTagWlinkStoreEntryId,[ref]$WlinkStoreEntryId))
{

$leLegDnStart = 0
for ($ssArraynum = (([byte[]]$WlinkStoreEntryId).Length - 2); $ssArraynum -ne 0; $ssArraynum--)
{
if (([byte[]]$WlinkStoreEntryId)[$ssArraynum] -eq 0)
{
$leLegDnStart = $ssArraynum;
$lnLegDN = [System.Text.ASCIIEncoding]::ASCII.GetString(([byte[]]$WlinkStoreEntryId), $leLegDnStart + 1, (([byte[]]$WlinkStoreEntryId).Length - ($leLegDnStart + 2)));
$ssArraynum = 1;
}
}

}
else{
if ($itItem.TryGetProperty($PidTagWlinkAddressBookEID,[ref]$WlinkStoreEntryId))
{

$leLegDnStart = 0
$lnLegDN = ""
for ($ssArraynum = (([byte[]]$WlinkStoreEntryId).Length - 2); $ssArraynum -ne 0; $ssArraynum--)
{
if (([byte[]]$WlinkStoreEntryId)[$ssArraynum] -eq 0)
{
$leLegDnStart = $ssArraynum;
$lnLegDN = [System.Text.ASCIIEncoding]::ASCII.GetString(([byte[]]$WlinkStoreEntryId), $leLegDnStart + 1, (([byte[]]$WlinkStoreEntryId).Length - ($leLegDnStart + 2)));
$ssArraynum = 1;
}
}
}

}
if($lnLegDN -ne ""){
$ncCol = $service.ResolveName($lnLegDN)
$mbox = ""
foreach ($NameResolution in $ncCol)
{
$mbox = $NameResolution.Mailbox.Address
}
$FolderType = $null
if ($itItem.TryGetProperty($PidTagWlinkFolderType,[ref]$FolderType)){
$hexArr = $FolderType | ForEach-Object { $_.ToString("X2") }
$hexString = $hexArr -join ''
$rptobj.FolderType = ReturnFolderType($hexString)
$hexString

}
$GroupName = $null
if ($itItem.TryGetProperty($PidTagWlinkGroupName,[ref]$GroupName)){
$rptobj.GroupName = $GroupName
}
$rptobj.MailboxName = $mbox
$rptobj.LinkName = $itItem.Subject
$rptobj.DateCreated = $itItem.DateTimeCreated
$rptobj.LastModified = $itItem.LastModifiedTime
$rptCollection += $rptobj}
}
}
$rptCollection
$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>
"@

$rptCollection | ConvertTo-HTML -head $tableStyle –body $body | Out-file $fileName

Thursday, October 14, 2010

Making OWA canary's sing using cookies in your OWA scripts

In Exchange 2007 SP3 Microsoft implemented canaries in OWA to help prevent man in the middle/cross scripting attacks in OWA http://msdn.microsoft.com/en-us/magazine/dd458793.aspx#id0080023 gives a good explanation around why you would want to use a canary and what it is. But put basically a canary is a secret between the client and server in OWA this gets stored in cookie collection of your browser and then it gets submitted with the various requests that your browser sends. If your request doesn't have the canary then the server pretty much says "no OWA for you!". Because in the scripting world it can be useful as a workaround to instrument OWA to automate particular things such as enabling the extended junk-Email rule in OWA and Outlook in the past I've posted scripts that use OWA automation that have now pretty much been broken by the canary's.

So to fix this you need to add some code that will deal with the canary my favorite object to use for these type of scripts is the MSXML2.ServerXMLHTTP object because this handles all the FBA cookies in OWA without the need to add lines of code. To Get the canary from the cookie collection once you have logged on it needs another request to the mailbox your going to access which if things go well means your cookie collection should now be populated and you can just parse the cookie with a few lines of script eg

req.Open "GET", "https://" & snServername & "/owa/" & Targetmailbox & "", False
req.SetOption 2, 13056
req.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
req.setRequestHeader "Content-Length", Len(xmlstr)
req.send szXml
reqhedrarry = split(req.GetAllResponseHeaders(), vbCrLf,-1,1)
for each ent in reqhedrarry
wscript.echo ent
Next
cookie = req.getResponseHeader("Set-Cookie")
if instr(cookie,"=") then
slen = instr(cookie,"=")+1
elen = instr(slen,cookie,"&")
canary = mid(cookie,slen,elen-slen)
wscript.echo "parsed canary : " & canary
end if

Once you have the canary you need to make sure you include it in your request for the junkemail script you need to modify the XML sent to look like

"<params><canary>" & canary & "</canary><fEnbl>1</fEnbl></params>"

And if everything is good your canary should sing to OWA to and achieve what it is your asking it to do. I've put a updated version of the junkemail script here

Monday, October 11, 2010

Modifying Outlook profiles in Outlook 2010 with VBS and Powershell

Outlook profiles can present somewhat of a challenge to any mail administrator regardless of the size of the mail system. One way of looking at Outlook profiles is that they are just a whole collection of registry settings on a machine which is true but the number and compelxity of structures involved means that if you want to write a script that will modify a particular profile for a particular user its not just as simple as modifying a single registry key. In previous versions of Outlook you could generally rely on the GUID "13dbb0c8aa05101a9bb000aa002fc45a" to locate the key where the values for the Exchange settings are located but if you have done any work with Outlook 2010 on windows 7 you will generally find this is nolonger the case and its now more reliable to enumerate all the keys under a particular profile and find a particular setting which will indicate this particular key has the values for the Exchange Setting you want to modify. The one thing that computers are really good at is processing things quickly so if you wanted to for instance modify the cache mode setting within a profile in VBS you could use the following code to locate the default profile

Set WshShell = WScript.CreateObject("WScript.Shell")
strComputer = "."
HKEY_CURRENT_USER = &H80000001
Set oReg = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")
keypath = "Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles\"

profile = WshShell.RegRead("HKCU\"&keypath&"DefaultProfile")


Then enumerate all the keys under this profile to identify the key that has the cache mode value.

oReg.EnumKey HKEY_CURRENT_USER, (keypath & profile & "\"), arrSubKeys
For Each subkey In arrSubKeys
oReg.EnumValues HKEY_CURRENT_USER, (keypath & profile & "\" & subkey & "\"), arrSubValues
on error resume next
for each ValueKey in arrSubValues
if ValueKey = "00036601" then
oReg.GetBinaryValue HKEY_CURRENT_USER,(keypath & profile & "\" & subkey),"00036601",prProxyValRes
if not IsNull(prProxyValRes) then
if prProxyValRes(0) = "132" then
Wscript.echo "Cache Mode Enabled"
else
Wscript.echo "Cache Mode Disabled"
end if
end if
end if
next
on error goto 0
next

If you want to change the setting instead of just echoing it out the current setting then you would need to include something like

newValue1 = Array(&H84,&H05,&H00,&H00)
oReg.SetBinaryValue HKEY_CURRENT_USER,(keypath & profile & "\" & subkey),"00036601",newValue1


Note the hex value that you put in the second array element can vary depending on whether you want to enable the download public folder or shared folders check boxes.

If you think vbs is for mugs and you want to do this in powershell instead then you would need something like this

$RootKey = "Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles"
$pkProfileskey = [Microsoft.Win32.Registry]::CurrentUser.OpenSubKey($RootKey, $true)
$defProf = $pkProfileskey.GetValue("DefaultProfile")
$pkSubProfilekey = [Microsoft.Win32.Registry]::CurrentUser.OpenSubKey(($RootKey + "\\" + $defProf), $true)
foreach($Valuekey in $pkSubProfilekey.getSubKeyNames()){
$pkSubValueKey = [Microsoft.Win32.Registry]::CurrentUser.OpenSubKey(($RootKey + "\\" + $defProf + "\\" + $Valuekey ), $true)
foreach ($values in $pkSubValueKey.GetValueNames())
{
if ($values -eq "00036601"){
if(($pkSubValueKey.GetValue("00036601"))[0] -eq 132){
"Cache Mode Enabled"}
else{
"Cache Mode Disabled"
}
}
}



}

Sunday, September 19, 2010

Recipient \ Email Address Policy GUI and browser for Exchange 2007 / 2010

Recipient policies where first introduced in Exchange 2003 and later became email address policies in Exchange 2007 where we where all introduced to the joys and sorrows of Opath. While policies are a simple idea and a good method to do this type of thing the complexities of what happens underneath can be hard to get your head around and test and this can at times lead to the odd mistake. While there is a preview box when your modifying an Address list policy if you wanted to look at the bigger picture of the filter that the address policy represents as it is and could be applied to active directory objects, this is where it can be useful to use a script. The other thing to take into account when looking a policies is the objects where the application of the address policies have been disabled. So what I've done is build a GUI to help this the first thing this script does is enumerate all the policy objects and retrieve the following properties that are associated with these object.

GatewayProxy - This contains the proxyaddresses that will be applied by the policy

msExchQueryFilter - This is the Opath filter for the policy

msExchPolicyOrder - This is the priority of the address policy

msExchPurportedSearchUI - This is a little bit of cheat as this property is used for the UI but it does contain a LDAP filter that can be used directly in ADSI so this is the property that I've used to search active directory for objects that the policy relates to.

GUID this is stored so it can be compared to the the msexchPoliciesIncluded property to work out if a policy has been applied to a specific object.

To work out if a policy has been applied to an object and also work out if policies have been disabled two properties are important. If polices have been disabled via the "Automatically update e-mail addresses based on e-mail address policy" then the msExchPoliciesExcluded will be set to 26491CFC-9E50-4857-861B-0CB8DF22B5D7.

Once a policy has been applied to an AD object the msexchpoliciesincluded will be updated with the GUID of the policy that has been applied. So when it comes time to enumerating applied polices using a script these a properties that you can put to use.

What happens when you hit the search button is the script does a ADSI query of active directory based on the LDAP filter and will return the objects that match this filter and tell you want policis has been applied to these objects. The radio button let you filter on all object, just the ones where the policy has been applied or disabled.

Some other things this script can be used for are backing up the current proxyaddress setting of accounts before you make any changes.

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

[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
$form = new-object System.Windows.Forms.form
$raCollection = @()
$usrCollection = @()
$policyhash = @{ }
# $policyhash.Add("26491CFC-9E50-4857-861B-0CB8DF22B5D7","Disabled")

function UpdateListBoxPolicy(){
foreach ($prxobj in $raCollection){
if ($prxobj.PolicyName -eq $policyNameDrop.SelectedItem.ToString()){
$lbListView.clear()
$lbListView.Columns.Add("Property",80)
$lbListView.Columns.Add("Value",220)
$item1 = new-object System.Windows.Forms.ListViewItem("GUID")
$item1.SubItems.Add($prxobj.GUID.ToString())
$lbListView.items.add($item1)
$item1 = new-object System.Windows.Forms.ListViewItem("OpathFilter")
$item1.SubItems.Add($prxobj.OpathFilter.ToString())
$lbListView.items.add($item1)
$item1 = new-object System.Windows.Forms.ListViewItem("gatewayProxy")
if ($prxobj.gatewayProxy -is [system.array]){
$item1.SubItems.Add(([string]::join(";", $prxobj.gatewayProxy)))
}
else{
$item1.SubItems.Add($prxobj.gatewayProxy.ToString())
}
$lbListView.items.add($item1)
$item1 = new-object System.Windows.Forms.ListViewItem("LDAPFilter")
$item1.SubItems.Add($prxobj.LDAPFilter.ToString())
$lbListView.items.add($item1)
$item1 = new-object System.Windows.Forms.ListViewItem("msExchPolicyOrder")
$item1.SubItems.Add($prxobj.msExchPolicyOrder.ToString())
$lbListView.items.add($item1)
}
}
}

function ExportGrid(){
$exFileName = new-object System.Windows.Forms.saveFileDialog
$exFileName.DefaultExt = "csv"
$exFileName.Filter = "csv files (*.csv)|*.csv"
$exFileName.InitialDirectory = "c:\temp"
$exFileName.ShowHelp = $true
$exFileName.ShowDialog()
if ($exFileName.FileName -ne ""){
$logfile = new-object IO.StreamWriter($exFileName.FileName,$true)
$logfile.WriteLine("DisplayName,PrimaryEmailAdrress,ProxyAddresses,RecipientPolicy")
foreach($row in $msTable.Rows){
$logfile.WriteLine("`"" + $row[0].ToString() + "`"," + $row[1].ToString() + "," + $row[2].ToString() + "," + $row[3].ToString())
}
$logfile.Close()
}



}

function SearchforObjects(){
$policyobject = ""
foreach ($prxobj in $raCollection){
if ($prxobj.PolicyName -eq $policyNameDrop.SelectedItem.ToString()){
$policyobject = $prxobj.LDAPFilter.ToString()
}
}
$msTable.clear()
$dfDefaultRootPath = "LDAP://" + $root.DefaultNamingContext.tostring()
$dfRoot = [ADSI]$dfDefaultRootPath
$gfGALQueryFilter = $policyobject
$dfsearcher = new-object System.DirectoryServices.DirectorySearcher($dfRoot)
$dfsearcher.Filter = $gfGALQueryFilter
$dfsearcher.PageSize = 1000
$dfsearcher.PropertiesToLoad.Add("msexchPoliciesIncluded")
$dfsearcher.PropertiesToLoad.Add("proxyAddresses")
$dfsearcher.PropertiesToLoad.Add("mail")
$dfsearcher.PropertiesToLoad.Add("displayName")
$dfsearcher.PropertiesToLoad.Add("distinguishedName")
$dfsearcher.PropertiesToLoad.Add("msExchPoliciesExcluded")
$srSearchResult = $dfsearcher.FindAll()
foreach ($emResult in $srSearchResult) {
$emProps = $emResult.Properties
$DisplayName = ""
$PrimaryEmailAdrress = ""
$ProxyAddresses = ""
$RecipientPolicy = ""
if($emProps.msexchpoliciesincluded -ne $null){
$polarray = $emProps.msexchpoliciesincluded[0].Split(",")
foreach($pol in $polarray){
$pol = $pol.ToString().Replace("{","").Replace("}","")
if ($policyhash.ContainsKey($pol.ToString())){
$RecipientPolicy = $policyhash[$pol.ToString()]

}
}
}
if ($emProps.msexchpoliciesexcluded -ne $null){
foreach($pol in $emProps.msexchpoliciesexcluded){
$pol = $pol.ToString().Replace("{","").Replace("}","")
$pol.ToString()
if ($pol.ToString() -eq "26491CFC-9E50-4857-861B-0CB8DF22B5D7"){
$RecipientPolicy = "Disabled"

}
}
}
$DisplayName = $emResult.Properties["displayname"][0]
$PrimaryEmailAdrress = $emResult.Properties["mail"][0]
$ProxyAddresses = [string]::join(";",$emProps.proxyaddresses)
if($rbinc.Checked -eq $true){
$msTable.rows.add($DisplayName,$PrimaryEmailAdrress,$ProxyAddresses,$RecipientPolicy)
}
if($rbinc1.Checked -eq $true -band $RecipientPolicy -eq $policyNameDrop.SelectedItem.ToString()){
$msTable.rows.add($DisplayName,$PrimaryEmailAdrress,$ProxyAddresses,$RecipientPolicy)
}
if($rbinc2.Checked -eq $true -band $RecipientPolicy -eq "Disabled"){
$msTable.rows.add($DisplayName,$PrimaryEmailAdrress,$ProxyAddresses,$RecipientPolicy)
}

}
$dgDataGrid.Datasource = $msTable
}

$root = [ADSI]'LDAP://RootDSE'
$cfConfigRootpath = "LDAP://" + $root.ConfigurationNamingContext.tostring()
$configRoot = [ADSI]$cfConfigRootpath
$searcher = new-object System.DirectoryServices.DirectorySearcher($configRoot)
$searcher.Filter = "(objectClass=msexchRecipientPolicy)"
$searchresults = $searcher.FindAll()
foreach ($searchresult in $searchresults){
$plcobj = "" | select PolicyName,GUID,gatewayProxy,OpathFilter,LDAPFilter,msExchPolicyOrder
$Policyobject = New-Object System.DirectoryServices.directoryentry
$Policyobject = $searchresult.GetDirectoryEntry()
$plcobj.PolicyName = $Policyobject.Name.Value
$plcobj.GUID = [GUID]$Policyobject.ObjectGUID.Value
$plcobj.OpathFilter = $Policyobject.msExchQueryFilter.Value
$plcobj.gatewayProxy = $Policyobject.GatewayProxy.Value
$plcobj.msExchPolicyOrder = $Policyobject.msExchPolicyOrder.Value
foreach ($val in $Policyobject.msExchPurportedSearchUI){
if($val -match "Microsoft.PropertyWell_QueryString="){
$plcobj.LDAPFilter = $val.substring(35,($val.length-35))
}
}
$raCollection += $plcobj
$policyhash.add($plcobj.GUID.ToString(),$plcobj.PolicyName)
}


$msTable = New-Object System.Data.DataTable
$msTable.TableName = "ProxyAddress"
$msTable.Columns.Add("DisplayName")
$msTable.Columns.Add("PrimaryEmailAdrress")
$msTable.Columns.Add("ProxyAddresses")
$msTable.Columns.Add("RecipientPolicy")


$PolicylableBox = new-object System.Windows.Forms.Label
$PolicylableBox.Location = new-object System.Drawing.Size(10,20)
$PolicylableBox.size = new-object System.Drawing.Size(120,20)
$PolicylableBox.Text = "Select Policy Name : "
$form.controls.Add($PolicylableBox)

# Add Policy Drop Down
$policyNameDrop = new-object System.Windows.Forms.ComboBox
$policyNameDrop.Location = new-object System.Drawing.Size(130,20)
$policyNameDrop.Size = new-object System.Drawing.Size(200,30)
$policyNameDrop.Enabled = $true
foreach ($prxobj in $raCollection){
$policyNameDrop.Items.Add($prxobj.PolicyName)
}
$policyNameDrop.Add_SelectedValueChanged({UpdateListBoxPolicy})
$form.Controls.Add($policyNameDrop)

$policySettingslableBox = new-object System.Windows.Forms.Label
$policySettingslableBox.Location = new-object System.Drawing.Size(340,20)
$policySettingslableBox.size = new-object System.Drawing.Size(80,20)
$policySettingslableBox.Text = "Policy Settings"
$form.controls.Add($policySettingslableBox)

$lbListView = new-object System.Windows.Forms.ListView
$lbListView.Location = new-object System.Drawing.Size(450,20)
$lbListView.size = new-object System.Drawing.Size(350,100)
$lbListView.LabelEdit = $True
$lbListView.AllowColumnReorder = $True
$lbListView.CheckBoxes = $False
$lbListView.FullRowSelect = $True
$lbListView.GridLines = $True
$lbListView.View = "Details"
$lbListView.Sorting = "Ascending"
$form.controls.Add($lbListView)

# Add RadioButtons
$rbinc = new-object System.Windows.Forms.RadioButton
$rbinc.Location = new-object System.Drawing.Size(150,50)
$rbinc.size = new-object System.Drawing.Size(220,17)
$rbinc.Checked = $true
$rbinc.Text = "All objects Policy Query matches"
$form.Controls.Add($rbinc)

$rbinc1 = new-object System.Windows.Forms.RadioButton
$rbinc1.Location = new-object System.Drawing.Size(150,70)
$rbinc1.size = new-object System.Drawing.Size(220,17)
$rbinc1.Checked = $false
$rbinc1.Text = "Only objects with Apply policy enabled"
$form.Controls.Add($rbinc1)

$rbinc2 = new-object System.Windows.Forms.RadioButton
$rbinc2.Location = new-object System.Drawing.Size(150,90)
$rbinc2.size = new-object System.Drawing.Size(220,17)
$rbinc2.Checked = $false
$rbinc2.Text = "Only objects with Apply policy disabled"
$form.Controls.Add($rbinc2)



$exButton1 = new-object System.Windows.Forms.Button
$exButton1.Location = new-object System.Drawing.Size(10,70)
$exButton1.Size = new-object System.Drawing.Size(125,20)
$exButton1.Text = "Search"
$exButton1.Add_Click({SearchforObjects})
$form.Controls.Add($exButton1)

# Add Export Grid Button

$exButton2 = new-object System.Windows.Forms.Button
$exButton2.Location = new-object System.Drawing.Size(10,760)
$exButton2.Size = new-object System.Drawing.Size(125,20)
$exButton2.Text = "Export Grid"
$exButton2.Add_Click({ExportGrid})
$form.Controls.Add($exButton2)



# Add DataGrid View

$dgDataGrid = new-object System.windows.forms.DataGridView
$dgDataGrid.Location = new-object System.Drawing.Size(10,145)
$dgDataGrid.size = new-object System.Drawing.Size(1000,600)
$dgDataGrid.AutoSizeRowsMode = "AllHeaders"
$form.Controls.Add($dgDataGrid)

$form.Text = "Address Policy GUI"
$form.size = new-object System.Drawing.Size(1200,750)
$form.autoscroll = $true
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()

Friday, September 10, 2010

Cleaning up and Reporting on Items based on the Subject on Exchange using a EWS search with Powershell

If your unaware there is nasty mass mailer virus outbreak at the moment if you haven't already i would strongly urge you to visit and implement the measures in http://social.technet.microsoft.com/wiki/contents/articles/worm-win32-visal-b.aspx

The above gives the best and quickest method for cleaning the affects of an outbreak using the EMS export-mailbox cmdlet but i thought I'd give some alternate methods using EWS . I can't guarantee any of these methods like i would with Export-mailbox and I would expect them to be slower (and possibly very slow) but they may come in use especially if your access is limited but you can use EWS Impersonation or if you looking to do some reporting based on subject.

Finding Items based on there Subject is relatively simple in EWS and involves the use of a Search filter or if your lucky enough to be using Exchange 2010 you can make use of AQS which I blogged about earlier here . With a virus outbreak they generally start on a particular date so it makes sense to make your query more efficient by only searching for items that where received in the last 7 days. So a search filter for 2007 would look like

$Sfir = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::Subject, $subjecttoSearch)
$Sflt = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsGreaterThan([Microsoft.Exchange.WebServices.Data.ItemSchema]::DateTimeReceived, $MailDate)
$sfCollection = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+SearchFilterCollection([Microsoft.Exchange.WebServices.Data.LogicalOperator]::And);
$sfCollection.add($Sfir)


The AQS query for the same thing would look like AQSQuery = "Received:this week AND subject:`"" + $subjecttoSearch + "`"" . There is one very important catch with the AQS query is that it will do a exact phrase match but not a exact subject match. So for example if you where trying to match the phrase "here you have" in the subject it would also match "here you have a example of" there are different operators that you can use in AQS but not one that i could find that would do a exact property match(I anybody finds an AQS query that works (emphasis on works rather then should work based on the documentation) please let me know). To get around this and also as a double check before you delete anything a simple logic if statement can be used in your code.

When it comes to deleting Items in the EWS Managed API you can use the Delete method of the Item object which would basically call a deleteItem operation each time you called this method or a faster method is create an array of itemid's that you would like deleted and then use the batch deleteItems method which is the method I've used.

The scripts are setup to use Delegation but if you want to use impersonation instead just add the following line

$service.ImpersonatedUserId = New-Object Microsoft.Exchange.WebServices.Data.ImpersonatedUserId([Microsoft.Exchange.WebServices.Data.ConnectingIdType]::SmtpAddress, $MailboxName)

Because searching like this is not only usefully for deleting Items but for also reporting on things I've created a reporting version of the scripts as well that just emails a html report of a what it finds within a mailbox and includes the transport headers of the messages in question. The script I've created does a deep traversal to get the folderid details and then a single finditems on each folder the 2007 version using searchfilters and the 2010 version use AQS.

The delete scripts do a SoftDelete meaning the item goes into the dumpster of the folder it was deleted from (you could make this a hard delete).

Before using these script you need to customize the following variable with the subject you want to search for the example I've used is I hate spam

$subjecttoSearch = "I hate Spam"

You also need to set the mailbox name to run against or change it to $arg[0] and run the script with a cmdline argument. In the reporting script the To,From and SMTP server needs to be set.

I've put a download of all 4 scripts here the 2007 delete version looks like.

$MailboxName = "user@mailbox.com"

$subjecttoSearch = "I hate Spam"
$MailDate = [system.DateTime]::Now.AddDays(-7)

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

$deleteMode = [Microsoft.Exchange.WebServices.Data.DeleteMode]::SoftDelete
$aptcancelMode = [Microsoft.Exchange.WebServices.Data.SendCancellationsMode]::SendToNone
$taskmode = [Microsoft.Exchange.WebServices.Data.AffectedTaskOccurrence]::AllOccurrences

$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())

$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 = $Propset
$ffResponse = $rfRootFolder.FindFolders($fvFolderView);

foreach ($ffFolder in $ffResponse.Folders){
"Checking " + $ffFolder.DisplayName
$Sfir = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::Subject, $subjecttoSearch)
$Sflt = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsGreaterThan([Microsoft.Exchange.WebServices.Data.ItemSchema]::DateTimeReceived, $MailDate)
$sfCollection = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+SearchFilterCollection([Microsoft.Exchange.WebServices.Data.LogicalOperator]::And);
$sfCollection.add($Sfir)
$sfCollection.add($Sflt)
$ivview = new-object Microsoft.Exchange.WebServices.Data.ItemView(20000)
$frFolderResult = $ffFolder.FindItems($sfCollection,$ivview)
$Itembatch = [activator]::createinstance(([type]'System.Collections.Generic.List`1').makegenerictype([Microsoft.Exchange.WebServices.Data.ItemId]))
foreach ($miMailItems in $frFolderResult.Items){
if ($miMailItems.Subject -eq $subjecttoSearch){
"****** Found" + $miMailItems.Subject
$Itembatch.add($miMailItems.Id)
}
}
"Number of Items found in folder : " + $Itembatch.Count
if($Itembatch.Count -ne 0){
"Deleting " + $Itembatch.Count + " Items"
$DelResponse = $service.DeleteItems($Itembatch,$deleteMode,$aptcancelMode,$taskmode)
foreach ($dr in $DelResponse) {
$dr.Result
}
}

}







Thursday, September 09, 2010

Getting the Folder Size of a folder using EWS

One of the things missing from the standard folder properties when you use EWS to access a mailbox or public folder is the size of the folder. Those that have used other API's to do mailbox and public folder size reporting should already know about the extended mapi property PR_Extended_Message_Size this is what you also need to use in EWS to get the folder size to define this in C# you can use

ExtendedPropertyDefinition PR_Extended_Message_Size = new ExtendedPropertyDefinition(3592, MapiPropertyType.Long);
PropertySet psPropertySet = new PropertySet(BasePropertySet.FirstClassProperties) { PR_Extended_Message_Size };
Folder Inbox = Folder.Bind(service, WellKnownFolderName.Inbox, psPropertySet);
Object FolderSize = null;
if (Inbox.TryGetProperty(PR_Extended_Message_Size, out FolderSize)) {
Console.WriteLine(FolderSize);
}


You can also make use of this in a script if you want an alternative to Get-MailboxFolderStatistics eg if you wanted to show the size of ever folder in a mailbox you can use the following script I've put a download of this here

$MailboxName = "user@domain.com"

$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())

$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);
$PR_MESSAGE_SIZE_EXTENDED = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(3592, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Integer)
$Propset = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)
$Propset.add($PR_MESSAGE_SIZE_EXTENDED)
$fvFolderView.Traversal = [Microsoft.Exchange.WebServices.Data.FolderTraversal]::Deep
$fvFolderView.PropertySet = $Propset
$ffResponse = $rfRootFolder.FindFolders($fvFolderView);

foreach ($ffFolder in $ffResponse.Folders){
$fldObject = "" | select FolderName,FolderSize
$folderSize = $null
$ptProptest2 = $ffFolder.TryGetProperty($PR_MESSAGE_SIZE_EXTENDED, [ref]$folderSize)
$fldObject.FolderName = $ffFolder.DisplayName
$fldObject.FolderSize = [INT]$folderSize
$ReportingCollection += $fldObject
}
$ReportingCollection

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)