Export AD Data into Access So Users Can Run Their Own AD Reports

If your organization is anything like mine, you get daily requests from managers for information about the users, computers, and groups in Active Directory (AD). These requests can be simple (e.g., "Can I get a list of all users who are in the New York office?") or detailed (e.g., "Can I get a list of all users with mailboxes on SERVER1 who haven't logged in since April?"). And managers often come back asking for slightly different information or for the information to be presented in a slightly different way. This leaves you—the busy administrator, who has servers to maintain and projects to complete—spending a lot of time tweaking and rerunning scripts.

One skill necessary to be a successful administrator is finding ways to provide people with the means to help themselves, while ensuring that they don't have the ability to break something. Given this goal, I developed a solution that gives managers an easy way to obtain custom AD reports without them having to directly access AD or learn how to write scripts. I export relevant AD data into a Microsoft Access database (although you can just as easily export the information into a SQL database). With this tool, managers can easily run a custom report and tweak it until it returns the data they need in a format they like.

To build this reporting tool, you first need to create a database in Access. If you're unfamiliar with Access, Microsoft offers a series of free Access 2003 courses at http:// office.microsoft.com/en-us/training/ CR061829401033.aspx. For our purposes, I created a sample database that has a single table named ADUsers with four fields: DisplayName, UserID, EmailAddress, and UserDisabled. Each field is a default text field with the exception of UserDisabled, which is a yes/no field. One note of caution: When you're creating your database, make sure the field lengths are large enough to store the information you're gathering. Some AD attribute values are quite long (e.g., dn) and won't fit into Access's default field length. You can download the sample database, ADUsers.mdb, from the Windows IT Pro Web site. (Go to http://www.windowsitpro.com, enter 96855 in the InstantDoc ID text box, then click the Download the Code Here hotlink.)

Now it's time to write the script. Listing 3 shows a sample script named PopulateDB.vbs, which you can also download from the Windows IT Pro Web site. After the script declares the variables and constants, it connects to and queries AD. As callout A in Listing 3 shows, an LDAP query is used. You need to modify this query to reflect your actual domain configuration. LDAP queries include three or four arguments, which are delimited with colons:

After executing the query and storing its results in the objRecordset variable, PopulateDB.vbs connects to Access and opens the ADUsers database. In the code at callout B, you need to customize the path to ADUsers. The script then deletes all the records in the database. The database is cleared each time so you don't have to have to search for existing records and determine whether those records have been updated.

In the code at callout C, the script loops through all the AD user objects in the objRecordset variable. After binding to each object using its ADsPath, the script retrieves the values of the displayName, sAMAccountName, mail, and userAccountControl attributes. For each attribute value, the script adds a new record to the database.

To run PopulateDB.vbs, open a command-shell window and run the command

cscript c:ADUsersPopulateDB.vbs 

The database can't be open when you launch the script. If it's open, the script will fail. I wrote and tested this script on Windows XP SP2 with Access 2003.

Once all the information is in the database, it's simple to sort and query the data because Access is designed for that purpose. If your managers aren't familiar with Access, you'll need to teach them the basics. Although training them will initially consume some of your time, the return on this investment is worth it because you'll no longer have to spend time every day writing and tweaking scripts for managers.

Listing 4 shows some sample queries for the ADUsers database. The first example generates a list of all disabled user accounts. The second example illustrates how to filter that list so only disabled user accounts that have an email address are selected.

As ADUsers and PopulateDB.vbs demonstrate, you can build a robust reporting tool by importing relevant AD information into a database that people can use to run their own reports. Because you'll no longer have to continually write and rewrite reporting scripts, you'll have more time left for important tasks, such as keeping your environment up to date and secure. In fact, you can even use this tool for some of those tasks. For example, if your users' home directory names match their user IDs, you can use the tool to determine which home directories are no longer in use. You just need to create an additional table named HomeDir, populate a field called HomeDir with a text list of all home directories on your servers, and run the third sample query in Listing 4. The query results will show you the records from HomeDir that don't have user IDs in ADUsers, which indicates those home directories aren't being used.
—Chris Scoggins