Create a table of AD users and groups

PowerShell Help
1

Hello,

I would like to create a .csv export that will have all AD users listed in the left column, all AD groups listed across the top row, and then show an X (or any other indicator) in each cell to map out which groups each user has. I have not worked with tables much, so I’m wondering if someone can point me in the right direction as to how to go about doing this?

So far, I have the Get-ADUser and Get-ADGroup commands supplying to a variable as follows:

$Groups = Get-ADGroup -Filter * -Properties Members | Sort-Object -Property Name
$Users = Get-ADUser -Filter * -Properties MemberOf | Sort-Object -Property Name

I’m guessing I would parse the $Groups variable through the $Users variable in a couple ForEach statements, and have it return value of “X” for each user and group match? I could probably accomplish this as a list for a single user, I’m just not sure how to do it for all users as a table.

Thanks in advance,

Andrew

2

So there are several ways to approach this. To clarify what you’re asking for everyone’s sake, it seems you want a CSV format with the first column being the user and the remaining columns being each group’s name. The user column will be filled with the users name/samaccountname/etc and each group column will be either blank or an X. The “table format” is really not a concern. If you export to CSV and open in excel, it will be a table. If you do Format-Table as shown, it’s likely all columns won’t fit in a single powershell window (depending on how many groups and your font/display settings, etc)

In this approach we get a list of all groups with members. We then loop through all users and for each user we check against all groups members. If they are a member, that group will populate the $ismember variable. Then we create an ordered hash table and loop through an alphabetically sorted list of groups and simply check if that groups distinguished name is in the $ismember variable. If that results to true an X will be the value, otherwise an empty string will be the value.

$allgroupsandmembers = Get-ADGroup -Filter * -Properties members 

$results = foreach($user in Get-ADUser -Filter *){
    $ismember = $allgroupsandmembers | Where-Object members -contains $user.distinguishedname
    
    $oht = [ordered]@{User = $user.SamAccountName}
    $allgroupsandmembers | Sort-Object -Property Name | ForEach-Object {
        $oht.Add($_.name,("","X")[$_.distinguishedname -in $ismember.distinguishedname])
    }
    [PSCustomObject]$oht
}

$results | Format-Table

Format-Table is strictly for users to view. It seems like you’d want to export this to CSV if you wanted to open in Excel or other programs. If so then change $results | Format-Table to $results | Export-Csv -Path c:\some\path\to.csv -NoTypeInformation or simply add the line if you want to both see it and export it.

Several people have issues understanding the (“”,“X”)[Some condition] portion so here’s a simple example you can run. It’s simply an array of values (“”,“X”) and you either get the first element [0] / [$false] or the second element [1] / [$true]

("False!","True!")[$true]

("False!","True!")[0]
3

Hi Doug,

Thanks for the info. This is working great so far. I decided that it would be useful to have each user’s department and title listed as well, so I tried adding that in line 8 as follows, but that is clearly not correct, as it threw a bunch of errors. Is there a different way I would need to add this info?

$oht.Add($.name,$.Department,$.Title,(“”,“X”)[$.distinguishedname -in $ismember.distinguishedname])

Thanks,

Andrew

4

Nevermind, I got that part figured out. Thanks again for the help.

Andrew

5

You are welcome, Andrew. For the sake of other readers you should share your solution. I assume it looked something like this.

$allgroupsandmembers = Get-ADGroup -Filter * -Properties members 
 
$results = foreach($user in Get-ADUser -Filter * -Properties Department,Title){
    $ismember = $allgroupsandmembers | Where-Object members -contains $user.distinguishedname
    
    $oht = [ordered]@{
        User       = $user.SamAccountName
        Department = $user.department
        Title      = $user.title
    }
    $allgroupsandmembers | Sort-Object -Property Name | ForEach-Object {
        $oht.Add($_.name,("","X")[$_.distinguishedname -in $ismember.distinguishedname])
    }
    [PSCustomObject]$oht
}
 
$results | Format-Table
6

Thanks again Doug, here is the final version that I am using, with a couple details anonymized:

$allgroupsandmembers = Get-ADGroup -Filter * -Properties members

$results = foreach($user in Get-ADUser -Filter 'Enabled -eq "True"' -Properties *) {
$ismember = $allgroupsandmembers | Where-Object members -contains $user.distinguishedname

$oht = [ordered]@{User = $user.DisplayName;Department = $User.Department;Title = $User.Title}
$allgroupsandmembers | Sort-Object -Property Name | ForEach-Object {
$oht.Add($_.Name,("","X")[$_.distinguishedname -in $ismember.distinguishedname])
}
[PSCustomObject]$oht
}

$results | Export-Csv -Path C:\Users\[USERNAME]\Documents\Scripts\Get-AllUsersGroups.csv -NoTypeInformation