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