Using WSH with ADSI to Create Excel Spreadsheets for Debugging

Editor's Note: This article is the fourth in a series that looks at how to automate reports in Windows 2000 (Win2K). The first three articles were

These articles are available online. For the Windows 2000 Magazine articles, go to http://www.win2000mag.com. For the Win32 Scripting Journal article, go to http://www.win32scripting.com.

A few weeks back, I was building a Windows Script Host (WSH) script that used Active Directory Service Interfaces (ADSI) to set numerous properties of an object in my Win2K AD store. While I was building this script, I was testing it at each step to make sure I wasn't generating bugs. The test consisted of running a second script that walks through the property cache and creates and displays a Notepad document listing an ADSI object's properties. This tester script is a modified version of a script that I wrote for the article "An ADSI Primer, Part 4: Manipulating the Property Cache" (April 1999). As I was continually repeating the process of writing code, running the tester script, and reviewing the Notepad document, I came to the conclusion that, although using Notepad was infinitely better than using a default message box, this process was still cumbersome. So I decided to find a better way to present the information.

Because I was working with tabular data, I had the option of displaying it in an Excel 2000 spreadsheet or in a table on a Web page or Microsoft Word document. I opted for the spreadsheet, and I now regularly use this newest version of the script, ADTester.wsh, for debugging. I've found that scanning through columns of data is much easier than scanning Notepad documents.

I'm going to walk you through writing this WSH script, which connects to a directory object, walks through the property cache to retrieve and examine the properties, then writes the desired properties to a spreadsheet. Although I'm going to show you how to work with an AD object, you can easily use this script for an object from any other Directory Service (DS).

ADTester.wsh applies the concepts and tools I discussed in the "An ADSI Primer" series (Win32 Scripting Journal, January 1999 through December 1999) and in the articles listed in the Editor's Note. You can find ADTester.wsh in the Code Library on the Win32 Scripting Journal Web site.

To create ADTester.wsh, you first need to write the code that automates Excel 2000. This setup code is almost identical to that in "Generating Deployment Reports," so I won't discuss how to create that code here. After you set up the spreadsheet, you need to write the core section of the script. The core code connects to an AD object, walks through the property cache, and collects, examines, and writes data to the spreadsheet. Before I show you how to write the core code, you need to understand the several schema concepts.

Understanding the Schema Concepts
In the article "An ADSI Primer, Part 12: Extending the Schema" (December 1999), I explained how the schema holds the set of blueprints for objects that you can create in the AD store. An AD object's blueprint contains all the attributes that you can possibly set for that object. This blueprint, or formal schema class definition, is itself an object called the classSchema object. So, for example, when you create a User object in the AD store, you're using the User object's formal schema class definition object—herein referred to as the User schema class object—as the blueprint.

In the article "An ADSI Primer, Part 4: Manipulating the Property Cache," I showed you how to use the IADsPropertyList object to retrieve a list of properties that have been set for an AD object. However, the IADsPropertyList object doesn't retrieve properties that don't have values. If you want to retrieve a list containing all of an AD object's properties whether they have values or not, you need to access the AD object's formal schema class definition by binding to the classSchema object for that AD object.

You can also access an AD object's formal schema property definition and formal schema syntax definition to find out a property's name and syntax (i.e., its data type), respectively. To access the formal schema property definition, you bind to the AD object's schema property object. To access the formal schema syntax definition, you bind to the AD object's schema syntax object. (For more information about formal schema property definition and formal schema syntax definition, see the discussions about the attributeSchema and attributeSyntax objects in "An ADSI Primer, Part 12: Extending the Schema.")

Connecting to an AD Object
To retrieve a list of all possible properties for an AD object, you must obtain the full ADsPath of the AD object's schema class object. To accomplish this, you first connect and bind to the AD object you're interested in, then you use the IADs::Schema property method to retrieve the ADsPath of the AD object's schema class object. (The AD object must already be written to the AD store with the SetInfo method. You can't use IADs::Schema on an object that is only in the property cache.)

For example, look at the code in callout A in Listing 1. In this code, you bind to the AD object that the ADS_OBJECT constant specifies and set the AD object to the adsObject variable. In this case, the constant represents the ADsPath of the Administrator object in the mycorp.com domain. You then use the IADs::Schema property method on the adsObject variable to retrieve the full ADsPath of the User schema class object. You use the retrieved ADsPath as the parameter to the GetObject method to bind the adsClass variable to the schema class object. Alternatively, you can write the ADsPath to the User schema class object as LDAP://schema/user, place that path in a constant, and use that constant as the parameter.

This approach isn't the only way to bind to the schema class object. The Web-exclusive sidebar "Another Way to Bind to Formal Schema Classes and Properties" illustrates another approach. You can find this sidebar on the Win32 Scripting Journal Web site.

Walking Through the Property Cache and Collecting the Data
After you've bound to the schema class object, you can use the methods and properties of the IADsClass, IADsProperty, and IADsSyntax interfaces. To access the names of a schema class object's mandatory and optional properties, you use the IADsClass::MandatoryProperties and IADsClass::OptionalProperties methods, respectively. As the code after callout A in Listing 1 shows, you first use a For Each...Next statement to iterate through all the names of the formally defined mandatory properties. You then use another For Each...Next statement to iterate through all the formally defined optional properties. Because the code in both loops is identical, you can put it into a subprocedure to simplify the script. This subprocedure, called EnumerateProperties, takes three parameters:

The EnumerateProperties Subprocedure
The EnumerateProperties subprocedure in Listing 2, page 8, examines the data and writes the desired data to the spreadsheet. You begin the EnumerateProperties subprocedure by declaring the subprocedure's name, arguments, and variables. Notice that the arguments in the suprocedure in Listing 2 differ from the arguments used in the main code in Listing 1. A good scripting practice to adopt when using suprocedures is to use different names for arguments in the subprocedure and in the main code and then use the keyword ByVal to specify that you want to pass in the arguments by value rather than by reference. If you pass by reference (the default when you don't use the keyword), any changes to the values in the subprocedure are instantly fed back to the original code that called the subprocedure (i.e., the parameters are read/write). If you pass by value, only the values are sent to the subprocedure (i.e., the parameters are read-only). Thus, using ByVal is a safer practice.

Next, you bind to the schema property object and set that object to the adsProperty variable. To specify this object's ADsPath, you concatenate "LDAP://Schema/" and the name of the property in the adsPropName variable. You then use a similar procedure to bind to the schema syntax object for that particular property and set that object to the adsSyntax variable.

Having bound to the schema property and syntax objects, you write two pieces of data to the spreadsheet. As callout A in Listing 2 shows, you write the property's name in column A and its syntax in column B. This code uses Excel object methods and properties to select and update cells. If you're unfamiliar with these methods and properties, see the Web-exclusive sidebar "Using Excel Objects to Manipulate a Spreadsheet" for more information. You can find this sidebar on the Win32 Scripting Journal Web site.

Next, you use the IADsProperty::MultiValued property method to determine whether the property is multivalued. If this property method yields True (i.e., the property contains multiple values), you use the code at callout B in Listing 2. If this property method yields False (i.e., the property contains one value), you use the code at callout C in Listing 2.

In the code at callout B in Listing 2, you first write that the property is multivalued in the spreadsheet. The property's values might be printable strings (e.g., text) or nonprintable strings (e.g., pictures). Because ADTester.wsh writes only printable strings to the spreadsheet, you must determine the property's syntax. You use the IADsSyntax::OleAutoDataType property method to test the property's syntax against the constants that this property method documentation defines. (For information about the constants, go to http://msdn.microsoft.com/isapi/msdnlib.idc?theurl=/library/psdk/adsi/if_schem_3uur.htm.)

If the syntax is a nonprintable string, you go to the next row in the spreadsheet and exit the subprocedure. If the syntax is a printable string, you retrieve the array of values and use the IsEmpty function to see whether the array is empty. If the array is empty, you don't write any information to the spreadsheet. If the array contains values, you write those values to the spreadsheet.

The code at callout C in Listing 2 is similar to that at callout B, except for two differences. First, you write that the property is single-valued rather than multivalued in the spreadsheet. Second, you use the Err object rather than the IsEmpty function to determine whether the property contains a value.

Potential Modifications
You can easily adapt ADTester.wsh to meet your needs. For example, I primarily use this script to debug the manipulation of one object at a time; therefore, I have no problems with hard-coding the object in my script. However, you can make this script into a self-contained utility by using the InputBox function to pass in an object's path. Another possible modification is adapting ADTester.wsh so that it opens an existing Excel spreadsheet and adds a new worksheet each time you run the script. You can use the worksheets to compare what objects look like before and after you run a modifying script. You can even adapt ADTester.wsh so that it records properties with other syntaxes.

I encourage you to experiment with ADTester.wsh until next month. At that time, I'll show you how to regularly gather data from multiple Win2K domain controllers so that you can generate reports about the authenticated users on individual servers and server farms.