Server-Side-Filtering in PowerShell

Retrieving objects in your scripts is something that you do regularly. It takes a long time to download and process in larger environments, wasting CPU and time. This blog post will explain how to speed up scripts by filtering server-side before downloading all results and filtering afterward.

What is Server-Side-Filtering?

It’s telling the server to filter for you and give those back to your session instead of asking the server for everything and filtering yourself. The benefit here is that the amount of data being transferred is less and that the server has cached a lot of data and can return it faster to you than doing it yourself.

How and where can you use it in PowerShell?

Well… That depends on the module or function. Usually, you can tell if it’s available if there is a Filter, LDAPFiler, SearchString, or similar Parameter available. (Not all servers/services have this capability)

Examples

Below are a few examples of Server-Side-Filtering, and I clocked the difference in time while getting the data to let you see the difference using the Measure-Object cmdlet.

Active Directory

In the examples below, I query an Active Directory Domain for all users with an Office location specified in their profile. Because I use the Measure-Object cmdlet so that the output is not shown on the screen, this in itself speeds up the process too (Displaying results to your screen slows down a lot, storing it in a variable or a file is faster)

[PS] C:\Windows\system32>Measure-Command -Expression {Get-Aduser -Filter * -Properties Office | Where-Object Office -ne $null}


Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 3
Milliseconds      : 629
Ticks             : 36297737
TotalDays         : 4,20112696759259E-05
TotalHours        : 0,00100827047222222
TotalMinutes      : 0,0604962283333333
TotalSeconds      : 3,6297737
TotalMilliseconds : 3629,7737

Running this without using Server-Side-Filtering took about 3,6 seconds. Running it with a filter like this only took 0,9 seconds, and while does not like much… If you run this multiple times in your script to query something… It all adds up 🙂

[PS] C:\Windows\system32>Measure-Command -Expression {Get-Aduser -Filter {-not (Office -like "*")}}


Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 0
Milliseconds      : 882
Ticks             : 8828710
TotalDays         : 1,02184143518519E-05
TotalHours        : 0,000245241944444444
TotalMinutes      : 0,0147145166666667
TotalSeconds      : 0,882871
TotalMilliseconds : 882,871

More information about using Active Directory Filters can be found here.

Azure Active Directory

In the examples below, I query an Azure AD for all users starting with ‘Harm’ in their UserPrincipalName field. (Notice the ^-sign, which tells the match statement to search for everything starting with ‘Harm’) Running this without a filter took 16,1 seconds:

C:\Users\HarmV> Measure-Command -Expression {Get-AzureADUser -All:$true | Where-object UserPrincipalName -match '^Harm'}


Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 16
Milliseconds      : 103
Ticks             : 161037704
TotalDays         : 0,000186386231481481
TotalHours        : 0,00447326955555556
TotalMinutes      : 0,268396173333333
TotalSeconds      : 16,1037704
TotalMilliseconds : 16103,7704

Doing the same query using a Filter only took 62 Milliseconds!

C:\Users\HarmV> Measure-Command -Expression {Get-AzureADUser -Filter "startswith(UserPrincipalName,'Harm')"}


Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 0
Milliseconds      : 62
Ticks             : 625142
TotalDays         : 7,23543981481482E-07
TotalHours        : 1,73650555555556E-05
TotalMinutes      : 0,00104190333333333
TotalSeconds      : 0,0625142
TotalMilliseconds : 62,5142

More information about Azure AD filters can be found here

Exchange Online

In the examples below, I query Exchange Online for mailboxes that have Forwarding enabled. Running this without a filter took 55 seconds.

C:\Users\HarmV> Measure-Command -Expression {Get-Mailbox -ResultSize 10000 | Where-Object ForwardingSmtpAddress -ne $null}


Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 54
Milliseconds      : 511
Ticks             : 545116426
TotalDays         : 0,000630921789351852
TotalHours        : 0,0151421229444444
TotalMinutes      : 0,908527376666667
TotalSeconds      : 54,5116426
TotalMilliseconds : 54511,6426

Running the query again using a filter only took 1,6 seconds!

C:\Users\HarmV> Measure-Command {Get-Mailbox -ResultSize 10000 -Filter {ForwardingSmtpAddress -ne $null}}


Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 1
Milliseconds      : 610
Ticks             : 16105845
TotalDays         : 1,86410243055556E-05
TotalHours        : 0,000447384583333333
TotalMinutes      : 0,026843075
TotalSeconds      : 1,6105845
TotalMilliseconds : 1610,5845

More information about Exchange Online filters can be found here.

Conclusion

When possible, use filters! Saves time, network bandwidth, and CPU performance on your client 🙂