PowerShell module for managing automated distribution groups

Years ago I used a third-party application for creating, managing, and updating automated DLs in Exchange.  When I moved to another company that didn’t have that application, nor would spend the money to buy it, I wrote a script as a poor-man’s version of that application.  Since then, it has been updated with more features, and I recently updated it again for a customer who wanted features I had never needed.

You might wonder why not use dynamic distribution groups?  There are a number of limitations when using DDGs that automated DGs (née DLs) overcome:

  • Use the group as a security principal
  • Allow static members (those that should be members even though they don’t match the filter)
  • Use any LDAP attribute in the schema in the search filter, not just those exposed via OPATH
  • View the group membership (when an end user)

The way the module works is it marks an existing DG as automated and updates the Note property stating that it is an automated group; you enter an LDAP filter to use for membership, optionally specifying any static members and domains to restrict membership to, and save it; and the membership criteria are saved as a binary value in the extensionData attribute.  Then you can run Update-AutoDL to update the membership of that one group or all (or all in a specified domain), or schedule a task to run that cmdlet to update membership, say, daily.  If you want to download it without reading all of the details:

  AutoDLManagement.zip (9.2 KiB)

Prerequisites:

  • Permission to manage the group objects in Active Directory
    The module uses ADSI to manage the groups, search for members, and to update membership.
  • A shell with Exchange cmdlets loaded
    A few of the Exchange cmdlets are used to validate that a group exists, and to enable it for automation.

Installation:

  • Put the module in an appropriate directory
    If you want the module to be implicitly loaded, or explicitly loaded without specifying a path, put the module inside a directory of the same name inside the Modules directory of your profile, e.g., C:\Users\<username>\Documents\Windows PowerShell\Modules\AutoDLManagement\AutoDLManagement.psm1.  You can put it anywhere if you want to load it manually and specify the full path to it.
  • For interactive or scheduled execution, load the Exchange cmdlets
    Whether using implicit remoting or a shell loading the Exchange snap-in locally, you’ll want to have the Exchange cmdlets loaded in the session before running any of the AutoDL cmdlets.

Features of the AutoDLManagement module:

  • Uses an LDAP filter as membership criterion
    Note: There is no syntax checking for the filter, so be sure you enter a valid one.
  • Membership criteria are stored in the group itself
    There is no external dependency except for the module to update the group membership.
  • Group can contain static members
    Include recipients that do not match the search filter.  The attribute used to match static members can be customized.  (The default is sAMAccountName.)
  • Restrict membership to specified domains
    The default configuration is to include all matching recipients in the forest, but you can specify one or more domains in the forest that membership is restricted to.
  • Global filter that applies to all groups
    This is a filter that is applied in addition to a group’s filter, such as not including terminated users.
  • Customize which custom attribute (extensionAttributeN) and what text string are used to indicate a group is automated
  • Email notification when a group update fails or membership is zero when it wasn’t previously
    If the module is unable to add members after clearing membership, resulting in no members, you are notified so you don’t end up with a group that people are emailing and no one gets the message.
  • Supports all versions of Exchange, including Exchange Online
    Note: Exchange Online support requires groups to be authoritative on-premises, syncing to Exchange Online via AAD Connect.
  • Display membership criteria in both “raw” and “pretty” formats
    When you run Get-AutoDLFilter, the result is an IE window that displays the LDAP filter, the static members, and domains for membership.  The filter is displayed as both a raw string that you can copy and paste for editing, and as a formatted filter (based on my LDAP-formatting script described here) that can be easier to read and is useful when sending a filter to a user.

  • Mirror membership from other groups
    Sometimes a group owner wants the membership to mirror a security group because it isn’t mail-enabled.  You can mirror membership of one or more groups by setting the LDAP filter to be “guid:<GUID of group>”.  Separate multiple GUIDs with a semicolon.
  • Logging of each group that is updated, including old and new count
  • Ability to update the membership of one group, all groups, or groups in a specified domain
    When running Update-AutoDL, you can provide the identity of a group, use the UpdateDomain parameter to specify a single domain’s groups to update, or no arguments to update all groups in the forest.  (If you update all groups interactively, it will ask for confirmation.  If updated via a scheduled task, the confirmation is suppressed.)
  • Set/Update a group’s criteria via command line or GUI
    Run Set-AutoDLFilter (alias sadl) with the identity of a group.  If the group is not already managed/automated, it will ask if you want to enable it:
    After entering ‘y’, the GUI will display so you can enter membership criteria:
    The form performs basic checks in order to enable the OK button: if static members is checked, then the text box has to be populated; if domain restrictions is checked, then at least one domain must be checked.When you run the cmdlet with a group that already has membership criteria, the form will be populated with the appropriate values, so you only have to modify what is to be changed about the criteria.For command-line updating, use the appropriate parameters for the filter, static members, and domain restrictions.  (The parameters are documented in the cmdlet’s help.)  If you update from the command-line, you need to include all values even if it isn’t changing.  For example, if you are modifying the LDAP filter for a group that has static members, you need to specify those members even if they aren’t changing.  Otherwise, the static members (or domain restrictions) will be lost.
  • Pipeline support
    For bulk-enabling and -changing groups, you can pipe groups to the Set-AutoDLFilter cmdlet.  This allows you to bulk-enable groups for automation and set their membership criteria if you already have that information in, say, a CSV.  You can use a switch parameter with the cmdlet to suppress the confirmation prompt to enable automation for a group.

Functionality that I am considering adding in the future:

  • Static exclusions
    If there are recipients you don’t want to be in the group even though they match the criteria, you can specify them.  (You can do this now by adding the username to the filter with a NOT operator, e.g., (!samaccountname=johndoe).)
  • Add object picker for selecting groups for mirrored membership
    Currently, you need to manually enter the source group’s GUID in the LDAP filter field (preceded by guid:).  The object picker would let you browse the forest and select a group to have its GUID automatically entered.
  • Perform syntax checking of an LDAP filter
  • Preview the pending membership of a group
    In the criteria editor, you could click a button to preview the membership based on the criteria currently specified (and without having to save it).
  • Add/remove only changed members
    Currently, group membership is cleared and then updated with all objects returned in the search.  This has an element of risk because if a failure occurs between clearing and adding membership, the group is left with no members.  This feature would compare the current and pending memberships and only add/remove the necessary objects.

If these or other features would be of interest to you, let me know in the comments.  Download the module:

  AutoDLManagement.zip (9.2 KiB)

Outlook macro to assign retention policy when item is deleted

I described in another post how I need to assign a retention policy tag to items in my Deleted Items folder because there isn’t a retention policy tag set on the folder.  I also set a different tag to items in my Sent Items folder.  While I run the script in that post every week or so, because of the large volume of mail I receive and subsequently delete, the script will sometimes need to process over a thousand items.  Updating that many items with EWS is not the fastest process, so I looked into how Outlook can do it for me at the time a message is put in its respective folder.

Applying a personal tag to sent items is easy because a rule can be created to assign the tag when items are sent:
Outlook RuleTo have a tag assigned when items are moved to the Deleted Items folder, I needed to create a macro that runs when the items are moved.  This can be done with an event that is registered when Outlook starts.  Paste the following code into ThisOutlookSession and restart Outlook.  (You will want to change the two constants to be the GUID and number of days for your tag.)

Public WithEvents deletedItems As Outlook.Items

Public Sub Application_Startup()
    Set deletedItems = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderDeletedItems).Items
End Sub

Public Sub deletedItems_ItemAdd(ByVal Item As Object)
    Const retPolicy As String = "03DACE3336054C428ECAE839E0BC5945" '30 Day tag GUID
    Const retPeriod As Long = 30 'Number of days the tag is set to expire items
    Set pa = Item.PropertyAccessor
    p = Empty
    On Error Resume Next
    p = pa.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x30190102") 'Get policy tag
    On Error GoTo 0
    If IsEmpty(p) Then
        IsEqual = False
    ElseIf pa.BinaryToString(p) <> retPolicy Then
        IsEqual = False
    Else
        IsEqual = True
    End If
    If Not IsEqual Then
        msgDate = Empty
        On Error Resume Next
        msgDate = pa.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E060040") 'Get delivery date
        On Error GoTo 0
        If IsEmpty(msgDate) Then
            msgDate = pa.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x30070040") 'Get creation date
        End If
    pa.SetProperty "http://schemas.microsoft.com/mapi/proptag/0x30190102", pa.StringToBinary(retPolicy) 'Set poliy tag
    pa.SetProperty "http://schemas.microsoft.com/mapi/proptag/0x301A0003", retPeriod 'Set retention period days
    pa.SetProperty "http://schemas.microsoft.com/mapi/proptag/0x301C0040", msgDate + retPeriod 'Set date of expiration
    Item.Save
    End If
End Sub

Both the rule and the macro only run when Outlook is the client doing the operation, so any messages sent or deleted from a mobile device (or OWA) won’t have the respective tag assigned. This means I still have a need to run the script, but it will need update far fewer items and, therefore, complete in much less time.

Delegate management module updated to 1.4.6

A small update has been made so that comparing delegates to users with Full Access and Send As works for Exchange Online accounts. The code to translate a user ID to a SID returns an error when used against Exchange Online. If the connection mode is for Exchange Online, it will now use SMTP address to match a delegate to a user with Full Access and Send As permissions. Download the updated module here:

  DelegateManagement.zip (7.6 KiB)

Default folder retention tag script updated to 1.3

Articles in the "Retention tag on default folder items" series

  1. Use EWS to apply retention policy to items in a default folder
  2. Script to set retention tag on default folder items updated to v1.1.1
  3. Default folder retention tag script updated to 1.3 [This article]

I spent some time figuring out why calendar items in the Deleted Items folder that would be immediately expired could not be updated with a tag. I found that Exchange wants to send an update to the organizer, regardless whether the calendar item is even a meeting or one that is in the past. If the item is in a different folder when the tag is applied, the error is that there is no recipient on the message, whereas the error when in the Deleted Items is that it can’t update an item that is already deleted. The latter error being misleading is why I originally couldn’t figure it out what they meant or why it was happening.

The solution is to use a second argument in the Update method to tell Exchange not to send an update message for invitations or cancellations. You have to do this even if it isn’t a meeting. Presumably, this is a bug where Exchange is running through logic against all IPM.Appointment-class items, rather than skipping those that aren’t meetings.

The script has been updated to account for this, plus some minor updates since the last version posted.

  Set-DefaultFolderItemsTag.zip (2.6 KiB)

Get Inumbo message tracking records with PowerShell

Inumbo is a cloud-based mail hygiene solution.  Whether using their free or paid subscription, you can search the message tracking log in the web portal to see a history of messages being processed for your subscription.  The main thing I use it for is to know if I haven’t received a message because it was marked as spam.  But rather than use the web portal, you can also use their REST API to programmatically get tracking records.  So instead of logging into the portal and searching, you just run a script and perform filtered searches and also get more information that what is exposed in the portal.

You need to get a read-only or read/write API key since that is what is used to authenticate the request.  You don’t have to use any search restrictions, and my script doesn’t require any either, but usually you’ll want to narrow it down to a time frame, sender, recipient, or action performed (delivered, rejected, etc.)  I have included these common search parameters, plus subject and sender IP.  Subject is a substring filter, and the start date and end date parameters are not mutually exclusive.

Speaking of dates, the API requires the format to be in epoch time (number of seconds since 1/1/1970, aka UNIX time).  To convert a .NET DateTime object to UNIX time, I use a method that was introduced in .NET 4.6.  If you aren’t using 4.6+, you can modify the Get-UnixTime function (at line 61) to calculate it using a time span, and there is link in the script for how to do that.  Furthermore, the script will account for time zone in the request and the response, so you can use “3/23/16 7:00 AM,” “3/23/16 7:00 AM -0700,” or “3/23/16 2:00 PM UTC” and get the same results, and the time stamp for each result will be in local time.

There are a number of properties for a record, so I only keep the ones that are relevant for normal queries.  These are time stamp, action, sender IP, sender, recipient, subject, RPD (anti-spam) score, SpamAssassin score, and description (why it was rejected or next hop information).  That’s still nine properties, too many to display in a table and see enough of the values to be meaningful, and I want the default output to be as a table.  So I specified the default properties to return five of the nine in a table.

How did I do that?  I created a custom formatting file (included in the download).  The file specifies that, for the custom type I assign to the record object, the default view is a table with five properties and specific widths for the columns, except for the subject which will fill the rest of the width.  To use the file, you need to run Update-FormatData .\TrackingInfo.format.psx1.  You will need to this once in each time you open a new shell.  You can add the command to your profile or even add the line to the script.  If you don’t use the formatting file, I still set a default property set in the script so the five properties are displayed, but the default will be in a list.  You, of course, can use standard format and export cmdlets to choose the properties displayed and how they are displayed.  So, if you want to see all properties, pipeline the results to, for example, Format-List -Property *.

The script’s code can be expanded and seen below, but you can download the script and the formatting file in the below attachment.

  Get-InumboTracking.zip (2.9 KiB)

<#
	.Synopsis
		Retrieve message tracking information from Inumbo
	.Description
		Get message tracking details for messages from Inumbo, optionally filtering on
		sender, recipient, date range, IP, subject, and action.
	.Parameter Action
		Restrict search results to valid action: quarantine, deliver, delete, bounce, reject, defer, or error.
	.Parameter Sender
		Sender email address to restrict search results.
	.Parameter Recipient
		Recipient email address to restrict search results.
	.Parameter Subject
		Substring of subject to restrict search results.
	.Parameter SenderIP
		IP address of sending server to restrict search results
	.Parameter StartDate
		Date and time to restrict the search results to messages with a timestamp
		after the date provided. Any DateTime object, or string that can be converted
		to DateTime, can be used and time zone will be accounted for. It can be used
		with or without EndDate.
	.Parameter EndDate
		Date and time to restrict the search results to messages with a timestamp
		before the date provided. Any DateTime object, or string that can be converted
		to DateTime, can be used and time zone will be accounted for. It can be used
		with or without StartDate.
	.Parameter ResultSize
		Maximum number of records to return.  There is no default other than any
		limit that Inumbo may enforce.  If a number larger than 50 is specified, the
		result size will be the number of available records if the total is within
		the next higher multiple of 50, or the next higher multiple of 50 if there
		are more records.  For example, if the ResultSize parameter is 65 and there
		are 80 records, the result size will be 80; if there are 120 records, the
		result size will be 100.
	.Example
		Get-InumboTracking.ps1 -StartDate "3/21/16 7:00 AM"
	.Example
		Get-InumboTracking.ps1 -Sender johndoe@company.com -EndDate "3/18/16 9:00 AM -0700"
	.Notes
		Version: 1.0
		Date: 3/21/16
#>

Param (
	[ValidateSet('QUARANTINE', 'DELIVER', 'DELETE', 'BOUNCE', 'REJECT', 'DEFER', 'ERROR')][string]$Action,
	[ValidateScript({$_ -as [System.Net.Mail.MailAddress]})][string]$Sender,
	[ValidateScript({$_ -as [System.Net.Mail.MailAddress]})][string]$Recipient,
	[string]$Subject,
	[ValidateScript({$_ -as [System.Net.IPAddress]})][string]$SenderIP,
	[DateTime]$StartDate,
	[DateTime]$EndDate,
	[int]$ResultSize
	)

#Put your Inumbo read-only or read/write key below
$apiKey = '123456789abcdefghijklmnopqrst'

#This function requires .NET 4.6
#If using a lower version, the function can be modified to use a timespan to make the calculation
#http://stackoverflow.com/questions/4192971/in-powershell-how-do-i-convert-datetime-to-unix-time
function Get-UnixTime ($date)
	{
	$dateOffset = New-Object -TypeName System.DateTimeOffset($date)
	$dateOffset.ToUnixTimeSeconds()
	}

function Get-RPDScoreTranslation ($score)
	{
	#RPD is CYREN's Recurrent Pattern Detection anti-spam engine
	if (-not($score))
		{return $null}
	switch ([int]$score)
		{
		0	{$return = 'Unknown'}
		10 	{$return = 'Suspect'}
		40 	{$return = 'ValidBulk'}
		50 	{$return = 'Bulk'}
		100 	{$return = 'Spam'}
		}
	$return
	}

#Convert API key to credential object to send as a password
$credential = New-Object -TypeName Management.Automation.PSCredential('api',(ConvertTo-SecureString $apiKey -AsPlainText -Force))
$url = 'https://api.inumbo.com/v1/tracking'

#Build query
if ($Action -or $Sender -or $Recipient -or $SenderIP -or $Subject -or $StartDate -or $EndDate)
	{
	$queryString = '&query='
	if ($Action)
		{
		$queryString += 'action=' + $Action.ToUpper() + ' '
		}
	if ($Sender)
		{
		$queryString += "from=$Sender "
		}
	if ($Recipient)
		{
		$queryString += "to=$Recipient "
		}
	if ($SenderIP)	
		{
		$queryString += "ip=$SenderIP "
		}
	if ($Subject)
		{
		$queryString += "subject~$Subject "
		}
	if ($StartDate)
		{
		#Convert date to Unix time format needed by API
		$start = Get-UnixTime $StartDate
		$queryString += "time>$start "
		}
	if ($EndDate)
		{
		#Convert date to Unix time format needed by API
		$end = Get-UnixTime $EndDate
		$queryString += "time<$end "
		}
	$queryString = $queryString.TrimEnd(' ')
	}
	
if ($ResultSize -le 50 -and $ResultSize -gt 0)
	{
	#Use limit parameter if result size specified is 50 or less
	$limitParam = "?limit=$ResultSize"
	$fullUrl = $url + $limitParam + $queryString
	$response = Invoke-RestMethod -Method Get -Uri $fullUrl -Credential $credential
	$result = $response.items
	}
else
	{
	$offset = 0
	do
		{
		#If no result size or larger than 50, use paging to build result object
		$offsetParam = "?offset=$offset"
		$fullUrl = $url + $offsetParam + $queryString
		$response = Invoke-RestMethod -Method Get -Uri $fullUrl -Credential $credential
		$result += $response.items
		$offset = $offset + 50
		}
	until ($response.total_count -eq 0 -or $result.Count -ge $ResultSize)
	}

#Filter results to contain desired properties
$output = $result | Sort-Object -Property msgts | 
	Select-Object -Property @{n='Timestamp';e={([DateTime]$_.msgts0).ToLocalTime()}},
		@{n='Action';e={$_.msgaction}},
		@{n='SenderIP';e={$_.msgfromserver}},
		@{n='Sender';e={$_.msgfrom}},
		@{n='Recipient';e={$_.msgto}},
		@{n='Subject';e={$_.msgsubject}},
		#Convert spam rating to corresponding word meaning
		@{n='RPDScore';e={Get-RPDScoreTranslation $_.score_rpd}},
		#SpamAssassin rating (>=5 means spam)
		@{n='SAScore';e={[math]::Round($_.score_sa,1)}},
		@{n='Description';e={$_.msgdescription}}

#Add custom type for use with formatting file
foreach ($entry in $output)
	{
	$entry.pstypenames.Insert(0,'Tracking.Information')
	}

#Create the default property display set (mostly for use without formatting file)
$defaultDisplaySet = 'Timestamp','Action','Sender','Recipient','Subject'
$defaultDisplayPropertySet = New-Object -TypeName System.Management.Automation.PSPropertySet('DefaultDisplayPropertySet',[string[]]$defaultDisplaySet)
$psStandardMembers = [System.Management.Automation.PSMemberInfo[]]@($defaultDisplayPropertySet)
$output | Add-Member -MemberType MemberSet -Name PSStandardMembers -Value $psStandardMembers

Write-Output $output

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++
	}