Updating Custom AD Attributes from CSV

PowerShell Help
1

I have a source .csv file that will be used to update custom attributes in AD. I am using the employeeid number as the identity. When I attempt to process more than the last user of the .csv in my testing it isn’t working because the hash table that is created, of course, cannot be re-used per user. I would really appreciate some guidance on how to handle the “foreach” in the code with regards to the hash table:

$OUpath = 'OU=Users and Workstations,DC=companydomain,DC=COM'
$csvdata = (Import-CSV D:\Data\HR_EMPLID_EXP.csv -Delimiter "|" | Select-Object -Last 3)
$userNameEid = $user.Employeeid
$RRAAttrib = @{}


foreach ($user in $csvdata) {

    if ($User.Sector -ne "#") {$Data = $User.Sector -split ";"
    $Sector = @() 
    ($Data.count-1)..0 | foreach {$Sector += $Data[$_]} }
    if ($User.CmbPractice -ne "#") {$Data = $User.CmbPractice -split ";"
    $CmbPractice = @() 
    ($Data.count-1)..0 | foreach {$CmbPractice += $Data[$_]} }
    if ($User.CmbMajorPractice -ne "#") { $Data = $User.CmbMajorPractice -split ";"
    $CmbMajorPractice = @()
    ($Data.count-1)..0 | foreach {$CmbMajorPractice += $Data[$_]} }
    if ($User.Region -ne "#") {$Data = $User.Region -split ";"
    $Region = @()
    ($Data.count-1)..0 | foreach {$Region += $Data[$_]} }
    if ($User.MajorIndustry_Capability -ne "#") {$Data = $User.MajorIndustry_Capability -split ";"
    $MajorIndustry_Capability = @()
    ($Data.count-1)..0 | foreach {$MajorIndustry_Capability += $Data[$_]} }
    if ($User.Committees -ne "#") {$Data = $User.Committees -split ";"
    $Committees = @()
    ($Data.count-1)..0 | foreach {$Committees += $Data[$_]} }
    if ($User.SectorLeaderRole -ne "#") {$Data = $User.SectorLeaderRole -split ";"
    $SectorLeaderRole = @()
    ($Data.count-1)..0 | foreach {$SectorLeaderRole += $Data[$_]} }
    if ($User.PracticeLeaderRole -ne "#") {$Data = $User.PracticeLeaderRole -split ";"
    $PracticeLeaderRole = @()
    ($Data.count-1)..0 | foreach {$PracticeLeaderRole += $Data[$_]} }
    if ($User.HubLeader) {$Data = $User.HubLeader -split ";"
    $HubLeader = @()
    ($Data.count-1)..0 | foreach {$HubLeader += $Data[$_]} }
    if ($User.FirmLeaderShipRoleOne -ne "#") {$Data = $User.FirmLeaderShipRoleOne -split ";"
    $FirmLeaderShipRoleOne = @()
    ($Data.count-1)..0 | foreach {$FirmLeaderShipRoleOne += $Data[$_]} }
    if ($User.FirmLeadershipRole -ne "#") {$Data = $User.FirmLeadershipRole -split ";"
    $FirmLeadershipRole = @()
    ($Data.count-1)..0 | foreach {$FirmLeadershipRole += $Data[$_]} }
    if ($User.PSSectorSL -ne "#") {$Data = $User.PSSectorSL -split ";"
    $PSSectorSL = @()
    ($Data.count-1)..0 | foreach {$PSSectorSL += $Data[$_]} }
    if ($User.PSSectorPL -ne "#") {$Data = $User.PSSectorPL -split ";"
    $PSSectorPL = @()
    ($Data.count-1)..0 | foreach {$PSSectorPL += $Data[$_]} }
    if ($User.PSHL -ne "#") {$Data = $User.PSHL -split ";"
    $PSHL = @()
    ($Data.count-1)..0 | foreach {$PSHL += $Data[$_]} }
    if ($User.PSDHL -ne "#") {$Data = $User.PSDHL -split ";"
    $PSDHL = @()
    ($Data.count-1)..0 | foreach {$PSDHL += $Data[$_]} }
    if ($User.PSHubPortfolio -ne "#") {$Data = $User.PSHubPortfolio -split ";"
    $PSHubPortfolio = @()
    ($Data.count-1)..0 | foreach {$PSHubPortfolio += $Data[$_]} }
    if ($User.PSHP -ne "#") {$Data = $User.PSHP -split ";"
    $PSHP = @()
    ($Data.count-1)..0 | foreach {$PSHP += $Data[$_]} }
    if ($User.IPL -ne "#") {$Data = $User.IPL -split ";"
    $IPL = @()
    ($Data.count-1)..0 | foreach {$IPL += $Data[$_]} }
    if ($User.CPL -ne "#") {$Data = $User.CPL -split ";"
    $CPL = @()
    ($Data.count-1)..0 | foreach {$CPL += $Data[$_]} }


    If ($Sector) {$RRAAttrib.Add("rraSector", $Sector)}
    If ($CmbPractice) {$RRAAttrib.Add("rraPractice", $CmbPractice)}
    If ($CmbMajorPractice) {$RRAAttrib.Add("rraCPractice", $CmbMajorPractice)}
    If ($Region) {$RRAAttrib.Add("rraRegion", $Region)}
    If ($MajorIndustry_Capability) {$RRAAttrib.Add("rraCsector", $MajorIndustry_Capability)}
    If ($Committees) {$RRAAttrib.Add("rraCommitteeM", $Committees)}
    If ($SectorLeaderRole) {$RRAAttrib.Add("rraSectorLR", $SectorLeaderRole)}
    If ($PracticeLeaderRole) {$RRAAttrib.Add("rraPracticeLR", $PracticeLeaderRole)}
    If ($HubLeader) {$RRAAttrib.Add("rraACntryR", $HubLeader)}
    If ($FirmLeaderShipRoleOne) {$RRAAttrib.Add("rraFirmLdrshipOne", $FirmLeaderShipRoleOne)}
    If ($FirmLeadershipRole) {$RRAAttrib.Add("rraFirmLR", $FirmLeadershipRole)}
    If ($PSSectorSL) {$RRAAttrib.Add("rraPSSectorSL", $PSSectorSL)}
    If ($PSSectorPL) {$RRAAttrib.Add("rraPSSectorPL", $PSSectorPL)}
    If ($PSHL) {$RRAAttrib.Add("rraPSAM", $PSHL)}
    If ($PSDHL) {$RRAAttrib.Add("rraPSDAM", $PSHDL)}
    If ($PSHubPortfolio) {$RRAAttrib.Add("rraPSRC", $PSHubPortfolio)}
    If ($PSHP) {$RRAAttrib.Add("rraPSHP", $PSHP)}
    If ($IPL) {$RRAAttrib.Add("rraIPL", $IPL)}
    If ($CPL) {$RRAAttrib.Add("rraCPL", $CPL)}

    $adUser = Get-ADUser -Filter "employeeid -eq '$userNameEid'"

    $NumAttr = $RRAAttrib.Count

    If ($NumAttr -ge 1) {Set-ADUser -Identity $adUser -Replace $RRAAttrib}
    


}
2

If it’s really just this - why don’t you move the hashtable initialization

$RRAAttrib = @{}

inside the loop? … or did I get something wrong?

3

If the goal is just to consume a csv and build a hash, this can be a bit simpler. Start by renaming the columns, to align with the Ad Property Attrributes, then loop through the properties filtering what you don’t need to build a hash and run your AD commands.

$csvData = @'
Employeeid|Name|Sector|CmbPractice|CmbMajorPractice
1234|Sam Johnson|Sector1|#|MP54321
1235|Frank Wu|#|P58392|MP53622
1236|Joe Mama|Sector5|#|#
1237|Julie Bick|#|#|#
'@ | ConvertFrom-Csv -Delimiter "|" |
        Select-Object -Property Employeeid,
                                @{Name='rraSector';Expression={$_.Sector}},
                                @{Name='rraPractice';Expression={$_.CmbPractice}},
                                @{Name='rraCPractice';Expression={$_.CmbMajorPractice}}

$propsToExclude = 'Employeeid','Name'

foreach ($row in $csvData) {
    $adCustomAttrib = @{}
    foreach ($prop in $row.PSObject.Properties | Where{$_.Value -ne '#' -and $propsToExclude -notcontains $_.Name}) {
        Write-Host -Message ('Adding property {0} with value {1} to hash table for empId {2}' -f $prop.Name, $prop.Value, $row.Employeeid)
        $adCustomAttrib.Add($prop.Name, $prop.Value)
    }

    if ($adCustomAttrib.Count -gt 0) {
        Write-Host -Message ('Updating employeeId {0} with {1} custom attrib updates' -f $row.Employeeid, $adCustomAttrib.Count)
        # $adUser = Get-ADUser -Filter "employeeid -eq '$userNameEid'"

        If ($adUser) {
            # Set-ADUser -Identity $adUser -Replace $adCustomAttrib
        }
    }
    else {
        Write-Host -Message ('Skipping employeeId {0}, no updates to perform' -f $row.Employeeid)
    }
}

Output:

Adding property rraSector with value Sector1 to hash table for empId 1234
Adding property rraCPractice with value MP54321 to hash table for empId 1234
Updating employeeId 1234 with 2 custom attrib updates
Adding property rraPractice with value P58392 to hash table for empId 1235
Adding property rraCPractice with value MP53622 to hash table for empId 1235
Updating employeeId 1235 with 2 custom attrib updates
Adding property rraSector with value Sector5 to hash table for empId 1236
Updating employeeId 1236 with 1 custom attrib updates
Skipping employeeId 1237, no updates to perform

Also remember to put a try\catch around the Set-ADUser.

4

That seemed to do the trick! I had that in my head to do that but there must’ve been a disconnect between there and my fingers! Thanks for the second set of eyes.

5

This is interesting. The information in $csvData - is that assumed to be from the imported .csv file?

6

Yes, that is just a method to emulate an Import-CSV and get some data in for testing, but the raw data can be seen in the script vs providing a separate CSV. Use it all the time to quickly build objects to test\mock things up. Just replace the here-string with Import-CSV, add the remaining column mapping and then test, test and test more!

1 Like