Duplicate names in csv

PowerShell Help
1

Good afternoon everyone! I hope this finds everyone well during this pandemic. I have been tasked with creating a new user automation script. I know what needs to be done but do not understand fully how to implement it into my script. I have a CSV with thousands of users which of course some have the same firstname and lastname, which is what is causing my problem. I know I need to add number at the end of the name to make this unique, but do not know how to accomplish this to not only add a number at the end but also check if the name is in use and if it is increment it up to 2, 3 and so on. Below is a screenshot of my csv and below that my script. Any help would be greatly appreciate it.

null

# Import active directory module for running AD cmdlets
Import-Module activedirectory

#Store the data from ADUsers.csv in the $ADUsers variable
$ADUsers = Import-csv C:\Temp\duplicates.csv

#Loop through each row containing user details in the CSV file
foreach ($User in $ADUsers)
{
#Read user data from each field in each row and assign the data to a variable as below

$Username = $User.SamAccountName
$Password = $User.pin
$Firstname = $User.firstname
$Lastname = $User.lastname
$DisplayName= "$LastName $FirstName"
#$Name = $FirstName.Substring(0,1)+$LastName
#$Description= $user.$Description
$OU_Adult = "OU=S_Adult,OU=Enrolled Students,DC=testlab,DC=local".Path #This field refers to the OU the user account is to be created in
$OU_Student = "OU=N_Student,OU=Enrolled Students,DC=testlab,DC=local"
#$email = $User.email
#$streetaddress = $User.streetaddress
#$city = $User.city
#$zipcode = $User.zipcode
#$state = "FL"
#$country = $User.country
#$telephone = $User.telephone
#$jobtitle = "Staff"
#$company = $User.company
#$department = $User.company

#Check if SamAccountName Starts with S or N
if ($Username -like 'S*')
{
$OU = "OU=S_Adult,OU=Enrolled Students,DC=testlab,DC=local"
}
if ($Username -like 'N*')
{
$OU = "OU=N_Student,OU=Enrolled Students,DC=testlab,DC=local"
}

#Check to see if the user already exists in AD
if (Get-ADUser -F {SamAccountName -eq $Username})
{
#If user does exist, give a warning
Write-Log -Message "A user account with username $Username already exist in Active Directory." -Level info -Path $Path
}
else
{
#User does not exist then proceed to create the new user account

#Account will be created in the OU provided by the $OU variable read from the CSV file
$NewUserParams = @{
#'SamAccountName' = $Username
'UserPrincipalName' = "$Username@testlab.local"
'Name' = $Name
'GivenName' = "$Firstname"
'Surname' = "$Lastname"
'Enabled' = $True
'DisplayName' = "$DisplayName"
#'Description' = $Description
'Path' = $OU
#-Company $company
#'State' = $state
#'Title' = $jobtitle
#'Department' = $department
'AccountPassword' = (convertto-securestring $Password -AsPlainText -Force)
}
New-AdUser @NewUserParams
}
}
2

This is a function I wrote years ago for IAM solutions. It’s a bit messy as the name format varies everywhere, so the SamAccountNameFormat changed from first initial and lastname, last 5 of the lastname and first initial and every other variation, so I would edit for what the requirements were:

function Get-UniqueSamAccountName { [CmdletBinding()] param( [string]$FirstName, [string]$LastName, [int]$MaxSamLength, [int]$MaxSamNames = 25 ) begin{ $PSBoundParameters.GetEnumerator() | foreach{ Write-Verbose ("{2}: {0}: {1}" -f $_.Key, $_.Value, $MyInvocation.MyCommand) } #$SamAccountNameFormat = "{0}{1}" -f $LastName.SubString(0,[System.Math]::Min($MaxSamLength, $LastName.Length)), $FirstName.SubString(0,2) $SamAccountNameFormat = "{0}{1}" -f $FirstName.SubString(0,1), $LastName Write-Verbose ("{1}: SamAccountName Format: {0}" -f $SamAccountNameFormat, $MyInvocation.MyCommand) Write-Verbose ("{1}: Compiling object with {0} SamAccountNames for comparison" -f ($MaxSamNames + 1), $MyInvocation.MyCommand) $possibleNames = @() $possibleNames += New-Object -TypeName PSObject -Property @{SamAccountName=$SamAccountNameFormat} $possibleNames += for($i=1;$i -le $MaxSamNames;$i++){$name = "{0}{1:D2}" -f $SamAccountNameFormat, $i; New-Object -TypeName PSObject -Property @{SamAccountName=$name}} #Create a search filter for Get-ADUser with a * wildcard $searchFilter = "{0}*" -f $SamAccountNameFormat } process { #Get any user that matches the searchFilter Write-Verbose ("{1}: Searching Active Directory for user(s): {0}" -f $searchFilter,$MyInvocation.MyCommand) #$currentlyInAD = Get-ADUser -Filter {SamAccountName -Like $searchFilter} -Properties SamAccountName | Select SamAccountName 
    if ($currentlyInAD) {
        Write-Verbose ("Found {0} matches in AD, comparing with possible names list for available SamAccountName" -f @($currentlyInAD).Count)
        # Compare the list possibilities with the returned AD accounts for the SamAccountName format
        $availableSAM = Compare-Object -ReferenceObject $currentlyInAD -DifferenceObject $possibleNames -Property SamAccountName -PassThru | Where{$_.SideIndicator -eq "=>"} | Select -ExpandProperty SamAccountName -First 1
    }
    else {
        $availableSAM = $possibleNames | Select -ExpandProperty SamAccountName -First 1
        Write-Verbose ("{1}: No matches found in AD, returning first possible SamAccountName: {0}" -f $availableSAM, $MyInvocation.MyCommand)
    }
}
end {
    $availableSAM.ToLower()
}

}

$params = @{
FirstName = ‘John’
LastName = ‘Smith’
MaxSamLength = 10
MaxSamNames = 25
}

$env:ADPS_LoadDefaultDrive = 0
Import-Module ActiveDirectory

Get-UniqueSamAccountName @params -Verbose

Currently, the SamAccountNameFormat is first initial last name:

$SamAccountNameFormat = "{0}{1}" -f $FirstName.SubString(0,1), $LastName

Basically, it generates possible names, so jsmith, jsmith01, jsmith02 up to the defined $MaxSamNames. If it’s a small Ad structure and there is is only like one set of users going up to 05, 25 is fine. If it’s a large AD and there are like jsmith62, you may want to use like 100 or 150 possible names. The next piece searches AD for jsmith* and then does a Compare-Object on possible names vs what’s in AD. If there is a jsmith, jsmith01 and jsmith03, it will return jsmith02. If you created jsmith02 and reran, it would return jsmith04 and so on. Use the -Verbose to see what its doing.

VERBOSE: Get-UniqueSamAccountName: MaxSamNames: 25 VERBOSE: Get-UniqueSamAccountName: FirstName: John VERBOSE: Get-UniqueSamAccountName: MaxSamLength: 10 VERBOSE: Get-UniqueSamAccountName: LastName: Smith VERBOSE: Get-UniqueSamAccountName: Verbose: True VERBOSE: Get-UniqueSamAccountName: SamAccountName Format: JSmith VERBOSE: Get-UniqueSamAccountName: Compiling object with 26 SamAccountNames for comparison VERBOSE: Get-UniqueSamAccountName: Searching Active Directory for user(s): JSmith* VERBOSE: Get-UniqueSamAccountName: No matches found in AD, returning first possible SamAccountName: JSmith jsmith

If there is a requirement to truncate the name like a max length, then there is a remarked example. This is a bit more complex because if a max length of 4 is used then Smith would be jsmit, but if you have Xi Wu then you get substring issues trying to truncate to 4 where there are only 3 characters (e.g. xwu), hence the math min. This doesn’t change anything, it’s just returning the next available name, so you can test with verbose until it works like you want.

3

It looks like the SAMACCOUNTNAME is based on the employee number, which is unique. If that information is not considered private in your company, cant you use the number portion as the unique identifier?

4

Thank you for the reply! This is a nice script. I am not sure how I would implement this into mine. Our SamAccountNames are unique and will not change. How would I go about using this just for the names themselves and not the SamAccountName? Would this even be possible?

5

Thank you Tony for the reply. Those numbers are made up but is definitely the format for our SamAccountName. I apologize for this but could you elaborate on your question? Can I use the number as the unique identifier?

 

 

6

Perhaps I misunderstood. I also did not take the time to read the code thoroughly. Based on your comment “I know I need to add number at the end of the name to make this unique”, if you had 3 John Doe’s in the company, you were looking to change the names to “John Doe 1”, “John Doe 2”, “John Doe 3” and keeping track of a counter.

My thought was to use the SAMACCOUNTNAME field as the unique identifier whereby you dont need to keep track of a counter, and that field (or portion of using SubString) in the name would also help in identifying the user for other reasons. My thought would be “John Doe N123456”, “John Doe N234567”, etc. …

Sorry for my misunderstanding and not reading through the code. Rob is the master, I would use/leverage what he has provided.

7

No problem at all Tony. I appreciate the time you took to reply. I am not powershell guru by any means I barely know enough to get by for what I need it for but learn more and more each day. Unfortunately, although I appreciate Robs reply as well i am not quite sure how to implement his function within the script. The SamAccountName cannot be changed. Hope you all have a great day! Stay safe out there.

8

Rob Simmers I have edited your script to change my displayname instead of the SamAccountName. It works just fine checking for users that are already in AD, but what about duplicates within the CSV that is being imported? Like my example above my csv has duplicate first and last names. How would I go about executing this function to check the csv?

 

Rich

9

Richard,

There are several ways you can accomplish it, typically you generate the unique values in the loop and ensure you reference a domain controller. Here is the basic pseudo code:

function Get-UniqueSamAccountName { param ( $Server ... ) ... $currentlyInAD = Get-ADUser -Filter {SamAccountName -Like $searchFilter} -Properties SamAccountName -Server $Server

}

$domainController = ‘NA-DC-01’

foreach ( $user in $adUsers ) {

$name = Get-UniqueSamAccountName -Server $domainController

New-ADUser -Server $domainController -Name $name

}

This is using AD as the database as long as you ensure all GET\SET operations are done on the same DC to avoid replication issues when provisioning multiple accounts.