Recently, I set out on populating a test Active Directory environment from a production environment. This included populating Active Directory Users and Computers with my users from production. I figured I could quickly export my users from production to a CSV file, include any properties I wanted to import over to the test environment, and then create the new users based on the CSV file using New-ADUser and Set-ADUser respectably. Quickly, I realized that I had a problem. I couldn’t just import the CSV file and have it iterate through each user because some parameters do not accept null values, meaning if I am calling the parameter, it wants a value, no exceptions. This is the same for LDAP properties as well that use the Replace parameter.

The Instance Parameter

The first possible fix I found, was the use of the Instance parameter. The Instance parameter will change properties of your AD User if there properties to change. So you just need to have an ‘If‘ statement to first check if the property contains a valid string or not, if so, modify the user object in memory, and then set the user, with all our new values at the end. Let’s walk through it

Check the CSV Property for Value

In this example, lets pretend that we already Imported our CSV file using the Import-CSV cmdlet, and now we are using foreach-object to iterate through the file.

Import-CSV -Path C:\Test\TestFile.CSV | Foreach-Object { 
    #We are doing stuff in here
}

Within this loop, we will do an AD lookup for our user based on the CSV. Here, we are filtering based on the name property.

Import-CSV -Path C:\Test\TestFile.CSV | Foreach-Object { 
    $ADUser = Get-ADuser -Filter "name -eq '$($_.name)'" -Properties *
}

Now, we need to check the CSV for values in the properties, if they contain a value then modify our ADUser variable, which is in memory. In the example below, if the CSV contains a value for the property MobilePhone, it will write that value to our user in memory. (It will not write it to the AD object just yet)

Import-CSV -Path C:\Test\TestFile.CSV | Foreach-Object { 
  $ADUser = Get-ADuser -Filter "name -eq '$($_.name)'" -Properties * 
  if ($_.MobilePhone) {     
       $ADUser.MobilePhone = $_.MobilePhone 
   } 
}

Let throw another property in there. This time it will be the City property. If the CSV file has a value for the property City, it will write that to our in-memory user. If there is no value for City it will not do anything.

Import-CSV -Path C:\Test\TestFile.CSV | Foreach-Object { 
  $ADUser = Get-ADuser -Filter "name -eq '$($_.name)'" -Properties * 
  if ($_.MobilePhone) {     
       $ADUser.MobilePhone = $_.MobilePhone 
   }
   if ($_.City) {     
       $ADUser.City = $_.City 
   } 
}

Once we are finished with our properties, we can now write our changes to our user in Active Directory. From the earlier steps our $ADUser variable has all of our property changes and using that we can apply those changes.

Import-CSV -Path C:\Test\TestFile.CSV | Foreach-Object { 
  $ADUser = Get-ADuser -Filter "name -eq '$($_.name)'" -Properties * 
  if ($_.MobilePhone) {     
       $ADUser.MobilePhone = $_.MobilePhone 
   }
   if ($_.City) {     
       $ADUser.City = $_.City 
   } 
   Set-ADUser -Instance $ADUser
}

That worked great! But lets say you have a ton of properties that you need to set. You don’t want to write an If statement for each one. Get-ADUser can return more than 120 different properties. Or maybe you just simply don’t have a lot of time, you need to get this resolved as soon as possible, and writing checks on if a property contains a value or not isn’t an option.

PowerShell Hashtables

PowerShell has what are known as hashtables, commonly referred to in other programming languages as dictionaries. It is an object that stores key-value pairs. To create a hashtable in PowerShell you use braces, instead of parentheses (like in an array).

Creating a Hashtable

$hashtable = @{}

Now that we have our hashtable created, lets add a new key/value pair. In my example below, the key will be the parameter name to set a users MobilePhone, which is -MobilePhone. The value will be whatever the CSV file has for that property.

Adding a Key/Value Pair

$hashtable = @{
  MobilePhone = $_.MobilePhone
}

Great! Now I will add my next parameter to set a users “City” which is -City. The value for that key will be whatever the CSV file has for that property.

$hashtable = @{
  MobilePhone = $_.MobilePhone
  City        = $_.City
}

Applying a Hashtable

I can then apply this to my user using the code below. Notice to set the properties to the user, I need to use the parameter –Identity. I am not longer getting the user, storing it in memory, and modifying properties in memory, then applying it.

To make it a little easier, PowerShell really sees this command: Set-ADUser -Identity $ADUser -MobilePhone $_.MobilePhone -City $_.City

The Keys are our parameters and the values are the property values. By just giving it the hashtable by using the @ symbol (and then the hashtable name) in Set-ADUser, I am really just calling parameters and their corresponding values.

Import-CSV -Path C:\Test\TestFile.CSV | Foreach-Object { 
   $ADUser = Get-ADuser -Filter "name -eq '$($_.name)'" -Properties *
   $hashtable = @{
      MobilePhone = $_.MobilePhone
      City        = $_.City
   } 
   Set-ADUser -Identity $ADUser @hashtable
}

Remove Keys with Null Values

But that still doesn’t address the issue if the property is NULL, or empty. The code above will fail if a parameter is expecting a value and you provide nothing to it. Hashtables also allow us to not only add key/value pairs, they also allow us to remove pairs.

The code below creates a new variable called $keysToRemove and it takes the hashtable, ‘$hashtable’ and expands its keys (MobilePhone, City) and searches the values to see if any are NULL. If any are NULL it will store the key name (MobilePhone or City) in the new variable.

$keysToRemove = $hashtable.keys | Where-Object { 
        !$hashtable[$_] 
    }

Now that we have all the Keys that need to be removed, we need to modify the original hashtable. Using the remove method we can iterate through the list of keys to remove, and remove them from the hashtable.

$keysToRemove | Foreach-Object { 
        $hashtable.remove($_) 
    }

Bringing all the code together from above, our new code will look like the following

Import-CSV -Path C:\Test\TestFile.CSV | Foreach-Object { 
   $ADUser = Get-ADuser -Filter "name -eq '$($_.name)'" -Properties *
   $hashtable = @{
      MobilePhone = $_.MobilePhone
      City        = $_.City
   }
   $keysToRemove = $hashtable.keys | Where-Object { 
        !$hashtable[$_] 
   }
   $keysToRemove | Foreach-Object { 
        $hashtable.remove($_) 
   }
   Set-ADUser -Identity $ADUser @hashtable
}

Adding Codeblock to a Hashtable

After seeing this, I decide that its best to fully utilize our hashtable. We are calling the parameters MobilePhone and City, but during Set-ADUser we are manually calling the –Identity parameter. The hashtable can take care of that for us.

Notice how instead of just .PROPERTY (eg $_.City, $_.MobilePhone) for a value, I just put the entire codeblock for Get-ADUser in the hashtable.

Import-CSV -Path C:\Test\TestFile.CSV | Foreach-Object { 
   $hashtable = @{
      Identity    = Get-ADuser -Filter "name -eq '$($_.name)'" -Properties *
      MobilePhone = $_.MobilePhone
      City        = $_.City
   }
   $keysToRemove = $hashtable.keys | Where-Object { 
        !$hashtable[$_] 
   }
   $keysToRemove | Foreach-Object { 
        $hashtable.remove($_) 
   }
   Set-ADUser @hashtable
}

With only two properties, this looks to be not as efficient as just using the –Instance parameter from earlier. In the example below I am now setting 18 different properties. My code is easy to read, and since I am not using the –Instance parameter, I don’t need multiple IF statements. My single code that strips NULL values works on all the key/pairs automatically.

 Import-CSV -Path C:\Test\TestFile.CSV | Foreach-Object { 
   $hashtable = @{
        Identity       = Get-ADuser -Filter "name -eq '$($_.name)'" -Properties *
        SAMAccountName = $_.SAMAccountName;
        EmailAddress   = $_.EmailAddress;
        DisplayName    = $_.DisplayName;
        GivenName      = $_.GivenName;
        Surname        = $_.Surname;
        Office         = $_.physicalDeliveryOfficeName;
        EmployeeID     = $_.EmployeeID;
        Title          = $_.title;
        Company        = $_.Company;
        Division       = $_.Division;
        Department     = $_.Department;
        MobilePhone    = $_.mobile;
        StreetAddress  = $_.streetAddress;
        City           = $_.City;
        State          = $_.State;
        PostalCode     = $_.postalCode;
        Country        = $_.Country;
        OfficePhone    = $_.telephoneNumber;
        ErrorAction    = "Stop"
   }
   $keysToRemove = $hashtable.keys | Where-Object { 
        !$hashtable[$_] 
   }
   $keysToRemove | Foreach-Object { 
        $hashtable.remove($_) 
   }
   Set-ADUser @hashtable
}

LDAP Attributes

If you have worked with the Set-ADUser cmdlet, you may know that not every attribute can be set by calling the attribute name as an parameter (e.g. to set the City, you just use the –City parameter). If that is the case you would use the –Replace parameter. One such example of this is the departmentNumber attribute, from looking at the Set-ADUser documentation, there is no parameter called –DepartmentNumber so we will need to set it using the –Replace parameter. Luckily, we can create another hashtable to set multiple attributes using the –Replace parameter.

$ldaphash = @{
        "departmentnumber" = $_.departmentNumber
    }

Once you create your LDAP hashtable, you will want to nest it in the hashtable from earlier with the Key called “Replace“.

 $hashtable = @{
        Identity       = $userobj.ObjectGUID
        SAMAccountName = $i.SAMAccountName;
        DisplayName    = $i.DisplayName;
        GivenName      = $i.GivenName;
        Surname        = $i.Surname;
        Office         = $i.physicalDeliveryOfficeName;
        EmployeeID     = $i.EmployeeID;
        Title          = $i.title;
        Company        = $i.Company;
        Division       = $i.Division;
        Department     = $i.Department;
        MobilePhone    = $i.mobile;
        StreetAddress  = $i.streetAddress;
        City           = $i.City;
        State          = $i.State;
        PostalCode     = $i.postalCode;
        Country        = $i.Country;
        OfficePhone    = $i.telephoneNumber;
        ErrorAction    = "Stop";
        Replace        = $ldaphash
    }

But we face the same issues as before, we need to remove items from our LDAP hashtable that contain empty values. We can do it the same way as the other hashtable, but this needs to occur prior so when we add our LDAP hashtable into our main hashtable it contains the proper attributes that don’t have NULL values.

 Import-CSV -Path C:\Test\TestFile.CSV | Foreach-Object { 
   $ldaphash = @{
        "departmentnumber" = $_.departmentNumber
    }
    #Get all keys that have null values and store in variable keystoremove 
    $keysToRemoveldap = $ldaphash.keys | Where-Object { 
        !$ldaphash[$_] 
    }
    #Foreach key, remove them from the hashtable 
    $keysToRemoveldap | Foreach-Object { 
        $ldaphash.remove($_) 
    }
   $hashtable = @{
        Identity        = Get-ADuser -Filter "name -eq '$($_.name)'" -Properties *
        SAMAccountName  = $_.SAMAccountName;
        EmailAddress    = $_.EmailAddress;
        DisplayName     = $_.DisplayName;
        GivenName       = $_.GivenName;
        Surname         = $_.Surname;
        Office          = $_.physicalDeliveryOfficeName;
        EmployeeID      = $_.EmployeeID;
        Title           = $_.title;
        Company         = $_.Company;
        Division        = $_.Division;
        Department      = $_.Department;
        MobilePhone     = $_.mobile;
        StreetAddress   = $_.streetAddress;
        City            = $_.City;
        State           = $_.State;
        PostalCode      = $_.postalCode;
        Country         = $_.Country;
        OfficePhone     = $_.telephoneNumber;
        ErrorAction     = "Stop";
        Replace         = $ldaphash
   }
   $keysToRemove = $hashtable.keys | Where-Object { 
        !$hashtable[$_] 
   }
   $keysToRemove | Foreach-Object { 
        $hashtable.remove($_) 
   }
   Set-ADUser @hashtable
}

Now here is what that code looks like when I add 5 more LDAP properties to set. Now my code is setting a grand total of 24 properties/attributes.

 Import-CSV -Path C:\Test\TestFile.CSV | Foreach-Object { 
   $ldaphash = @{
        "employeeType"                  = $_.employeeType;
        "departmentnumber"              = $_.departmentNumber;
        "msDS-cloudExtensionAttribute1" = $_."msDS-cloudExtensionAttribute1";
        "msDS-cloudExtensionAttribute2" = $_."msDS-cloudExtensionAttribute2";
        "msDS-cloudExtensionAttribute3" = $_."msDS-cloudExtensionAttribute3";
        "msDS-cloudExtensionAttribute4" = $_."msDS-cloudExtensionAttribute4"
    }
    #Get all keys that have null values and store in variable keystoremove 
    $keysToRemoveldap = $ldaphash.keys | Where-Object { 
        !$ldaphash[$_] 
    }
    #Foreach key, remove them from the hashtable 
    $keysToRemoveldap | Foreach-Object { 
        $ldaphash.remove($_) 
    }
   $hashtable = @{
        Identity        = Get-ADuser -Filter "name -eq '$($_.name)'" -Properties *
        SAMAccountName  = $_.SAMAccountName;
        EmailAddress    = $_.EmailAddress;
        DisplayName     = $_.DisplayName;
        GivenName       = $_.GivenName;
        Surname         = $_.Surname;
        Office          = $_.physicalDeliveryOfficeName;
        EmployeeID      = $_.EmployeeID;
        Title           = $_.title;
        Company         = $_.Company;
        Division        = $_.Division;
        Department      = $_.Department;
        MobilePhone     = $_.mobile;
        StreetAddress   = $_.streetAddress;
        City            = $_.City;
        State           = $_.State;
        PostalCode      = $_.postalCode;
        Country         = $_.Country;
        OfficePhone     = $_.telephoneNumber;
        ErrorAction     = "Stop";
        Replace         = $ldaphash
   }
   $keysToRemove = $hashtable.keys | Where-Object { 
        !$hashtable[$_] 
   }
   $keysToRemove | Foreach-Object { 
        $hashtable.remove($_) 
   }
   Set-ADUser @hashtable
}

NULL Checking the LDAP Hashtable

But what if you have a user in your CSV that has no values for any of your LDAP attributes? You will end up passing an empty hashtable to your –Replace parameter and it will throw an error. We need to add one more check, check if that LDAP hashtable is NULL, and if so, remove it from the main hashtable.

In the example below we have an IF statement that says if the LDAP hashtable contains nothing in it, then use the remove method in the main hashtable and remove the Replace key/value pair.

    #If there are no LDAP properties to change, remove it from the main hash table 
    if ($ldaphash.count -eq 0)
    {
        $hashtable.remove("Replace")
    }

This check needs to occur after the main hashtable checks for NULL key/value pairs but before it changes our AD user using Set-ADUser.

 Import-CSV -Path C:\Test\TestFile.CSV | Foreach-Object { 
   $ldaphash = @{
        "employeeType"                  = $_.employeeType;
        "departmentnumber"              = $_.departmentNumber;
        "msDS-cloudExtensionAttribute1" = $_."msDS-cloudExtensionAttribute1";
        "msDS-cloudExtensionAttribute2" = $_."msDS-cloudExtensionAttribute2";
        "msDS-cloudExtensionAttribute3" = $_."msDS-cloudExtensionAttribute3";
        "msDS-cloudExtensionAttribute4" = $_."msDS-cloudExtensionAttribute4"
    }
    #Get all keys that have null values and store in variable keystoremove 
    $keysToRemoveldap = $ldaphash.keys | Where-Object { 
        !$ldaphash[$_] 
    }
    #Foreach key, remove them from the hashtable 
    $keysToRemoveldap | Foreach-Object { 
        $ldaphash.remove($_) 
    }
   $hashtable = @{
        Identity        = Get-ADuser -Filter "name -eq '$($_.name)'" -Properties *
        SAMAccountName  = $_.SAMAccountName;
        EmailAddress    = $_.EmailAddress;
        DisplayName     = $_.DisplayName;
        GivenName       = $_.GivenName;
        Surname         = $_.Surname;
        Office          = $_.physicalDeliveryOfficeName;
        EmployeeID      = $_.EmployeeID;
        Title           = $_.title;
        Company         = $_.Company;
        Division        = $_.Division;
        Department      = $_.Department;
        MobilePhone     = $_.mobile;
        StreetAddress   = $_.streetAddress;
        City            = $_.City;
        State           = $_.State;
        PostalCode      = $_.postalCode;
        Country         = $_.Country;
        OfficePhone     = $_.telephoneNumber;
        ErrorAction     = "Stop";
        Replace         = $ldaphash
   }
   $keysToRemove = $hashtable.keys | Where-Object { 
        !$hashtable[$_] 
   }
   $keysToRemove | Foreach-Object { 
        $hashtable.remove($_) 
   }
   #If there are no LDAP properties to change, remove it from the main hash table 
   if ($ldaphash.count -eq 0)
   {
       $hashtable.remove("Replace")
   }
   Set-ADUser @hashtable
}

Finishing Touches

To circle back, we started by using the –Instance parameter, which is a great method to use if you are setting a small subset of attributes. This method allows you to grab your AD User object and store it in memory, make your changes, and then apply them at the end. The downside to this is setting your IF statements to check to see if the incoming property contains a NULL value or not.

Lastly, we discovered using PowerShell hashtables, which allow us to set all of our key/value pairs at once, then in one sweep remove any that contain NULL values. We also used this method to set LDAP attributes that don’t have any corresponding parameter. The code is slimmed down, easy to read, and manage from person to person.

Brad Wyatt

My name is Bradley Wyatt; I am a 5x Microsoft Most Valuable Professional (MVP) in Microsoft Azure and Microsoft 365. I have given talks at many different conferences, user groups, and companies throughout the United States, ranging from PowerShell to DevOps Security best practices, and I am the 2022 North American Outstanding Contribution to the Microsoft Community winner.