• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2437
  • Last Modified:

Update AD attribute with PowerShell

My goal is to update ~5,000 users in Active Directory with an "employeeID" attribute from a CSV file.  I've trimmed the CSV down to the following columns:  sn, givenname, employeeID.  I would like to have the PS script verify the sn and givenname match between the CSV and AD, then update the employee ID in AD from the CSV file.  I made a futile attempt in PowerShell using some Quest add-ons; but I'm still lost.  Here's my PS script:


$OU = "domain.com\testou"
import-csv testcsv.csv | where { Get-QADUser -ou $OU -FirstName $_.FirstName -LastName $_.LastName } | Set-QADUser -Identity { $_.employeeID }

Clearly this is not the way to go about this.  Can anyone offer some advice for my PowerShell debacle?
0
Acosta Technology Services
Asked:
Acosta Technology Services
  • 11
  • 11
  • +1
1 Solution
 
KenMcFCommented:
You will want to add samaccountname or some other unique attribute to your csv file so there are no false positives.

$Users = import-csv c:\temp\test.csv
Foreach ($User in $Users){
Get-qaduser $User.samaccountname | Set-qaduser -firstname $User.FirstName -Lastname $user.lastname -objectattributes @{employeeID=$($user.employeeID)}
}
0
 
YZlatCommented:
use

 
$user = Get-ADUser -SearchBase "AD path" -filter  ""
 
 Set-ADUser -Identity { $_.employeeID }

Open in new window


or

 
Import-Module ActiveDirectory  
$user = [ADSI]“LDAP://CN=...”
$user.put(“attribute”, “value”)
$user.setInfo()

Open in new window

0
 
Acosta Technology ServicesAuthor Commented:
YZlat, that doesn't seem to address the CSV import.  

Ken,
That's part of the problem.  The HR system that creates the CSV does not have any other fields which are also defined in AD, so we're pretty much stuck with first and last name.  Is there anywhere for it to skip duplicates and export them to a text file?  Or, can this be applied to an OU instead of the whole domain which would eliminate most duplicates?
0
Who's Defending Your Organization from Threats?

Protecting against advanced threats requires an IT dream team – a well-oiled machine of people and solutions working together to defend your organization. Download our resource kit today to learn more about the tools you need to build you IT Dream Team!

 
KenMcFCommented:
you can do this

get-qaduser -searchroot OU=staff,DC=Doamin,DC=Local -LDAPFILTER "(&(GivenName=$_.FirstName)(SN=$_.Lastname))
0
 
KenMcFCommented:
Sorry hit post to soon

What i was going to say is you can add some error checking in there. Something like this may work for you after the import of the csv

$u = get-qaduser -searchroot OU=staff,DC=Doamin,DC=Local -LDAPFILTER "(&(GivenName=$_.FirstName)(SN=$_.Lastname))

If ($u.count -gt 1){

set-qaduser
}
Else{
Write-output $u.samaccountname
}
0
 
Acosta Technology ServicesAuthor Commented:
Thanks for the help Ken.  This is what I'm running now:

$ou = "domain.pvt/Test"
$u = get-qaduser -ou $ou -LDAPFILTER "(&(GivenName=$_.FirstName)(SN=$_.LastName))"
$User = import-csv c:\csv\testcsv.csv
If ($u.count -gt 1){
set-qaduser -firstname $User.givenname -Lastname $user.sn -objectattributes @{employeeID=$($user.employeeID)}
}
Else{
Write-output $u.samaccountname
}

It's running without error, but there are no changes in AD and I'm not sure how to tell if it's actually trying to modify anything.  Any thoughts on the above?
0
 
KenMcFCommented:
try to change this, this has not been tested so plase test in DEV or a single user first

Flip these two lines

$u = get-qaduser -ou $ou -LDAPFILTER "(&(GivenName=$_.FirstName)(SN=$_.LastName))"
$User = import-csv c:\csv\testcsv.csv

So they should be

$User = import-csv c:\csv\testcsv.csv
Foreach ($user in $Users){
$u = get-qaduser -ou $ou -LDAPFILTER "(&(GivenName=$User.FirstName)(SN=$User.LastName))"

If ($u.count -gt 1){
set-qaduser $u -firstname $User.givenname -Lastname $user.sn -objectattributes @{employeeID=$($user.employeeID)}}
Else{
Write-output $u.samaccountname
}


}
0
 
KenMcFCommented:
and this line

$u.count -gt 1

should be

$u.count -eq 1
0
 
Acosta Technology ServicesAuthor Commented:
Still getting blank output, no changes.

$User.FirstName  <-- in $u; should "firstname" be the header column in my csv or is that from something else?
0
 
KenMcFCommented:
Try this, i tested real quick in my VM and it seems to work. But you will need to test more to make sure.


$ou = "devlab.local/User_Accounts"
$Users = import-csv c:\temp\users.csv
Foreach($user in $Users){
    
    $u = get-qaduser -ou $ou -LDAPFILTER "(&(GivenName=$($User.FirstName))(SN=$($User.LastName)))"
    $u.count
    If ($u.count -gt 1){
                $u | Foreach{ 
                    Write-output $_.samaccountname
                }
        
    }
    Else{
        set-qaduser $u -firstname $User.givenname -Lastname $user.sn -objectattributes @{employeeID=$($user.employeeID)}
    }
}

Open in new window

0
 
Acosta Technology ServicesAuthor Commented:
I removed the LDAP filter line since my headers are the same as the attributes in AD (I was getting $Null errors otherwise).  When I run the script I get the following results in PowerShell:

2
testtest1
testtest2
2
testtest1
testtest2
2
testtest1
testtest2
2
testtest1
testtest2

The CSV file is the following:

employeeID,GivenName,sn
303,Test2,Test3
404,Test1,Test2

The users in AD are the following:
Test2, Test1   sam=testtest1
Test3, Test2   sam=testtest2

It's is pulling the correct AD names and it's writing them (multiple times); but I can't figure out why it's not putting the employeeID in place.


 
$ou = "domain/Test"
$Users = import-csv c:\csv\testcsv.csv
Foreach($user in $Users){
    
    $u = get-qaduser -ou $ou 
    $u.count
    If ($u.count -gt 1){
                $u | Foreach{ 
                    Write-output $_.samaccountname
                }
        
    }
    Else{
        set-qaduser $u -firstname $User.givenname -Lastname $user.sn -objectattributes @{employeeID=$($user.employeeID)}
    }
}

Open in new window

0
 
KenMcFCommented:
You need the LDAP filter in this line becuase if this filter finds more than one user it will not change any attributes.

$u = get-qaduser -ou $ou


$u = get-qaduser -ou $ou -LDAPFILTER "(&(GivenName=$($User.GivenName))(SN=$($User.SN)))"
0
 
Acosta Technology ServicesAuthor Commented:
Ken, thanks for the assistance; I put the LDAPFilter back in place and am now receiving the following error, any thoughts?


Set-QADUser : Cannot validate argument on parameter 'Identity'. The argument is null or empty. Supply an argument that
is not null or empty and then try the command again.
At C:\Users\aaron.trentacosta\Desktop\PS Scripts\Test Scripts\2.ps1:14 char:20
+         set-qaduser <<<<  $u -firstname $User.givenname -Lastname $user.sn -objectattributes @{employeeID=$($user.emp
loyeeID)}
    + CategoryInfo          : InvalidData: (:) [Set-QADUser], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Quest.ActiveRoles.ArsPowerShellSnapIn.Powershell.Cmdlet
   s.SetUserCmdlet

Set-QADUser : Cannot validate argument on parameter 'Identity'. The argument is null or empty. Supply an argument that
is not null or empty and then try the command again.
At C:\Users\aaron.trentacosta\Desktop\PS Scripts\Test Scripts\2.ps1:14 char:20
+         set-qaduser <<<<  $u -firstname $User.givenname -Lastname $user.sn -objectattributes @{employeeID=$($user.emp
loyeeID)}
    + CategoryInfo          : InvalidData: (:) [Set-QADUser], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Quest.ActiveRoles.ArsPowerShellSnapIn.Powershell.Cmdlet
   s.SetUserCmdlet

Get-QADUser : The search filter is invalid.
At C:\Users\aaron.trentacosta\Desktop\PS Scripts\Test Scripts\2.ps1:5 char:21
+     $u = get-qaduser <<<<  -ou $ou -LDAPFILTER "(&(GivenName=$($User.GivenName))(SN=$($User.SN)))"
    + CategoryInfo          : NotSpecified: (:) [Get-QADUser], LdapException
    + FullyQualifiedErrorId : System.DirectoryServices.Protocols.LdapException,Quest.ActiveRoles.ArsPowerShellSnapIn.P
   owershell.Cmdlets.GetUserCmdlet
0
 
KenMcFCommented:
I can do more testing with this tonight and will post back.
0
 
Acosta Technology ServicesAuthor Commented:
I found one issue.  Checked in AD; the sn and givenname attributes were blanked out.  I re-entered them and ran the script; I received some output but then went back into AD and found the entries blanked out again.  It looks like the script is erasing those values, but not inputting the employeeID value.  I'd like the script to select the users based on the sn and givenname attributes, but I don't want them to be modified during the process.
0
 
Acosta Technology ServicesAuthor Commented:
I modified the else statement as shown below.  Starting to see some results.  The other attributes were not blanked out, and the employee ID updated for the "Test3,Test2" user.  Nothing updated on the "Test2,Test1" account.  I think we're almost there...

 
}
    Else{
        set-qaduser $u -objectattributes @{employeeID=$($user.employeeID)}
        
    }

Open in new window

0
 
Acosta Technology ServicesAuthor Commented:
Sorry to keep spamming the board.   I ran a few more tests and found the following.  Whatever entry comes last in the csv file errors out and doesn't update the employee ID.  I added a dummy line to the end of the CSV (with 3 legitimate entries above it) and the 3 users updated successfully.  I'll continue testing and post my findings.  Thanks again Ken.
0
 
KenMcFCommented:
ok, try this one. You should not have to update the users First name and Last name in AD. You are doing the search so you know they are correct. This will output the users that are duplicates in the query by Firstname,LastName,Samaccountname



$ou = "devlab.local/User_Accounts"
$Users = import-csv c:\temp\users.csv
Foreach($user in $Users){
    $u = get-qaduser -ou $ou -LDAPFILTER "(&(GivenName=$($User.FirstName))(SN=$($User.LastName)))"
        If (($u | Measure-Object).count -eq "1"){
            $u | set-qaduser -objectattributes @{employeeID=$($user.employeeID)}      
        }
        Else{
            $u | Foreach{ 
                Write-output "$($user.Firstname),$($user.LastName),$($_.samaccountname)"
            }
        }
}

Open in new window

0
 
Acosta Technology ServicesAuthor Commented:
I made a few slight modifications (below) to match my environment and put an out-file command for the duplicate users section.  It's making the correct employeeID modifications, but I've noticed two issues.  The output file is only showing the 2nd duplicate and beyond.  ie: if I have 5 John Smith's, it only outputs the last 4.  Also, I'm receiving this error 4 times at the end of each run:

Get-QADUser : The search filter is invalid.
At C:\Users\aaron.trentacosta\Desktop\PS Scripts\Test Scripts\3.ps1:4 char:21
+     $u = get-qaduser <<<<  -ou $ou -LDAPFILTER "(&(GivenName=$($User.GivenName))(sn=$($User.sn)))"
    + CategoryInfo          : NotSpecified: (:) [Get-QADUser], LdapException
    + FullyQualifiedErrorId : System.DirectoryServices.Protocols.LdapException,Quest.ActiveRoles.ArsPowerShellSnapIn.P
   owershell.Cmdlets.GetUserCmdlet



$ou = "corp.mosaic.com/Test"
$Users = import-csv c:\csv\testcsv.csv
Foreach($user in $Users){
    $u = get-qaduser -ou $ou -LDAPFILTER "(&(GivenName=$($User.GivenName))(sn=$($User.sn)))"
        If (($u | Measure-Object).count -eq "1"){
            $u | set-qaduser -objectattributes @{employeeID=$($user.employeeID)}      
        }
        Else{
            $u | Foreach{ 
                Write-output "$($user.Firstname),$($user.LastName),$($_.samaccountname)" | out-file "c:\output.txt"
            }
        }
} 

Toggle HighlightingOpen in New WindowSelect All

Open in new window

0
 
KenMcFCommented:
Try this one


$outfile = @()
$ou = "devlab.local/User_Accounts"
$Users = import-csv "C:\temp\Users.csv"
Foreach($user in $Users){
    $u = get-qaduser -ou $ou -LDAPFILTER "(&(GivenName=$($User.GivenName))(sn=$($User.sn)))"
        If (($u | Measure-Object).count -eq "1"){
            $u | set-qaduser -objectattributes @{employeeID=$($user.employeeID)}      
        }
        Else{
            $u | Foreach{ 
                Write-output "$($user.Givenname),$($user.SN),$($_.samaccountname)" 
                $outfile += "$($user.Givenname),$($user.SN),$($_.samaccountname)"
            }
        }
} 
$outfile | out-file c:\temp\out.txt

Open in new window

0
 
Acosta Technology ServicesAuthor Commented:
Here's the text output from that one:

john,smith,johnsmith
john,smith,johnsmith2
,,johnsmith
,,johnsmith2
,,johnsmith
,,johnsmith2
,,johnsmith
,,johnsmith2

John Smith is my test duplicate in AD, not sure why it's outputting so many times.  I received the following error, but only 3 times on this run:

Get-QADUser : The search filter is invalid.
At C:\Users\aaron.trentacosta\Desktop\PS Scripts\Test Scripts\4.ps1:5 char:21
+     $u = get-qaduser <<<<  -ou $ou -LDAPFILTER "(&(GivenName=$($User.GivenName))(sn=$($User.sn)))"
    + CategoryInfo          : NotSpecified: (:) [Get-QADUser], LdapException
    + FullyQualifiedErrorId : System.DirectoryServices.Protocols.LdapException,Quest.ActiveRoles.ArsPowerShellSnapIn.P
   owershell.Cmdlets.GetUserCmdlet
0
 
KenMcFCommented:
if you have duplicate names in the input file and the same names in AD it will output every matched user in AD. So if you have 3 John Smith's in your input file and three in AD you will end up with 9 in your output file. This can be changed however you want the output to be.

So if you just want the users in the input file you can use this


$outfile = @()
$ou = "devlab.local/User_Accounts"
$Users = import-csv "C:\temp\Users.csv"
Foreach($user in $Users){
    $u = get-qaduser -ou $ou -LDAPFILTER "(&(GivenName=$($User.GivenName))(sn=$($User.sn)))"
        If (($u | Measure-Object).count -eq "1"){
            $u | set-qaduser -objectattributes @{employeeID=$($user.employeeID)}      
        }
        Else{
                $outfile += "$($user.Givenname),$($user.SN),$($user.employeeID)"
        }
} 
$outfile | out-file c:\temp\out.txt

Open in new window

0
 
Acosta Technology ServicesAuthor Commented:
Thanks for the assistance Ken.
0
 
mrsnetopsCommented:
Ken,  I am looking to do about the same thing.  What happens if the user is in the csv file but not in AD.  We are also getting this file from HR but we have employees in this file that may not have an user account in AD. Will this error out or jus skip them.
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

  • 11
  • 11
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now