MMbill MMbill - 1 year ago 86
PowerShell Question

Cross-matching username field from csv to samaccount name in Active Directory? + unique filter?

I was recently given a list of 60k usernames. I was then asked to find these usernames within active directory, list their display name, and keep them in separate CSV files based on their affiliate (extensionattribute7).

I've been fumbling around with this for a good 3 hours.. There's not many resources available around here to help me, and i'm getting desperate. Thank you for taking the time to help me out!

Answer Source

I haven't had a chance to test this but here is a quick script that will do what you need.

Essentially, the script enumerates your csv file of usernames and will build an array with all of the matching users within your csv. In addition using 'extensionattribute7', it will build a unique list of affiliates within the $aAffiliates array.

Next the script enumerates the unique affiliates and for each one will find all of the users who's affiliate property matches the current affiliate in the loop. These found users are then added to the $aResults array which are then exported to a csv file that contains the affiliate name in the file name.

Hope this helps!

Import-Module ActiveDirectory

$aUsers = @()
$aAffiliates = @()
$List = Get-Content "C:\Temp\List.txt"

ForEach ($Item in $List) {
    $Item = $Item.Trim()
    $User = Get-ADUser -Filter { SamAccountName -like $Item } -Properties DisplayName, extensionattribute7

    # Build unique list of affiliates
    If ($aAffiliates -notcontains $User.extensionattribute7) {
      $aAffiliates += $User.extensionattribute7

    $hItemDetails = New-Object -TypeName psobject -Property @{
        Username = $Item
        DisplayName = $User.DisplayName
        Affiliate = $User.extensionattribute7

    #Add data to array
    $aUsers += $hItemDetails

ForEach ($Affiliate in $aAffiliates) {
  $aResults = @()

  ForEach ($User in $aUsers) {
    If ($User.Affiliate -eq $Affiliate) {
      $aResults += $User    

  $Path = "C:\Temp\Results-$($Affiliate).csv"
  $aResults | Export-CSV $Path