Export the AD Group Membership of a list of users from a CSV Powershell

Question

I am trying to get a CSV output of all the users in the 'VPN Users' group that are also in the 'Domain Users' group. I'd like it to give their name and then their group membership. Each member should only be in the VPN group. I am looking to identify who is in both groups.

To achieve this I have first exported a list of the users in the VPN Group to a CSV which works fine. Second part of the code is meant to go through the list of users in the CSV from that AD group and export an output as a CSV containing the users and their group membership which isn't working for me. For each user in the CSV it returns the error: Get-ADUser : Cannot find an object with identity: '"User001_vpn"'.....

I'm not sure if the way I'm going about it is the best way to achieve the task in hand or if any of you might be able to help me make it work? It seems like Get-ADUser isn't finding the users from the CSV.

#Retrieves list of users that are in the VPN Users group and exports them into a CSV
(Get-ADGroupMember "VPN Users" -Recursive | Get-ADUser -Properties * |
Select-Object SamAccountName |
ConvertTo-Csv -NoTypeInformation) |
Select-Object -Skip 1 |
Set-Content -Path "C:\Documents\VPN Users Report\VPNUsers.csv”


Get-Content “C:\Documents\VPN Users Report\VPNUsers.csv” | Get-ADUser | ForEach{
    $user = $_
    $groups = Get-ADPrincipalGroupMembership $user
    $groups | %{ New-Object PSObject -Property @{ User = $user.SamAccountName; Group = $_.SamAccountName } }
} | Export-Csv "C:\VPN Users Report\Results\Output.csv"

I see you're using `-Recursive`, those would be indirect members of the group, meaning the cmdlet could bring members of a nested group. Are you sure this is what you want? — Santiago Squarzon, Aug 10 '21 at 14:03
You could read the csv, group the objects based on the user, then query for the group conditions -> `Import-Csv output.csv | Group User | Where { $_.Group.group -eq 'domain users' -and $_.Group.group -eq 'vpn users' } | Select -Expand Name` — AdminOfThings, Aug 10 '21 at 14:04

Theo · Accepted Answer · 2021-08-11T14:16:36.583

I believe the problem is with the quotes, since you are using ConvertTo-Csv while retrieving the users, all user SamAccountNames will be quoted.

Afterwards you read this csv file using Get-Content, not Import-Csv, so the quotes will not be removed and you have user names like "User001_vpn" instead of User001_vpn

You do not have to use this 'in-between' cvs at all and on second thought, you can leave out Get-ADUser too unless you want other properties from these users than what Get-ADGroupMember already returns (i.e. distinguishedName, name, objectClass, objectGUID, SamAccountName, SID)

Try

# Retrieves list of users that are in the VPN Users group
(Get-ADGroupMember "VPN Users" -Recursive | Where-Object { $_.objectClass -eq 'user' }).SamAccountName | ForEach-Object {
    foreach ($group in (Get-ADPrincipalGroupMembership -Identity $_)) {
        [PsCustomObject]@{
            User  = $_
            Group = $group.SamAccountName
        }
    }
} | Export-Csv "C:\VPN Users Report\Results\Output.csv" -NoTypeInformation

^{I have added Where-Object { $_.objectClass -eq 'user' }, because the Get-ADGroupMember cmdlet can return users, groups, and/or computer objects}

As per your latest comment:

If you want more properties from the user, you need Get-ADUser, which by default returns objects with these properties:
DistinguishedName, Enabled, GivenName, Name, ObjectClass, ObjectGUID, SamAccountName, SID, Surname, UserPrincipalName

If you still need more, like for instance the users email address, you need to ask for it by adding -Properties EmailAddress

# demo to include the users first and last name
Get-ADGroupMember "VPN Users" -Recursive | Where-Object { $_.objectClass -eq 'user' } | ForEach-Object {
    $user = Get-ADUser -Identity $_.DistinguishedName
    foreach ($group in (Get-ADPrincipalGroupMembership -Identity $user.DistinguishedName)) {
        [PsCustomObject]@{
            User      = $user.SamAccountName
            FirstName = $user.GivenName
            LastName  = $user.Surname
            Group     = $group.SamAccountName
        }
    }
} | Export-Csv "C:\VPN Users Report\Results\Output.csv" -NoTypeInformation

I’m trying to work out how to include the users first and last name in the output? — Mark Corrigan, Aug 11 '21 at 13:42
@MarkCorrigan I have added code including Get-ADUser so more properties can be output in the CSV. — Theo, Aug 11 '21 at 14:17

score 0 · Answer 2 · answered Aug 10 '21 at 14:29

I propose an alternative to Theo's helpful answer letting LDAP handle the filtering of users.

The result of this query would be those users which are members of GroupX and members of GroupY:

$groupX = (Get-ADGroup 'VPN Users').DistinguishedName
$groupY = (Get-ADGroup 'Domain Users').DistinguishedName

Get-ADUser -LDAPFilter "(&(memberOf=$groupX)(memberOf=$groupY))"

If you were looking for indirect members of one of the groups or both groups, meaning, possible members of nested groups in those groups you could use memberOf:1.2.840.113556.1.4.1941:. Example:

Get-ADUser -LDAPFilter "(&(memberOf:1.2.840.113556.1.4.1941:=$groupX)(memberOf=$groupY))"

Would bring all members (recursive) of GroupX which are also direct members of GroupY.

Export the AD Group Membership of a list of users from a CSV Powershell

2 Answers2