Replace AD attribute value with csv file header values

Question

I want to replace AD attribute "userPrincipalName" value according to CSV file header value here is what csv file(group.csv) contains

sAMAccountName
--------------
test.user1    
test.user2

below the script

$data = Import-Csv -Path .\group.csv -Header 'sAMAccountName'
foreach($user in $data){
    Get-ADUser -Filter {sAMAccountName -eq "$($user.sAMAccountName)"} | Set-ADUser -Replace @{userPrincipalName="$($user.sAMAccountName)@RES.GROUP"}
}

here I want to replace AD attribute "userPrincipalName" with the value of sAMAccountName from csv file, something like sAMAccountName@RES.GROUP

this script does not work, can anyone please correct it?

_this script does not work_.. Why not? Error messages? Explain what happens. Looks like your csv already has a header, so why add it again with parameter `-Header` ? `-Filter` should be a **string**, not a scriptblock, so `"SamAccountName -eq '$($user.sAMAccountName)'"` — Theo, Jan 19 '22 at 10:04
it should go through each "sAMAccountName " fron csv file, inside foreach it should get a user by filtering and replace the attribute value which is "userPrincipalName" in AD with sAMAccountName@RES.GROUP, which means userPrincipalName=sAMAccountName@RES.GROUP for example it gets test.user1 from cycle and sets this user's attribute(userPrincipalName) value to test.user1@RES.GROUP, which means the value of "userPrincipalName" attribute in AD will be "test.user1@RES.GROUP" for a user "test.user1" — khamro, Jan 19 '22 at 10:51
Yes, your question was clear about what you are aiming to do. I'm asking you if your code produces errors and if so you need to show them. I also suggested two possible fixes. Did you try that? — Theo, Jan 19 '22 at 10:59
Yes, Sir, I tried those fixes, shown by you, but nothing happens, it is not showing an error but at the same time it is not working as expected, attribute "userPrincipalName" is not being replaced — khamro, Jan 19 '22 at 11:32
if I remove -Header 'sAMAccountName' and run the script, I got this error WARNING: One or more headers were not specified. Default names starting with "H" have been used in place of any missing headers. Get-ADUser : The search filter cannot be recognized At line:4 char:5 + Get-ADUser -Filter "sAMAccountName -eq '$($user.sAMAccountName)'" ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Get-ADUser], ADException + FullyQualifiedErrorId : ActiveDirectoryServer:8254,Microsoft.ActiveDirectory.Management.Commands.GetADUser — khamro, Jan 19 '22 at 11:40
can we have a sample of your csv (first 2 lines). Your `Get-ADUser` is useless here because `sAMAccountName` is understand as an identity, so it is easier AND faster to do this : `$data | ForEach {Set-ADUser -Identity $_.sAMAccountName -UserPrincipalName "$($_.sAMAccountName)@RES.GROUP"}` — CFou, Jan 19 '22 at 12:08
csv file: sAMAccountName -------------- test.user1 test.user2 your script not working either — khamro, Jan 19 '22 at 13:14

Theo · Accepted Answer · 2022-01-20T12:52:14.170

Ok, since your comment shows the CSV file indeed does not have a header, I would suggest changing the code to:

$data = Import-Csv -Path .\group.csv -Header 'sAMAccountName'
foreach($user in $data) {
    $adUser = Get-ADUser -Filter "SamAccountName -eq '$($user.sAMAccountName)'" -ErrorAction SilentlyContinue
    if ($adUser) {
        $newUPN = '{0}@res.group' -f $user.sAMAccountName
        $adUser | Set-ADUser -UserPrincipalName $newUPN
    }
    else {
        Write-Warning "No user with SamAccountName '$($user.sAMAccountName)' could be found.."
    }
}

This way, any mistakes in the file will not make the code quit when a user with that samaccountname cannot be found. Instead, in that case you will see a warning about it and the code will continue with the rest of the data.

It might be worth mentioning that you can use parameter -Server on both the Get-ADUser and Set-ADUser cmdlets to make sure you use the same domain server (DC) to set the new UPN. Otherwise, you can set it on one DC, but are looking at another which doesn't show the change immediately because the servers need time to synchronize..

Now that we have cleared up the question about the CSV and to answer your comment:

If you want to do this as a two-script solution, here's how you can do that

step 1: get all users in the search OU that have a UserPrincipalName ending in '*@test.group'

$searchBase = "OU=Teams,OU=Prod,DC=RES,DC=TEST,DC=GROUP"
Get-ADUser -SearchBase $searchBase -Filter "UserPrincipalName -like '*@test.group'" | 
# select ony the SamAccountName and write to CSV with column header
Select-Object SamAccountName | Export-Csv -Path .\group.csv -NoTypeInformation

step 2: read the csv created above and

$searchBase = "OU=Teams,OU=Prod,DC=RES,DC=TEST,DC=GROUP"
$data = Import-Csv -Path .\group.csv
$result = foreach($user in $data) {
    $adUser = Get-ADUser -SearchBase $searchBase -Filter "SamAccountName -eq '$($user.sAMAccountName)'" -ErrorAction SilentlyContinue
    # if we have a user object AND its UserPrincipalName is not as desired go ahead and change that
    if ($adUser) {
        if ($adUser.UserPrincipalName -notlike '*@res.test.group') {
            $newUPN = '{0}@res.test.group' -f $user.sAMAccountName
            $adUser | Set-ADUser -UserPrincipalName $newUPN
            # output this user object to be collected in variable $result
            $adUser
        }
        else {
            Write-Host "User $($user.sAMAccountName) already has UPN '$($adUser.UserPrincipalName)'"
        }
    }
    else {
        Write-Warning "User with SamAccountName '$($user.sAMAccountName)' not found.."
    }
}

# now that we have changed some users, create a second csv with all users that were actually changed
if (@($result).Count) {
    $result | Select-Object SamAccountName | Export-Csv -Path .\Updatedgroup.csv -NoTypeInformation
}
else {
    Write-Host 'No users needed updating'
}

^{It seems a waste writing only the users SamAccountName property to the csv files.. Especially since Get-ADUser by default already returns these properties: DistinguishedName, Enabled, GivenName, Name, ObjectClass, ObjectGUID, SamAccountName, SID, Surname, UserPrincipalName}

no need to test this, a user not found does not block others treatments. As I have already commented, after the `Import-Csv`, simply use this line `$data| ForEach {Set-ADUser -Identity $_.sAMAccountName -UserPrincipalName "$($_.sAMAccountName)@res.group"}`. The only thing is you will have an error per user not found. If you don't care about errors, then add a `try` `catch` block : `$data| ForEach {try{Set-ADUser -Identity $_.SamAccountName -UserPrincipalName "$($_.sAMAccountName)@res.group"}catch{}}` — CFou, Jan 19 '22 at 13:38
thanks for your help bro, but it is still not working I am getting an error that per user not found — khamro, Jan 19 '22 at 17:27
@khamro Nope, seeing your post below, your csv is **definitely** not ok. What you are saving is the output of `Format-Table`, which is for display purposes only. To create a proper csv, use `Export-Csv` on the data (the **objects**) directly and forget about `FT | Out-File`. So, first create a real csv and use my function. — Theo, Jan 19 '22 at 18:48
Good Day! Thanks, Bro It worked, the problem was with my csv file — khamro, Jan 20 '22 at 04:24
can we also extend this script? I mean after running this script, it should store sAMAccountName in anthoer scv file, this csv file should store users to whom were been made changes by running the first script — khamro, Jan 20 '22 at 05:01

Replace AD attribute value with csv file header values

1 Answers1