Looking at the Code
Since most of the code is relatively straightforward, I will concentrate on the areas of main importance rather than doing a detailed section-by-section code analysis. There’s admittedly a good bit of coding that takes place prior to the code excerpt in Listing 1, but nothing that can’t be readily understood by reading through the code.
However, at callout A in Listing 1, you need to be mindful of any modifications that you make if you want to add or remove categories. This code uses the Dim statement to declare the Categories array, which contains 11 elements. The code then assigns values to each element. If you add or remove any elements, you must adjust the Dim statement to the appropriate number. These elements are going to be your first set of category names that get written to the database along with the accompanying AD objects’ DN.
The code in callout B declares the LDAPFilter array, which stores the LDAP query statements for the categories defined in the Categories array, and obviously the query statements and the categories must coincide with each other. Let’s take a look at one of the LDAP queries stored in element 0 of the LDAPFilter array. Keep in mind this query will be associated with the value stored in element 0 (AdminGroups) of the Categories array. In the LDAP statement, you can see that the query will be looking for an AD objectCategory attribute value equal to Group and AD objects that have a sAMAccountName attribute value that contains the string Admin. If you examine each element in the LDAPFilter array, you can see how it was designed to coincide with the Categories array. It's very important that they coincide because the associated category is written to the database for each collection object, as you'll see shortly.
In callout C, you’ll see I sort the disconnect recordset so that the database is sorted by the CatDN field in ascending order. Next, I start a For…Next statement that steps through each element in the LDAPFilter array and places the element's value into a string that I use to create a collection of AD objects for each category. I construct the LDAP query string in this statement:
strQuery = ";" _
& LDAPFilter(i) _
& ";DistinguishedName;subtree"
I then execute the query against AD with these statements:
objCommand.CommandText = strQuery
Set objRecordSet = objCommand.Execute
Afterward, I simply cycle through the returned recordset and write the collection object information to the ADO database with the lines of code in the Do…Loop statement in callout C. This cycle is repeated for each LDAPFilter element. A similar process takes place for the second set of categories, except that this set collects members of groups. Callout D shows a similar layout of categories and query arrays, and a similar looping process takes place for these arrays' elements. However, the process branches off and calls a subroutine that evaluates each group and writes all of the members and their associated categories to the database.
You need to check the DNs in the DistinguishedName Query Array (DNQA) for accuracy; it’s possible that you or your domain administrator might have moved some of these groups into another OU. It's not an uncommon practice to move Domain Admins, Enterprise Admins, and Schema Admins from the Users container into the Builtin container. If any of these Admins are incorrectly placed, a 15-second pop-up message lets you know which DNQA elements weren't found. If you do have to modify the DN, just change the portion within the double quotes. For example, if your Domain Admins were in the Builtin container and not the Users container, you’d change
DNQA(3) = "CN=Domain Admins,CN=Users," _
& DNC to
DNQA(3) = "CN=Domain Admins,CN=Builtin," _
& DNC
DNC should remain untouched. That's your Domains Default naming context and needs to be concatenated to the portion of the DN within the quotation marks. The subroutine GetGroupMembers in Listing 2 gets called for this group of categories. The code in callout A in Listing 2 first gets the group’s primaryGroupToken attribute value and uses an LDAP query to find accounts that have matching primaryGroupID attribute values. In most cases, this step isn't necessary when performing group membership listings, but using it eliminates the possibility of missing members with out-of-the ordinary primary groups defined. This is particularly important when looking at Domain Admin groups.
You’ll notice at callout B of Listing 2 that before writing any item in the returned collection, the sAMAccountName attribute value is checked to see if it exists in a dictionary. If it doesn’t exist, the object is written to the database and the value is added to the dictionary. You’ll also notice in callout B that the same type of process is undertaken as with the first set of categories when writing a record to the database. The category element (in this case MemberCats(j)) contains the name of the group currently being evaluated.
After checking the primary group, the process at callout C gets members of the group by again checking a dictionary for the existence of a group or member name first. If the group or member name exists in the dictionary, it’s bypassed and the next member is retrieved from the group member collection.
If the member isn’t in the dictionary, it’s added to the dictionary, then the member item is checked to see if it is a group. If it is, then the group item is written to the database and a recursive call is made to the GetGroupMembers subroutine. This gets members from nested groups. By checking the dictionary for existing group and member names, we can avoid endless loops should nested groups refer to each another. If the member is not a group, then the process simply writes the member data to the database and this process is repeated for each of the elements in the DNQA.
After all of the categories have been evaluated and written to the database, all that’s left to do is compare this newly collected data with the previous data. That process is the same as what I summarized earlier.
Examining the Results
I occasionally use a little trick to get an Excel report of changes that took place over the entire month. First I move the NewestAcctTracker.xml and PreviousAcctTracker.xml databases to a folder named SafeKeep, then I make a copy of the ArcAcctTrackerDateTime.xml file that I want to compare the current run with, rename that copy NewestAcctTracker.xml, and run my script. I then save my spreadsheet as Account changes for August.xls, for instance, then move the original NewestAcctTracker.xml and PreviousAcctTracker.xml files back, choosing “overwrite existing” files.
Here is an example of how your spreadsheets would look after AD changes were made. Let's say I started off with members in the group Administrators, which included Domain Admins and Enterprise Admins. Under Domain Admins Properties, Members, I had Administrator and Planning. Under Enterprise Admins Properties, Members, I had Administrator. Under Schema Admins Properties, Members, I had Administrator. Then let’s say I ran the script and added more members: Under Domain Admins Properties, Members, I added David Wall; under Enterprise Admins Properties, Members, I added Elizabeth Borg; and finally, under Schema Admins Properties, Members, I added Shannon Green. Figure 1 shows the resulting spreadsheet pivot table.
Now let’s say someone deleted the Domain Admins group from the Administrators group. When the script is run again the next day, the resulting pivot table would look like what you see in Figure 2. Note that not only does the pivot table show you the Domain Admins group wasn’t found, but it also shows you the members of that group that are no longer administrators. Formerly, they were members of the Administrators group by virtue of being members of the Domain Admins group. But since the Domain Admins group was removed, they are no longer members of the Administrators group and thus their status shows as Not Found. However, those users are still members of the Domain Admins group because the group itself underwent no changes.
Finally, the spreadsheet in Figure 3 and the pivot table in Figure 4 show what the report would look like if I added an Account Operator, a Backup Operator, a member to the Domain Admins group, a new Group, and a member to the Server Operators group; disabled an account; and deleted an account. The New section of the pivot table in Figure 4 shows what was added, but you might need to review the Not Found section a little closer to understand what’s happened.
Testing the Script
ADO is very useful in accessing and creating databases. I particular like the fact that I can create simple and easy-to-use .xml database files that are useful in keeping history-related data. I use these database files to keep track of all of my printers as well as of changes that take place. I also use them for keeping track of all domain account SIDs and reference those database files when checking Recycle Bins on the servers.
If you’re interested in testing this script, you can access the TechNet Virtual Lab “Microsoft Office PerformancePoint Server 2007 - Excel Dashboards,” and copy the code to the virtual server for use as a sandbox without having to make changes to AD. You can paste the code by clicking the Action button near the upper left of the screen. Just keep in mind that when you paste the code into a file on the virtual server you might need to check it for accuracy, as the paste routine sometimes chops things up a bit. I generally comment out the On error resume next statement and run the script until I get no errors.