Search mailboxes for large items that may impede migrations to Exchange Online

I have a customer that will be enabling hybrid mode soon and moving mailboxes to Exchange Online. One part of the project entails finding mailboxes that will not have a successful migration because they contain items over 150 MB. I referred them to the script on the TechNet Gallery that does exactly that. When that script was run against an admin’s mailbox, it took 10 minutes to complete. Extrapolating that single mailbox’s time for all 80,000 mailboxes (555 days) is far from accurate, but it does indicate that the process would likely take far longer than they have available to complete that part. (The extrapolation also doesn’t factor running multiple threads of the script.) So I looked at the script to see how it is doing what it does. It enumerates every folder, searches every folder for every item in it, then looks at the size of each item so it can report how many total items there are in each folder and how many are over the size limit.

So they could get results in far shorter time, I wrote a script that uses the EWS Managed API and leverages the hidden AllItems search folder created by Outlook 2010+ when it connects to a mailbox. Since it isn’t a well-known folder name, you have to find the folder first. The following code searches the root of the mailbox for a search folder (folder property type is 2) whose display name is AllItems:

$folderIdRoot = New-Object -TypeName Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Root,$mailbox)
$folderView = New-Object -TypeName Microsoft.Exchange.WebServices.Data.FolderView(10)
$folderView.Traversal = [Microsoft.Exchange.WebServices.Data.FolderTraversal]::Shallow
$propFolderType = New-Object -TypeName Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(13825,[Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Integer)
$folderSearchFilter1 = New-Object -TypeName Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo($propFolderType,"2")
$folderSearchFilter2 = New-Object -TypeName Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.FolderSchema]::DisplayName,"AllItems")
$folderSearchFilterColl = New-Object -TypeName Microsoft.Exchange.WebServices.Data.SearchFilter+SearchFilterCollection([Microsoft.Exchange.WebServices.Data.LogicalOperator]::And)
$folderSearchFilterColl.Add($folderSearchFilter1)
$folderSearchFilterColl.Add($folderSearchFilter2)
$folderSearchResult = $exchangeService.FindFolders($folderIdRoot,$folderSearchFilterColl,$folderView)

What if the folder doesn’t exist? If Outlook 2010+ for Windows hasn’t been used against the mailbox, the folder won’t exist. If this is the case, the folder needs to be created. To determine the search restriction used for the folder when created by Outlook, I used MFCMAPI and saw that there is only one: the item has the message class property populated. To create the same search folder with EWS:

$searchFolder = New-Object Microsoft.Exchange.WebServices.Data.SearchFolder($exchangeService)
$folderSearchFilter = New-Object -TypeName Microsoft.Exchange.WebServices.Data.SearchFilter+Exists([Microsoft.Exchange.WebServices.Data.ItemSchema]::ItemClass)
$searchFolder.SearchParameters.SearchFilter = $folderSearchFilter
$searchFolder.SearchParameters.Traversal = [Microsoft.Exchange.WebServices.Data.SearchFolderTraversal]::Deep
$msgRootFolderId = New-Object -TypeName Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::MsgFolderRoot)
$searchFolder.SearchParameters.RootFolderIds.Add($msgRootFolderId)
$searchFolder.DisplayName = 'AllItems'
$folderIdRoot = New-Object -TypeName Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Root,$mailbox)
$searchFolder.Save($folderIdRoot)

When you create a search folder you need to specify the folders to search and whether subfolders are included, the name of the folder, the search parameters (restrictions), and where to put it. In this case, the folder to search is the well-known folder MsgFolderRoot, which is the visible root folder in the IPM subtree, and subfolders are included by specifying a deep traversal. (This means the recoverable items folders are not included. If you want to include them, you can add to RootFolderIds with the well-known folder for Recoverable Items.) The search parameter is that the ItemClass property exists. (This translates to PR_MESSAGE_CLASS when viewed with MFCMAPI.) The folder is then saved in the root of the mailbox. The folder search can be run again to get the newly created folder.

To get a count of any items that are over 150 MB, do a search against that search folder using a query string. This type of search leverages the content index and is faster than using a search filter with a restriction. This search returns the count of any items over 150 MB, where $maxSize is an integer representing the limit in MB:

$searchBase = $folderSearchResult.Folders[0]
$itemSizeBytes = ($maxSize.ToString()+'MB')/1l
$searchQuery = "Size:>$itemSizeBytes"
$propertySet = New-Object -TypeName Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::IdOnly)  
$itemView = New-Object -TypeName Microsoft.Exchange.WebServices.Data.ItemView(10)
$itemView.PropertySet = $propertySet
$itemSearchResults = $searchBase.FindItems($searchQuery,$itemView)

Putting this all together, the script takes an email address (or mailboxes or email addresses from the pipeline), looks for the search folder, creates it if missing and looks for it again, searches for any items in the search folder over a given size, and outputs an object with the email address, the number of items found, and any errors. Running it in the customer’s environment went from 10 minutes per mailbox to 14 mailboxes per minute. You can pipeline the output to CSV to use a source with the large item script from TechNet to get more details of which folder has the oversize items, etc.

For performance, the autodiscover URL of the first mailbox in the pipeline will be cached and used for subsequent mailboxes. Or you can specify a URL to use instead. The default item limit is 150 MB, but you can specify any size you want. There is a switch to use impersonation; otherwise, full mailbox access is needed. I found that you don’t need any permission to a mailbox in order to bind to the root folder, so if you then do a search for the search folder, you get the same result when there isn’t a folder or when you don’t have permission. Therefore, the script checks the account’s permission to the root folder (which is contained in the bind response). Depending on what you want to do with the output, such as feed it to the TechNet script, you can choose to not include mailboxes with 0 large items in the output with the appropriate switch. Lastly, since creating a search folder and waiting for it to initially be populated can take a little time, when a mailbox needs the search folder created and you want to know that it is doing so, use the Verbose switch to see that in the console.

You can download the script via the link below or expand and copy code:

  Get-MailboxLargeItemCount.ps1 (8.3 KiB)

<#
	.Synopsis
		Get number of items in a mailbox over a given size
	.Description
		Get number of items in a mailbox over a given size, leveraging the AlItems search folder.
		If the folder does not exist, it will be created.
	.Parameter EmailAddress
		Email address of the mailbox.  Accepts pipeline input from Get-Mailbox.
	.Parameter EWSUrl
		To not use autodiscover, specify the URL to use for EWS.
	.Parameter Credential
		Provide credentials to use instead of the current user.
	.Parameter EWSApiPath
		Explicit path to EWS API DLL if it has not been installed via setup routine.
	.Parameter UseImpersonation
		Switch to specify connection to the mailbox via impersonation instead of
		full mailbox access.
	.Parameter MaxItemSizeMB
		Integer, in megabytes, of the item size that must be exceeded to be included in the count.
		Default is 150.
	.Parameter DoNotIncludeZeroCountMailboxInOutput
		Switch to indicate that a successful search that returns 0 matching items should not be 
		included in the output.
	.Example
		Get-MailboxLargeItemCount.ps1 -EmailAddress johndoe@company.com -Credential (get-credential)
	.Example
		Get-Mailbox johndoe | Get-MailboxLargeItemCount.ps1 -EWSUrl 'https://owa.company.com/ews/exchange.asmx' -UseImpersonation
	.Notes
		Version: 1.1
		Date: 2/10/16
	#>
	
[CmdletBinding()]
param
	(
	[parameter(Mandatory=$true,Position=0,ValueFromPipelinebyPropertyName=$true)][Alias('PrimarySMTPAddress')][string]$EmailAddress,
	#Requires -Version 3
	[pscredential]$Credential, #If not using v3+, you can remove the [pscredential] accelerator reference
	[string]$EWSUrl,
	[string]$EWSApiPath,
	[switch]$UseImpersonation,
	[int]$MaxItemSizeMB = 150,
	[switch]$DoNotIncludeZeroCountMailboxInOutput
	)
begin
	{
	$i = 0
	function Get-SearchFolder ($mailbox)
		{
		$folderIdRoot = New-Object -TypeName Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Root,$mailbox)
		$folderView = New-Object -TypeName Microsoft.Exchange.WebServices.Data.FolderView(10)
		$folderView.Traversal = [Microsoft.Exchange.WebServices.Data.FolderTraversal]::Shallow
		#Property that indicates type of folder
		$propFolderType = New-Object -TypeName Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(13825,[Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Integer)
		#Folder property that equates to search folder
		$folderSearchFilter1 = New-Object -TypeName Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo($propFolderType,"2")
		$folderSearchFilter2 = New-Object -TypeName Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.FolderSchema]::DisplayName,"AllItems")
		$folderSearchFilterColl = New-Object -TypeName Microsoft.Exchange.WebServices.Data.SearchFilter+SearchFilterCollection([Microsoft.Exchange.WebServices.Data.LogicalOperator]::And)
		$folderSearchFilterColl.Add($folderSearchFilter1)
		$folderSearchFilterColl.Add($folderSearchFilter2)
		if (([Microsoft.Exchange.WebServices.Data.Folder]::Bind($exchangeService,$folderIdRoot)).EffectiveRights -eq [Microsoft.Exchange.WebServices.Data.EffectiveRights]::None)
			{
			return 'NoPerm'
			}
		,$exchangeService.FindFolders($folderIdRoot,$folderSearchFilterColl,$folderView)
		}
	function Create-SearchFolder ($mailbox)
		{
		$searchFolder = New-Object Microsoft.Exchange.WebServices.Data.SearchFolder($exchangeService)
		#Include all items that have a message class
		$folderSearchFilter = New-Object -TypeName Microsoft.Exchange.WebServices.Data.SearchFilter+Exists([Microsoft.Exchange.WebServices.Data.ItemSchema]::ItemClass)
		$searchFolder.SearchParameters.SearchFilter = $folderSearchFilter
		$searchFolder.SearchParameters.Traversal = [Microsoft.Exchange.WebServices.Data.SearchFolderTraversal]::Deep
		#Include all items in the visible folder structure
		$msgRootFolderId = New-Object -TypeName Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::MsgFolderRoot)
		$searchFolder.SearchParameters.RootFolderIds.Add($msgRootFolderId)
		$searchFolder.DisplayName = 'AllItems'
		#Save the folder in the mailbox root (not visible to users)
		$folderIdRoot = New-Object -TypeName Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Root,$mailbox)
		try
			{
			$searchFolder.Save($folderIdRoot)
			$true
			}
		catch
			{
			$false
			}
		}
	function Get-ItemsOverSize ($folder, $maxSize)
		{
		$itemSizeBytes = ($maxSize.ToString()+'MB')/1l
		$searchQuery = "Size:>$itemSizeBytes"
		$propertySet = New-Object -TypeName Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::IdOnly)  
		$itemView = New-Object -TypeName Microsoft.Exchange.WebServices.Data.ItemView(10)
		$itemView.PropertySet = $propertySet
		,$searchBase.FindItems($searchQuery,$itemView)
		}
	}
process
	{
	#Test if any version of API is installed before continuing
	if ($EWSApiPath)
		{$apiPath = $EWSApiPath}
	else
		{
		$apiPath = (($(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')
		}
	if (Test-Path $apiPath)
		{
		Add-Type -Path $apiPath
		}
	else
		{
		Write-Error "The Exchange Web Services Managed API is required to use this script." -Category NotInstalled
		break
		}
	$exchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP2
	$exchangeService = New-Object -TypeName Microsoft.Exchange.WebServices.Data.ExchangeService($exchangeVersion)
	if ($Credential)
		{
		$exchangeService.Credentials = New-Object -TypeName Microsoft.Exchange.WebServices.Data.WebCredentials($Credential)
		}
	if ($EWSUrl)
		{
		$exchangeService.Url = $EWSUrl
		}
	elseif ($i -eq 0)
		{
		#Improve autodiscover performance for foreign mailboxes by disabling SCP 
		$exchangeService.EnableScpLookup = $false
		$exchangeService.AutodiscoverUrl($EmailAddress, {$true})
		#Cache the autodiscover URL for subsequent objects in the pipeline
		$autoEWSUrl = $exchangeService.Url
		}
	else
		{
		$exchangeService.Url = $autoEWSUrl
		}
	if ($UseImpersonation)
		{
		$exchangeService.ImpersonatedUserId = New-Object -TypeName Microsoft.Exchange.WebServices.Data.ImpersonatedUserId([Microsoft.Exchange.WebServices.Data.ConnectingIdType]::SmtpAddress, $EmailAddress)
		}
	$include = $null
	$output = "" | Select-Object 'EmailAddress','ItemsOverSize','Note'
	$output.EmailAddress = $EmailAddress
	try
		{
		#Get AllItems search folder
		$folderSearchResult = Get-SearchFolder -mailbox $EmailAddress
		if ($folderSearchResult -eq 'NoPerm')
			{
			throw 'Error'
			}
		try
			{
			$searchBase = $folderSearchResult.Folders[0]
			if ($searchBase)
				{
				#Search for any items over the limit
				$itemSearchResult = Get-ItemsOverSize -folder $searchBase -maxSize $MaxItemSizeMB
				if ($itemSearchResult.TotalCount -gt 0)
					{
					$output.ItemsOverSize = $itemSearchResult.TotalCount
					$include = $true
					}
				elseif ($itemSearchResult.TotalCount -eq 0 -and -not ($DoNotIncludeZeroCountMailboxInOutput))
					{
					$output.ItemsOverSize = 0
					$include = $true
					}
				}
			else #Mailbox is missing the AllItems search folder
				{
				#Create AllItems search folder
				Write-Verbose "Creating search folder for $($EmailAddress)"
				if (Create-SearchFolder -mailbox $EmailAddress)
					{
					$folderSearchResult = Get-SearchFolder -mailbox $EmailAddress
					$searchBase = $folderSearchResult.Folders[0]
					#Search for any items over the limit
					$itemSearchResult = Get-ItemsOverSize -folder $searchBase -maxSize $MaxItemSizeMB
					if ($itemSearchResult.TotalCount -gt 0)
						{
						$output.ItemsOverSize = $itemSearchResult.TotalCount
						$include = $true
						}
					elseif ($itemSearchResult.TotalCount -eq 0 -and -not ($DoNotIncludeZeroCountMailboxInOutput))
						{
						$output.ItemsOverSize = 0
						$include = $true
						}
					}
				else
					{
					$output.Note = 'ErrorCreatingSearchFolder'
					$include = $true
					}
				}
			}
		catch {}
		}
	catch
		{
		$output.Note = 'ErrorSearchingFolders'
		$include = $true
		}
	if ($include)
		{
		$output
		}
	$i++
	}

Leave a Reply

Your email address will not be published. Required fields are marked *

*