Null and Not Null with PowerShell

Finding out if an object has a null (i.e. blank) value or not isn’t a difficult task to do.

Consider this scenario – you’ve found a bunch of old disabled accounts that someone forgot to remove the ‘Manager’ field. Finding accounts that have another field that would be populated for a current employee but blank for a departed would be a reasonable way of finding the problem accounts, then you could null the ‘Manager field. (note – you could just refine your search to disabled accounts but that’s not as fun).

To find all Active Directory users that have a blank ‘Department’ field is easily done with this command:

get-aduser -filter * -properties department | where department -eq $null

Then, showing the users that don’t have a blank ‘Department’ field is a slight change. You can’t use !$null (!=not), but you can use -ne (not equals)

get-aduser -filter * -properties department | where department -ne $null

You can also check for users that have a manger by switching ‘department’ to ‘manager’:

get-aduser -filter * -properties maanger | where manager -ne $null

Easy. Adding in a second ‘where’ statement so we can get results of users that have a manager, but no department means we have to add in a few extra characters to make PowerShell happy:

get-aduser -filter * -properties department,manager | where {($_.department -eq $null) -and ($_.manager -ne $null)}

The results can be a bit hard to read, so piping (|) to a select command will just show us the results of each user we want to see:

get-aduser -filter * -properties department,manager | where {($_.department -eq $null) -and ($_.manager -ne $null)} | select name

Finally, to blank the ‘manager’ field, we can swap the ‘select name’ command with this:

get-aduser -filter * -properties department,manager | where {($_.department -eq $null) -and ($_.manager -ne $null)} |  set-aduser -manager $null

You can then go back to a previous command to confirm you get no results. As always, check your data first before blanking out a bunch of user’s values!

Update

As @mickesunkan pointed out, the above isn’t the most efficient way to do searches. I’m sure I’ve mentioned this before, but I’m not always going to write the cleanest, quickest way of doing something. For a once off tasks this really doesn’t matter. For a daily task it starts to matter – not really by itself, but if you keep making more and more inefficient scripts, you’re putting extra unnecessary load on your environment with lots of LDAP lookups.

Above, I’m just getting ALL AD users. You could use a better filter and narrow down to a certain OU. You could also put part of your ‘where’ command into the filter, such as this:

get-aduser -properties manager,department -filter {department -notlike “*”}

This doesn’t work for the ‘Manager’ field though, you’ll see this error:

get-aduser : Operator(s): The following: ”Eq’, ‘Ne” are the only operator(s) supported for searching on extended attribute: ‘Manager’.

I couldn’t work out a way of putting the $null value as part of the filter, but if you do – please share :)

 

@mickesunkan also wrote this github code showing a few differnet ways to do this search, and which way is most efficient. Thanks Micke!

 

 

 

7 thoughts on “Null and Not Null with PowerShell

  1. get-aduser -filter {employeenumber -like ‘*’ } -Properties employeenumber |ogv is an example of getting results where an attribute is null

    1. Just tested it and doesn’t work for me, no results (and I don’t use that field so ALL users have it null).

      If you use -notlike instead of -like it works :)

  2. Even if the field isn’t used, it should still work.
    works >> get-aduser -filter{(employeeNumber -like “*”)} -Properties employeeNumber | ogv
    works >> get-aduser -filter{(employeeNumber -like ‘*’)} -Properties employeeNumber | ogv
    this works too >> get-aduser -filter{employeeNumber -like ‘*’} -Properties employeeNumber | ogv

    **** If you copy and paste BritV8’s cmdlet, it WON’T work because of this >> ‘*’ . Just change the single quotes ***

    1. Yes my point was that it’s unused by me, so all users should come back as a result, yet I get none.

      I’m getting a syntax error on the lines you posted, but worked when retyping manually (probably WordPress doing something ‘smart’ with the characters!). I still get no results on those unless I change -like to -notlike.

      I changed the value of one account to have a value for the EmployeeNumber field. Now with those commands, I get back the single result that has a value for that field.

      Why would matching a field to a wildcard value only bring back null values?

      Thanks for taking the time to post :)

    1. I’ll try it on the manager field and report back.

      Edit: As I thought:

      Get-aduser -properties * -filter “manager -notlike ‘*'”
      Get-aduser : Operator(s): The following: ”Eq’, ‘Ne” are the only operator(s) supported for searching on extended
      attribute: ‘Manager’.
      At line:1 char:1
      + Get-aduser -properties * -filter “manager -notlike ‘*'”
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : InvalidOperation: (:) [Get-ADUser], NotSupportedException
      + FullyQualifiedErrorId : ActiveDirectoryCmdlet:System.NotSupportedException,Microsoft.ActiveDirectory.Management.
      Commands.GetADUser

      Get-aduser -properties * -filter “manager -ne ‘*'”
      Get-aduser : Identity info provided in the extended attribute: ‘Manager’ could not be resolved. Reason: ‘Cannot find
      an object with identity: ‘*’ under: ‘DC=xxx.
      At line:1 char:1
      + Get-aduser -properties * -filter “manager -ne ‘*'”
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : InvalidData: (:) [Get-ADUser], ADIdentityResolutionException
      + FullyQualifiedErrorId : ActiveDirectoryCmdlet:Microsoft.ActiveDirectory.Management.ADIdentityResolutionException
      ,Microsoft.ActiveDirectory.Management.Commands.GetADUser

  3. Hi… I could use $null.

    Get-ADUser -Filter {httSAPUser -ne ‘$null’ -and Enabled -eq $true}

    Where htttSAPUser is a custom attribute.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.