PowerShell: Update Accounts Basing on CSV File

# adAccountsCsvUpdate.ps1

$originalCsvFile='C:\Users.csv'
$newCsvFile='C:\Users-finalized.csv'
$newEmailSuffix='@kimconnect.net'
$newOu='CN=Users,DC=kimconnect,DC=com'

function adAccountsCsvUpdate{
  param(
    $originalCsvFile,
    $newCsvFile,
    $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 $originalCsvFile
  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=if($userPrincipalName){$allExistingUsers|?{$_.EmailAddress -eq $userPrincipalName}}else{$false}
      if($matchedEmail){
        return $matchedEmail.SamAccountName
      }

      # Method 1: check to determine whether there are not duplicating records
      $matchedSam=$allExistingUsers|?{$_.SamAccountName -eq $samAccountName}
      if(!$matchedSam){
        return $samAccountName
      }
      # Method 2: 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 3: 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 $newCsvFile){remove-item $newCsvFile -force}
  if(!(test-path $(split-path $newCsvFile -parent))){mkdir $(split-path $newCsvFile -parent) -force}
  $oldHeaders='"'+$($csvContents[0].psobject.Properties.Name -join '","')+'"'
  $newHeaders=$oldHeaders+',"newSamAccountName","newManagerSamAccount","newManagerDN","newEmailAddress","newPassword"'
  Add-Content -Path $newCsvFile -Value $newHeaders
  $newCsvContents|Export-Csv $newCsvFile -NoTypeInformation -append
}

adAccountsCsvUpdate $originalCsvFile $newCsvFile $newEmailSuffix $newOu

# Checking a single user for an associated email address record
# function getEmailOfUser($username=$env:username){
#   $query = "SELECT * FROM ds_user where ds_sAMAccountName='$username'"
#   $user = Get-WmiObject -Query $query -Namespace "root\Directory\LDAP"
#   return $user.DS_mail
# }