Duplicate names in csv

Welcome Forums General PowerShell Q&A Duplicate names in csv

Viewing 8 reply threads
  • Author
    Posts
    • #247549
      Participant
      Topics: 8
      Replies: 12
      Points: 168
      Rank: Participant

      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

    • #247555
      Participant
      Topics: 15
      Replies: 1765
      Points: 3,178
      Helping Hand
      Rank: Community Hero

      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:

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

      Currently, the SamAccountNameFormat is first initial last name:

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

      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.

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

      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.

    • #247599
      Participant
      Topics: 7
      Replies: 122
      Points: 593
      Helping Hand
      Rank: Major Contributor

      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?

    • #247631
      Participant
      Topics: 8
      Replies: 12
      Points: 168
      Rank: Participant

      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?

    • #247633
      Participant
      Topics: 8
      Replies: 12
      Points: 168
      Rank: Participant

      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?

       

       

    • #247637
      Participant
      Topics: 7
      Replies: 122
      Points: 593
      Helping Hand
      Rank: Major Contributor

      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.

    • #247765
      Participant
      Topics: 8
      Replies: 12
      Points: 168
      Rank: Participant

      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.

    • #248689
      Participant
      Topics: 8
      Replies: 12
      Points: 168
      Rank: Participant

      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

    • #249086
      Participant
      Topics: 15
      Replies: 1765
      Points: 3,178
      Helping Hand
      Rank: Community Hero

      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:

      <br />function Get-UniqueSamAccountName {<br />    param (<br />        $Server<br />        ...<br />    )<br />    ...<br />    $currentlyInAD = Get-ADUser -Filter {SamAccountName -Like $searchFilter} -Properties SamAccountName -Server $Server</p><p>}</p><p>$domainController = 'NA-DC-01'</p><p>foreach ( $user in $adUsers ) {</p><p>    $name = Get-UniqueSamAccountName -Server $domainController</p><p>    New-ADUser -Server $domainController -Name $name<br />}<br />

      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.

Viewing 8 reply threads
  • You must be logged in to reply to this topic.