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.

21 comments:

  1. You saved my day, pal. Thank you!

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

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

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

    ReplyDelete
  5. your post is still relavant

    ReplyDelete
  6. good stuff thanks!

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

    ReplyDelete
  8. :) Thumbs up on this one

    ReplyDelete
  9. Thanks man.
    Gotta love Microsoft...

    ReplyDelete
  10. Unfortunately, this doesn't work with extended attributes, such as "manager". You get the following error: The following: ''Eq', 'Ne'' are the only operator(s) supported for searching on extended
    attribute: 'Manager'.

    ReplyDelete
    Replies
    1. Kinda ugly, but maybe dump the whole set of users into a variable and then you can evaluate with Where-Object. Terribly inefficient, but might work.

      Delete
  11. If searching for extended attributes, you can use LDAPFilter, I managed to look for empty Managers by using

    get-ADuser -ldapfilter "(!Manager=*)"

    (and using -searchbase to check real users, of course.)

    ReplyDelete
  12. even in 2021 still saving a panicked apprentice! thx a lot for the pointer!

    ReplyDelete
  13. Still great, especially in 2021! Thank you very much.

    ReplyDelete