none
Searching for Duplicate User (duplicate Surname and Firstname) RRS feed

  • Question

  • Hi all,

    I am looking for a way to find users with a duplicate SamAccountName/Name/Name/UPN... basically if a user has 2 or more accounts (excluding admin accounts). The way I am checking it now is by creating a CSV file with users filtered on duplicated Surnames (and excluding some OU's which contain Removed Accounts and Administration Accounts).

    So that part is fixed, but now I have to manually check the Surnames to see if it has a duplicate Firstname (GivenName). Could you help me find a way to put this in Powershell as well?

    I was thinking of a solution like: If Surname and Givenname -eq 1; then show results…

    This is my code so far:

    $users = Get-ADUser -Filter {(SN -like "a*")} -Properties SN
    $filteredOU = $users | Where-Object -FilterScript { ($_.distinguishedname -notlike '*LSA*') -and ($_.distinguishedname -notlike '*Removed*') -and ($_.distinguishedname -notlike '*Disabled*') }
    $idLookup = $filteredOU | ForEach-Object { $idLookup = @{} } { if($_.Surname) { $idLookup[$_.Surname] += 1 } } { $idLookup }
    $filteredUsers = $filteredOU | Where-Object { if($_.Surname) { $idLookup[$_.Surname] -gt 1 } }
    $filteredUsers | Select-Object -Property SN, Givenname, Name, DistinguishedName | Sort-object -property SN |
    Export-Csv -Path H:\Test\Duplicate_ADusers4.csv -NoTypeInformation

    Feel free to correct me and make the code much better, as i am in an early stage of learning Powershell.

    Thanks in advance.

    Thursday, April 18, 2019 12:06 PM

Answers

  • First, restricting the output to all but three OU's is problematic. You cannot use the -SearchBase parameter for this, and you cannot filter on part of the distinguished names of users. Instead you must pipe the collection of all the users in all OU's to a Where clause that eliminates users in the 3 OU's. We would also need to assume that you don't want users in any child OU's of the 3 designated OU's. Something similar to below would be required:

    $OU1 = "ou=East,ou=USA,dc=MyDomain,dc=com"
    $OU2 = "ou=West,ou=USA,dc=MyDomain,dc=com"
    $OU3 = "ou=South,ou=USA,dc=MyDomain,dc=com"
    
    $Users = Get-ADUser -Filter 'GivenName -Like "*" -And Surname -Like "*"' | Where {$_.distinguishedName -NotLike "*$OU1" -And $_.distinguishedName -NotLike "*$OU2" -And $_.distinguishedName -NotLike "*$OU3"} | Select GivenName, Surname, sAMAccountName
    

    Next, if you ran the code I suggested, you can see that the output is a bit strange for a CSV file. Each line would have a different number of fields, which is unusual. But the output could be formated comma limited and exported to a CSV file. The final ForEach loop could be revised as follows to make the values comma delimited and export:

    $results =@()
    ForEach ($Key In $Names.Keys)
    {
        If ($Names[$Key] -Like "*;*")
        {
            $Line = "$Key," + $Names[$Key].Replace(";", ",")
            $Results += New-Object PsObject -Property @{
                Duplicates = $Line
            }
        }
    }
    
    $Results | Export-Csv C:\rlm\PowerShell\Test169a.csv -NoTypeInformation
    

    Finally, I am not sure what you mean when you say you changed sAMAccountName to Name. The sAMAccountName is unique in the domain, even if the first and last names are the same. But Name only needs to be unique in the parent OU or container. If the first and last names are same, there is a good chance that the Names will also match, as long as the users are in different OU's. Still, that should not prevent output to a CSV file.



    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    • Marked as answer by Mikkelback Wednesday, April 24, 2019 6:41 AM
    Friday, April 19, 2019 4:06 PM

All replies

  • Use Group=Object on the Surname where the count is greater than 1.

    Also use Group on both Surname and GivenName where count is greater than 1.

    help group-object -online


    \_(ツ)_/

    Thursday, April 18, 2019 12:48 PM
    Moderator
  • I often search for duplicates in AD, but I use hash tables to recognize the duplicates. I will see if I have a similar example.

    Edit: Found this:

    # Hash table of GivenName/Surname combinations.
    $Names = @{}
    
    # Retrieve all users with GivenName and Surname.
    $Users = Get-ADUser -Filter 'GivenName -Like "*" -And Surname -Like "*"' | Select GivenName, Surname, sAMAccountName
    
    # Find all users with each unique GivenName/Surname combination.
    ForEach ($User In $Users)
    {
        $Name = $User.GivenName + "/" + $User.Surname
        $NTName = $User.sAMAccountName
        If ($Names.ContainsKey($Name))
        {
            # Duplicate. Add the sAMAccountName to the value, delimited by a semicolon.
            # Semicolons are not allowed in sAMAccountName.
            $Names[$Name] = $Names[$Name] + ";" + $NTName
        }
        Else {$Names.Add($Name, $NTName)}
    }
    
    # Output entries in the hash table where the value includes the ";" character, indicating duplicate accounts.
    ForEach ($Key In $Names.Keys)
    {
        If ($Names[$Key] -Like "*;*") {$Key + " : " +$Names[$Key]}
    }
    


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)


    Thursday, April 18, 2019 3:00 PM
  • This is really helpful, but i'm not getting there yet.

    I have changed SamAccountName to Name (it is easier due to our AD/User configuration). But somehow the Export-CSV does not work.

    And is it possible to filter out 2 or 3 OU's?

    Friday, April 19, 2019 9:06 AM
  • Use Group=Object on the Surname where the count is greater than 1.

    Also use Group on both Surname and GivenName where count is greater than 1.

    -----------------------

    Where should i put it?

    • Edited by Mikkelback Friday, April 19, 2019 12:04 PM
    Friday, April 19, 2019 9:14 AM
  • First, restricting the output to all but three OU's is problematic. You cannot use the -SearchBase parameter for this, and you cannot filter on part of the distinguished names of users. Instead you must pipe the collection of all the users in all OU's to a Where clause that eliminates users in the 3 OU's. We would also need to assume that you don't want users in any child OU's of the 3 designated OU's. Something similar to below would be required:

    $OU1 = "ou=East,ou=USA,dc=MyDomain,dc=com"
    $OU2 = "ou=West,ou=USA,dc=MyDomain,dc=com"
    $OU3 = "ou=South,ou=USA,dc=MyDomain,dc=com"
    
    $Users = Get-ADUser -Filter 'GivenName -Like "*" -And Surname -Like "*"' | Where {$_.distinguishedName -NotLike "*$OU1" -And $_.distinguishedName -NotLike "*$OU2" -And $_.distinguishedName -NotLike "*$OU3"} | Select GivenName, Surname, sAMAccountName
    

    Next, if you ran the code I suggested, you can see that the output is a bit strange for a CSV file. Each line would have a different number of fields, which is unusual. But the output could be formated comma limited and exported to a CSV file. The final ForEach loop could be revised as follows to make the values comma delimited and export:

    $results =@()
    ForEach ($Key In $Names.Keys)
    {
        If ($Names[$Key] -Like "*;*")
        {
            $Line = "$Key," + $Names[$Key].Replace(";", ",")
            $Results += New-Object PsObject -Property @{
                Duplicates = $Line
            }
        }
    }
    
    $Results | Export-Csv C:\rlm\PowerShell\Test169a.csv -NoTypeInformation
    

    Finally, I am not sure what you mean when you say you changed sAMAccountName to Name. The sAMAccountName is unique in the domain, even if the first and last names are the same. But Name only needs to be unique in the parent OU or container. If the first and last names are same, there is a good chance that the Names will also match, as long as the users are in different OU's. Still, that should not prevent output to a CSV file.



    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    • Marked as answer by Mikkelback Wednesday, April 24, 2019 6:41 AM
    Friday, April 19, 2019 4:06 PM
  • Thanks for all your information. The solution works but in my environment this is not workable. We have a crazy AD environment with User OU's in more than 15 parent OU's… I will try to make one long string of code, or else i will use Excel to filter out.

    But thanks anyway, this information would definitely help me someday!

    Wednesday, April 24, 2019 6:44 AM