Powershell to read a list Usernames to show the AD group membership of each user and to output this to CSV file

Question

I have a command that will display the AD group membership of a user:

Get-ADPrincipalGroupMembership username | select name

But I have to type each username for every user.

I was wondering if someone could assist me in script where I would provide CSV with list of usernames and to read each user from the file and to output the AD groups the user is part of to CSV file.

UPDATE 1

Guys I have come up with (I know its not the best):

$users = Get-Content -path 'C:\temp\disabledadusersnameaudit.txt'
foreach($user in $users){
write-host "Group Membership for: " $user
Get-ADPrincipalGroupMembership -Identity $user | Select name | ft -hidetableheaders
}

But getting this error message:

Get-ADPrincipalGroupMembership : An unspecified error has occurred
At line:4 char:1
+ Get-ADPrincipalGroupMembership -Identity $user | Select name | ft -hidetablehead ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (SSmall:ADPrincipal) [Get-ADPrincipalGroupMembership], ADException
    + FullyQualifiedErrorId : ActiveDirectoryServer:0,Microsoft.ActiveDirectory.Management.Commands.GetADPrincipalGroupMembership

UPDATE 2

Managed to find script from a previous answer:

How to get multiple users membership groups from AD using Powershell script?

$userlist = Get-Content 'C:\temp\disabledadusersnameaudit.txt'

Get-ADUser -Filter '*' -Properties memberof | Where-Object {
  $userlist -contains $_.SamAccountName
} | ForEach-Object {
  $username = $_
  $groups = $_ | Select-Object -Expand memberof |
            ForEach-Object { (Get-ADGroup $_).Name }
  "{0}: {1}" -f $username, ($groups -join ', ')
} | Out-File 'c:\temp\Audit.csv'

This works but 2 issues here the audit CSV looks like this:

All the AD groups are in one column would need to separate them via the , I guess as delimiter?

The other issue is I would like to state the username it would unable to find in the audit too.

UPDATE 3

@Santiago I ran you script and got this via the PS screen:

WARNING: Cannot find an object with identity: 'Username1' under: 'DC=my,DC=domain,DC=net'.
WARNING: The search filter cannot be recognized
WARNING: Cannot find an object with identity: 'Username2' under: 'DC=my,DC=domain,DC=net'.
WARNING: Cannot find an object with identity: 'Username3' under: 'DC=my,DC=domain,DC=net'.
WARNING: The search filter cannot be recognized

Identifies the users it can't find but not working for the user it can find state "WARNING: The search filter cannot be recognized"

UPDATE 4

Making progress now.

User                                                               Membership                                                        
----                                                               ----------                                                        
Username1                                                                                                                             
WARNING: Cannot find an object with identity: 'Username2' under: 'DC=my,DC=domain,DC=net'.
WARNING: Cannot find an object with identity: 'Username3' under: 'DC=my,DC=domain,DC=net'.
WARNING: Cannot find an object with identity: 'Username4' under: 'DC=my,DC=domain,DC=net'.
Username5

The usernames it can find but is not showing the AD groups? Showing as blank

UPDATE 5

Getting there:

User                                                               Membership                                                        
----                                                               ----------                                                        
Username1                                                           AD GroupName1, AD GroupName2, AD GroupName3, AD GroupName4, AD Group...
WARNING: Cannot find an object with identity: 'Username2' under: 'DC=my,DC=domain,DC=net'.
WARNING: Cannot find an object with identity: 'Username3' under: 'DC=my,DC=domain,DC=net'.
WARNING: Cannot find an object with identity: 'Username4' under: 'DC=my,DC=domain,DC=net'.
Username5                                                           AD GroupName1, AD GroupName2, AD GroupName3, AD GroupName4

When a user has a lot of AD group it doesn't show all of the group this is on the ... (3 full stop/periods) I guess this is due to the amount of characters it can output - 2 questions of improvement.

First is it possible when outputting to CSV will it still show as ... or will it have the full AD groups the user is part off?

Second is when the user is not found instead of:

WARNING: Cannot find an object with identity: 'Username2' under: 'DC=my,DC=domain,DC=net'.

Could it echo '$User is not in the domain!' as this would much better.

Thanks again for the help Santiago!

Do you want each group membership on a separate line/row, or only one row per user, with all the group names crammed into a single column? — Mathias R. Jessen, Aug 01 '23 at 10:53
Hi Mathias I was think of exporting the data like this, so displaying user of each user then below this showing the AD group names (the users is part of) on each line then the next username and the AD group names (the users is part of) if that makes sense. — S.Mahmood, Aug 01 '23 at 11:31
But then your data is disconnected - if someone sorts the rows in the CSV the relationship between user and group is lost. — Mathias R. Jessen, Aug 01 '23 at 11:40
Ok is it possible to have a column with username and column with AD group name? — S.Mahmood, Aug 01 '23 at 11:45
Get users with following : https://learn.microsoft.com/en-us/powershell/module/activedirectory/get-adgroupmember?view=windowsserver2022-ps#example-2-get-all-group-members-of-all-domain-local-groups Then pipe results to : | Foreach { Get-ADPrincipalGroupMembership $_.name} — jdweng, Aug 01 '23 at 12:22
,what part of the process you're having difficulties? im sure you know how to `Import-Csv` and how to loop over each Csv row then for each row you query the principal membership and construct an object out of it — Santiago Squarzon, Aug 01 '23 at 12:22
@SantiagoSquarzon I believe it is Import-CSV -Path C:\Temp\DisADUserAud.csv to import and to export is | Export-Csv C:\Temp\AuditDisADUsers.csv -NoTypeInformation What I am stuck is reading each username and displaying the usernames in one column and the ad group name in the other so if a user is part of 20 AD group the username will display 20 times in one column and in the AD group column will have 20 different AD group names if that makes sense — S.Mahmood, Aug 01 '23 at 12:53
that error shown in your update does it happen for all users? — Santiago Squarzon, Aug 01 '23 at 12:55
Yes apart from the username it is unable to find (I need also address that where if it is unable to find a username I would need to state "$User does not exist") as I am going to being audited Prod and Test Environments. — S.Mahmood, Aug 01 '23 at 13:01

Santiago Squarzon · Accepted Answer · 2023-08-01T16:45:59.093

1

I don't have an explanation for the error you have shown in your question but also Get-ADPrincipalGroupMembership is known to be buggy. I would recommend querying the user first to get their DistinguishedName and from there you can query all groups having this user as a member:

Get-Content -Path 'C:\temp\disabledadusersnameaudit.txt' | ForEach-Object {
    try {
        $user = Get-ADUser $_
        $membership = Get-ADGroup -LDAPFilter "(member=$($user.DistinguishedName))"

        [pscustomobject]@{
            User       = $user.samAccountName
            Membership = $membership.samAccountName -join ', '
        }
    }
    catch [Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException] {
        [pscustomobject]@{
            User       = "'$($_.TargetObject)' could not be found in Domain."
            Membership = $null
        }
    }
    catch {
        Write-Warning $_
    }
} | Export-Csv .....path.csv -NoTypeInformation

edited Aug 01 '23 at 16:45

answered Aug 01 '23 at 13:11

Santiago Squarzon

41,465
5
14
37

Please see update 3 on my question. – S.Mahmood Aug 01 '23 at 13:19
@S.Mahmood sorry! had a typo, should've been `"(memberof=$($user.DistinguishedName))"` try again – Santiago Squarzon Aug 01 '23 at 13:20
1

making progress please see update 4 above. – S.Mahmood Aug 01 '23 at 13:37
@S.Mahmood this is why one needs to drink coffee in the morning! lol sorry, it was `member=` instead of `memberOf=` – Santiago Squarzon Aug 01 '23 at 13:39
I am just happy getting help to be honest. Script works! But I have 2 requests for improvement please could you see update 5. – S.Mahmood Aug 01 '23 at 15:26
1

@S.Mahmood see update. the 3 dots should not appear in the csv – Santiago Squarzon Aug 01 '23 at 16:05
Works but one issue the users who itCAN'T find only output to the Powershell screen and the users it CAN find are only on the CSV. Is it possible to have users it CAN'T find and CAN find both in the CSV export? – S.Mahmood Aug 01 '23 at 16:42
1

@S.Mahmood thats my last update, i will not be updating this answer any further – Santiago Squarzon Aug 01 '23 at 16:46
No worries thank you for the assistance sir. Much appreciated! – S.Mahmood Aug 01 '23 at 16:48

Powershell to read a list Usernames to show the AD group membership of each user and to output this to CSV file

1 Answers1