Why a query with PowerShell in Active Directory should be built very precisely!

 

Dear Microsoft and PowerShell Friends,

 

In this article I will describe why it is important to create a query with PowerShell in Active Directory as precisely as possible.

 

Imagine you have to find all accounts where the value "Luzern" is entered in the attribute "City". This may not be a challenge at first sight. Let's take it step by step. Let's first see how many user accounts there are in my demo Active Directory.

 

(The hashtags are comments):

#How many AD object are there?
Get-ADUser -Filter * | Measure-Object

 

These are really very few accounts. After all, it is only a test environment. But imagine if there were 1000, 10,000 or 50,000 accounts. Does that make a difference? YES! This can have a bad effect on the performance of the server (domain controller) during a query.

 

Now let's find those accounts which have the value "Luzern" in the attribute "City" and we will measure the query time. I will first be very general with the queries and then become more and more precise. Let's rock!

 

#A first search
Get-ADUser -Filter * -Properties * | Where-Object {$PSItem.city -eq "Luzern"} |
Select-Object Name,Department,Title

 

We get the result means we see the accounts we were looking for. But how long did this query take? Let us measure this.

 

#How long does this search take? We focus on the TotalSeconds
Measure-Command {Get-ADUser -Filter * -Properties * |
Where-Object {$PSItem.city -eq "Luzern"} | Select-Object Name,Department,Title}

 

This query took (TotalSeconds) 0.0442281 seconds. We do the exact same query again but we now focus directly on the "TotalSeconds".

 

#So we get the TotalSeconds right away
(Measure-Command {Get-ADUser -Filter * -Properties * | Where-Object {$PSItem.city -eq "Luzern"} | Select-Object Name,Department,Title}).TotalSeconds

 

The query time is about the same. That fits. But now we will optimize the query a bit and then measure the time again.

 

#Now we adjust our search and measure the time again
(Measure-Command {Get-ADUser -Filter {city -eq "Luzern"} -Properties * |
Select-Object Name,Department,Title}).TotalSeconds

 

We have already been able to reduce the query time! Now let us make the query even more precise and we will measure again.

 

#And again an adjustment of the search and measure again
(Measure-Command {Get-ADUser -Filter {city -eq "Luzern"} -Properties Name,Department,Title | Select-Object Name,Department,Title}).TotalSeconds

 

Awesome. We achieved a query time of 0.0084811 (instead of 0.0442281) seconds. Super! Don't forget we are testing in a very small environment here. What would the times be like if the Active Directory had thousands of accounts?

 

Clearly, that was not super spectacular or fancy. But I still wanted to share my experience with you.

 

In this article I wanted to show how important it is to create the PowerShell queries as precisely as possible. Thank you for taking the time to read this article.

 

Kind regards, Tom Wechsler

 

P.S. All scripts (#PowerShell, Azure CLI, #Terraform, #ARM) that I use can be found on github! https://github.com/tomwechsler