PowerShell: Update CSV File Using Active Directory

# adAccountsCsvUpdate.ps1

$originalCsv='C:\Users\rambo\Desktop\kimconnectUsers.csv'
$newCsv='C:\Users\rambo\Desktop\kimconnectUsers-processed.csv'
$newEmailSuffix='@kimconnect.com'
$newOu='OU=Test,DC=kimconnect,DC=com'

function adAccountsCsvUpdate{
  param(
    $originalCsv,
    $newCsv,
    $newEmailSuffix,
    $newOu
  )

  function generateRandomPassword{
    param(
        $minLength = 10,
        $maxLength = 16,
        $nonAlphaChars = 2,
        $excludeRegex='[:\$\%\&\,]',
        $replaceExclusionWith=@(';','!','/','{','^','+','-','*','_')
    )
    add-type -AssemblyName System.Web
    $randomLength = Get-Random -Minimum $minLength -Maximum $maxLength   
    $randomPassword = [System.Web.Security.Membership]::GeneratePassword($randomLength, $nonAlphaChars)
    $sanitizedPassword = $randomPassword -replace $excludeRegex,"$(Get-Random -InputObject $replaceExclusionWith)"
    $fixedRepeating = .{$rebuiltString=''
                        for ($i=0;$i -lt $sanitizedPassword.length;$i++){
                        $previousChar=$sanitizedPassword[$i-1]
                        $thisChar=$sanitizedPassword[$i]
                        $nextChar=$sanitizedPassword[$i+1]
                        if($thisChar -eq $nextChar){
                            do{
                                $regenChar=[char](Get-Random (65..122) )
                                }until($regenChar -ne $previousChar -and $regenChar -ne $nextChar)
                            $rebuiltString+=$regenChar
                            }
                        else{$rebuiltString+=$thisChar}
                        }
                        return $rebuiltString
                        }
                             
    return $fixedRepeating
  }

  $csvContents=import-csv $originalCsv
  write-host "Pulling existing records from Active Directory of $env:USERDNSDOMAIN..."
  $allExistingUsers=get-aduser -Filter * -property SamAccountName,GivenName,sn,EmailAddress,Department,Description,telephoneNumber,Title,Manager,ManagedBy,City,State,postalCode,Enabled

  write-host "First pass: newSamAccountName"
  $firstPass=@()
  $count=$csvContents.count
  $itemIndex=0
  foreach ($row in $csvContents){
    $samAccountName=$row.SamAccountName
    $firstName=$row.GivenName
    $lastName=$row.Surname
    #$userPrincipalName=$row.UserPrincipalName
    $itemIndex++
    write-host "Processing $itemIndex of $count`: $samAccountName..."
    $newSamAccountName=.{
      # Default: return NULL if account already exists
      # $matchedEmail=$allExistingUsers|?{$_.EmailAddress -eq $userPrincipalName}
      # if($matchedEmail){
      #   return $null
      # }

      # Method 1: check to determine whether there are not duplicating records
      $matchedSam=$allExistingUsers|?{$_.SamAccountName -eq $samAccountName}
      if(!$matchedSam){
        return $samAccountName
      }
      # Method 1: testing firstname initials + lastname combinations
      for ($i=0;$i -lt $firstName.length;$i++){
        $testUsername=($firstName[0..$i] -join '')+$lastName
        if($testUserName -notin $allExistingUsers.SamAccountName){
          return $testUsername
        }
      }
      # Method 2: incrementing the username by a single digit
      for($i=1;$i -lt 11;$i++){
        $testUsername2=$samAccountName+$i
        if($testUserName2 -notin $allExistingUsers.SamAccountName){
          return $testUsername2
        }
      }
    }
    if($newSamAccountName -ne $samAccountName){
      write-host "SAM in CSV $samAccountName shall be updated as $newSamAccountName"
    }  
    $firstPass+=$row|select-object *,@{Name='newSamAccountName';Expression={$newSamAccountName}}
  }

  write-host "Second pass: newManagerSamAccount"
  $secondPass=@()
  foreach ($row in $firstPass){
    $manager=$row.Manager
    $firstName=[regex]::match($manager,'^(.+)\s(.+)').groups[1].Value
    $lastName=[regex]::match($manager,'^(.+)\s(.+)').groups[2].Value
    $matchedManagerSam=$firstPass|?{$_.GivenName -eq $firstName -and $_.Surname -eq $lastName}
    $newManagerSamAccount=.{      
      if($matchedManagerSam){
        return $matchedManagerSam.newSamAccountName
      }else{
        return $null
      }
    }
    if($newManagerSamAccount -ne $manager){
      write-host "Manager in CSV '$manager' shall be updated as '$newManagerSamAccount'"
    }
    $secondPass+=$row|select-object *,@{Name='newManagerSamAccount';Expression={$newManagerSamAccount}}
  }

  write-host "Third pass: adding new manager Distinguished Name paths..."
  $thirdPass=@()
  foreach ($row in $secondPass){
    $thisNewManagerSamAccount=$row.newManagerSamAccount   
    $newManagerDN=if($thisNewManagerSamAccount){
      $matchedRow=$secondPass|?{$_.newSamAccountName -eq $thisNewManagerSamAccount}
      $surName=$matchedRow.Surname
      $givenName=$matchedRow.GivenName
      "CN=$surName\, $givenName,"+$newOu
    }else{''}
    $thirdPass+=$row|select-object *,@{Name='newManagerDN';Expression={$newManagerDN}}
  }

  
  write-host "Forth pass: adding new email addresses..."
  $forthPass=@()
  foreach ($row in $thirdPass){
    $username=$row.newSamAccountName
    $forthPass+=$row|select-object *,@{Name='newEmailAddress';Expression={$username+$newEmailSuffix}}
  }  

  write-host "Fifth pass: generating new randomized passwords"
  $fifthPass=@()
  foreach ($row in $forthPass){
    $fifthPass+=$row|select-object *,@{Name='newPassword';Expression={[string](generateRandomPassword)}}
  }

  $newCsvContents=$fifthPass
  $conflictingUserNames=$newCsvContents|?{$_.SamAccountName -ne $_.newSamAccountName}
  write-host "There are $($conflictingUsernames.count) usernames that have conflicted with existing accounts in Active Directory. Hence, new account usernames would be modified to mitigate collisions."
  if(test-path $newCsv){remove-item $newCsv -force}
  if(!(test-path $(split-path $newCsv -parent))){mkdir $(split-path $newCsv -parent) -force}
  $oldHeaders='"'+$($csvContents[0].psobject.Properties.Name -join '","')+'"'
  $newHeaders=$oldHeaders+',"newSamAccountName","newManagerSamAccount","newManagerDN","newEmailAddress","newPassword"'
  Add-Content -Path $newCsv -Value $newHeaders
  $newCsvContents|Export-Csv $newCsv -NoTypeInformation -append
}

adAccountsCsvUpdate $originalCsvFile $newCsvFile $newEmailSuffix $newOu

$originalCsvFile='C:\temp\ActiveDirectoryUsers.csv'
$newCsvFile='C:\temp\ActiveDirectoryUsers_Updated.csv'

function updateRecordsUsingActiveDirectory($originalCsv,$newCsv){
  $csvContents=import-csv $originalCsv
  write-host "Pulling existing records from Active Directory of $env:USERDNSDOMAIN..."
  $allExistingUsers=get-aduser -Filter * -property SamAccountName,GivenName,sn,EmailAddress,Department,Description,telephoneNumber,Title,Manager,ManagedBy,City,State,postalCode,Enabled

  write-host "First pass: newSamAccountName"
  $firstPass=@()
  $count=$csvContents.count
  $itemIndex=0
  foreach ($row in $csvContents){
    $samAccountName=$row.SamAccountName
    $firstName=$row.GivenName
    $lastName=$row.sn
    $itemIndex++
    write-host "Processing $itemIndex of $count`: $samAccountName..."  
    $newSamAccountName=.{
      # Default if there are not duplicating records
      $matchedSam=$allExistingUsers|?{$_.SamAccountName -eq $samAccountName}
      if(!$matchedSam){
        return $samAccountName
      }
      # Method 1: testing firstname initials + lastname combinations
      for ($i=0;$i -lt $firstName.length;$i++){
        $testUsername=($firstName[0..$i] -join '')+$lastName
        if($testUserName -notin $allExistingUsers.SamAccountName){
          return $testUsername
        }
      }
      # Method 2: incrementing the username by a single digit
      for($i=1;$i -lt 11;$i++){
        $testUsername2=$samAccountName+$i
        if($testUserName2 -in $allExistingUsers.SamAccountName){
          return $testUsername2
        }      
      }
    }
    if($newSamAccountName -ne $samAccountName){
      write-host "SAM in CSV $samAccountName shall be updated as $newSamAccountName"
    }  
    $firstPass+=$row|select-object *,@{Name='newSamAccountName';Expression={$newSamAccountName}}
  }
  
  write-host "Second pass: newManagerSamAccount & newManagerDN"
  $secondPass=@()
  foreach ($row in $firstPass){
    $manager=.{if($row.Manager -notmatch '\s'){
        return $row.Manager
      }else{
        $managerArray=$row.Manager -split ' '
        $managerLastName=$managerArray[$managerArray.count-1]
        return $($row.Manager)[0]+$managerLastName
      }
    }
    $matchedManagerSam=$firstPass|?{$_.SamAccountName -eq $manager}
    $newManagerSamAccount=.{      
      if($matchedManagerSam){
        return $matchedManagerSam.newSamAccountName
      }else{
        return $null
      }
    }
    if($newManagerSamAccount -ne $manager){
      write-host "Manager in CSV '$manager' shall be updated as '$newManagerSamAccount'"
    }
    $newManagerDN=.{
      if($matchedManagerSam.OU){
        return "CN=$($matchedManagerSam.sn)\, $($matchedManagerSam.GivenName),"+$matchedManagerSam.OU
      }else{
        return "CN=$($row.sn)\, $($row.GivenName),"+$row.OU
      }
    }
    $secondPass+=$row|select-object *,@{Name='newManagerSamAccount';Expression={$newManagerSamAccount}},@{Name='newManagerDN';Expression={$newManagerDN}}
  }
  
  write-host "Third pass: generating new randomized passwords"
  $thirdPass=@()
  function generateRandomPassword{
    param(
        $minLength = 10,
        $maxLength = 16,
        $nonAlphaChars = 2,
        $excludeRegex='[:\$\%\&\,]',
        $replaceExclusionWith=@(',',';','!','/','{','^','+','-','*','_')
    )
    add-type -AssemblyName System.Web
    $randomLength = Get-Random -Minimum $minLength -Maximum $maxLength   
    $randomPassword = [System.Web.Security.Membership]::GeneratePassword($randomLength, $nonAlphaChars)
    $sanitizedPassword = $randomPassword -replace $excludeRegex,"$(Get-Random -InputObject $replaceExclusionWith)"
    $fixedRepeating = .{$rebuiltString=''
                        for ($i=0;$i -lt $sanitizedPassword.length;$i++){
                        $previousChar=$sanitizedPassword[$i-1]
                        $thisChar=$sanitizedPassword[$i]
                        $nextChar=$sanitizedPassword[$i+1]
                        if($thisChar -eq $nextChar){
                            do{
                                $regenChar=[char](Get-Random (65..122) )
                                }until($regenChar -ne $previousChar -and $regenChar -ne $nextChar)
                            $rebuiltString+=$regenChar
                            }
                        else{$rebuiltString+=$thisChar}
                        }
                        return $rebuiltString
                        }
                             
    return $fixedRepeating
  }

  foreach ($row in $secondPass){
    $thirdPass+=$row|select-object *,@{Name='newPassword';Expression={[string](generateRandomPassword)}}
  }

  $newCsvContents=$thirdPass
  $conflictingUserNames=$newCsvContents|?{$_.SamAccountName -ne $_.newSamAccountName}
  write-host "There are $($conflictingUsernames.count) usernames that have conflicted with existing accounts in Active Directory. Hence, new account usernames would be modified to mitigate collisions."
  if(test-path $newCsv){remove-item $newCsv -force}
  if(!(test-path $(split-path $newCsv -parent))){mkdir $(split-path $newCsv -parent) -force}
  $oldHeaders='"'+$($csvContents[0].psobject.Properties.Name -join '","')+'"'
  $newHeaders=$oldHeaders+',"newSamAccountName","newManagerSamAccount","newManagerDN","newPassword"'
  Add-Content -Path $newCsv -Value $newHeaders
  $newCsvContents|Export-Csv $newCsv -NoTypeInformation -append
}

updateRecordsUsingActiveDirectory $originalCsvFile $newCsvFile