PowerShell Script - List Certain ADGroups as Column Headings & their Users as Row Headings showing who is in which groups

PowerShell Help
1

PowerShell Script - List Certain ADGroups as Column Headings & their Users as Row Headings showing who is in which groups

How do I do this in PowerShell please?

I can only find a way to list Users in groups or list groups for a user, not list both in this way.

List AdUsers (in certain groups) as row headings and a subset of AdGroups as Column headings (eg: Group1, Group3 & Group10 only) with the column values having 1’s or Y’s to show which of those groups the users are in.

eg:

Group1 members are:
UserA
UserC

Group3 members are:
UserA
UserB

Group10 members are:
UserB
UserC

What I'm expecting:
User       Group1    Group3    Group10
UserA      1         1
UserB                1         1
UserC      1                   1

I have this so far, but want to add the matrix shown above as a summary worksheet in the Excel file.

# The ActiveDirectory (AD) App generally only lists Staff Ids which is painful when trying to find out each staff members name to determine who needs to be removed from what groups.
# This script will:
# * Export certain properties of AD Group Members (eg: StaffIds, Names, Depts, etc) for each SSRS AD Group that <Dept> uses for <Dept> RAP Report Security.
# * Exports the data to an Excel Spreadsheet with 1 worksheet per AD Group, listing it's members.
#
# PRE-REQUISITES: 
# * Export-Excel module must be installed
# * ActiveDirectory module must be installed
# ** Remote Server Administration Tools (RSAT) must be installed
# --------------------------------------------------------------------------

# Shows what Properties are available that can be added to lines 30-35 below
#Get-ADUser 12345678 -Properties *

Clear-Host

# Set the filename for the output Excel file
$dateTimeStr = (get-date).tostring('yyyyMMdd_HHmmss')
[String]$ExcelPath  = "C:\Temp\DeptSsrsAdGroups_$dateTimeStr.xlsx"

# Set the list of AD Groups to export results for
[String[]]$AdGroups = ("Group1", "Group3", "Group10")

# Loop through each AD Group in the list
# Export certain properties/fields for each user/member in the AD Group to a worksheet with the same name as the AD Group
# Each excel worksheet will have a table with the AD Group name, auto sized and the top row frozen for formatting/layout purposes.
ForEach($AdGroup in $AdGroups)
{
     Get-AdGroupMember -Identity $AdGroup -Recursive | `
     Get-ADUser -Properties SamAccountName,Surname,GivenName,DisplayName,EmailAddress,Enabled,Title,Department,Office,Company,Manager | `
     #Select SamAccountName,Surname,GivenName,DisplayName,EmailAddress,Enabled,Title,Department,Office,Company,Manager | `
     Select SamAccountName,Surname,GivenName,DisplayName,EmailAddress,Enabled,@{n="ManagerName";e={(Get-AdUser $_.manager -Properties DisplayName).DisplayName}},Title,Department,Office,Company | `
     Sort-Object Surname,GivenName | `
     Export-Excel -Path $ExcelPath -AutoSize -WorksheetName $AdGroup -TableName $AdGroup -FreezeTopRow
} 

# --------------------------------------------------------------------------
2

When you crosspost the same question at the same time to different forums you should at least post links to the other forums along with your question to avoid people willing to help you making their work twice or more.

Thanks

And you may add much less comments. Since PowerShell is most of the time pretty self explanatory it is not necessray to comment each command. It actually bothers more than it helps.

3

I add comments to all my code whether you think it is self explanatory or not, someone may be looking at my code that has very little PowerShell knowledge.

It is good coding practice to ALWAYS comment code well, so that’s what I try to do.

Thanks for your opinion though. :slight_smile:

Apologies, I wasn’t aware we must do that when cross posting.

4

It’s not a must. It is a kind of polite and best practice. Just like not to overcomment code. :wink:

If you like you can read more about here:

1 Like
8

I added a ‘Summary’ worksheet which has 2 columns: User & Group
Then added a ‘PivotData’ worksheet which pivots the ‘Summary’ worksheet data.

These are the new/extra sections of code …

Add to beginning of Foreach loop:

    # Summary Excel Worksheet - Users & their matching Groups - data source for an Excel pivot table
    $Users = Get-AdGroupMember -Identity $AdGroup -Recursive | `
             Get-ADUser -Properties DisplayName | `
             ForEach { "{0}  ({1})" -f $_.DisplayName, $_.SamAccountName } 
    ForEach( $User in $Users ) {
        $UserGroupInfo = [PSCustomObject]@{
                            User  = $User
                            Group = $AdGroup
                        }
        $UserGroupInfo | Export-Excel -Path $ExcelPath -AutoSize -WorksheetName Summary -TableName Summary -FreezeTopRow -Append

    # AdGroup Excel Worksheets

Add to end:

# Add a Pivot Table to the Excel file (if it exists) based on the Summary worksheet
If (Test-Path $ExcelPath) 
{
	Try {

		# Open the Excel file
		$excel = Open-ExcelPackage -Path $ExcelPath

        # Create new worksheet at the start
        Add-Worksheet -ExcelPackage $excel -Activate -MoveToStart -WorksheetName PivotData | Out-Null

		# Add a pivot table to the new Excel worksheet
		Add-PivotTable -ExcelPackage $excel -SourceWorkSheet "Summary" -PivotTableName "PivotData" -PivotTableStyle Medium15 -PivotRows User -PivotColumns Group -PivotData Group #-Activate

		# Save & close the Excel file
		Close-ExcelPackage $excel
	}
	Catch {
		# An error occurred
		$message = $_
		write-output "ERROR - $message"
		write-warning "ERROR updating existing Excel file $ExcelPath. ABORTING. $message"
	}

    # Open the excel file
    $excelObj = New-Object -ComObject Excel.Application
    $excelObj.DisplayAlerts = $false;

    # Change pivot table to tabular format
    $excelWb = $excelObj.Workbooks.Open($ExcelPath)
    #$excelObj.Visible = $true
    $excelObj.Visible = $false
    $excelWsh = $excelWb.Sheets("PivotData")
    $pivot = $excelWsh.PivotTables(1)
    $pivot.RowAxisLayout(1)   # Tabular layout
    $pivot.EnableFieldList = $false

    # Freeze panes at B3 in PivotData Worksheet - not working at present
    [void]$excelObj.Cells.Item(3,2).Select()
    $excelObj.ActiveWindow.FreezePanes = $True

    # Save and close
    $excelObj.DisplayAlerts = $false;
    $excelObj.ActiveWorkbook.SaveAs($ExcelPath, 51)   # xlsx
    $excelObj.Workbooks.Close()
    $excelObj.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObj) | Out-Null

}

write-output "Finished exporting data to $ExcelPath"

# --------------------------------------------------------------------------
9

When your post is moderated/held, please give a reasonable amount of time for one of us to approve it before posting it again. Thanks!

10

It would help if the website actually told me that was the case.
I had no idea that my posts were actually auto-hidden & sent to a moderator.
Perhaps a website enhancement is required to notify the user that the post was successful but auto-hidden & sent to the moderator to approve.

11

It’s not the best website, I agree. However, rapid fire posts wouldn’t be suggested if the site was having issues either. Thanks for sharing your solution.