Thursday, August 21, 2014

Sent and Received Time on a Message and EWS

This one has come up a couple of times for me over the last couple of weeks so I thought I'd put together a post to expand on the Subject a little.

The easiest place to start before talking about Exchange is to look at a ordinary MIME message and its associated headers. In a MIME message there is one Message date  header http://tools.ietf.org/html/rfc4021#section-2.1.1 which outlines "Specifies the date and time at which the creator of the message indicated that the message was complete and ready to enter the mail delivery system" which is a way of saying that its the Sent Time. eg In a Message

Subject: Re: Oh no
Date: Thu, 14 Aug 2014 18:44:52 +1000
Message-ID: <nk0h69wn6waj8s32rnc3kma0.1408005882691@email.android.com>

 As the Message traverses various MTA's along the way to its final destinations, Received headers with dates are added to the Transport Headers  of a message indicating the date\time a particular MTA's processed the message eg

Received: from BY2PR03MB459.namprd03.prod.outlook.com (10.141.141.147) by
 DM2PR03MB463.namprd03.prod.outlook.com (10.141.85.20) with Microsoft SMTP
 Server (TLS) id 15.0.859.15 via Mailbox Transport; Wed, 29 Jan 2014 22:06:05
 +0000

When the Message finally arrives at is destination and is delivered by the Store to a users Mailbox two MAPI properties will be created to  reflect the Sent Time and also the Date Time the message was delivered by the store (which should match (most recent) received header in the message). With POP3 and and some POP downloaders this is where the date can get a little offset and not represent the real time of message delivery. But sticking to Exchange the following two props get set

PR_MESSAGE_DELIVERY_TIME which in EWS is also represented by the Strongly typed DateTimeReceived property

PR_CLIENT_SUBMIT_TIME property which in EWS is represented by the Strongly typed DateTimeSent property.

Exchange when it stores these dates like with other dates will store these in UTC format. When your using Outlook with the default views it will use the PR_MESSAGE_DELIVERY_TIME for every Mail folder other then the Sent Items where the view will use the PR_CLIENT_SUBMIT_TIME.

So whenever you importing EML's and you see an unexpected Received (or Sent) by date the first thing to check is the Transport Headers and look at the most recent received header. If your missing these headers then that maybe why your dates aren't what you expect.

In EWS you can access the Transport Headers via the InternetMessageHeaders strongly typed property or you can use the PR_TRANSPORT_MESSAGE_HEADERS extended properties. Depending on the version of Exchange you are using there can be issues with the strongly typed property so you should read http://msdn.microsoft.com/EN-US/library/office/hh545614(v=exchg.140).aspx . Because of the size of these properties this information will only be returned when you use a GetItem's operation

The other thing you can do with these message dates is track the amount of time it took from submit to the delivery of a message eg the following script will use EWS to grab both the PR_MESSAGE_DELIVERY_TIME  and PR_CLIENT_SUBMIT_TIME MAPI properties and use those to calculate the delivery time is also parses the Sent Header datetime and creates a CSV of the output. I've put a download of this script here the code looks like

  1. ## Get the Mailbox to Access from the 1st commandline argument  
  2.   
  3. $MailboxName = $args[0]  
  4.   
  5. ## Load Managed API dll    
  6.   
  7. ###CHECK FOR EWS MANAGED API, IF PRESENT IMPORT THE HIGHEST VERSION EWS DLL, ELSE EXIT  
  8. $EWSDLL = (($(Get-ItemProperty -ErrorAction SilentlyContinue -Path Registry::$(Get-ChildItem -ErrorAction SilentlyContinue -Path 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Exchange\Web Services'|Sort-Object Name -Descending| Select-Object -First 1 -ExpandProperty Name)).'Install Directory') + "Microsoft.Exchange.WebServices.dll")  
  9. if (Test-Path $EWSDLL)  
  10.     {  
  11.     Import-Module $EWSDLL  
  12.     }  
  13. else  
  14.     {  
  15.     "$(get-date -format yyyyMMddHHmmss):"  
  16.     "This script requires the EWS Managed API 1.2 or later."  
  17.     "Please download and install the current version of the EWS Managed API from"  
  18.     "http://go.microsoft.com/fwlink/?LinkId=255472"  
  19.     ""  
  20.     "Exiting Script."  
  21.     exit  
  22.     }  
  23.     
  24. ## Set Exchange Version    
  25. $ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP2    
  26.     
  27. ## Create Exchange Service Object    
  28. $service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService($ExchangeVersion)    
  29.     
  30. ## Set Credentials to use two options are availible Option1 to use explict credentials or Option 2 use the Default (logged On) credentials    
  31.     
  32. #Credentials Option 1 using UPN for the windows Account    
  33. $psCred = Get-Credential    
  34. $creds = New-Object System.Net.NetworkCredential($psCred.UserName.ToString(),$psCred.GetNetworkCredential().password.ToString())    
  35. $service.Credentials = $creds        
  36.     
  37. #Credentials Option 2    
  38. #service.UseDefaultCredentials = $true    
  39.     
  40. ## Choose to ignore any SSL Warning issues caused by Self Signed Certificates    
  41.     
  42. ## Code From http://poshcode.org/624  
  43. ## Create a compilation environment  
  44. $Provider=New-Object Microsoft.CSharp.CSharpCodeProvider  
  45. $Compiler=$Provider.CreateCompiler()  
  46. $Params=New-Object System.CodeDom.Compiler.CompilerParameters  
  47. $Params.GenerateExecutable=$False  
  48. $Params.GenerateInMemory=$True  
  49. $Params.IncludeDebugInformation=$False  
  50. $Params.ReferencedAssemblies.Add("System.DLL") | Out-Null  
  51.   
  52. $TASource=@' 
  53.   namespace Local.ToolkitExtensions.Net.CertificatePolicy{ 
  54.     public class TrustAll : System.Net.ICertificatePolicy { 
  55.       public TrustAll() {  
  56.       } 
  57.       public bool CheckValidationResult(System.Net.ServicePoint sp, 
  58.         System.Security.Cryptography.X509Certificates.X509Certificate cert,  
  59.         System.Net.WebRequest req, int problem) { 
  60.         return true; 
  61.       } 
  62.     } 
  63.   } 
  64. '@   
  65. $TAResults=$Provider.CompileAssemblyFromSource($Params,$TASource)  
  66. $TAAssembly=$TAResults.CompiledAssembly  
  67.   
  68. ## We now create an instance of the TrustAll and attach it to the ServicePointManager  
  69. $TrustAll=$TAAssembly.CreateInstance("Local.ToolkitExtensions.Net.CertificatePolicy.TrustAll")  
  70. [System.Net.ServicePointManager]::CertificatePolicy=$TrustAll  
  71.   
  72. ## end code from http://poshcode.org/624  
  73.     
  74. ## Set the URL of the CAS (Client Access Server) to use two options are availbe to use Autodiscover to find the CAS URL or Hardcode the CAS to use    
  75.     
  76. #CAS URL Option 1 Autodiscover    
  77. $service.AutodiscoverUrl($MailboxName,{$true})    
  78. "Using CAS Server : " + $Service.url     
  79.      
  80. #CAS URL Option 2 Hardcoded    
  81.     
  82. #$uri=[system.URI] "https://casservername/ews/exchange.asmx"    
  83. #$service.Url = $uri      
  84.     
  85. ## Optional section for Exchange Impersonation    
  86.     
  87. #$service.ImpersonatedUserId = new-object Microsoft.Exchange.WebServices.Data.ImpersonatedUserId([Microsoft.Exchange.WebServices.Data.ConnectingIdType]::SmtpAddress, $MailboxName)   
  88.   
  89. # Bind to the Inbox Folder  
  90. $folderid= new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox,$MailboxName)     
  91. $Inbox = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$folderid)  
  92.   
  93. $PR_MESSAGE_DELIVERY_TIME = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(0x0E06, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::SystemTime)  
  94. $PR_CLIENT_SUBMIT_TIME = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(0x0039, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::SystemTime)  
  95. $PR_TRANSPORT_MESSAGE_HEADERS = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(0x007D,[Microsoft.Exchange.WebServices.Data.MapiPropertyType]::String);  
  96. $psPropset= new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::IdOnly)    
  97. $psPropset.Add($PR_CLIENT_SUBMIT_TIME)  
  98. $psPropset.Add($PR_MESSAGE_DELIVERY_TIME)  
  99. $psPropset.Add($PR_TRANSPORT_MESSAGE_HEADERS)  
  100. $psPropset.Add([Microsoft.Exchange.WebServices.Data.ItemSchema]::Subject)  
  101. #Define ItemView to retrive just 1000 Items      
  102. $ivItemView =  New-Object Microsoft.Exchange.WebServices.Data.ItemView(1000)    
  103. $ivItemView.PropertySet = $psPropset  
  104. $rptCollection = @()  
  105. $fiItems = $null      
  106. do{      
  107.     $fiItems = $service.FindItems($Inbox.Id,$ivItemView)      
  108.     [Void]$service.LoadPropertiesForItems($fiItems,$psPropset)    
  109.     foreach($Item in $fiItems.Items){      
  110.         $Headers = $null;  
  111.         $ClientSubmitTime = $null  
  112.         $DeliveryTime = $null  
  113.         [Void]$Item.TryGetProperty($PR_CLIENT_SUBMIT_TIME,[ref]$ClientSubmitTime)  
  114.         [Void]$Item.TryGetProperty($PR_MESSAGE_DELIVERY_TIME,[ref]$DeliveryTime)  
  115.         if($Item.TryGetProperty($PR_TRANSPORT_MESSAGE_HEADERS,[ref]$Headers)){  
  116.             $slen = $Headers.ToLower().IndexOf("`ndate: ")  
  117.             if($slen -gt 0){  
  118.                 $elen = $Headers.IndexOf("`r`n",$slen)  
  119.                 $TimeSpan =  NEW-TIMESPAN –Start $ClientSubmitTime –End $DeliveryTime   
  120.                 $rptobj = "" | select Subject,HeaderDate,DELIVERY_TIME,SUBMIT_TIME,Diff  
  121.                 $rptobj.Subject = $Item.Subject  
  122.                 $parsedDate = $Headers.Substring(($slen+7),($elen-($slen+7)))                 
  123.                 $rptobj.HeaderDate = [DateTime]::Parse($parsedDate).ToLocalTime()  
  124.                 $rptobj.DELIVERY_TIME = $DeliveryTime.ToLocalTime()  
  125.                 $rptobj.SUBMIT_TIME = $ClientSubmitTime.ToLocalTime()  
  126.                 $rptobj.Diff = [Math]::Round($TimeSpan.TotalMinutes,0)  
  127.                 $rptCollection += $rptobj  
  128.             }  
  129.         }        
  130.     }      
  131.     $ivItemView.Offset += $fiItems.Items.Count  
  132.     Write-Host ("Processed " + $ivItemView.Offset + " of " + $fiItems.TotalCount)  
  133. }while($fiItems.MoreAvailable -eq $true)   
  134. $rptCollection | Export-Csv -NoTypeInformation -Path "c:\Temp\$mailboxName-mTimes.csv"  
  135. Write-Host("Exported to c:\Temp\$mailboxName-mTimes.csv")  

Wednesday, August 06, 2014

Getting Folder Sizes and other Stats via EWS with Powershell

Somebody asked a question the other week about getting all the Folder Sizes via EWS which you can do easily using the PR_MESSAGE_SIZE_EXTENDED property and FindFolders operation (you can also get the folder size's using the Exchange Management Shell via Get-MailboxFolderStatistics cmdlet). But there is some other interesting stuff you can get via EWS that you can't from the EMS cmdlet such as the Default FolderClass (eg the PR_CONTAINER_CLASS_W http://msdn.microsoft.com/en-us/library/office/cc839839(v=office.15).aspx) which will tell you what items are being stored in that particular Folder (although as documented it's not a mandatory property although its absence in the past has caused problem in OWA etc). Another property that looks interesting but doesn't seem to be well documented is the PR_ATTACH_ON_NORMAL_MSG_COUNT which appears to the be the total number of attachments on messages in that folder including seemly inline attachment (Note I can't confirm this as it doesn't appear to be documented and can only give anecdotal test results so do your own testing if your interested in this).

So with all these interesting properties you can put together a different type of Mailbox statistics script that will grab Folder stats by FolderClass and show the following info about a mailbox


I've put a download of this script here to run this script enter the EmailAddress of the mailbox you want to run it against and It will will output a CSV to the c:\temp directory the code itself looks like

  1. ## Get the Mailbox to Access from the 1st commandline argument  
  2.   
  3. $MailboxName = $args[0]  
  4.   
  5. ## Load Managed API dll    
  6. Add-Type -Path "C:\Program Files\Microsoft\Exchange\Web Services\2.1\Microsoft.Exchange.WebServices.dll"    
  7.     
  8. ## Set Exchange Version    
  9. $ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2013_SP1    
  10.     
  11. ## Create Exchange Service Object    
  12. $service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService($ExchangeVersion)    
  13.     
  14. ## Set Credentials to use two options are availible Option1 to use explict credentials or Option 2 use the Default (logged On) credentials    
  15.     
  16. #Credentials Option 1 using UPN for the windows Account    
  17. $psCred = Get-Credential    
  18. $creds = New-Object System.Net.NetworkCredential($psCred.UserName.ToString(),$psCred.GetNetworkCredential().password.ToString())    
  19. $service.Credentials = $creds        
  20.     
  21. #Credentials Option 2    
  22. #service.UseDefaultCredentials = $true    
  23.     
  24. ## Choose to ignore any SSL Warning issues caused by Self Signed Certificates    
  25.     
  26. ## Code From http://poshcode.org/624  
  27. ## Create a compilation environment  
  28. $Provider=New-Object Microsoft.CSharp.CSharpCodeProvider  
  29. $Compiler=$Provider.CreateCompiler()  
  30. $Params=New-Object System.CodeDom.Compiler.CompilerParameters  
  31. $Params.GenerateExecutable=$False  
  32. $Params.GenerateInMemory=$True  
  33. $Params.IncludeDebugInformation=$False  
  34. $Params.ReferencedAssemblies.Add("System.DLL") | Out-Null  
  35.   
  36. $TASource=@' 
  37.   namespace Local.ToolkitExtensions.Net.CertificatePolicy{ 
  38.     public class TrustAll : System.Net.ICertificatePolicy { 
  39.       public TrustAll() {  
  40.       } 
  41.       public bool CheckValidationResult(System.Net.ServicePoint sp, 
  42.         System.Security.Cryptography.X509Certificates.X509Certificate cert,  
  43.         System.Net.WebRequest req, int problem) { 
  44.         return true; 
  45.       } 
  46.     } 
  47.   } 
  48. '@   
  49. $TAResults=$Provider.CompileAssemblyFromSource($Params,$TASource)  
  50. $TAAssembly=$TAResults.CompiledAssembly  
  51.   
  52. ## We now create an instance of the TrustAll and attach it to the ServicePointManager  
  53. $TrustAll=$TAAssembly.CreateInstance("Local.ToolkitExtensions.Net.CertificatePolicy.TrustAll")  
  54. [System.Net.ServicePointManager]::CertificatePolicy=$TrustAll  
  55.   
  56. ## end code from http://poshcode.org/624  
  57.     
  58. ## Set the URL of the CAS (Client Access Server) to use two options are availbe to use Autodiscover to find the CAS URL or Hardcode the CAS to use    
  59.     
  60. #CAS URL Option 1 Autodiscover    
  61. $service.AutodiscoverUrl($MailboxName,{$true})    
  62. "Using CAS Server : " + $Service.url     
  63.      
  64. #CAS URL Option 2 Hardcoded    
  65.     
  66. #$uri=[system.URI] "https://casservername/ews/exchange.asmx"    
  67. #$service.Url = $uri      
  68.     
  69. ## Optional section for Exchange Impersonation    
  70.     
  71. #$service.ImpersonatedUserId = new-object Microsoft.Exchange.WebServices.Data.ImpersonatedUserId([Microsoft.Exchange.WebServices.Data.ConnectingIdType]::SmtpAddress, $MailboxName)   
  72. function ConvertToString($ipInputString){    
  73.     $Val1Text = ""    
  74.     for ($clInt=0;$clInt -lt $ipInputString.length;$clInt++){    
  75.             $Val1Text = $Val1Text + [Convert]::ToString([Convert]::ToChar([Convert]::ToInt32($ipInputString.Substring($clInt,2),16)))    
  76.             $clInt++    
  77.     }    
  78.     return $Val1Text    
  79. }   
  80.   
  81. $FolderClassrpt = @{}  
  82. function GetFolderSizes{  
  83.     param (  
  84.             $rootFolderId = "$( throw 'rootFolderId is a mandatory Parameter' )"  
  85.           )  
  86.     process{  
  87.     #Define Extended properties    
  88.     $PR_FOLDER_TYPE = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(13825,[Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Integer);    
  89.     $PR_MESSAGE_SIZE_EXTENDED = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(3592, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Long);  
  90.     $folderidcnt = $rootFolderId  
  91.     #Define the FolderView used for Export should not be any larger then 1000 folders due to throttling    
  92.     $fvFolderView =  New-Object Microsoft.Exchange.WebServices.Data.FolderView(1000)    
  93.     #Deep Transval will ensure all folders in the search path are returned    
  94.     $fvFolderView.Traversal = [Microsoft.Exchange.WebServices.Data.FolderTraversal]::Deep;    
  95.     $psPropertySet = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)    
  96.     $PR_Folder_Path = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(26293, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::String);    
  97.     $PR_ATTACH_ON_NORMAL_MSG_COUNT = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(0x66B1, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Long);  
  98.     #Add Properties to the  Property Set    
  99.     $psPropertySet.Add($PR_Folder_Path);    
  100.     $psPropertySet.Add($PR_MESSAGE_SIZE_EXTENDED)  
  101.     $psPropertySet.Add($PR_ATTACH_ON_NORMAL_MSG_COUNT)  
  102.     $fvFolderView.PropertySet = $psPropertySet;    
  103.     #The Search filter will exclude any Search Folders    
  104.     $sfSearchFilter = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo($PR_FOLDER_TYPE,"1")    
  105.     $fiResult = $null    
  106.     #The Do loop will handle any paging that is required if there are more the 1000 folders in a mailbox    
  107.     do {    
  108.         $fiResult = $Service.FindFolders($folderidcnt,$sfSearchFilter,$fvFolderView)    
  109.         foreach($ffFolder in $fiResult.Folders){  
  110.             $foldpathval = $null    
  111.             #Try to get the FolderPath Value and then covert it to a usable String     
  112.             if ($ffFolder.TryGetProperty($PR_Folder_Path,[ref] $foldpathval))    
  113.             {    
  114.                 $binarry = [Text.Encoding]::UTF8.GetBytes($foldpathval)    
  115.                 $hexArr = $binarry | ForEach-Object { $_.ToString("X2") }    
  116.                 $hexString = $hexArr -join ''    
  117.                 $hexString = $hexString.Replace("FEFF""5C00")    
  118.                 $fpath = ConvertToString($hexString)    
  119.             }   
  120.             $folderSize = $null  
  121.             [Void]$ffFolder.TryGetProperty($PR_MESSAGE_SIZE_EXTENDED,[ref] $folderSize)  
  122.             [Int64]$attachcnt = 0  
  123.             [Void]$ffFolder.TryGetProperty($PR_ATTACH_ON_NORMAL_MSG_COUNT,[ref] $attachcnt)  
  124.             if($attachcnt -eq $null){  
  125.                 $attachcnt = 0  
  126.             }  
  127.             "FolderPath : " + $fpath + " : " + $folderSize  
  128.             $fldClass = $ffFolder.FolderClass  
  129.             if($fldClass -eq $null){$fldClass = "IPF.Note"}  
  130.             if($FolderClassrpt.ContainsKey($fldClass)){  
  131.                 $FolderClassrpt[$fldClass].NumberOfFolders += 1  
  132.                 $FolderClassrpt[$fldClass].AttachOnMsgCount += $attachcnt  
  133.                 $FolderClassrpt[$fldClass].ItemSize += [Int64]$folderSize  
  134.                 $FolderClassrpt[$fldClass].ItemCount += [Int64]$ffFolder.TotalCount  
  135.             }  
  136.             else{  
  137.                 $rptObj = "" | select FolderClass,NumberOfFolders,AttachOnMsgCount,ItemSize,ItemCount  
  138.                 $rptObj.FolderClass = $fldClass  
  139.                 $FolderClassrpt[$fldClass].NumberOfFolders  
  140.                 $rptObj.ItemSize = [Int64]$folderSize  
  141.                 $rptObj.ItemCount = [Int64]$ffFolder.TotalCount  
  142.                 $rptObj.AttachOnMsgCount += $attachcnt  
  143.                 $rptObj.NumberOfFolders = 1  
  144.                 $FolderClassrpt.Add($fldClass,$rptObj)  
  145.             }  
  146.         }   
  147.         $fvFolderView.Offset += $fiResult.Folders.Count  
  148.     }while($fiResult.MoreAvailable -eq $true)    
  149.     }  
  150. }  
  151. GetFolderSizes -rootFolderId (new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::MsgFolderRoot,$MailboxName))     
  152.   
  153. $FolderClassrpt.Values | select FolderClass,NumberOfFolders,AttachOnMsgCount,ItemCount,@{label="TotalSize(MB)";expression={[math]::Round($_.ItemSize/1MB,2)}}  | export-csv c:\temp\$MailboxName-fldsizebyclass.csv -NoTypeInformation