The final report will please you, your security department, and your auditors
| Downloads |
|---|
| 103358.zip |
Let me start by saying that this isn't your typical group-listing script. This admin script produces a very thorough single-domain listing of all your Active Directory (AD) groups in a nicely formatted, easy-to-read layout. The report it produces provides you with an excellent point-in-time group history document and supplies a great deal of information to you, your security department, and your auditors.
Here's a list of the script's major features:
- enumerates all domain groups within a single domain
- lists and enumerates all nested groups
- presents information in one Excel document with each group on its own worksheet
- lists users whose primary group is the group being enumerated (these members don't normally show up in group listings unless you take care to account for this situation as this script does; for example, if you as a domain admin set your primary group to Domain Admins and do a simple group listing of that group, you won't see yourself as a member in that listing)
- colors nested group names in red and recurring groups in purple
- avoids possible endless loops should one group contain another group that contains the first group
- sorts and indents by group
- provides a domain group summary list with hyperlinks to all groups that have members
- provides a no members summary list if there are any groups without members, including hyperlinks to the groups
- colors worksheet tabs in red for easy identification of groups with no members
- uses blue text for disabled user accounts within groups
- provides total group counts for both groups with and without members
Script Overview
The script cycles through a collection of groups within a single AD domain and writes each group's members and nested group members into its own specific Excel worksheet. (You can download the script by clicking the Download the Code Here button.) In the end, all the individual domain group worksheets make up the overall domain group listing spreadsheet. In addition to the individual group worksheets, a summary worksheet is created and, if any groups have no members, an additional worksheet is created listing all the groups that have no members.
During the process of evaluating individual groups, disabled accounts are highlighted in blue; any nested groups are indented and highlighted in red and members of those nested groups are also indented and listed under their specific parent group.
If a specific nested group happens to occur more than once in any specific listing, it's not enumerated again but rather highlighted in purple; you'll find that the fully enumerated group already exists elsewhere within that group listing. A summary worksheet is created at the end of the process, providing group total information and easy-to-use hyperlinks to all your groups for quick and easy analysis.
If your domain has any groups that do not contain members, a separate groups with no members worksheet is created, providing the names and hyperlinks to those groups that have no members. The resulting spreadsheet with its color coding and nested group indentation scheme provides a very thorough and easy-to-comprehend picture of your domain's group infrastructure.
How It Works
Aside from setting up some variables, constants, and an instance of Excel, the first process the script undertakes is to create a dictionary that contains a list of all disabled accounts. This is done by querying AD. The dictionary is used at a couple of key points within the script while enumerating group member users to determine if the user account is disabled or not. You'll see this later on in the script. If an account is disabled, that userid is formatted in the Excel output with a blue font, making it easy to spot within the group listings.
Next, a collection of all domain groups is gathered using the simple AD query shown in Listing 1. Stepping though the group collection constitutes the main loop of the script, where all groups are ultimately enumerated. Within this main loop a call is made to the GetGroupMembers subroutine, which is really the workhorse of this whole process. It's within the GetGroupMembers subroutine that each group's membership is individually evaluated and written to the Excel report.
Inside the Subroutine
Once a specific group object is passed to the GetGroupMembers subroutine the process of enumerating the members of that group begins.
The first section of code shown in Listing 2 performs an often-overlooked process: checking for user accounts that have a Primary Group assignment that belongs to the group being evaluated. As strange as it may sound, user accounts whose Primary Group is the group being evaluated will not be part of the collection returned with an AD group query. To account for this condition, you must approach it from another angle, which is to look for accounts whose PrimaryGroupToken is that of the Group you're evaluating.
The chunk of code in Listing 2 ensures that you capture these members. You'll see that I also trigger a flag within this section of code to indicate that this group "Has Members" whenever a user or group exists within the group being evaluated. By triggering I mean I change the value of the HasMember variable from False to True. If this flag isn't triggered while evaluating the group, I can capture the group name to a dictionary called NoMembersList, which is used near the end of the program to list all the groups with no members. If the flag is triggered (set to True), the group is stored to a dictionary called YesMembersList, which is used to list all the groups that do have members. You'll also notice that within this section of code I check to see if the user item is found within the DisabledAccounts dictionary and highlight the output in blue if the userid exists within the dictionary.
After checking for the Primary Group the script starts to enumerate the group's members, as seen in the section of code in Listing 3. The process here is mainly concerned with whether the member is a regular account or whether it's a group member. If it's a regular account, the member goes through the same process it did when checking the Primary Group members: The group member is checked against the disabled accounts dictionary and colored if it is a disabled account and the HasMembers flag is set to True.
If the member is another group, the group is evaluated against the group dictionary. If the group doesn't exist, it's added to the group dictionary and a recursive call is made to the GetGroupMembers subroutine to gather the members of that particular nested group.
If the nested group exists in the group dictionary, the recursion process is not undertaken; the group name is simply written to the worksheet, colored purple, and the script moves on to the next group member. Keeping track of groups in the group dictionary in this fashion prevents the possibility of the code going into an endless loop if a group happens to contain another group that contains it as a member.
Without going into a complicated explanation involving the indenting process let me summarize what I do within the code to make sure the indentation stays accurate and the group members and nested groups are indented and sorted properly.
Typically, formatting wouldn't be a problem for groups that just had users as members; you could easily sort on column A. But it's a different story when nested groups are involved. If a nested group is encountered when enumerating a group, I write the nested group under its parent group and then increase the column number by one so that the nested group's members appear indented by one column to the right. I also keep track of the group names whenever I enter a recursive call that sends the script off to enumerate a nested group. So when the recursive call is finished and the current group name is no longer the same as the one stored, I decrease the indent by subtracting one from the current column number, thus putting me back into the correct column of the previous group.
Even though the indentations might seem to be sufficient for having all the group information sorted properly, it is not sufficient for groups that contain other groups. Under normal circumstances, when a group with users and nested groups is enumerated you'll see that your first item in the collection might be a user, the second might be a group, and the third might be another user, and so on. Therefore your users would not necessarily all fall under each other but would rather be separated by groups in between them. Technically, the listing would still be correct; however, visually this could make analyzing the data a bit confusing.
The piece to this process that helps keep things sorted properly involves writing the current overall nested group hierarchy level of each group member to the 256th column (which has column heading IV). To help understand this, a picture is truly worth a thousand words. So let's take a look at Figure 1 and Figure 2. Here you'll see an example of a fairly complex nested group listing. In Figure 1 you'll see that the group being processed is named RTC Local User Administrators. It contains one user—Mark Hassall—and two nested groups—RTCDomainUserAdmins and VPNUsers (note the red font, indicating they are groups). These three members fall directly under RTC Local User Administrators in column A. You'll see that the RTCDomainUserAdmins group has only one member—Michael Holms—and that it is indented to indicate that it belongs to RTCDomainUserAdmins group. The VPNUsers group contains one user—Craig Playstead—and two nested groups—Domain Admins and RTCDomainUserAdmins—and they are indented one level to indicate that they belong to the VPNUsers group. The Domain Admins group members are indented as well to show they belong to the Domain Admins group. And finally, you'll see that the VPNUsers member RTCDomainUserAdmins is colored with a purple font and also has a notation indicating it is a recurring group and, therefore, you will not see indented members listed under it. You can, however, find the RTCDomainUserAdmins group in the listing and ascertain who the members of that group are.
Sorting It Out
To ensure that I all my group listings would be sorted properly, I needed a single column to sort on that was structured in a fashion that would guarantee everything fell into its proper place.
As you can see in Figure 2, by simply keeping track of the complete path of each group member's hierarchical structure and writing that to column 256 I could sort the worksheet on column IV and everything would fall right into place. The asterisks are used as a delimiter to mark the change in the hierarchy.
Wrapping It Up
The process of evaluating my groups continues until every group in the domain is evaluated. The process then exits the main loop and the script begins to wrap things up by providing group summary information and hyperlink listings to all groups with and without members. Accessing any particular group is simply a matter of clicking on the group name hyperlink. Note that if all your groups do contain members there will not be any references to "No Members."
I think that you'll find that having this documented within Excel has its advantages. Everything is contained within one file and all the groups are contained within their own individual worksheet. And if you need to know which groups a specific user belongs to you can easily use the "Find All" feature in Excel and locate every occurrence of that user. You could also use the "Find All" feature to locate everywhere a disable account appears or where you had recurring nested groups, which could ultimately help you find inconsistencies that might exist within some of your group structures.
This script is a very useful admin utility that will provide invaluable reports as well as a permanent record of your group structures for any given point in time. It will also save you a lot of time and serve you and your auditors well if you are asked to provide detailed group information during the hectic audit season.
