Solved

Update AD attribute with PowerShell

Posted on 2011-09-09
24
2,177 Views
Last Modified: 2013-12-03
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
Comment
  • 11
  • 11
  • +1
24 Comments
 
LVL 27

Expert Comment

by:KenMcF
ID: 36512693
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
 
LVL 35

Expert Comment

by:YZlat
ID: 36512705
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
 
LVL 8

Author Comment

by:Acosta Technology Services
ID: 36512718
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
 
LVL 27

Expert Comment

by:KenMcF
ID: 36512751
you can do this

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

Expert Comment

by:KenMcF
ID: 36512784
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
 
LVL 8

Author Comment

by:Acosta Technology Services
ID: 36512894
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
 
LVL 27

Expert Comment

by:KenMcF
ID: 36512971
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
 
LVL 27

Expert Comment

by:KenMcF
ID: 36512997
and this line

$u.count -gt 1

should be

$u.count -eq 1
0
 
LVL 8

Author Comment

by:Acosta Technology Services
ID: 36513019
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
 
LVL 27

Expert Comment

by:KenMcF
ID: 36513109
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
 
LVL 8

Author Comment

by:Acosta Technology Services
ID: 36513404
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
 
LVL 27

Expert Comment

by:KenMcF
ID: 36513458
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
 
LVL 8

Author Comment

by:Acosta Technology Services
ID: 36513484
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
 
LVL 27

Expert Comment

by:KenMcF
ID: 36513680
I can do more testing with this tonight and will post back.
0
 
LVL 8

Author Comment

by:Acosta Technology Services
ID: 36513708
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
 
LVL 8

Author Comment

by:Acosta Technology Services
ID: 36513734
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
 
LVL 8

Author Comment

by:Acosta Technology Services
ID: 36513770
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
 
LVL 27

Expert Comment

by:KenMcF
ID: 36514567
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
 
LVL 8

Author Comment

by:Acosta Technology Services
ID: 36522436
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
 
LVL 27

Expert Comment

by:KenMcF
ID: 36522516
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
 
LVL 8

Author Comment

by:Acosta Technology Services
ID: 36522557
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
 
LVL 27

Accepted Solution

by:
KenMcF earned 500 total points
ID: 36522577
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
 
LVL 8

Author Closing Comment

by:Acosta Technology Services
ID: 36525091
Thanks for the assistance Ken.
0
 

Expert Comment

by:mrsnetops
ID: 39693900
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

Join & Write a Comment

In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now