Tuesday, May 08, 2012

How To Series Sample 6 : Show a sorted list of Subject's and number of messages within a mailbox

The following script shows how you can create a summary report of the Subjects of all Messages within a Mailbox. It makes use of the PR_NORMALIZED_SUBJECT http://msdn.microsoft.com/en-us/library/cc815282.aspx. property which contains the subject of a message minus the prefixes such as RE, FW etc. The end result is it will pump out a sorted list of the Number of messages per subject and also create a CSV report. The script is designed to work on Exchange 2010 but you could make it work on 2007 as well by converting the AQS QueryString (which limits the finditems call to just Email) to a search filter. I've put a download of this script here the script looks like
  1. $RptObjColl = @()  
  2. $MailboxName = "user@domain.com"  
  3.   
  4. ## Load Managed API dll    
  5. Add-Type -Path "C:\Program Files\Microsoft\Exchange\Web Services\1.1\Microsoft.Exchange.WebServices.dll"    
  6.     
  7. ## Set Exchange Version    
  8. $ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP1    
  9.     
  10. ## Create Exchange Service Object    
  11. $service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService($ExchangeVersion)    
  12.     
  13. ## Set Credentials to use two options are availible Option1 to use explict credentials or Option 2 use the Default (logged On) credentials    
  14.     
  15. #Credentials Option 1 using UPN for the windows Account    
  16. $creds = New-Object System.Net.NetworkCredential("user@domain.com","password")     
  17. $service.Credentials = $creds        
  18.     
  19. #Credentials Option 2    
  20. #service.UseDefaultCredentials = $true    
  21.     
  22. ## Choose to ignore any SSL Warning issues caused by Self Signed Certificates    
  23.     
  24. [System.Net.ServicePointManager]::ServerCertificateValidationCallback = {$true}    
  25.     
  26. ## 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    
  27.     
  28. #CAS URL Option 1 Autodiscover    
  29. $service.AutodiscoverUrl($MailboxName,{$true})    
  30. "Using CAS Server : " + $Service.url     
  31.      
  32. #CAS URL Option 2 Hardcoded    
  33.     
  34. #$uri=[system.URI] "https://casservername/ews/exchange.asmx"    
  35. #$service.Url = $uri      
  36.     
  37. ## Optional section for Exchange Impersonation    
  38.     
  39. #$service.ImpersonatedUserId = new-object Microsoft.Exchange.WebServices.Data.ImpersonatedUserId([Microsoft.Exchange.WebServices.Data.ConnectingIdType]::SmtpAddress, $MailboxName)   
  40.   
  41. # Bind to the Archive Root folder    
  42. $folderid= new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::MsgFolderRoot,$MailboxName)     
  43. $MsgRoot = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$folderid)  
  44.   
  45. $fvFolderView =  New-Object Microsoft.Exchange.WebServices.Data.FolderView(1000)    
  46. #Deep Transval will ensure all folders in the search path are returned   
  47.    
  48. $fvFolderView.Traversal = [Microsoft.Exchange.WebServices.Data.FolderTraversal]::Deep;    
  49. $ivItemView = New-Object Microsoft.Exchange.WebServices.Data.ItemView(1000)    
  50. #The Search filter will exclude any Search Folders   
  51. $psPropertySet = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)    
  52. $PR_NORMALIZED_SUBJECT = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(0x0E1D,[Microsoft.Exchange.WebServices.Data.MapiPropertyType]::String);     
  53. $psPropertySet.add($PR_NORMALIZED_SUBJECT)  
  54. $PR_FOLDER_TYPE = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(13825,[Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Integer);    
  55. $sfSearchFilter = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo($PR_FOLDER_TYPE,"1")    
  56. $fiResult = $null    
  57. #The Do loop will handle any paging that is required if there are more the 1000 folders in a mailbox   
  58. $rptHash = @{}  
  59. $AQSString = "kind:email"   
  60. do {   
  61.     $ivItemView = New-Object Microsoft.Exchange.WebServices.Data.ItemView(1000)    
  62.     $ivItemView.PropertySet = $psPropertySet  
  63.     $fiResult = $Service.FindFolders($folderid,$sfSearchFilter,$fvFolderView)    
  64.     foreach($ffFolder in $fiResult.Folders){    
  65.     "Processing Folder : " + $ffFolder.displayName   
  66.     if($ffFolder.UnreadCount -gt 0){  
  67.         $fiResults = $null  
  68.         $updateColl = @()  
  69.         do{    
  70.             $fiResults = $ffFolder.findItems($AQSString,$ivItemView)  
  71.             foreach($Item in $fiResults.Items){    
  72.                 $subject = $null  
  73.                 if($Item.TryGetProperty($PR_NORMALIZED_SUBJECT,[ref]$subject)){  
  74.                     if($subject -ne $null){  
  75.                         "Processing Messsage : " + $subject  
  76.                         if($rptHash.Contains($subject) -eq $false){  
  77.                             $rptHash.add($subject,1);  
  78.                         }  
  79.                         else{  
  80.                             $rptHash[$subject] +=1  
  81.                         }  
  82.                     }  
  83.                 }  
  84.             }    
  85.             $ivItemView.Offset += $fiResults.Items.Count    
  86.         }while($fiResults.MoreAvailable -eq $true)  
  87.     }           
  88.     }   
  89.     $fvFolderView.Offset += $fiResult.Folders.Count  
  90. }while($fiResult.MoreAvailable -eq $true)   
  91.   
  92. $rptHash.GetEnumerator() | Sort-Object value -Descending | ForEach-Object{  
  93.     $rptobj = "" | select Subject, NumberofMessages  
  94.     $rptobj.Subject = $_.Key  
  95.     $rptobj.NumberofMessages = $_.Value  
  96.     $RptObjColl += $rptobj  
  97. }  
  98. $RptObjColl | Export-Csv -NoTypeInformation -path c:\temp\subjectReport.csv  
  99. $RptObjColl  

1 comment:

Anonymous said...

Hello Glen,

I am using EWS to create Mapi fields using Extended property like this :

'Dim myGuid As Guid = New Guid("0006200E-0000-0000-C000-000000000046")

'Dim Hyperlink As New ExtendedPropertyDefinition(myGuid, "Hyperlink", MapiPropertyType.String)
'Dim Policy As New ExtendedPropertyDefinition(myGuid, "Policy", MapiPropertyType.String)

'emailMsg.SetExtendedProperty(Hyperlink, "my hyperlink")

but when i try to read these fields in another application using MAPI, i am not able to..

Is it possible to do so ?

Please do let me know.

Thanks

Prateek