| Downloads |
|---|
| 100428.zip |
Where I work, we have a relatively large domain and every day some type of Active Directory (AD) change takes place: new users are added, users are moved from one organizational unit (OU) to another, computers are added, removed, and disabled from the domain, admins leave the company and new ones join—you get the picture. Keeping track of all those changes would be virtually impossible for one person to do manually, but with the help of a script, it's an almost effortless task.
The script I wrote captures a snapshot of specific AD objects such as groups and members of groups and writes the distinguished name (DN) of each object along with a run date and category to an .xml file in the form of an ActiveX Data Objects (ADO) database. (If you’re not familiar with ADO, you might first check out “Rem: Obtaining Data from a SQL Server Database,” InstantDoc ID 25628, and “Introduction to ADO,” InstantDoc ID 98718.) Each subsequent run of the script compares the new database with the previous database. By using a simple compare process, you can detect new AD objects as well as objects that existed in the previous database but aren't present in the new database.
As you'll see, I structured this script to query specific groups, but you can add your own queries within the code fairly easily and start keeping tabs on the objects of your choice. The script does cover a wide range of AD objects and should provide you with useful and comprehensive reports.
The script helps you monitor general AD activity, and, more importantly, it's a valuable tool that you can use to spot new accounts or missing accounts that were added to or removed from security groups such as Enterprise Admins, Domain Admins, and Administrators. With this script you can also see new, moved, disabled, or deleted user and computer accounts, spot OU changes, and keep tabs on group membership changes that take place within groups such as Server Operators and Account Operators.
Querying Sets of AD Categories
The script's main thrust is based on querying two sets of AD categories. The first set pertains to groups and class queries that can ascertain AD objects with fairly generalized LDAP query statements:
• AdminGroups: any group name containing the string Admin
• ComputersDisabled: disabled computer accounts; ComputersEnabled: enabled computer accounts
• Groups: all groups
• GroupsNoMembers: groups that have no members
• OUs: all OUs
• Servers: all computer objects whose operatingSystem attribute value contains the string Server
• ServiceAccounts: any account whose description attribute value contains the string Service
• ServiceGroups: any group whose sAMAccountName attribute value contains the string Service
• UserAccountsDisabled: disabled user accounts
The second set centers on obtaining memberships of high-level security-related groups:
• Account Operators
• Administrators
• Backup Operators
• Domain Admins
• Enterprise Admins
• Replicator
• Schema Admins
• Server Operators
The second set also requires a bit more scripting logic than the first set—the script needs to evaluate group membership, which involves checking for nested groups, acquiring members of nested groups if nested groups exist, avoiding endless loop recursion should nested groups refer to each other, and checking for domain accounts whose primary group is set to a group being evaluated. As you are probably aware, if an account's primary group is set to a specific group, querying that specific group's membership won't return that account nor any other accounts whose primary group is set to that specific group.
How the Script Works
When the script is run, each object from both sets of category queries is written to an ADO disconnected recordset. Each record contains the script's run date, the object's DN, the category description, and a concatenation of the category and the DN. I’ll explain those areas, including the concatenated field, in the next section.
After the script's initial run, any AD changes in any of the defined object categories can be detected on a subsequent run simply by traversing the current run's database and checking it against the previous run's database to see if those objects were found. The script checks each record in the previous database against the new database to see if the previous objects still exist in the new database. If records from either database are not found in the other, those records get written to a Microsoft Excel spreadsheet. After all of the records have been written to the spreadsheet, an Excel pivot table worksheet is produced within the Excel workbook showing the AD changes by categories of new AD objects and by objects that weren't found, providing a clear snapshot of changes that took place between the dates of the newest run and the previous run.
How often you run this process should coincide with the amount of activity your domain undergoes. The more activity you have, the more frequently you should run this process. I run mine daily, but should activity slow down, I can choose to run it only once a week. Incidentally, I have coded the script so that it can easily be run as a scheduled task. I avoided using message boxes created with VBScript's MsgBox function and used pop-ups created with Windows Script Host's (WSH's) WshShell.Popup method instead. Message boxes shouldn't be used in scripts that run as scheduled tasks because they don't go away until a user clicks a button. Unlike message boxes, pop-ups appear for only a given number of seconds. The added benefit of pop-ups is that you see the messages even if you decide to run the script manually.
The databases created and used in this script contain four fields as I mentioned above: Rundate, which is simply the date that the script was run; Category, which is an item from one of the two sets of categories I described earlier (e.g., UserAccountsDisabled); DN, which is the DN of the AD object; and CatDN, which is a combination of the values in the Category and DN fields.
The reason behind concatenating the values in the two fields has to do with the way ADO functions when you use the Find method to find a record within the database. As much as I like ADO, it does have shortcomings: One is that you can't use the AND operator with the Find method—and my script depends on finding a category and a DN. An alternative to the Find method, the Filter method, allows the use of the AND operator; however, I found that using the Filter method with mid-to-large-sized databases (over 500 records) results in terrible performance hits on your computer. To counter that, I decided to take the disk-space hit over the performance hit and combine the two fields so I could use the speedy Find method.
I should also point out that you should carefully consider where you choose to house your databases. Depending on the size of your domain you could have databases that are a few megs in size for every run of the script. Currently each of my databases is roughly 3.5MB. You can, of course, zip and/or archive older databases if need be. The .xml files zip quite nicely; a 3.5MB file zips down to approximately 145KB. To change the default location of where your files are stored, locate the line DBPath = C:\Scripts\ADacctTrack\ in the script and change C:\Scripts\ADacctTrack\ to the appropriate path.
The first time this script is run, only the xml database is produced because there’s nothing to compare it with. Whenever the script is run, the database produced is saved as NewestAcctTracker.xml when the process completes. When the script is run the second time, the newest database is renamed PreviousAcctTracker.xml and the database created from the current run is named NewestAcctTracker.xml. On the third and all subsequent runs, the database named PreviousAcctTracker.xml gets renamed to ArcAcctTrackerDateTime.xml. (e.g., ArcAcctTracker09-26-20081305-45.xml). DateTime will always be the DateLastModified property value of PreviousAcctTracker.xml before it's renamed. I obtain this value by using the GetFile method of the Scripting.FileSystemObject object to access the PreviousAcctTracker.xml file properties. I store the value in a variable named DateTime, making sure I fill dates with leading zeroes (e.g., 07/07/2008), convert the time portion of the date to military time (e.g., 1307:54), and replace every slash (/) and colon (:) with a hyphen (-). This makes it very easy to find a specific database if you need to examine one by date. The files also sort by name more appropriately when you use this naming convention.
One last note about how the script works before we explore the code. When the script runs, it creates a new ADO disconnected recordset. After the script retrieves the data from the category queries and stores it in the ADO database, it then opens the previous database and steps through each of the new records, attempting to find that record within the previous database. If it can't find that data, then that record is considered new since it didn’t exist in the previous database and it's written to an Excel spreadsheet. Each record written to the spreadsheet includes
• A Status entry of New
• A Category entry that refers to the Category field of the current database record
• A DN entry that refers to the DN field of the current database record
• A Note entry of Not in Previous List.
After reaching the end of the file in the current database, the script steps through the previous database and attempts to find a matching record in the current database. If a matching record isn't found then that record is considered "not found" and a similar process occurs in which I write the data from the previous database to the spreadsheet. The Status entry in this case becomes Not Found and the Note entry becomes In Previous – Not in Most Recent List.
A Not Found entry could mean that the object in question could have been deleted, moved, renamed, or disabled. Whatever the case, the original DN and category of that entry no longer exist. It's certainly possible that the object in question will appear in one of the other categories as a “New” object, unless the object was deleted. You’ll see later on that I choose to sort the master worksheet by DN rather than Status or Category—it makes finding moved, disabled, and renamed objects much easier when evaluating the spreadsheet because the DN entries are grouped together.
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.