performance – Get specific properties for all Active Directory groups and all their users, then combine them into CSV


I’ve written a script that gets a list of all groups in AD along with a few specific properties for each group (DistinguishedName, CN, Type, and Description). Then it goes through each group and, for each member of type “user” (to exclude computers, servers, etc.), it gets the user’s full name, title, and their manager’s full name. Finally, it combines the two using a PS Module called Join-Object to join the two lists together and output it as a CSV file.

Getting the AD Groups at the beginning is quick enough; it takes less than a minute to run. Combining all the output at the end likewise is pretty quick; a minute or less and it’s done.

The slow part is the middle step of looping through each group. For ~1500 groups and a total of about ~75k to ~85k users across all groups (many users are in multiple AD groups, naturally), it takes 7 hours to run. Is there some way I can improve on that 7 hour runtime (preferably drastically)? Extrapolating from the run-time of the other sections, I would expect the middle section to take an hour or less.

I’m running PowerShell 5.1 on Win10 and querying Active Directory on Windows Server 2016. This script was requested by our security team so they could generally audit AD group membership information in a format familiar to them (a CSV file inside Excel).

Import-Module -Name ActiveDirectory -Force
Import-Module -Name Join-Object -Force

#Get list of AD Groups. Currently ~1500 rows
$ADGroupsList = Get-ADGroup -Filter * -Properties * | Select-Object DistinguishedName,CN,GroupCategory,Description | Sort-Object CN

#Declare ADUsersList object
$ADUsersList = @()

#Declare Record variable and set parameter schema. The (ordered) keyword ensures the data is entered in this order
$Record = (ordered) @{
    "Group Name" = ""
    "Employee Name" = ""
    "Title"= ""
    "Manager" = ""
}

#Loop through each group in the groups list. This is the really, really slow part. If the AD Web Service limit is ever set back to the default (5000), this will fail for any groups that have more than 5,000 users.
foreach ($Group in $ADGroupsList) {
    #Get list of members of the current group in the loop who are of type "user" (excludes servers or other computers, for example)
    $ArrayofMembers = Get-ADGroupMember -Identity $Group.DistinguishedName | Where { $_.objectClass -eq "user" }

    #Loop through each member in the list of members from above
    foreach ($Member in $ArrayofMembers) {
        #Get detailed user info about the current user like title and manager that aren't available from Get-ADGroupMember
        $User = Get-ADUser -Identity $Member -Properties name,title,manager | Select-Object Name, Title, @{Label="Manager";Expression={(Get-ADUser (Get-ADUser $Member -Properties Manager).Manager).Name}}
        
        #Specifies what values to apply to each property of the $Record object
        $Record."Group Name" = $Group.CN
        $Record."Employee Name" = $Member.Name
        $Record."Title" = $User.Title
        $Record."Manager" = $User.Manager

        #Put all the stored information above in a 'copy' record
        $objRecord = New-Object PSObject -property $Record

        #Append that copy to the existing data in the ADUsersList object
        $ADUsersList += $objRecord
    }
}

#Combines data from the two objects into one. This uses the Join-Object module from https://www.powershellgallery.com/packages/Join-Object/2.0.1
Join-Object -Left $ADGroupsList -Right $ADUsersList -LeftJoinProperty "CN" -RightJoinProperty "Group Name" -Type AllInBoth -LeftMultiMode DuplicateLines -RightMultiMode DuplicateLines -ExcludeLeftProperties DistinguishedName | Export-Csv C:CombinedADResults.csv -NoTypeInformation