Powershell - Editing extensionAttribute in Bulk

Isaias Perez
Isaias Perez used Ask the Experts™
on
Recently I posted a question on how to edit the following set of Attributes in AD for bulk users. I was given the following PS Script.

Import-Csv -Path C:\Temp\Attributes.csv | ForEach-Object {
	$user = Get-ADUser -Filter "mail -eq '$($_.mail)'"
	Set-Aduser -Identity $user -Replace @{ 
    extensionAttribute1 = $_.extensionAttribute1
    extensionAttribute2 = $_.extensionAttribute2
     
    }
}

Open in new window


It was working but i was trying to tweak it to be able to edit ExtensionAttributes 1-10. And now its no longer working. Its giving me an error.

Get-ADUser : The search filter cannot be recognized
At C:\Users\1083786\OneDrive - Contoso\IT - Powershell\Attribute 
Changer for Group of Users by Email.ps1:2 char:10
+     $user = Get-ADUser -Filter "mail -eq '$($_.mail)'"
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-ADUser], ADException
    + FullyQualifiedErrorId : ActiveDirectoryServer:8254,Microsoft.ActiveDirec 
   tory.Management.Commands.GetADUser
 
Set-Aduser : replace
At C:\Users\1083786\OneDrive - Contoso Ltd\IT - Powershell\Attribute 
Changer for Group of Users by Email.ps1:3 char:2
+     Set-Aduser -Identity $user -Replace @{
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (CN=Perez\, Isai...ownepark,DC 
   =net:ADUser) [Set-ADUser], ADInvalidOperationException
    + FullyQualifiedErrorId : ActiveDirectoryServer:0,Microsoft.ActiveDirector 
   y.Management.Commands.SetADUser

Open in new window


Does anyone know what I am doing wrong? What we are trying to do is import a CSV file that has the following headers:

import-csv-attributes-ps-script.png
Mail > ExtensionAttributes1-9

Can someone help me fix this script so that i am able to bulk edit these specific AD Attributes for a Bulk Amount of Users please.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Isaias PerezIT Operations Manager

Author

Commented:
So I edited my script and whats odd is that it works. If i manually insert the email address and manually edit the cells under (extensionAttribute1-9). But whats really odd is that if i copy and paste it from another sheet it errors out every time.

Correct Code that works if manually editing the script

Import-Csv -Path C:\Scripts\Attributes.csv | ForEach-Object {
       $user = Get-ADUser -Filter "mail -eq '$($_.Email)'"
       Set-Aduser -Identity $user -Replace @{
              extensionAttribute1 = $_.ExtensionAttribute1
              extensionAttribute2 = $_.ExtensionAttribute2
             extensionAttribute3 = $_.ExtensionAttribute3
             extensionAttribute4 = $_.ExtensionAttribute4
             extensionAttribute5 = $_.ExtensionAttribute5
             extensionAttribute6 = $_.ExtensionAttribute6
             extensionAttribute7 = $_.ExtensionAttribute7
             extensionAttribute8 = $_.ExtensionAttribute8
             extensionAttribute9 = $_.ExtensionAttribute9
       }
} 

Open in new window


Error message if i copy and paste values from another CSV file

Set-Aduser : replace
At C:\Users\1083786\OneDrive - Contoso Ltd\IT - Powershell\Attribute 
Changer.ps1:3 char:2
+     Set-Aduser -Identity $user -Replace @{
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (CN=Averton\,Chr...Contoso,DC 
   =net:ADUser) [Set-ADUser], ADInvalidOperationException
    + FullyQualifiedErrorId : ActiveDirectoryServer:0,Microsoft.ActiveDirector 
   y.Management.Commands.SetADUser 

Open in new window

Isaias PerezIT Operations Manager

Author

Commented:
OK furthermore i have found more interesting information. I now know why the above error occurs. If I do not put any data in one of the columns then it errors out. So all columns have to have some sort of data. How can fix this issue? I added a new email address to the Email column and then added 1 entry to Only extensionAttribute1 and left the rest empty and it errors out, but if i fill each column then it works?!?
Isaias PerezIT Operations Manager

Author

Commented:
Someone on another site suggested this as a solution but i do not know how to implement it unto my script. This will set the column to Null if no data was provided for it.

Import-Csv C:\test2.csv | ForEach-Object {
$officephone = $_.OfficePhone
if($officephone.Length -lt 1)
{$officephone = $null}
$HomePhone = $_.HomePhone
if($HomePhone.Length -lt 1)
{$HomePhone = $null}
$MobilePhone = $_.MobilePhone 
if($Mobilephone.Length -lt 1)
{$MobilePhone = $null}

Set-ADUser -Identity $_.samAccountName -OfficePhone $officephone  -HomePhone $HomePhone -mobilephon $MobilePhone 
}

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jose Gabriel Ortega CastroTop Rated Freelancer on MS Technologies
Awarded 2018
Distinguished Expert 2018

Commented:
The issue is related to the "CSV" itself.

If you have a property that is "single value" but it's comma-separated that mess up all the structure, you need to tell that basically everything that is inside double quotes is NOT Separable in CSV. so that's the error you're getting there.


On the excel spreadsheet, you don't have any value like "OU=domain,DC=manila,=DC=com"
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Just create the hashtable so that it only contains the attributes that are not empty:
$replace = @{}
Import-Csv -Path C:\Temp\Attributes.csv | ForEach-Object {
	$user = Get-ADUser -Filter "mail -eq '$($_.mail)'"
	$replace.Clear()
	If ($_.extensionAttribute1) {$replace['extensionAttribute1'] = $_.extensionAttribute1}
	If ($_.extensionAttribute2) {$replace['extensionAttribute2'] = $_.extensionAttribute2}
	If ($_.extensionAttribute3) {$replace['extensionAttribute3'] = $_.extensionAttribute3}
	If ($_.extensionAttribute4) {$replace['extensionAttribute4'] = $_.extensionAttribute4}
	If ($_.extensionAttribute5) {$replace['extensionAttribute5'] = $_.extensionAttribute5}
	If ($_.extensionAttribute6) {$replace['extensionAttribute6'] = $_.extensionAttribute6}
	If ($_.extensionAttribute7) {$replace['extensionAttribute7'] = $_.extensionAttribute7}
	If ($_.extensionAttribute8) {$replace['extensionAttribute8'] = $_.extensionAttribute8}
	If ($_.extensionAttribute9) {$replace['extensionAttribute9'] = $_.extensionAttribute9}
	Set-Aduser -Identity $user -Replace $replace
}

Open in new window

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
On a side note: If you're doing some remapping from your own custom column names to extensionAttributeX, you'll obviously have to type all the column and attribute names like above.
But if (and only if) your column names are actually the same as the corresponding attribute names, you can shorten that:
$replace = @{}
Import-Csv -Path C:\Temp\Attributes.csv | ForEach-Object {
	$user = Get-ADUser -Filter "mail -eq '$($_.mail)'"
	$replace.Clear()
	For ($i = 1; $i -le 45; $i++) {
		If ($_."extensionAttribute$($i)") {$replace["extensionAttribute$($i)"] = $_."extensionAttribute$($i)"}
	}
	Set-Aduser -Identity $user -Replace @replace
}

Open in new window

Isaias PerezIT Operations Manager

Author

Commented:
oBdA i tried your script and this is the error it gives me below. The headers on the CSV file are as follows: mail, extensionAttribute1-9 (Each extensionAttribute is its own column)

Get-ADUser : The search filter cannot be recognized
At C:\Scripts\Attribute Changer 2.ps1:3 char:10
+     $user = Get-ADUser -Filter "mail -eq '$($_.mail)'"
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-ADUser], ADException
    + FullyQualifiedErrorId : ActiveDirectoryServer:8254,Microsoft.ActiveDirec 
   tory.Management.Commands.GetADUser
 
Set-ADUser : Missing an argument for parameter 'Replace'. Specify a parameter 
of type 'System.Collections.Hashtable' and try again.
At C:\Scripts\Attribute Changer 2.ps1:14 char:29
+     Set-Aduser -Identity $user -Replace @replace
+                                ~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Set-ADUser], ParameterBind 
   ingException
    + FullyQualifiedErrorId : MissingArgument,Microsoft.ActiveDirectory.Manage 
   ment.Commands.SetADUser

Open in new window

Isaias PerezIT Operations Manager

Author

Commented:
When i tried the second one and tweaked the location of the CSV

$replace = @{}
Import-Csv -Path 'C:\Scripts\Attributes.csv' | ForEach-Object {
	$user = Get-ADUser -Filter "mail -eq '$($_.mail)'"
	$replace.Clear()
	For ($i = 1; $i -le 45; $i++) {
		If ($_."extensionAttribute$($i)") {$replace["extensionAttribute$($i)"] = $_."extensionAttribute$($i)"}
	}
	Set-Aduser -Identity $user -Replace @replace
}

Open in new window


I get this error:

Set-ADUser : Missing an argument for parameter 'Replace'. Specify a parameter of 
type 'System.Collections.Hashtable' and try again.
At C:\Scripts\Attribute Changer 2.ps1:25 char:29
+     Set-Aduser -Identity $user -Replace @replace
+                                ~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Set-ADUser], ParameterBindingEx 
   ception
    + FullyQualifiedErrorId : MissingArgument,Microsoft.ActiveDirectory.Management. 
   Commands.SetADUser

Open in new window

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
I'd guess you have empty rows or rows where the mail column is empty, or the "mail" column is actually named "mail ", and/or roes with an entry in the mail column, but all extensionAttributes are empty, or all of the above.
Open the csv file in a text editor and verify the format.
This is a bit more tolerant:
$replace = @{}
Import-Csv -Path C:\Temp\Attributes.csv | Where-Object {$_.mail} | ForEach-Object {
	Write-Host "Processing '$($_.mail)' ..."
	$user = Get-ADUser -Filter "mail -eq '$($_.mail)'"
	$replace.Clear()
	For ($i = 1; $i -le 9; $i++) {
		If ($_."extensionAttribute$($i)") {$replace["extensionAttribute$($i)"] = $_."extensionAttribute$($i)"}
	}
	If ($replace.psbase.Count -gt 0) {
		Set-ADUser -Identity $user -Replace $replace
		Write-Host "... updated."
	} Else {
		Write-Warning "$($_.mail): no attributes specified in the input csv!"
	}
}

Open in new window

Edit: Fixed "-replace" argument.
Isaias PerezIT Operations Manager

Author

Commented:
Thank you so much for being patient and helping me with this. I tried the new script above and this is what i get.


Processing 'vbilotta@contoso.com' ...
Set-ADUser : Missing an argument for parameter 'Replace'. Specify a parameter 
of type 'System.Collections.Hashtable' and try again.
At C:\Scripts\Attribute Changer 2.ps1:38 char:30
+         Set-ADUser -Identity $user -Replace @replace
+                                    ~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Set-ADUser], ParameterBind 
   ingException
    + FullyQualifiedErrorId : MissingArgument,Microsoft.ActiveDirectory.Manage 
   ment.Commands.SetADUser
 
... updated.
Processing 'adesantis@contoso.com' ...
Set-ADUser : Missing an argument for parameter 'Replace'. Specify a parameter 
of type 'System.Collections.Hashtable' and try again.
At C:\Scripts\Attribute Changer 2.ps1:38 char:30
+         Set-ADUser -Identity $user -Replace @replace
+                                    ~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Set-ADUser], ParameterBind 
   ingException
    + FullyQualifiedErrorId : MissingArgument,Microsoft.ActiveDirectory.Manage 
   ment.Commands.SetADUser
 
... updated.

Open in new window


All  lines within the mail column have an email address specific with the user. Each row has atleast 1 extensionAttribute designated for that row.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Sorry, that last one was my bad. Accidentally turned the "replace" hashtable into a splatted argument list; that should have been "$replace" instead of "@replace". Edited the script at https://www.experts-exchange.com/questions/29164552/Powershell-Editing-extensionAttribute-in-Bulk.html#a42981441
Download again, or just replace the @ before the "replace" in line 10 with a $.
Isaias PerezIT Operations Manager

Author

Commented:
Thank you very much for your assistance. This worked now perfectly. Lastly anyway i can make this script generate a logfile that will be placed in C:\temp showing the successful change date and time?
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
Just use a transcript:
Start-Transcript -Path C:\Temp\ExtAtt.log
$replace = @{}
Import-Csv -Path C:\Temp\Attributes.csv | Where-Object {$_.mail} | ForEach-Object {
	Write-Host "Processing '$($_.mail)' ..."
	$user = Get-ADUser -Filter "mail -eq '$($_.mail)'"
	$replace.Clear()
	For ($i = 1; $i -le 9; $i++) {
		If ($_."extensionAttribute$($i)") {$replace["extensionAttribute$($i)"] = $_."extensionAttribute$($i)"}
	}
	If ($replace.psbase.Count -gt 0) {
		Set-ADUser -Identity $user -Replace $replace
		Write-Host "... updated."
	} Else {
		Write-Warning "$($_.mail): no attributes specified in the input csv!"
	}
}
Stop-Transcript

Open in new window

Isaias PerezIT Operations Manager

Author

Commented:
perfection!! Thank you sir.
Isaias PerezIT Operations Manager

Author

Commented:
oBdA is a true professional. Just Awesome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial