Powershell script to compare AD to Csv Export - Update missing attribute with Csv data

Question

Powershell script to compare AD to Csv Export - Update missing attribute with Csv data

John Adams 1

Hello,

We have a process where an export for users is dumped to a location as a csv file. The csv file has two columns: Extensionattribute10 and Extensionattribute11.

We need to check Ad for users where Extensionattribute10 is empty and then update that user from the data in the csv file. All users have Extensionattribute11 so we can

use that column to callback to the csv and get the proper Extensionattribute10 data that should be set on the user.

Any tips of assistance is much appreciated.

Thank you

Rich Matheisen 47,901 Reputation points

2022-03-10T20:11:18.523+00:00

Are you sure that ALL users in the AD have a populated ExtensionAttribute11? What about users that aren't mail-enabled in some way? What about built-in accounts? Service accounts? Disabled accounts? What about accounts that should have a populated ExtensionAttribut11 but, for whatever reason, do not?

How many users are represented in that CSV? 100, 1000, 10,000, more?

If there are a sizable number of rows in the CSV it might make sense to build a hash from the CSV using ExtensionAttribute11 as the key and ExtensionAttribute10 as the data.

If you're sure that every user that should have a populated ExtensionAttribute truly does have one then use "Get-ADUser -filter "extensionattribute11 -like '' -and extensionattribute10 -notlike ''" to get only the users with missing extensionattribute10 values. If their extensionattribute11 is a key in the hash then populate extensionattribute 10 using the value in the hash.
John Adams 1 Reputation point

2022-03-10T20:37:36.087+00:00

All active users in AD have extensionattribute11 populated. All other accounts built-in, service accounts, disabled accounts, etc don't have the value.

There are about 20k users in the csv but not all them need to be updated.

The issue is that all active users in AD don't have extensionattribute10 populated (most do but not all) and that information is in the csv export. We need to search for all users that don't have extensionattribute10 and then call the csv file with the proper data to update the users account.

Thank you
Rich Matheisen 47,901 Reputation points

2022-03-10T20:51:41.087+00:00

See the 3rd and 4th paragraph in my earlier reply.

If you simply look for users that don't have a populated extensionattribute10 you're going to find all the users that need attention AND the inactive accounts, service accounts, etc.

The problem with using a CSV (or an array) to store the two attribute values is that there's no way to quickly find what you're looking for. You'd either have to search the entire CSV for each user missing extensionattribute10, or store the CSV as an array of arrays and then search that sequentially.
John Adams 1 Reputation point

2022-03-10T23:46:05.303+00:00

The users that are in the csv are all under one OU that only has enabled user accounts; no service account, disabled accounts, etc.

Are there any script examples to get us in the right direction? Still working increasing my powershell skills.

thank you

Rich Matheisen 47,901

You can try using this as a framework for what you want to do:

# EXAMPLE ONLY -- WILL NOT WORK "AS-IS"

$hash = @{}
Import-Csv <Your-CSV-File-Name-Here> |
    ForEach-Object{
        # populate hash here
    }
Get-ADUser -f "your-filter-string-here" -properties extensionattribute10,extensionattribute11 |
    if ($hash.ContainsKey(<extensionattribute11>){
        $_ | Set-ADUser -add @{extenssionattribute10 = $hash.[extensionattribute11]}
    }

John Adams 1 Reputation point

2022-03-14T17:34:33.82+00:00

This might seem like a stupid question but what details need to be put into # populate hash here?

Also where ($hash.ConainsKey(<extensionattribute11>): There is an error in < Missing ')' in method call. If I update using the following the error disappears but not sure if it will mess up the code yet as have not tested yet: ($hash.ContainsKey('extensionattribute11'))

thank you for the assistance with this.
Rich Matheisen 47,901 Reputation points

2022-03-14T18:39:43.89+00:00
You load the has like this:

$hash[$_.Extensionattribute11] = $_.Extensionattribute10

As I said, what I provided was a framework, not a working bit of code. Thus the # EXAMPLE ONLY -- WILL NOT WORK "AS-IS" at the top of the code.

The output from the Get-ADUser must be piped into a Foreach-Object (not included in the example I posted), and both references to extensionattribute11 within that loop must be prefaced with $<underbar><period> so they reference the current user.

The missing right parenthesis was a mistake -- as is the misspelling of "extenssionattribute10 (note the presence of two "s"'s).

Download and read this free PDF: Windows-PowerShell-4. Work through the 1st half of the book and do the exercises. You'll learn a lot. The 2nd half applies what you learn in the 1st half to common administrative tasks. Wait until you finish the 1st half before reading the 2nd half.