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)

2 comments:

DJ said...

After getting the report, I am looking at trying to restore the items back to the folders by clearing the ptagDeletedOnFlag but I can't seem to find the way to do that. I have tried doing:

$Item.RemoveExtendedProperty($PR_DELETED_ON)
$Item.update()

or doing:

$Item.SetExtendedProperty($PR_DELETED_ON,"")
$Item.update()

But neither of them work, any ideas?

Glen said...

That wont work there is no way to restore an Item in 2007 using EWS. The only way to copy it using another API like WebDAV or Mapi

cheers
Glen