How to export a detailed list of Active Directory users' "Member of" tab?

Question

I am currently in the process of restructuring the Active Directory user list of the company I'm working for, and the person who did it did an awful job, and is of course, not working here anymore.

My question is the following: I want to have an Excel spreadsheet (ideally) containing all the information contained in the "Member of" tab of a user chart.

I have tried creating a query, but the result only gives me a list of users that are a "member of" something, not the actual content of the "member of" tab.

Is there a way to do this, either through command prompt or directly from Active Directory?

Fair warning: I know nothing about VBS and Powershell.

score 7 · Answer 1 · edited Mar 19 '19 at 16:17

7

If you want to get a user's group memberships, run this PowerShell command:

Get-ADPrincipalGroupMembership $Username | Select Name | out-file "filepath" where you want the document saved, including the name you want the document"

Where $Username is the name of the user you're querying.

edited Mar 19 '19 at 16:17

Drifter104

3,693
2
22
39

answered May 22 '14 at 21:49

Josh

417
4
13

IMO this is the correct tool for the job. – blaughw Nov 05 '15 at 16:46

score 4 · Answer 2 · edited Mar 19 '19 at 16:10

4

I have this, you will need to learn a bit of PowerShell to have it dump to a CSV, right now it just dumps to a text file.

$users = Get-ADUser -Filter * -Properties * -SearchBase "OU=something,DC=domain,DC=net"
foreach ($user in $users) {
    $file = $user.Name + '_ACL'        
    (Get-ADUser –Identity $user –Properties MemberOf).MemberOf -replace '^CN=([^,]+),OU=.+$','$1' | Out-File c:\PSResults\$file.txt
    }

I never took the time to get it working for CSV output as this did what I needed.

-- If you want it to export to a csv, just change the out-file path to the path where you want it to be saved, plus the name of the document.csv, for example, out-file C:\PSResults\$file.csv would export to a CSV named $file

edited Mar 19 '19 at 16:10

Community

1

answered May 22 '14 at 21:49

mortenya

321
1
8

Just saw that Josh R beat me to it. His is simpler as well. – mortenya May 22 '14 at 21:52
1

However, yours will work on lower versions of PowerShell. – Josh May 22 '14 at 21:53
True, I have had to keep that in mind when I share scripts in my group, as I'm the only one with PowerShell experience, and as such the only one who ever upgraded past 2.0 – mortenya May 22 '14 at 21:55
Now that I think about it, I believe `Get-ADPrincipalGroupMembership` is in 2.0. Not sure what I was thinking. – Josh May 22 '14 at 21:59
Thanks both of you for the reply. I'll try Josh R's one tomorrow while at work, since it actually looks a lot simpler. Unless I'm misunderstanding, I'll have to generate this query for each and every user on my domain ? Or if I were to use Mortenya's one, it would generate a complete list the first time ? Just want to make sure I understand correctly! :) – May 23 '14 at 02:24

score 1 · Answer 3 · edited Nov 05 '15 at 17:16

1

You could create and define a new Query applied to the OU that contains your users in "AD Users and Computers" entering this query string:

(&(&(&(&(objectCategory=user)(userAccountControl=512)))))

then export the results to a csv using the "Export List" at the top of the AD window.

edited Nov 05 '15 at 17:16

kasperd

29,894
16
72
122

answered Nov 05 '15 at 15:42

user320631

11
1

score 0 · Answer 4 · edited Jun 15 '16 at 11:36

To get the file into a CSV, just replace "Out-File c:\PSResults\$file.txt" in your code with "Export-CSV -path c:\PSResults\$file.csv -NoTypeInformation"

So it would look something like this:

$users = Get-ADUser -Filter * -Properties * -SearchBase "OU=something,DC=domain,DC=net"
foreach ($user in $users) {
$file = $user.Name + '_ACL'        
(Get-ADUser –Identity $user –Properties MemberOf).MemberOf -replace '^CN=([^,]+),OU=.+$','$1' | Export-CSV -path c:\PSResults\$file.csv -NoTypeInformation
}

However as others have already posted, the below is the best to use as it's an easy one liner:

Get-ADPrincipalGroupMembership USERNAME | Select Name | Export-CSV -path C:\Temp\file.csv -NoTypeInformation

this will not work in case if there is nested group guys .. like one user is a member of 20 group it won't display — user420934, Jun 18 '17 at 23:53

How to export a detailed list of Active Directory users' "Member of" tab?

4 Answers4