What the heck is going on with this AD filter in PowerShell?

Question

I recently wrote this answer and stumbled on something interesting.

get-aduser -filter {-not (description -eq "auto")} | measure-object

and

get-aduser -filter {description -ne "auto"} | measure-object

return two very different things when run against the same data, with the first command returning the expected value. It appears at first glance that users with a null value in the description field are not returned in as matches in the second command, even though NULL clearly doesn't equal "auto".

A couple of people in chat have looked at this and verified that I'm not insane. What's going on here?

Answer 1

The key difference between the two is the first command does not involve a direct comparison of values to get all the results, and the second command does. The first command includes NULL results and the second one does not (as MDMarra already discovered). Both commands start with this cmdlet:

get-aduser

When going through the below, remember that the results of this cmdlet include all AD users regardless of anything else in the -filter parameter after it.

Now let's break down the two parts that are different. The first one:

{-not (description -eq "auto")}

...means

1. "figure out where the description attribute equals the text string "auto". For this comparison to work, a string needs to exist in the description field for the -eq operator to be able to compare it to "auto". NULL values are dropped from this comparison since it can't compare a NULL to a string value.
2. independently of the -eq filter parameter give me EVERYTHING that is NOT the result of (description -eq "auto"), which will include NULLs, because the original cmdlet, get-aduser, includes all AD users. It did not have to compare anything to anything else with the -not operator. It just gave you everything besides what the results of the (description -eq "auto") filter were.

In your example suppose you have 1 AD user that has their description equal to "auto", a few hundred with something other than "auto" and a few hundred with NULL descriptions. Stepping through the command logic it will do:

1. Give me all AD users (get-aduser) where the description equals "auto" - results in 1 user
2. Give me all the AD users that are NOT what you just gave me - result is the few hundred with something else AND the few hundred that have NULL.

Since it didn't have to compare anything to anything else using the -not operator, the result included the NULL description users that were captured in the original get-aduser cmdlet.

The second command:

{description -ne "auto"}

...means

1. "figure out where the description attribute does not equal the exact string "auto". Again, for this comparison to work, a string needs to exist in the description field for the -ne operator to be able to compare it to "auto". NULL values are dropped from this comparison since it can't compare a NULL to a string value.

In your example again suppose you have 1 AD user that has their description equal to "auto", a few hundred with something other than "auto" and a few hundred with NULL descriptions. Stepping through the command logic it will do:

1. Give me all AD users where the description is not equal to "auto" - results in the few hundred users with the something other than "auto" in their description. It doesn't pull the users with NULL descriptions because it can't compare a NULL to a text string.

Either way the whole difference between the two commands is definitely non-intuitive.

Using this command you should be able to catch the NULLs with an "-and" in there too like this:

{description -ne "auto" -and description -ne $NULL}

I'm not 100% on the syntax since I can't test it right now, and there is probably a better way to do it than this too. When it's all broken down it is fairly anti-climatic and took a lot of typing to explain, but I've run into weird stuff like this before using the various operators and a lot of trial and error since I can never remember all the caveats that go along with using each one.

Reference: http://technet.microsoft.com/en-us/library/hh847732.aspx:

Comparison Operators

Use comparison operators (-eq, -ne, -gt, -lt, -le, -ge) to compare values and test conditions. For example, you can compare two string values to determine whether they are equal.

The comparison operators include the match operators (-match, -notmatch), which find patterns by using regular expressions; the replace operator (-replace), which uses regular expressions to change input values; the like operators (-like, -notlike), which find patterns using wildcard characters (*); and the containment operators (in, -notin, -contains, -notcontains), which determine whether a test value appears in a reference set.

They also include the bitwise operators (-bAND, -bOR, -bXOR, -bNOT) to manipulate the bit patterns in values.

For more information, see about_Comparison_Operators

Logical Operators

Use logical operators (-and, -or, -xor, -not, !) to connect conditional statements into a single complex conditional. For example, you can use a logical -and operator to create an object filter with two different conditions.

For more information, see about_Logical_Operators.

Answer 2

Adding to this old question as it comes up when searching:

Using -Filter with negative match such as -ne or -notlike excludes results with empty null values. For them to be included you also need to explicitly match using -notlike '*' as -eq '' and -eq $NULL are not valid filters. Note this is a quirk with -Filter, using a direct -LdapFilter DOES negative match empty values.

Here is a Filter and LdapFilter example of multi match with negative:

Get-ADUser -Filter { mail -like '*example*' -and (description -ne 'example' -or description -notlike '*') }

Get-ADUser -LdapFilter '(&(mail=*example*)(!description=example))'