CODEWITHSUNDEEP
powershellcsvactive-directory
ineedhelp
ineedhelp

Reputation: 35

Creating mapping from CSV to get AD Users

I am new to PowerShell. I am trying to create a mapping from my CSV that will list users from AD Groups into a hashtable. My csv looks something like this:

ClientCode,GroupCode
1234,ABC
1234,DEF
1235,ABC

GroupCode is code for an Active-Directory Group.

Example:

GroupAcronym 'ABC' maps to an AD group called 'Group_One'

GroupCode 'DEF' maps to an AD group called 'Group_Two'

GroupCode 'GHI' maps to an AD group called 'Group_Three'

Each of these AD Groups have users. Example:

Users in AD 'Group_One': John Doe, Jessica Simmons

Users in AD 'Group_Two': Carter McCarthy, Jeremy L

Users in AD 'Group_Three': Alyson Carter, Eric Cross, Alejandra Fischer

Essentially I want to create some kind of mapping or a way to link the 'GroupCode' within my CSV to AD-Groups and then list its users in a hashtable.

Desired output would be:

ClientCode GroupUsers      
---------- ---------      
1234       John Doe, Jessica Simmons, Carter McCarthy, Jeremy L        
1235       John Doe, Jessica Simmons
1236       Alyson Carter, Eric Cross, Alejandra Fischer

Upvotes: 1

Views: 164

Answers (2)

Theo
Theo

Reputation: 61028

You can create a lookup Hashtable with the mappings for code to group name and use that to retrieve all user names like below:

# create a mapping hash for the group codes to the real group names
$groupsMap = @{
    'ABC' = 'Group_One'
    'DEF' = 'Group_Two'
    'GHI' = 'Group_Three'
    #etc.
}

$csv = Import-Csv -Path 'ClientGroupCodes.csv'

# loop though the csv items, group by property ClientCode
$result = $csv | Group-Object ClientCode | ForEach-Object {
    # get an array of the various group codes inside each client group
    $groupCodes = $_.Group.GroupCode | Select-Object -Unique

    # loop through these codes, find the real names from the hash 
    # and collect the user names in variable $groupUsers
    $groupUsers = New Object 'System.Collections.Generic.List[string]'
    foreach ($code in $groupCodes) {
        # test if the group code can be found in the hash
        if ($groupsMap.ContainsKey($code)) {
            $users = [string[]](Get-ADGroupMember -Identity $groupsMap[$code] | Where-Object { $_.objectClass -eq 'user' }).Name
            $groupUsers.AddRange($users)
        }
        else {
            Write-Warning "No mapping found for group code $code"
        }
    }

    # output an object with the properties combined
    [PsCustomObject]@{
        'ClientCode' = $_.Name
        'GroupUsers' = ($groupUsers.ToArray() | Sort-Object -Unique) -join ', '
    }
}

# output on screen
$result

# output to CSV file
$result | Export-Csv -Path 'GroupUsersPerClient.csv' -NoTypeInformation

Upvotes: 1

A.Codes
A.Codes

Reputation: 197

To help get you started you could add the GroupCode to one the notes field in the AD Group object. Then with 

get-adgroup Group1 -Properties info

you'd be able to access that data.

I saw that you or someone posted the same question on here, someone seems to have suggested: https://community.spiceworks.com/topic/2253543-creating-mapping-from-csv-to-get-ad-users?page=1#entry-8732830

Upvotes: 1

Related Questions