Saturday, September 22, 2012

Filtering for $null Values with Get-ADUser

Get-ADUser includes a -Filter parameter that lets you define queries for users with specific characteristics. Today I was trying to figure out how to filter for $null values.  Here is my example of why you might care to do this......

Let's say that in your organization, that you always set the Department attribute to match the department that users work in. This could be required for dynamic groups or address books. You've just created 100 new users, but forgot to configure the department. You need to make a query for all of the users without a department configured.

My first attempt was this:
Get-ADUser -Filter {company -eq $null}
However, this generates an error. You can't use $null in a filter.

What finally worked was this:
Get-ADUser -Filter {company -notlike "*"}
The gets a list of users where the company attribute is not like anything.

I should also note that if you try to query for not equal (-ne) then it will skip $null values when comparing. The above example is the only way that I know of to get $null values.

Update Apr 2017:
A quick note that the corollary of  the above is that when you want to query objects with any value set, you can filter for -like "*". I recently used this in a script where I only wanted users with values in the proxyAddresses property that I wanted to copy to the UPN.

16 comments:

  1. Another thanks; not at all intuitive that -ne *skips* null values.

    ReplyDelete
  2. the -LDAPFilter also gets around this :)

    ReplyDelete
  3. weird one but saved me some time trying to figure out why this wasn't working. Thanks!!

    ReplyDelete
  4. your post is still relavant

    ReplyDelete
  5. good stuff thanks!

    ReplyDelete
  6. Thanks man, so simple but was banging my head trying to figure it out!

    ReplyDelete
  7. :) Thumbs up on this one

    ReplyDelete
  8. Thanks man.
    Gotta love Microsoft...

    ReplyDelete