# 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