Thursday, December 06, 2007

Mailbox Folder Size comparison Powershell GUI

While updating the mailbox size GUI the other week and getting a grip on the Get-Mailboxfolderstatitics cmdlet I had a few ideas of some other cool stuff you could do with this. For example if I used this cmdlet to get all the mailbox folders sizes on a server and then put them into a ADO.NET datatable I then have the ability to do a whole bunch of funky data manipulation that can be used to produce a whole bunch of useful information. For example I can show in a datagird a comparison between the sizes of everybody’s inbox, Sent Item, Deleted Item’s etc. Which allows me to answer questions like?

Who has the largest inbox on the server
Which users have too many items in their Inbox
How much each user has sitting in their deleted Items folder

One of the Properties Get-Mailboxfolderstatitics also allows you to see is the age of the newest item in the folder. So this can come in handy if you’re looking for mailboxes that might no longer be in use because you will be able to see and compare both what the date of newest item is in their inbox and sent items folder.

The script itself reuses a lot of the code from the mailbox size GUI the main difference is that it loops through ever mailbox on the server you select and runs the Get-Mailboxfolderstatitics on each mailbox. The results are then loaded into a ADO.NET datatable and then a dataview of this table is used with different Row Fitlers to filter out the particular folders you want to look at. To come up with the list of selected folders a hash table is used during the initial table fill and if the same folder name is found 5 times it’s added to the list of selectable folders. If this selectable folders list becomes a bit unmanageable you could tweak this value. The export code has been carried over from the mailboxsize gui so this allows you to export the results to a CSV file.

To cater for mailbox server with more the 1000 mailbox the -ResultSize Unlimited is used with the get-mailbox cmdlet. Also the –IncludeOldestAndNewestItems is used with Get-Mailboxfolderstatitics to ensure that the folder datetimes are included. This is something that was changed in SP1 so to cater for this I created two versions of the script a pre sp1 and post sp1 version.

Because the Get-Mailboxfolderstatitics cmdlet takes a while to run if you have a large number of mailboxes this script can take some time to process. It does write out its progress to the powershell cmd window if your worried the script may have hung. By default it will show a comparison of the inbox folder if your using a localized version of Exchange that has the inbox name in the local language then it will show you a blank screen but the folder list should get populated with your localized foldernames and the rest of the code should work fine.

I’ve put a download of the script here the SP1 version looks like

[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
$form = new-object System.Windows.Forms.form
function processmailboxes(){
$fsTable.clear()
get-mailbox -Server $snServerNameDrop.SelectedItem.ToString() -ResultSize Unlimited | ForEach-Object{
$siSIDToSearch = get-user $_.DisplayName
write-host $_.DisplayName
Get-MailboxFolderStatistics $siSIDToSearch.SamAccountName.ToString() -IncludeOldestAndNewestItems | ForEach-Object{
$ficount = 0
$flastaccess = ""
$fisize = 0
$fsisize = 0
$fscount = 0
$fname = $_.Name
if ($fnhash.ContainsKey($_.Name)){
$fnhash[$_.Name] = [int]$fnhash[$_.Name] + 1
}
else {
if ($_.Name -ne $null){ $fnhash.add($_.Name,1)}
}
if ($_.FolderSize -ne $null){$fsisize = [math]::round(($_.FolderSize/1mb),2)}
if ($_.ItemsInFolder -ne $null){$ficount = $_.ItemsInFolder}
if ($_.NewestItemReceivedDate -ne $null){$flastaccess = $_.NewestItemReceivedDate}
if ($_.ItemsInFolderAndSubfolders -ne $null){$fscount = $_.ItemsInFolderAndSubfolders}
if ($_.FolderAndSubfolderSize -ne $null){$fsisize = [math]::round(($_.FolderAndSubfolderSize/1mb),2)}
$fsTable.Rows.add($siSIDToSearch.SamAccountName.ToString(),$siSIDToSearch.DisplayName.ToString(),$fname,$ficount,$fsisize,$fscount,$fsisize,$flastaccess)

}
}
$arylst = new-object System.Collections.ArrayList
$Dataveiw.RowFilter = "FolderName = 'Inbox'"
$dgDataGrid.DataSource = $Dataveiw
foreach ($key in $fnhash.keys){
if ($fnhash[$key] -gt 5) {
$arylst.Add($key)
write-host $key
}}
$arylst.Sort()
foreach ($val in $arylst){
$fnTypeDrop.Items.Add($val)
}

}

function selectfolder(){
$Dataveiw.RowFilter = "FolderName = '" + $fnTypeDrop.SelectedItem.ToString() + "'"
$dgDataGrid.DataSource = $Dataveiw

}

function ExportFScsv{

$exFileName = new-object System.Windows.Forms.saveFileDialog
$exFileName.DefaultExt = "csv"
$exFileName.Filter = "csv files (*.csv)|*.csv"
$exFileName.InitialDirectory = "c:\temp"
$exFileName.ShowDialog()
if ($exFileName.FileName -ne ""){
$logfile = new-object IO.StreamWriter($exFileName.FileName,$true)
$logfile.WriteLine("SamAccountName,DisplayName,FolderName,# Items,Folder Size(MB),# Items + Sub,Folder Size + Sub(MB),Last Item Received")
foreach($row in $dgDataGrid.Rows){
if ($row.cells[0].Value -ne $null){
$logfile.WriteLine("`"" + $row.cells[0].Value.ToString() + "`",`"" + $row.cells[1].Value.ToString() + "`",`"" + $row.cells[2].Value.ToString() + "`"," + $row.cells[3].Value.ToString() + "," + $row.cells[4].Value.ToString() + "," + $row.cells[5].Value.ToString() + "," + $row.cells[6].Value.ToString() + "," + $row.cells[7].Value.ToString())
}
}
$logfile.Close()
}
}

$fnhash = @{ }
$Dataset = New-Object System.Data.DataSet
$fsTable = New-Object System.Data.DataTable
$fsTable.TableName = "Folder Sizes"
$fsTable.Columns.Add("SamAccountName")
$fsTable.Columns.Add("DisplayName")
$fsTable.Columns.Add("FolderName")
$fsTable.Columns.Add("# Items",[int64])
$fsTable.Columns.Add("Folder Size(MB)",[int64])
$fsTable.Columns.Add("# Items + Sub",[int64])
$fsTable.Columns.Add("Folder Size + Sub(MB)",[int64])
$fsTable.Columns.Add("Last Item Received")
$Dataset.tables.add($fsTable)
$Dataveiw = New-Object System.Data.DataView($fsTable)

# Add Server DropLable
$snServerNamelableBox = new-object System.Windows.Forms.Label
$snServerNamelableBox.Location = new-object System.Drawing.Size(10,20)
$snServerNamelableBox.size = new-object System.Drawing.Size(80,20)
$snServerNamelableBox.Text = "Server Name"
$form.Controls.Add($snServerNamelableBox)

# Add Server Drop Down
$snServerNameDrop = new-object System.Windows.Forms.ComboBox
$snServerNameDrop.Location = new-object System.Drawing.Size(90,20)
$snServerNameDrop.Size = new-object System.Drawing.Size(100,30)
get-mailboxserver | ForEach-Object{$snServerNameDrop.Items.Add($_.Name)}
$form.Controls.Add($snServerNameDrop)

# folder Size Button

$fsizeButton = new-object System.Windows.Forms.Button
$fsizeButton.Location = new-object System.Drawing.Size(200,19)
$fsizeButton.Size = new-object System.Drawing.Size(120,23)
$fsizeButton.Text = "Get Folder Sizes"
$fsizeButton.visible = $True
$fsizeButton.Add_Click({processmailboxes})
$form.Controls.Add($fsizeButton)

# Add Folder Name Type Drop Down
$fnTypeDrop = new-object System.Windows.Forms.ComboBox
$fnTypeDrop.Location = new-object System.Drawing.Size(350,20)
$fnTypeDrop.Size = new-object System.Drawing.Size(150,30)
$fnTypeDrop.Add_SelectedValueChanged({if ($snServerNameDrop.SelectedItem -ne $null){SelectFolder}})
$form.Controls.Add($fnTypeDrop)

# Add Export FolderSize Button

$exButton1 = new-object System.Windows.Forms.Button
$exButton1.Location = new-object System.Drawing.Size(10,660)
$exButton1.Size = new-object System.Drawing.Size(125,20)
$exButton1.Text = "Export Folder Sizes"
$exButton1.Add_Click({ExportFScsv})
$form.Controls.Add($exButton1)


# Add DataGrid View

$dgDataGrid = new-object System.windows.forms.DataGridView
$dgDataGrid.Location = new-object System.Drawing.Size(10,50)
$dgDataGrid.size = new-object System.Drawing.Size(900,600)
$form.Controls.Add($dgDataGrid)

$form.Text = "Exchange 2007 Mailbox Folder Compare Folder Size Form"
$form.size = new-object System.Drawing.Size(1000,620)
$form.autoscroll = $true
$form.topmost = $true
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()

Friday, November 23, 2007

Exchange 2007 Mailbox Size Powershell Form Script version 3

Version 4 has now been released that includes mailbox quotas here

I've been promising to update this for a while to fix a few issues and fulfill of few requests . I've switched the FolderSize code from using EWS to query the foldersizes to now use the Get-MailboxFolderStatistics Cmdlet instead. This makes the code a lot more functional and easy to use and doesn't require all that messing around with Impersonation and SSL (I kind of missed the whole Get-MailboxFolderStatistics cmdlet but it was fun building the EWS stuff anyway).

I've also now used DataGrids to display the result which means it is now sortable (Yeah!) you can now sort to your hearts content on any of the displayed columns. The only thing with Datagrid 's because of the threading issue with Powershell i couldn't use the click event so to display the foldersize you need to select the mailbox you want to show and then click the Get Folder Size button.

Another feature I've added is the ability to export both the Mailbox Size Grid and the Folder size grid. There are buttons bellow both the Datagrids that will open a dialogue box to allow you to choose a location to export to and produce a CSV file of the current results in the datagrid.

The last feature I've added is the ability to show just the Disconnected mailboxes so this will allow you to report just the sizes of all the Disconnected Mailboxes on you server. The one thing you cant do with a disconnected mailbox is show the foldersizes.

One last bug fix was to switch to using 64 Bit integers to fix a problem when the folder sizes go over 2 GB. I haven't been able to test this properly yet so please let me know if its still an issue.

The only real requirement now to run this code is that you run it from within the Exchange Management Shell because it uses a couple of the Exchange cmdlets the user running the code needs to have a least Exchange View-Only Administrator role rights.

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

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


function getMailboxSizes(){
$msTable.clear()

if ($mtTypeDrop.SelectedItem -ne $null){
if ($mtTypeDrop.SelectedItem.ToString() -eq "Disconnected"){
get-mailboxstatistics -Server $snServerNameDrop.SelectedItem.ToString() | Where {$_.DisconnectDate -ne $null} | ForEach-Object{
$icount = 0
$tisize = 0
$disize = 0
if ($_.DisplayName -ne $null){$dname = $_.DisplayName}
if ($_.ItemCount -ne $null){$icount = $_.ItemCount}
if ($_.TotalItemSize.Value.ToMB() -ne $null){$tisize = $_.TotalItemSize.Value.ToMB()}
if ($_.TotalDeletedItemSize.Value.ToKB() -ne $null){$disize = $_.TotalDeletedItemSize.Value.ToKB()}
$msTable.Rows.add($dname,$icount,$tisize,$disize)
}
}
else{ get-mailboxstatistics -Server $snServerNameDrop.SelectedItem.ToString() | Where {$_.DisconnectDate -eq $null} | ForEach-Object{
$icount = 0
$tisize = 0
$disize = 0
if ($_.DisplayName -ne $null){$dname = $_.DisplayName}
if ($_.ItemCount -ne $null){$icount = $_.ItemCount}
if ($_.TotalItemSize.Value.ToMB() -ne $null){$tisize = $_.TotalItemSize.Value.ToMB()}
if ($_.TotalDeletedItemSize.Value.ToKB() -ne $null){$disize = $_.TotalDeletedItemSize.Value.ToKB()}
$msTable.Rows.add($dname,$icount,$tisize,$disize)
}

}
}
else{
get-mailboxstatistics -Server $snServerNameDrop.SelectedItem.ToString() | ForEach-Object{
$icount = 0
$tisize = 0
$disize = 0
if ($_.DisplayName -ne $null){$dname = $_.DisplayName}
if ($_.ItemCount -ne $null){$icount = $_.ItemCount}
if ($_.TotalItemSize.Value.ToMB() -ne $null){$tisize = $_.TotalItemSize.Value.ToMB()}
if ($_.TotalDeletedItemSize.Value.ToKB() -ne $null){$disize = $_.TotalDeletedItemSize.Value.ToKB()}
$msTable.Rows.add($dname,$icount,$tisize,$disize)
}

}
write-host $fstring

$dgDataGrid.DataSource = $msTable

}


function GetFolderSizes(){
$fsTable.clear()
$snServername = $snServerNameDrop.SelectedItem.ToString()
write-host $dgDataGrid.CurrentCell.RowIndex
$siSIDToSearch = get-user $msTable.DefaultView[$dgDataGrid.CurrentCell.RowIndex][0]
write-host $siSIDToSearch.SamAccountName.ToString()
Get-MailboxFolderStatistics $siSIDToSearch.SamAccountName.ToString() | ForEach-Object{
$ficount = 0
$fisize = 0
$fsisize = 0
$fscount = 0
$fname = $_.Name
if ($_.FolderSize -ne $null){$fsisize = [math]::round(($_.FolderSize/1mb),2)}
if ($_.ItemsInFolder -ne $null){$ficount = $_.ItemsInFolder}
if ($_.ItemsInFolderAndSubfolders -ne $null){$fscount = $_.ItemsInFolderAndSubfolders}
if ($_.FolderAndSubfolderSize -ne $null){$fsisize = [math]::round(($_.FolderAndSubfolderSize/1mb),2)}
$fsTable.Rows.add($fname,$ficount,$fsisize,$fscount,$fsisize)
}
$dgDataGrid1.DataSource = $fsTable
}

function ExportMBcsv{

$exFileName = new-object System.Windows.Forms.saveFileDialog
$exFileName.DefaultExt = "csv"
$exFileName.Filter = "csv files (*.csv)|*.csv"
$exFileName.InitialDirectory = "c:\temp"
$exFileName.ShowDialog()
if ($exFileName.FileName -ne ""){
$logfile = new-object IO.StreamWriter($exFileName.FileName,$true)
$logfile.WriteLine("UserName,# Items,MB Size(MB),DelItems(KB)")
foreach($row in $msTable.Rows){
$logfile.WriteLine("`"" + $row[0].ToString() + "`"," + $row[1].ToString() + "," + $row[2].ToString() + "," + $row[3].ToString())
}
$logfile.Close()
}
}

function ExportFScsv{

$exFileName = new-object System.Windows.Forms.saveFileDialog
$exFileName.DefaultExt = "csv"
$exFileName.Filter = "csv files (*.csv)|*.csv"
$exFileName.InitialDirectory = "c:\temp"
$exFileName.ShowDialog()
if ($exFileName.FileName -ne ""){
$logfile = new-object IO.StreamWriter($exFileName.FileName,$true)
$logfile.WriteLine("DisplayName,# Items,Folder Size(MB),# Items + Sub,Folder Size + Sub(MB)")
foreach($row in $fsTable.Rows){
$logfile.WriteLine("`"" + $row[0].ToString() + "`"," + $row[1].ToString() + "," + $row[2].ToString() + "," + $row[3].ToString() + "," + $row[4].ToString())
}
$logfile.Close()
}
}

$form = new-object System.Windows.Forms.form
$global:LastFolder = ""
# Add DataTable

$Dataset = New-Object System.Data.DataSet
$fsTable = New-Object System.Data.DataTable
$fsTable.TableName = "Folder Sizes"
$fsTable.Columns.Add("DisplayName")
$fsTable.Columns.Add("# Items",[int64])
$fsTable.Columns.Add("Folder Size(MB)",[int64])
$fsTable.Columns.Add("# Items + Sub",[int64])
$fsTable.Columns.Add("Folder Size + Sub(MB)",[int64])
$Dataset.tables.add($fsTable)

$msTable = New-Object System.Data.DataTable
$msTable.TableName = "Mailbox Sizes"
$msTable.Columns.Add("UserName")
$msTable.Columns.Add("# Items")
$msTable.Columns.Add("MB Size(MB)",[int64])
$msTable.Columns.Add("DelItems(KB)",[int64])
$Dataset.tables.add($msTable)

# Add Server DropLable
$snServerNamelableBox = new-object System.Windows.Forms.Label
$snServerNamelableBox.Location = new-object System.Drawing.Size(10,20)
$snServerNamelableBox.size = new-object System.Drawing.Size(80,20)
$snServerNamelableBox.Text = "ServerName"
$form.Controls.Add($snServerNamelableBox)

# Add Server Drop Down
$snServerNameDrop = new-object System.Windows.Forms.ComboBox
$snServerNameDrop.Location = new-object System.Drawing.Size(90,20)
$snServerNameDrop.Size = new-object System.Drawing.Size(100,30)
get-mailboxserver | ForEach-Object{$snServerNameDrop.Items.Add($_.Name)}
$snServerNameDrop.Add_SelectedValueChanged({getMailboxSizes})
$form.Controls.Add($snServerNameDrop)

# Add Mailbox Type DropLable
$mtTypeDroplableBox = new-object System.Windows.Forms.Label
$mtTypeDroplableBox.Location = new-object System.Drawing.Size(200,20)
$mtTypeDroplableBox.size = new-object System.Drawing.Size(80,20)
$mtTypeDroplableBox.Text = "MailboxType"
$form.Controls.Add($mtTypeDroplableBox)

# Add Mailbox Type Drop Down
$mtTypeDrop = new-object System.Windows.Forms.ComboBox
$mtTypeDrop.Location = new-object System.Drawing.Size(290,20)
$mtTypeDrop.Size = new-object System.Drawing.Size(100,30)
$mtTypeDrop.Items.Add("Disconnected")
$mtTypeDrop.Items.Add("Connected")
$mtTypeDrop.Add_SelectedValueChanged({if ($snServerNameDrop.SelectedItem -ne $null){getMailboxSizes}})
$form.Controls.Add($mtTypeDrop)

# Add Export MB Button

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

# Add Export FG Button

$exButton2 = new-object System.Windows.Forms.Button
$exButton2.Location = new-object System.Drawing.Size(500,560)
$exButton2.Size = new-object System.Drawing.Size(135,20)
$exButton2.Text = "Export FolderSize Grid"
$exButton2.Add_Click({ExportFScsv})
$form.Controls.Add($exButton2)

# Add DataGrid View

$dgDataGrid = new-object System.windows.forms.DataGridView
$dgDataGrid.Location = new-object System.Drawing.Size(10,50)
$dgDataGrid.size = new-object System.Drawing.Size(450,500)
$form.Controls.Add($dgDataGrid)

$dgDataGrid1 = new-object System.windows.forms.DataGridView
$dgDataGrid1.Location = new-object System.Drawing.Size(500,50)
$dgDataGrid1.size = new-object System.Drawing.Size(450,500)
$form.Controls.Add($dgDataGrid1)

# folder Size Button

$fsizeButton = new-object System.Windows.Forms.Button
$fsizeButton.Location = new-object System.Drawing.Size(500,19)
$fsizeButton.Size = new-object System.Drawing.Size(120,23)
$fsizeButton.Text = "Get Folder Size"
$fsizeButton.visible = $True
$fsizeButton.Add_Click({GetFolderSizes})
$form.Controls.Add($fsizeButton)



$form.Text = "Exchange 2007 Mailbox Size Form"
$form.size = new-object System.Drawing.Size(1000,620)
$form.autoscroll = $true
$form.topmost = $true
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()

Thursday, November 22, 2007

Script to find and fix mismatches between the Reply Address and Active Directory Mail property

The following script is an ADSI powershell script that can be used check and update any user accounts where you have a mismatch between the mail property in Active Directory and the Reply address that has been set for a users mailbox. If you’re running Exchange 2007 you might want to look at using Set-Mailbox –ApplyMandatoryProperties instead.

This script is pretty simple it will look at any mail-enabled users and check the active directory mail attribute against the Identified Reply address. If any mismatches are found it will then prompt to ask if the user wants to update the Active Directory Mail property to be the same as the Reply Address. To guard against null address if a null property is founds the script will just echo out the account that is have a null property.

I’ve created two version of the script the first just uses ADSI so it will work on any version of Exchange and will to scan the current domain the executing workstation is in the second uses get-mailbox in Exchange 2007 which may be more helpful if you have a multi-domain forest (this will only work with the Exchange Management Shell).

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

$root = [ADSI]'LDAP://RootDSE'
$dfDefaultRootPath = "LDAP://" + $root.DefaultNamingContext.tostring()
$dfRoot = [ADSI]$dfDefaultRootPath
$gfGALQueryFilter = "(&(&(&(& (mailnickname=*)(objectCategory=person)(objectClass=user)(msExchHomeServerName=*)))))"
$dfsearcher = new-object System.DirectoryServices.DirectorySearcher($dfRoot)
$dfsearcher.PageSize = 900
$dfsearcher.Filter = $gfGALQueryFilter
$srSearchResult = $dfsearcher.FindAll()
$mbcount = 0
$upall = 0
$skippall = 0
foreach ($emResult in $srSearchResult) {
$mbcount++
if (($mbcount % 100) -eq 0 ) {$mbcount.ToString() + " Mailboxes Processed"}
$uoUserobject = New-Object System.DirectoryServices.directoryentry
$uoUserobject = $emResult.GetDirectoryEntry()
$raReplayAddress = ""
foreach($maMailAddress in $uoUserobject.Proxyaddresses){
if ($maMailAddress.indexofany("SMTP:") -eq 0){
$raReplayAddress = $maMailAddress.ToString().Replace("SMTP:","")
}
}
if ($uoUserobject.mail.value -ne $null -band $raReplayAddress -ne ""){
if($uoUserobject.mail.Value.ToLower() -ne $raReplayAddress.ToLower()){
"MissMatch " + $uoUserobject.mail.Value.ToLower() + " " + $raReplayAddress.ToLower()
if ($upall -eq 0 -band $skippall -eq 0){
$answer = Read-Host "Do you want to modify this Object [Y] Yes [A] Yes to All [N] No [L] No to all "
switch ($Answer)
{
"Y" { if ($raReplayAddress -ne ""){
$uoUserobject.mail.Value = $raReplayAddress
$uoUserobject.SetInfo()
"Address Updated"}
else {"Proxy Address Null !! not updating"}
}
"" { "Not updating"}
"A" { if ($raReplayAddress -ne ""){
$uoUserobject.mail.Value = $raReplayAddress
$uoUserobject.SetInfo()
$upall = 1
"Address Updated"}
else {"Proxy Address Null !! not updating"}
}
"N" {"Not updating" }
"L" {"Not updating"
$skippall = 1
}
}

}
else {if ($upall -eq 1 -band $skippall -eq 0){
if ($raReplayAddress -ne ""){
$uoUserobject.mail.Value = $raReplayAddress
$uoUserobject.SetInfo()
"Address Updated"}
else {"Proxy Address Null !! not updating"}
}
}
}
}
else{
if ($uoUserobject.mail.value -eq $null){"**** Null Ad Mail Property : " + $uoUserobject.name}
if ($raReplayAddress -eq ""){"***** Null Proxyaddress : " + $uoUserobject.name}

}
}

"Total number of Mailboxes Processed :" + $mbcount


Friday, October 26, 2007

Uploading a document into a SharePoint Document library from a Exchange 2007 Transport Agent.

SharePoint servers these days are multiplying like rabbits while Sharepoint is a great place to put and index information breaking habits and making sure you have a central repository of things is always a challenge. What this agent does is looks at any email which has any Pdf attachments that have the word “quote” in the filename if these emails have any external recipients this document will get uploaded into a specific SharePoint’s sites shared documents library. So this allows me in this case to have a central repository of all the quotes sent out via email. To make sure the documents that are being uploaded have a unique filename the processing time is added to the filename of the document at the time it’s uploaded to SharePoint.

Because programmatically uploading a document into SharePoint isn’t that straight forward To upload the document into the SharePoint Document library itself I’ve use the S.S. Ahmed cool WSUploadservice which is free,easy to install and use you can get the source from codeplex http://www.codeplex.com/wsuploadservice.

Stepping through the code

The first part of this code is responsible for doing the recipient check as I was only interested in capturing email that was going to be delivered to a external recipient I need some code that would first check the recipient list.

ArrayList dmarray = new ArrayList();
dmarray.Add("domain.com");
Boolean pmProcMessage = false;
EmailMessage emMessage = e.MailItem.Message;
foreach (EnvelopeRecipient recp in e.MailItem.Recipients) {
if (dmarray.Contains(recp.Address.DomainPart)== false) {
pmProcMessage = true;
}

}

This code loops though the recipients of the message and checks the domainpart of the recipient address to see if it matches any of the internal domains. I could have quiered active directory to get the internal domain address information but for performance reasons Its probably better to either hardcode them in the code or put them in a registry key.

The next section of code processes any attachments on a message that is identiified as having a external recipeint. The following line will find any attachments that have a pdf attachment with quote in the filename

if (feFileExtension.ToLower() == ".pdf" | atAttach.FileName.ToLower().IndexOf("quote") != -1)

The next section of code is what performs the Sharepoint upload using the WSUploadservice service. Because Transport agents run under the \NetworkService account privileges to be able to successfully upload a document to a SharePoint site alternate credentials or impersonation must be used. I took the easy way out by using hard coded credentials in the code itself.

string spDocumentLibrary = "http://server/sites/Quotes/Shared%20Documents";
string strUserName = "username";
string strPassword = "password";
string strDomain = "domain";
System.Net.CredentialCache spUploadUserCredentials = new System.Net.CredentialCache();
spUploadUserCredentials.Add(new System.Uri(spDocumentLibrary),
"NTLM", new System.Net.NetworkCredential(strUserName, strPassword, strDomain) ); spUploader.PreAuthenticate = true;
spUploader.Credentials = spUploadUserCredentials;
urUploadResult = spUploader.UploadDocument(fnFileName.ToLower().Replace(".pdf", nfNewFileName), atBytes, spDocumentLibrary);
return urUploadResult;

To use this code you first need to have the WSuploadservice installed on your target SharePoint server. You then need to configure the URL of the SharePoint document library you want to use as well as the user account detail for the account that is going to upload the document to SharePoint and also set your local email domains in the code.

I would consider what this code is doing is pretty heavy lifting so before doing this type of thing in production you might want to ask yourself will it scale?. Its something you can really only determine yourself though your own testing based on the size and volume of information you’re going to process.

I’ve put a download of this code here the full agent looks like

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Diagnostics;
using Microsoft.Exchange.Data.Transport;
using Microsoft.Exchange.Data.Mime;
using Microsoft.Exchange.Data.Transport.Email;
using Microsoft.Exchange.Data.Transport.Smtp;
using Microsoft.Exchange.Data.Transport.Routing;
using Microsoft.Exchange.Data.Common;
using SharepointAttachmentUploadAgent.UploadwebService;

namespace msgdevExchangeRoutingAgents
{
public class SharepointAttachmentUploadFactory : RoutingAgentFactory
{
public override RoutingAgent CreateAgent(SmtpServer server)
{
RoutingAgent emSpUpload = new SharepointAttachUploadAgent();
return emSpUpload;
}
}
}
public class SharepointAttachUploadAgent : RoutingAgent
{
public SharepointAttachUploadAgent()
{
base.OnSubmittedMessage += new SubmittedMessageEventHandler(SharepointAttachUploadAgent_OnSubmittedMessage);
}

void SharepointAttachUploadAgent_OnSubmittedMessage(SubmittedMessageEventSource source, QueuedMessageEventArgs e)
{
ArrayList dmarray = new ArrayList();
dmarray.Add("domain.com");
Boolean pmProcMessage = false;
EmailMessage emMessage = e.MailItem.Message;
foreach (EnvelopeRecipient recp in e.MailItem.Recipients) {
if (dmarray.Contains(recp.Address.DomainPart)== false) {
pmProcMessage = true;
}

}
if (pmProcMessage == true) { ProcessMessage(emMessage); }
}
public static byte[] ReadFully(Stream stream, int initialLength)
{
// ref Function from http://www.yoda.arachsys.com/csharp/readbinary.html
// If we've been passed an unhelpful initial length, just
// use 32K.
if (initialLength < initiallength =" 32768;" buffer =" new" read =" 0;" chunk =" stream.Read(buffer,"> 0)
{
read += chunk;

// If we've reached the end of our buffer, check to see if there's
// any more information
if (read == buffer.Length)
{
int nextByte = stream.ReadByte();

// End of stream? If so, we're done
if (nextByte == -1)
{
return buffer;
}

// Nope. Resize the buffer, put in the byte we've just
// read, and continue
byte[] newBuffer = new byte[buffer.Length * 2];
Array.Copy(buffer, newBuffer, buffer.Length);
newBuffer[read] = (byte)nextByte;
buffer = newBuffer;
read++;
}
}
// Buffer is now too big. Shrink it.
byte[] ret = new byte[read];
Array.Copy(buffer, ret, read);
return ret;
}
static void ProcessMessage(EmailMessage emEmailMessage)
{
for (int index = emEmailMessage.Attachments.Count - 1; index >= 0; index--)
{
Attachment atAttach = emEmailMessage.Attachments[index];
if (atAttach.EmbeddedMessage == null)
{
if (atAttach.AttachmentType == AttachmentType.Regular & atAttach.FileName != null)
{
// Find Any PDF attachments with Quote in the File Name
if (atAttach.FileName.Length >= 3)
{
String feFileExtension = atAttach.FileName.Substring((atAttach.FileName.Length - 4), 4);
if (feFileExtension.ToLower() == ".pdf" | atAttach.FileName.ToLower().IndexOf("quote") != -1)
{
Stream attachstream = atAttach.GetContentReadStream();
String uploadResult = uploadAttachment(attachstream, atAttach.FileName.ToString());
}

}
}
atAttach = null;
}
}
}
static string uploadAttachment(Stream atAttachStream,String fnFileName) {
string nfNewFileName = "-Sent(" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss")+").pdf";

string urUploadResult;
byte[] atBytes = ReadFully(atAttachStream, (int)atAttachStream.Length);
Files spUploader = new Files();
string spDocumentLibrary = "http://servername/sites/Quotes/Shared%20Documents";
string strUserName = "username";
string strPassword = "password";
string strDomain = "domain";
System.Net.CredentialCache spUploadUserCredentials = new System.Net.CredentialCache();
spUploadUserCredentials.Add(new System.Uri(spDocumentLibrary),
"NTLM",
new System.Net.NetworkCredential(strUserName, strPassword, strDomain)
);
spUploader.PreAuthenticate = true;
spUploader.Credentials = spUploadUserCredentials;
urUploadResult = spUploader.UploadDocument(fnFileName.ToLower().Replace(".pdf", nfNewFileName), atBytes, spDocumentLibrary);
return urUploadResult;


}
}

Thursday, October 18, 2007

Exchange 2007 Content Agent Log Message Tracker Gui

One of the cool features from a logging perspective on Exchange 2007 is the ability to log the SCL of every message when you have the Content Filtering Agent enabled (and also logging enabled). There is a Exchange Management Shell powershell cmdlet for reading these logs called get-agentlog which gives a good cmdline experience but as these logs are something you might want to check on a regular basis and the information contained in them is a little unwieldy to display in a cmdline environment I decided to put together a little GUI to make my life a little easier. I based the GUI on my Exchange 2000/3 WMI message tracker and I was able to carry over a lot of the cool little aggregation functions of this utility using most of the same code with a few tweaks here and there.
What this script does is creates a Winform and adds a whole bunch of controls to that winform such as datapickers, textboxes, checkboxes,labels and buttons. Its then wires ups some functions to the button clicks so that when you click the search button in will run the get-agentlog cmdlet with parameters for the daterange you specified in the GUI. To make sure you only get the events in the log that relate to the content filter it use a filter $_.agent -eq "Content Filter Agent”
The data returned by get-agent log is then added to a ADO.NET data table which is then data bound to a datagridview to display back in the form. Clicking the export button will fire a save-file dialogue box and some code will then export the table results to a csv file.
There are textboxes to allows you to search based on the from and/or To address.
There is a drop down list to allow you to select a SCL level to look at so you can choose to filter by only messages that have been assigned a specific SCL value.
The Extra sections has the aggregate options currently it has four aggregate option is can aggregate
By SCL this shows you by SCL Value how many messages where received between the dates specified
By Receiver will show you grouped by receiver how many messages have been received for each SCL value
By Sender will show you grouped by Sender(P1) how many messages have been received for each SCL value
By Date will show you by Date how many messages have been received for each SCL value.
Currently the script is only designed to be run locally on the Exchange box where the agent logs files are located. The Get-agentlog cmdlet doesn’t have a server parameter although it does have a log file location parameter so this maybe an option if you did want to run this script from a machine other then the Local Exchange server. To run the script is basically straight forward when you run it the script will build the winform and should then present this as an active window. Select the date you want to scan and the click the search button.
This is really only kind of scratching the surface of what you could do with the agent logs im working on a geolcation version so I can show the country origins of SCL values and also a version that will integrated the message tracking logs so I can include subject information in with the SCL. Also there is a lot more then just the content filtering information stored in the agent logs other things such as RBL use and effectiveness can be reported and other Transport agents that log to these files.
I’ve put a download of the code here It’s a bit to large to past verbaitem in the blog the main get-aglog section looks like
$filter = "$_.agent -eq ""Content Filter Agent"""
$dtQueryDT = New-Object System.DateTime $dpTimeFrom.value.year,$dpTimeFrom.value.month,$dpTimeFrom.value.day,$dpTimeFrom2.value.hour,$dpTimeFrom2.value.minute,$dpTimeFrom2.value.second
$dtQueryDTf = New-Object System.DateTime $dpTimeFrom1.value.year,$dpTimeFrom1.value.month,$dpTimeFrom1.value.day,$dpTimeFrom3.value.hour,$dpTimeFrom3.value.minute,$dpTimeFrom3.value.second
if ($extrasettings -eq 0){
get-agentlog -StartDate $dtQueryDT -EndDate $dtQueryDTf | where {$filter} | ForEach-Object {
$exclude = 0
if ($sclFilterboxCheck.Checked -eq $true -band $_.ReasonData -ne $sclFilterboxDrop.SelectedItem){$exclude = 1}
$repstring = ""
$incRec = $false
$p2string = [string]::join(" , ", $_.P2FromAddresses)
$repstring = [string]::join(" , ",$_.Recipients)
if ($snSenderAddressTextBox.text -ne ""){
if ($snSenderAddressTextBox.text.ToString().ToLower() -eq $_.P1FromAddress.ToString().ToLower()){
$incRec = $true
}
}
else {
if ($snRecipientAddressTextBox.text.ToString().ToLower() -ne ""){
if ($repstring -match $snRecipientAddressTextBox.text.ToString().ToLower()){
$incRec = $true
}
}
else{$incRec = $true}
}
if ($incRec -eq $true -band $exclude -eq 0){$ssTable.Rows.Add($_.Timestamp,$_.P1FromAddress,$p2string,$repstring,$_.Action,$_.Reason,$_.ReasonData)}
}
$dgDataGrid.DataSource = $ssTable}
else{
get-agentlog -StartDate $dtQueryDT -EndDate $dtQueryDTf | where {$filter} | ForEach-Object {
if ($GroupbySCL.Checked -eq $true){
[String]$sclival = "SCL " + $_.ReasonData
if ($sclhash.ContainsKey($sclival)){
$tsize = [int]$sclhash[$sclival] + 1
$sclhash[$sclival] = $tsize
}
else{
$sclhash.add($sclival,1)
}

}
if ($GroupByReciever.Checked -eq $true){
foreach($recp in $_.Recipients){
$sclagkey = $recp.ToString().replace("|","-") + "|" + $_.ReasonData
AggResults($sclagkey)
}

}
if ($GroupBySender.Checked -eq $true){
$sclagkey = $_.P1FromAddress.ToString().replace("|","-") + "|" + $_.ReasonData
AggResults($sclagkey)
}
if ($GroupByDate.Checked -eq $true){
$sclagkey = $_.Timestamp.toshortdatestring().replace("|","-") + "|" + $_.ReasonData
AggResults($sclagkey)
}

}

foreach($sclval in $sclhash.keys){
$sclTable.rows.add("",$sclval,$sclhash[$sclval])
}
foreach($adr in $gbhash1.keys){
$daDatarray = $adr.split("|")
$sclTable.rows.add($daDatarray[0],$daDatarray[1],$gbhash1[$adr])
}
$dgDataGrid.DataSource = $sclTable


}
}

Wednesday, October 17, 2007

Adding an attachment in a Transport Agent on Exchange 2007

This is a quick post to give an example of something that from the outset I would have thought would be easy but for some reason took me a little time to get my head around. Having been using CDOSYS/EX for a number of years adding a attachment to a message Is as easy as just using the addattachment method and specifying the filepath and the class will do the rest and add that attachment to the message. When working in a transport agent with the Microsoft.Exchange.Data.Transport.Email class to add an attachment you can use the attachment collections add method. There is an overload for this method that allows you to specify the filename which I thought would mean that this would work like CDOSYS’s addattachment method which didn’t turn out to be the case. Using this overload just gives you a blank attachment object with the filename property set. So to add an attachment within a Transport agent you first need to open the file in question and read-in the stream of bytes from the file and then write that stream into the attachment objects stream and then make sure you flush the attachment stream to have it committed to the message.

The following is a partial code sample of adding a word document as an attachment to a message in a Transport Agent.

void EmailAddAttachmentAgent_OnSubmittedMessage(SubmittedMessageEventSource source, QueuedMessageEventArgs e)
{

Attachment newattach = e.MailItem.Message.Attachments.Add("answer.doc");
Stream fsFileStream1 = new FileStream(@"C:\temp\answer.doc", FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
byte[] bytes1 = ReadFully(fsFileStream1, (int)fsFileStream1.Length);
Stream newattachstream = newattach.GetContentWriteStream();
newattachstream.Write(bytes1, 0, bytes1.Length);
newattachstream.Flush();
newattachstream.Close();

}

public static byte[] ReadFully(Stream stream, int initialLength)

{

// ref Function from http://www.yoda.arachsys.com/csharp/readbinary.html
// If we've been passed an unhelpful initial length, just
// use 32K.
if (initialLength < 1)
{
initialLength = 32768;
}
byte[] buffer = new byte[initialLength];
int read = 0;
int chunk;
while ((chunk = stream.Read(buffer, read, buffer.Length - read)) > 0)

{
read += chunk;

// If we've reached the end of our buffer, check to see if there's
// any more information
if (read == buffer.Length)
{

int nextByte = stream.ReadByte();
// End of stream? If so, we're done
if (nextByte == -1)

{
return buffer;
}

// Nope. Resize the buffer, put in the byte we've just
// read, and continue
byte[] newBuffer = new byte[buffer.Length * 2];
Array.Copy(buffer, newBuffer, buffer.Length);
newBuffer[read] = (byte)nextByte;
buffer = newBuffer;
read++;

}

}

// Buffer is now too big. Shrink it.

byte[] ret = new byte[read];
Array.Copy(buffer, ret, read);
return ret;

}

Thursday, October 04, 2007

Adding Document Favorite links for Direct File Access in OWA 2007 via a script

Direct File access is a pretty cool feature of OWA 2007 although may not be the easiest thing for your average user to get their head around. Somebody asked a question about pre-populating the favorites for a user which is a pretty good idea (actually when you think about it you really want something that’s policy driven). Currently there is no really easy or supported way of doing this programmatically. The document links themselves are stored in a storage item with a messageclass of IPM.Configuration.Owa.DocumentLibraryFavorites in the associated folder collection of NON_IPM_Subtree root of a mailbox. On this storage item there is a binary mapi property 0x7C080102 and the links are stored in a XML document.

I decided to see if I could write a script that could open this storage item and then add some nodes into the already existing property or create the property if it didn’t exist. The format of the doclib node looks something like

<docLib uri="file://sername/directory" dn="DisplayName"
hn="servername" uf="2"/>

This isn’t documented anywhere so I can only hazard to guess at that these elements actually mean but logically I would think

Uri - Path to the directory or sharepoint server

Dn – The displayName in OWA

Hn – HostName ?

Uf – This property seems to get set to one of 3 values which affects the way the icon shows in OWA if you have added just a hostname then it gets set to 2, if you add a share mapping it gets set to 6 if you added a deep url mapping eg a couple of directorys deep it gets set to 34.

From what I’ve noticed the IPM.Configuration.Owa.DocumentLibraryFavorites storage item only gets created the first time the user click on the documents link in OWA so the item wouldn’t be there normally if the user has not logged onto OWA or has never clicked this link.

As I said this script is unsupported and pretty untested and should only be used in test/dev environment. Also make sure you know how to use a Mapi editor like Outlook Spy or mfcMapi worse case is you’ll stuff up the IPM.Configuration.Owa.DocumentLibraryFavorites object and need to delete it.

The script uses CDO 1.2 to use the script you need to configure the first 4 lines which reflect the doclib I just talked about,

shareDN = "temp"
ShareHostName = "servername"
Shareuf = "6"
ShareURI = "file://servername/temp"

and then the 6,7 with the servername and mailboxname of the mailbox you want to access.

I’ve put a download of the script here the code itself look like

shareDN = "temp"
ShareHostName = "servername"
Shareuf = "6"
ShareURI = "file://servername/temp"

snServername = "mailserver"
mbMailboxName = "mailbox"

Const PR_PARENT_ENTRYID = &H0E090102
set xdXmlDocument = CreateObject("Microsoft.XMLDOM")
xdXmlDocument.async="false"
ifound = false
Set objSession = CreateObject("MAPI.Session")
objSession.Logon "","",false,true,true,true,snServername & vbLF & mbMailboxName
Set CdoInfoStore = objSession.GetInfoStore
Set CdoFolderRoot = CdoInfoStore.RootFolder
set non_ipm_rootfolder =
objSession.getfolder(CdoFolderRoot.fields.item(PR_PARENT_ENTRYID),CdoInfoStore.id)
For Each soStorageItem in non_ipm_rootfolder.HiddenMessages
If soStorageItem.Type = "IPM.Configuration.Owa.DocumentLibraryFavorites" Then
ifound = true
Set actionItem = soStorageItem
End if
Next
If ifound = false Then
wscript.echo "No Storage Item Found"
Else
On Error Resume Next
hexString = actionItem.fields(&h7C080102).Value
If Err.number <> 0 Then
On Error goto 0
wscript.echo "Property not set"
actionItem.fields.Add &h7C080102, vbBlob
actionItem.fields(&h7C080102).Value = StrToHexStr("<docLibs></docLibs>")
actionItem.update
hexString = actionItem.fields(&h7C080102).Value
End If
On Error goto 0
wscript.echo hextotext(hexString)
xdXmlDocument.loadxml(hextotext(hexString))
Set xnNodes = xdXmlDocument.selectNodes("//docLibs")
update = false
Call Adddoclib(shareDN,ShareHostName,Shareuf,ShareURI,xnNodes)
If update = True Then
nval = StrToHexStr(CStr(xdXmlDocument.xml))
actionItem.fields(&h7C080102).Value = nval
actionItem.update
wscript.echo "Storage Object Updated"
Else
wscript.echo "No Updates Performed"
End If
End If



Function hextotext(binprop)
arrnum = len(binprop)/2
redim aout(arrnum)
slen = 1
for i = 1 to arrnum
if CLng("&H" & mid(binprop,slen,2)) <> 0 then
aOut(i) = chr(CLng("&H" & mid(binprop,slen,2)))
end if
slen = slen+2
next
hextotext = join(aOUt,"")
end Function

Function StrToHexStr(strText)
Dim i, strTemp
For i = 1 To Len(strText)
strTemp = strTemp & Right("0" & Hex(Asc(Mid(strText, i, 1))), 2)
Next
StrToHexStr = Trim(strTemp)
End Function

Function Searchfordoclib(elElementName,cnvalue,XMLDoc)
Set xnSearchNodes = XMLDoc.selectNodes("//*[@" & elElementName & " = '" &
cnvalue & "']")
If xnSearchNodes.length = 0 Then
Searchfordoclib = False
else
Searchfordoclib = True
End if

End Function

sub Adddoclib(dn,hn,uf,uri,xnNodes)
If Searchfordoclib("dn",dn,xdXmlDocument) = False then
Set objnewEle = xdXmlDocument.createElement("docLib")
objnewEle.setAttribute "uri",uri
objnewEle.setAttribute "dn",dn
objnewEle.setAttribute "hn", hn
objnewEle.setAttribute "uf", uf
xnNodes(0).appendchild objnewEle
update = true
Else
wscript.echo "Dn exists " & DN
End if
End sub

Monday, October 01, 2007

Processing embedded attachments in a Transport Agent

Continuing on from my other post last week this is a little bit more of an advanced Transport agent that can process attachments within embedded messages down to any depth. Most of the code is the same as last week except that the structure of the code has been changed so each message and embedded message is processed by the routine. To see if a attachment has an assoiciated embeeded message all you need to do is check the EmbeddedMessage property on each message. The one thing that I found interesting is when processing an embedded message vs. just processing the message that caused the Agent to fire is that there is no underlying MIME document so the code I had that was saving out the message to a separate directory using the MIME document in this case wouldn’t work for an embedded message. As I was really only interested in processing attachments this was not so much of a problem but its one thing to be careful of if you do what to processes embedded messages. For details of what this code does please read last weeks post.

I’ve put a download of the code here the code itself looks like

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Diagnostics;
using Microsoft.Exchange.Data.Transport;
using Microsoft.Exchange.Data.Mime;
using Microsoft.Exchange.Data.Transport.Email;
using Microsoft.Exchange.Data.Transport.Smtp;
using Microsoft.Exchange.Data.Transport.Routing;
using Microsoft.Exchange.Data.Common;

namespace msgdevExchangeRoutingAgents
{
public class EmailArchivingFactory : RoutingAgentFactory
{
public override RoutingAgent CreateAgent(SmtpServer server)
{
RoutingAgent emMailArchive = new EmailArchivingRoutingAgent();
return emMailArchive;
}
}
}

public class EmailArchivingRoutingAgent : RoutingAgent
{
public EmailArchivingRoutingAgent()
{
base.OnSubmittedMessage += new SubmittedMessageEventHandler(EmailArchivingRoutingAgent_OnSubmittedMessage);
}

void EmailArchivingRoutingAgent_OnSubmittedMessage(SubmittedMessageEventSource source, QueuedMessageEventArgs e)
{
EmailMessage emMessage = e.MailItem.Message;
String MessageGuid = Guid.NewGuid().ToString();
ProcessMessage(emMessage, MessageGuid);

}
public static byte[] ReadFully(Stream stream, int initialLength)
{
// ref Function from http://www.yoda.arachsys.com/csharp/readbinary.html
// If we've been passed an unhelpful initial length, just
// use 32K.
if (initialLength < initiallength =" 32768;" buffer =" new" read =" 0;" chunk =" stream.Read(buffer,"> 0)
{
read += chunk;

// If we've reached the end of our buffer, check to see if there's
// any more information
if (read == buffer.Length)
{
int nextByte = stream.ReadByte();

// End of stream? If so, we're done
if (nextByte == -1)
{
return buffer;
}

// Nope. Resize the buffer, put in the byte we've just
// read, and continue
byte[] newBuffer = new byte[buffer.Length * 2];
Array.Copy(buffer, newBuffer, buffer.Length);
newBuffer[read] = (byte)nextByte;
buffer = newBuffer;
read++;
}
}
// Buffer is now too big. Shrink it.
byte[] ret = new byte[read];
Array.Copy(buffer, ret, read);
return ret;
}
static void ProcessMessage(EmailMessage emEmailMessage, String MessageGuid)
{
//Archive Message


if (emEmailMessage.MimeDocument != null)
{

Stream fsFileStream = new FileStream(@"C:\temp\archive\messages\" + MessageGuid + ".eml", FileMode.OpenOrCreate);
emEmailMessage.MimeDocument.WriteTo(fsFileStream);
fsFileStream.Close();
}
//Archive Any Attachments Check for pdf attachments under 20 K and delete
ArrayList adAttachmenttoDelete = new ArrayList();
for (int index = emEmailMessage.Attachments.Count - 1; index >= 0; index--)
{
Attachment atAttach = emEmailMessage.Attachments[index];
if (atAttach.EmbeddedMessage != null)
{
EmailMessage ebmMessage = atAttach.EmbeddedMessage;
ProcessMessage(ebmMessage, MessageGuid);
}
else
{
if (atAttach.AttachmentType == AttachmentType.Regular & atAttach.FileName != null)
{
FileStream atFileStream = File.Create(Path.Combine(@"C:\temp\archive\attachments\", MessageGuid + "-" + atAttach.FileName));
Stream attachstream = atAttach.GetContentReadStream();
byte[] bytes = ReadFully(attachstream, (int)attachstream.Length);
atFileStream.Write(bytes, 0, bytes.Length);
atFileStream.Close();
atFileStream = null;
bytes = null;
// Find Any PDF attachments less then 20 KB
if (atAttach.FileName.Length >= 3)
{
String feFileExtension = atAttach.FileName.Substring((atAttach.FileName.Length - 4), 4);
if (feFileExtension.ToLower() == ".pdf" & attachstream.Length < 20480)
{
adAttachmenttoDelete.Add(atAttach);
}

}
attachstream.Close();
attachstream = null;
}
atAttach = null;
}
}
//Delete Attachments
if (adAttachmenttoDelete.Count != 0)
{
IEnumerator Enumerator = adAttachmenttoDelete.GetEnumerator();
while (Enumerator.MoveNext())
{
emEmailMessage.Attachments.Remove((Attachment)Enumerator.Current);
}
}

}
}

Thursday, September 20, 2007

Email and Attachment Archiving with a Transport Agent on Exchange 2007

I’ve been continuing on with building and learning about Transport Agents over the past couple of weeks and thought I’d share an agent I’ve found useful. The following agent is a simple archiving agent it saves the serialized version of the message from the Mimedocument class to an eml file in a directory assigning it a GUID as a filename to make sure its unique. It also enumerates though the attachments of a message and saves them to a separate directory using the attachment filename and the message guid to link the message and attachments. I also added some code into to delete pdf files that where smaller than 20 KB this was for testing purposes but it’s something I’ve used in the past in SMTP sinks to overcome certain issues.

Like the last Agent I posted this is a Routing agent I’m running on Hub Server the code is relatively simple to follow. To do the attachment processing I’ve used the new EmailMessage class that’s part of the Microsoft.Exchange.Data.Transport.Email namespace. The cool thing about this class is it does provide a level of abstraction above TNEF and MIME. So if say you’re sending a meeting appointment internally to another user and you have attached a document if you where to parse this at the MIME level the message and attachment would be in TNEF format (good old winmail.dat) but the EmailMessage class allows you to enumerate though any attachments in the calendar invitation without needing to worry about using the lower level TNEF parsers. The one complaint I have about this class is that downloading an attachment is a little bit of a pain. After initially having problems with streams that would get corrupted intermittently I found Jon Skeet’s page http://www.yoda.arachsys.com/csharp/readbinary.html which had a function that worked well.

The other challenge I had was with removing particular attachments, generally deleting objects while enumerating though a collection isn’t the best of programming practices. Sometimes enumerating the collection in reverse can overcome this issue but for some reason when I did this with the attachment collection it would always give me an issue when I removed an attachment. So the solution I came up with for this was just to store the attachments that I wanted to delete in an arraylist during the initial attachment check and then loop though the arraylist at the end and delete those objects which seemed to work okay.

The one thing this agent is yet to handle is processing attachments within embedded messages which I think will be a separate post.

I’ve put a download of the code from this post here the code itself looks like

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Diagnostics;
using Microsoft.Exchange.Data.Transport;
using Microsoft.Exchange.Data.Mime;
using Microsoft.Exchange.Data.Transport.Email;
using Microsoft.Exchange.Data.Transport.Smtp;
using Microsoft.Exchange.Data.Transport.Routing;
using Microsoft.Exchange.Data.Common;

namespace msgdevExchangeRoutingAgents
{
public class EmailArchivingFactory : RoutingAgentFactory
{
public override RoutingAgent CreateAgent(SmtpServer server)
{
RoutingAgent raXheader = new EmailArchivingRoutingAgent();
return raXheader;
}
}
}

public class EmailArchivingRoutingAgent : RoutingAgent
{
public EmailArchivingRoutingAgent()
{
base.OnSubmittedMessage += new SubmittedMessageEventHandler(EmailArchivingRoutingAgent_OnSubmittedMessage);
}

void EmailArchivingRoutingAgent_OnSubmittedMessage(SubmittedMessageEventSource source, QueuedMessageEventArgs e)
{
//Archive Message
String MessageGuid = Guid.NewGuid().ToString();
Stream fsFileStream = new FileStream(@"C:\temp\archive\messages\" + MessageGuid + ".eml", FileMode.OpenOrCreate);
e.MailItem.Message.MimeDocument.WriteTo(fsFileStream);
fsFileStream.Close();
//Archive Any Attachments Check for pdf attachments under 20 K and delete
ArrayList adAttachmenttoDelete = new ArrayList();
for (int index = e.MailItem.Message.Attachments.Count - 1; index >= 0; index--)
{
Attachment atAttach = e.MailItem.Message.Attachments[index];
if (atAttach.AttachmentType == AttachmentType.Regular & atAttach.FileName != null)
{
FileStream atFileStream = File.Create(Path.Combine(@"C:\temp\archive\attachments\", MessageGuid + "-" + atAttach.FileName));
Stream attachstream = atAttach.GetContentReadStream();
byte[] bytes = ReadFully(attachstream, (int)attachstream.Length);
atFileStream.Write(bytes, 0, bytes.Length);
atFileStream.Close();
atFileStream = null;
bytes = null;
// Find Any PDF attachments less then 20 KB
if (atAttach.FileName.Length >= 3)
{
String feFileExtension = atAttach.FileName.Substring((atAttach.FileName.Length - 4), 4);
if (feFileExtension.ToLower() == ".pdf" & attachstream.Length < attachstream =" null;" atattach =" null;" enumerator =" adAttachmenttoDelete.GetEnumerator();" initiallength =" 32768;" buffer =" new" read =" 0;" chunk =" stream.Read(buffer,"> 0)
{
read += chunk;

// If we've reached the end of our buffer, check to see if there's
// any more information
if (read == buffer.Length)
{
int nextByte = stream.ReadByte();

// End of stream? If so, we're done
if (nextByte == -1)
{
return buffer;
}

// Nope. Resize the buffer, put in the byte we've just
// read, and continue
byte[] newBuffer = new byte[buffer.Length * 2];
Array.Copy(buffer, newBuffer, buffer.Length);
newBuffer[read] = (byte)nextByte;
buffer = newBuffer;
read++;
}
}
// Buffer is now too big. Shrink it.
byte[] ret = new byte[read];
Array.Copy(buffer, ret, read);
return ret;
}
}




Thursday, September 06, 2007

Adding an X-header in an Exchange Routing Transport Agent in Exchange 2007

This week I’ve been looking at Transport Agents which are the replacement to Transport Event Sinks in Exchange 2007. After the initial struggle of finding some decent information to get going these Agents are surprising easy to code and have a bunch of powerful functions that are more readily available then they were when compared to SMTP transport Event Sinks.

Getting Start info

If you’re looking for info on getting start the first place is this simple sample . One piece of advice is before you look at installing a Transport Agent making sure you know how to disable or remove one first.

There are two types of Transport Agents Routing and SMTP which one you use really depends where in the Transport Pipeline you want to intercept the message. I choose a Routing Transport agent because I wanted to be able to add an X-header to a message regardless of whether the message arrived via SMTP or if it was sent locally on a Mailbox server (if you are looking to do this in a SMTP Transport agent there is a X-header Sample in the Exchange 2007 SDK). The Transport Architecture document describes the process of Mail routing via the Hub server.

Once you get over modifying the subject you may want to start modifying the rest of the message this is where it starts getting hard and the information starts to get a little scarcer. Depending on what type of message you’re dealing with you may need to cope with MIME and TNEF formats. The later being a slightly harder thing to deal with for serialized messages most of the time MIME should be enough. So I would recommend reading the following MIME Architecture document from the SDK. Being able to deal with a serialized message in the MimeDocument Class is pretty cool and very powerful. You should always be carefully that any modifications you do make to a message aren’t going to mean that message breaks the Email RFC’s.

Getting down to the code

There are a number of different events you can hook into with a Transport Agent the one I was interested in was the OnSubmittedMessage. The event occurs just after the message is submitted because I only want to modify the headers content conversion shouldn’t really be an issue. The code is rather simple it first looks to see if there is a Xheader within the message already with the Name i want to use. If there isn't it creates a new Text header and inserts it before the last header in the current header list in the root MIME document.

I’ve put a download of the Transport Agent here the the main code itself is the following Event Handler

public class XHeaderRoutingAgent : RoutingAgent
{
public XHeaderRoutingAgent()
{ base.OnSubmittedMessage += new SubmittedMessageEventHandler(XHeaderRoutingAgent_OnSubmittedMessage);
} void XHeaderRoutingAgent_OnSubmittedMessage(SubmittedMessageEventSource esEvtsource, QueuedMessageEventArgs qmQueuedMessage)
{
MimeDocument mdMimeDoc = qmQueuedMessage.MailItem.Message.MimeDocument;
HeaderList hlHeaderlist = mdMimeDoc.RootPart.Headers;
Header mhProcHeader = hlHeaderlist.FindFirst("X-MyProcess");
if (mhProcHeader == null)
{
MimeNode lhLasterHeader = hlHeaderlist.LastChild;
TextHeader nhNewHeader = new TextHeader("X-MyProcess", "Logged00");
hlHeaderlist.InsertBefore(nhNewHeader, lhLasterHeader);
}

}

}

Thursday, August 23, 2007

Assigning categories based on the Attachments in a Message via a CDO 1.2 script

This is part 2 of a 2 part post of a few scripts to assign different colored categories to messages based on the type of attachments a message has. See the first post for information on how modify the Outlook 2007 categories’

This script handles enumerating the existing messages within a mailbox and then assigning categories (or keywords) based on the attachment types on the message. When I used this script I did every message in my mailbox which worked okay but because I was using Outlook in Cache Mode updating a lot a messages this way caused a major re-syncing or the cache (e.g. it seemed to pull down every message again that was updated with an attachment which can consume a lot of bandwidth if you have a large mailbox with large attachments). While this may be okay for some people this could cause some havoc in some networks so with this script I put a filter value so it will only update the messages that are less then 1 month old in the inbox. This could still be a considerable number of messages so you should use this script with great care and always test it first in your dev environment.

The script works by first creating a filter on the Messages in the inbox so only messages that have an attachment and are under 1 month old are included in the collection. It then loops through each message and first build a list of any existing keywords on a message. It then checks the attachment and if there is an attachment types that doesn’t have an existing keyword set on the message a new keyword is added to that message. To avoid assigning a keyword to a message with inline attachments such as people who use images in signatures etc the script checks to see if the attachment in question is a inline attachment if that’s the case it skips over this attachment. The keywords assigned to the message match the categories that where created with the first script. The categories themselves are held in a multivalued String property http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/Keywords.

This script only updates existing message if you want to set the categories on new messages you would need to look at writing an Event Sink or if your using Exchange 2007 you could use a Transport Agent or EWS Notifications application.

As I said before there is filter to stop it updating more than 1 month worth of mail in the line

attFilter.TimeFirst = DateAdd("m",-1,Now())

To run the code you need to supply the servername and mailbox name of the mailbox you want to run it against as commandline parameters eg

Cscript setkeywords.vbs.vbs servername mailboxname

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

snServername = wscript.arguments(0)
mbMailboxName = wscript.arguments(1)

Set objSession = CreateObject("MAPI.Session")
Set catDict = CreateObject("Scripting.Dictionary")
objSession.Logon "","",false,true,true,true,snServername & vbLF & mbMailboxName
set ifInboxFolderCol = objSession.inbox.messages
set attFilter = ifInboxFolderCol.Filter
Set attFilterFiled = attFilter.Fields.Add(&h0E1B000B,true)
attFilter.TimeFirst = DateAdd("m",-1,Now())
For Each moMessageobject In ifInboxFolderCol
ceCatExists = False
catDict.RemoveAll
On Error Resume Next
ccCurrentCats = moMessageobject.Fields.item("{2903020000000000C000000000000046}Keywords").value
If Err.number = 0 Then
ceCatExists = True
For Each existingcat In ccCurrentCats
catDict.add existingcat,1
next
End If
On Error goto 0
oldcatlength = catDict.Count
Call GetCategories(moMessageobject,catDict)
If catDict.Count > oldcatlength Then
wscript.echo moMessageobject.Subject
ReDim newcats(catDict.Count-1)
catkeys = catDict.Keys
For i = 0 to catDict.Count-1
newcats(i) = catkeys(i)
Next
If ceCatExists = True then
moMessageobject.Fields.item("{2903020000000000C000000000000046}Keywords").value = newcats
Else
moMessageobject.Fields.add "Keywords", vbArray , newcats, "2903020000000000C000000000000046"
End If
moMessageobject.update
End if
next
sub GetCategories(msgObject,catDict)
For Each attachment In msgObject.Attachments
On Error Resume Next
inline = 0
fnFileName = attachment.fields(&h3704001E)
Err.clear
contentid = attachment.fields(&h3712001F)
If Err.number = 0 Then
inline = 1
Else
inline = 0
End if
Err.clear
attflags = attachment.fields(&h37140003)
If Err.number = 0 Then
If attflags = 4 Then inline = 1
End if
If Len(fnFileName) > 4 And inline = 0 Then
Select Case Right(LCase(fnFileName),4)
Case ".doc" If Not catDict.exists("Word Attachment") Then
catDict.add "Word Attachment",1
End if
Case ".ppt" If Not catDict.exists("PowerPoint Attachment") Then
catDict.add "PowerPoint Attachment",1
End if
Case ".xls" If Not catDict.exists("Excel Attachment") Then
catDict.add "Excel Attachment",1
End if
Case ".jpg" If Not catDict.exists("Image Attachment") Then
catDict.add "Image Attachment",1
End if
Case ".bmp" If Not catDict.exists("Image Attachment") Then
catDict.add "Image Attachment",1
End if
Case ".mov" If Not catDict.exists("Video Attachment") Then
catDict.add "Video Attachment",1
End if
Case ".mpg" If Not catDict.exists("Video Attachment") Then
catDict.add "Video Attachment",1
End if
Case ".wmv" If Not catDict.exists("Video Attachment") Then
catDict.add "Video Attachment",1
End if
Case ".pdf" If Not catDict.exists("PDF Attachment") Then
catDict.add "PDF Attachment",1
End if
Case ".mp3" If Not catDict.exists("Sound Attachment") Then
catDict.add "Sound Attachment",1
End if
Case ".pps" If Not catDict.exists("PowerPoint Attachment") Then
catDict.add "PowerPoint Attachment",1
End if
Case ".zip" If Not catDict.exists("Zip Attachment") Then
catDict.add "Zip Attachment",1
End if
End select

End if
On Error goto 0

next

End sub



Adding Categories to the Master categories list in Outlook 2007 with a CDO 1.2 script

This is a two part post that I thought I’d separate this idea came from someone who asked about how you could group messages by their attachment types. Normally this would be a pretty hard thing to achieve manly because of the way attachments are stored doesn’t lend itself well to being grouped by in a search folder or an Outlook Customize view. But this got me thinking about what if you could use the new colored category feature in Outlook 2007 instead. Eg for each attachment type you have a separate color and Label. This works out pretty cool because you go from being able to look at your email and seeing that there is an attachment eg the paper clip icon to being able to look at a message and if you see a blue category mark you know that message has got an attachment and it’s a word document if it’s a green mark you know it’s a Excel document. You can then also create Views or Search folders based on the attachment categorization you could also could create an event sink or Notification app to assign the category to new items when they arrive.

To start with this idea however I first needed to make changes to the master categories list in Outlook 2007 while doing this from Outlook 2007 is the easy method I wanted to do this programmatically instead. The master categories list is held in a hidden message (in the associated contents collection) with a message class of “IPM.Configuration.CategoryList”. On this message there is a binary Mapi property 0x7C080102 which holds the category list which in is XML format. So to modify the list you need some code that will first read this property I used CDO 1.2 so when you read the property with CDO you get back a hex string representation of the Binary property. To make use of this the Hex needs to be converted to a String which will represent the XML document. I then loaded the XML back into the XMLDom object and used the clonenode method to copy one of the existing nodes and then modified the necessary properties for the new category I wanted to add. The three important bits of information you need to set are the Name which is the keyword value you going to use for you category. An integer for the color you want the category to be and a unique GUID value. To stop duplicates there’s some code to check if the name of Guid already exists in the XML document if so it doesn’t try to add another node. To write the modified XML back to the property theres a function that coverts the XML String back to hex.

The code will only work to modify an existing category list it won’t create one from scratch. To run the code you need to supply the servername and mailbox name of the mailbox you want to run it against as commandline parameters eg

Cscript modcats.vbs servername mailboxname

I’ve put a download of the scrip here the script itself looks like

snServername = wscript.arguments(0)
mbMailboxName = wscript.arguments(1)

' Word Documents
ReDim wdattah(1)
wdattah(0) = "{DB13F464-2FAA-48F2-8D1B-ADB5ED4FD1F7}"
wdattah(1) = 22
'Excel Attachments
ReDim edattach(1)
edattach(0) = "{D549D2BB-E1BF-47DE-B713-784771F059A1}"
edattach(1) = 19
'PowerPoint Attachments
ReDim pptattach(1)
pptattach(0) = "{1E8ADCFB-AC2C-4FEF-ABB5-C5349A359CC8}"
pptattach(1) = 0
'PDF attachments
ReDim pdfattach(1)
pdfattach(0) = "{707D20D7-5EF8-47D7-B6C8-47FCB606EEB5}"
pdfattach(1) = 15
'Audio Attachments
ReDim sndattach(1)
sndattach(0) = "{B28E76F5-127B-4356-9150-D2A0B84E8DCE}"
sndattach(1) = 18
'Video
ReDim vdoattach(1)
vdoattach(0) = "{E633EC9C-9B29-4608-A4BA-CFBFA886702B}"
vdoattach(1) = 23
'Image Attachment
ReDim imgAttach(1)
imgAttach(0) = "{BB488D85-76FE-408F-9DD4-617041DBFDA6}"
imgAttach(1) = 13
'Zip Attachment
ReDim zipAttach(1)
zipAttach(0) = "{B4423425-54F1-304F-92F3-63451D3BFDB6}"
zipAttach(1) = 8

Set catDict = CreateObject("Scripting.Dictionary")
catDict.add "Word Attachment",wdattah
catDict.add "Excel Attachment",edattach
catDict.add "PowerPoint Attachment", pptattach
catDict.add "PDF Attachment", pdfattach
catDict.add "Audio Attachment", sndattach
catDict.add "Image Attachment", imgAttach
catDict.add "Video Attachment", vdoattach
catDict.add "Zip Attachment", zipAttach

set xdXmlDocument = CreateObject("Microsoft.XMLDOM")
xdXmlDocument.async="false"
Set objSession = CreateObject("MAPI.Session")
objSession.Logon "","",false,true,true,true,snServername & vbLF & mbMailboxName
Set CdoInfoStore = objSession.GetInfoStore
Set CdoFolderRoot = CdoInfoStore.RootFolder
set cdocalendar = objSession.GetDefaultFolder(CdoDefaultFolderCalendar)
For Each soStorageItem in cdocalendar.HiddenMessages
If soStorageItem.Type = "IPM.Configuration.CategoryList" Then
hexString = soStorageItem.fields(&h7C080102).Value
xdXmlDocument.loadxml(hextotext(hexString))
For Each cat In catDict
catval = catDict(cat)
If SearchforCategory("name",cat,xdXmlDocument) = True Or SearchforCategory("guid",catval(0),xdXmlDocument) Then
wscript.echo "Category Name or GUID alread Exists " & cat
Else
wscript.echo "Adding category " & cat
Call AddCategory(cat,catDict(cat),xdXmlDocument)
End if
next
nval = StrToHexStr(CStr(xdXmlDocument.xml))
soStorageItem.fields(&h7C080102).Value = nval
soStorageItem.update
End if
Next



Function hextotext(binprop)
arrnum = len(binprop)/2
redim aout(arrnum)
slen = 1
for i = 1 to arrnum
if CLng("&H" & mid(binprop,slen,2)) <> 0 then
aOut(i) = chr(CLng("&H" & mid(binprop,slen,2)))
end if
slen = slen+2
next
hextotext = join(aOUt,"")
end Function


Function StrToHexStr(strText)
Dim i, strTemp
For i = 1 To Len(strText)
strTemp = strTemp & Right("0" & Hex(Asc(Mid(strText, i, 1))), 2)
Next
StrToHexStr = Trim(strTemp)
End Function

Function SearchforCategory(elElementName,cnvalue,XMLDoc)
Set xnNodes = XMLDoc.selectNodes("//*[@" & elElementName &amp; " = '" & cnvalue & "']")
If xnNodes.length = 0 Then
SearchforCategory = False
else
SearchforCategory = True
End if

End Function

Function AddCategory(cnCategoryName,setarray,XMLDoc)
Set xnNodes = XMLDoc.selectNodes("//categories")
Set xnCatNodes = XMLDoc.selectNodes("//category")
Set objnewCat = xnCatNodes(0).cloneNode(true)
objnewCat.setAttribute "name",cnCategoryName
objnewCat.setAttribute "guid",setarray(0)
objnewCat.setAttribute "keyboardShortcut", 0
objnewCat.setAttribute "color", setarray(1)
objnewCat.setAttribute "usageCount", 0
objnewCat.setAttribute "lastTimeUsedNotes","1601-01-01T00:00:00.000"
objnewCat.setAttribute "lastTimeUsedJournal","1601-01-01T00:00:00.000"
objnewCat.setAttribute "lastTimeUsedTasks","1601-01-01T00:00:00.000"
objnewCat.setAttribute "lastTimeUsedContacts","1601-01-01T00:00:00.000"
objnewCat.setAttribute "lastTimeUsedMail","1601-01-01T00:00:00.000"
objnewCat.setAttribute "lastSessionUsed","1601-01-01T00:00:00.000"
xnNodes(0).appendChild objnewCat
End Function