Powershell - Exporting MemberOf to csv file from active directory

Question

i'm using this powershell to export displayname and MemberOf data to a csv

Get-ADUser -Filter * -Properties * -SearchBase "OU=xxx,OU=xxx,OU=xxx,DC=xx,DC=xx" | select displayname, MemberOf | Export-Csv -Path C:\Script\Export.csv

I believe the buffer is limiting the MemberOf field in fact if the user is member of multiple groups in AD it terminates with ...

i.e.

MemberOf
--------
{CN=MICKEY MOUSE,OU=LOONEY TUNES,OU=TOONS,DC=XX,DC=XX, CN=DAFFY D...

is there any way to put another filter on top of memberof to filter out just characters in between "CN=" and "," to read only MICKEY MOUSE and DAFFY DUCK ?

Thank you very much

For example

Get-ADUser -Filter * -Properties * -SearchBase "OU=LOONEY TUNES,OU=TOONS,DC=xx,DC=xx" | select displayname, MemberOf | Export-Csv -Path C:\Script\Export.csv

Should list me all users and their membership in a specific OU, it's working but it's badly formatted because i need only CN= data and not OU= and DC=

i.e.

displayname           MemberOf
-----------           --------
PORKY PIG             {CN=MICKEY MOUSE,OU=LOONEY TUNES,OU=TOONS,DC=XX,DC=XX, CN=DAFFY D...

Answer 1

As Santiago already commented, the MemberOf property of an AD user is an array of DistinguishedNames.
I gather you want a CSV file where the groups are listen with their Name, rather then their DistinguishedName.

The next code will output a csv file where for each group a user is member of a separate line is created

Get-ADUser -Filter * -Properties DisplayName, MemberOf -SearchBase "OU=xxx,OU=xxx,OU=xxx,DC=xx,DC=xx" | 
ForEach-Object {
    foreach ($groupDN in $_.MemberOf) {
        [PsCustomObject]@{
            # you can add more interesting properties her if you want
            User     = $_.DisplayName
            MemberOf = (Get-ADGroup -Identity $groupDN).Name
        }
    }
} | Export-Csv -Path 'C:\Script\Export.csv' -NoTypeInformation

If you rather have one line per user and have the groups listed separated by some delimiter character, use this instead

Get-ADUser -Filter * -Properties DisplayName, MemberOf -SearchBase "OU=xxx,OU=xxx,OU=xxx,DC=xx,DC=xx" | 
ForEach-Object {
    $groupNames = foreach ($groupDN in $_.MemberOf) {
        (Get-ADGroup -Identity $groupDN).Name
    }
    [PsCustomObject]@{
        User     = $_.DisplayName
        MemberOf = $groupNames -join '; '  # change the separator to whatever character you want
    }
} | Export-Csv -Path 'C:\Script\Export.csv' -NoTypeInformation

^{P.S. Don't use -Properties * on Get-ADUser if all you are after are just two extra properties which are not already in the default set}

Answer 2

Pls use this cmdlet:

Get-ADPrincipalGroupMembership

You can format the output with format-table including the properties you require, and to a csv file.

Ref: https://learn.microsoft.com/en-us/powershell/module/activedirectory/get-adprincipalgroupmembership?view=windowsserver2022-ps

Gets the Active Directory groups that have a specified user, computer, group, or service account.

Get-ADPrincipalGroupMembership -Identity Administrator

distinguishedName : CN=Domain Users,CN=Users,DC=Fabrikam,DC=com GroupCategory : Security GroupScope : Global name : Domain Users objectClass : group objectGUID : 86c0f0d5-8b4d-4f35-a867-85a006b92902 SamAccountName : Domain Users SID : S-1-5-21-41432690-3719764436-1984117282-513 etc...