Generating Deployment Reports

Many large corporations spend a lot of time writing scripts and programs to automatically generate reports on databases. Of these corporations, some write their programs in Microsoft Visual Basic (VB) or Visual C++ (VC++); some use applications such as Microsoft Excel, Access, or Visual Basic for Applications (VBA); and the rest use other methods. With the coming of Windows 2000's (Win2K's) Active Directory (AD), learning how to use simple scripts to automate Excel 2000 and generate reports seems worthwhile. These scripts can use Active Directory Service Interfaces (ADSI) to query AD.

In "Extending Active Directory's GUI," February 2000, I discussed how to use context menus and property pages to modify the user interface (UI) in Win2K's shell and administrator tools. You can use Extension-Attribute-1 through Extension-Attribute-15 on an object to incorporate as many as 15 extra items of data. Let's use these attributes in a real-world scenario.

Suppose that in your organization, a user must use an inhouse custom system with a Web-based front end to create a machine account for a client before installing Win2K on the client. The user will input his or her profile and the details of the machine. The nine machine details include the media access control (MAC) address of the network card; the machine installer's name; the department, building, floor, and room location; and the name, phone number, and email address of the person who knows the machine's local administrator password. The user also has an option to specify a name that he or she wants to use for the machine. When the user submits the Web form, the back-end system runs a series of checking procedures that verifies the details of the user and the machine. Then, the system allocates the machine a name. The system can accept the name that the user provided, if the user provided one, or it can generate a name. Then, the Web system returns the resulting name to the user and asks whether the name is acceptable. If the name is acceptable, the user agrees, and the Web system creates the computer account object in AD. If the name isn't acceptable, the form reappears with the provided data intact in the fields, and a negotiation process ensues with the system suggesting a list of unique names or the user providing one.

The whole process from keying in the data to receiving and agreeing on the name takes only a few moments. Now, the user can use the existing computer account in AD to install Win2K on the client. The back-end system easily provides the nine machine details as the data for the first nine extension attributes of the computer account in AD. You can use a script such as the one in Listing 1 to make a context menu Display-Specifier object display the nine exten-sion attributes in a message box, which Screen 1 shows.

Additional Attributes
The ability to print the computer information and examine this data at your leisure is a useful feature. Viewing this data in the form of tables and charts makes the data much more useful. Let's look at how you can quickly write VBScript routines that automate Excel 2000 to display this data in useful ways. Although this article's focus is on computer objects, the same principles apply to any objects in AD.

Suppose you have a spreadsheet with 13 columns. These columns correspond to the computer name, the machine's nine attributes, and three additional computer object attributes: OS-Version, When-Created, and When-Changed. When you use the back-end processes to first create a computer account in AD, the When-Created and When-Changed object attributes store a timestamp to represent that moment. From that moment on, whenever someone modifies the computer account object, only the When-Changed attribute changes. For example, when a Win2K client (for which you used an inhouse, custom system with a Web-based front end to set up) joins a domain account, the client modifies the account, so the When-Changed attribute changes. This change also modifies the OS-Version attribute, which holds a value that identifies the version and the build number. So, if you had clients on Win2K build 2031 and build 2072, the values would be 5.0 (2031) and 5.0 (2072), respectively.

Knowing these three pieces of information lets you deduce other useful information. For example, if you know the two timestamps, you can check to determine whether the timestamps are the same or different for a computer account. This check tells you whether anyone has ever used the account (i.e., whether a PC with that name has ever joined that account's domain). Then, you can use this information to expire any unused accounts that are more than a month old to keep the data in AD active.

You can also use the OS-Version information. (Although build 2031 and 2072 are beta builds, the concept will work the same for the released version of Win2K and its service packs.) So, you can easily assemble a pie chart to identify how many computers are on a particular OS version or build.

Assembling the Script
A script that displays a pie chart to identify the computers on a particular build will be long. You'll need a way to search AD to retrieve the desired attributes for a user and a method to iterate through the result set and populate a spreadsheet with data. The spreadsheet will need titles, headings, and customizations. Such a script won't necessarily process quickly. If the script has to write individual lines to the spreadsheet one at a time, it will take a few minutes to run. This waiting period shouldn't be a problem as long as you're aware of it. You can draw the pie chart last to provide a visual sign of the script's completion to users. Listing 2 shows an example report generation script.

Automating Excel
In Listing 2, you'll notice several Excel commands. These commands might look straightforward or complex, depending on how often you use scripts. I didn't write many of the commands; instead, I used Excel 2000 to record a VBA macro, then cut out the commands and altered them to conform to VBScript.

To write a macro for a simple script, start Excel 2000 with a blank spreadsheet. From the Tools menu, choose Macros, then Record New Macro. The Record Macro dialog box prompts you for a macro name. Click OK on the default Macro1 because you won't need the spreadsheet very long. A Stop Rec dialog box pops up that lets you stop recording the macro when you're finished with your tasks.

Let's complete the following steps to the Excel spreadsheet to see what VBA code it generates. Set cell A1 to 3, cell B1 to 2, and cell C1 to 1. Right-click the sheet and choose Rename to rename Sheet1 Moose (each workbook has three sheets by default). Make row A's text bold and centered, and fill the entire first row with a background fill color of yellow. Expand the column width of the first three columns. To use Freeze Panes and to make sure that row 1 never scrolls, select the 2 header, and from the menu choose Window, Freeze Panes. (Freeze Panes lets you hold columns or rows in place, yet the rows beneath the freeze point can scroll.) To end the macro, click the stop button on the Stop Rec dialog box.

You have now recorded all these VBA commands in Macro1, as Screen 2 shows. Now, you need to retrieve the text of that macro to review. To examine the macro, choose Tools, Macro, Macros (or press Alt+F8), then select Macro1, Edit. Because you conducted the tasks in a particular order, you can see exactly which commands the macro used to accomplish those tasks.

You still need to convert the VBA code to VBScript so that you can use it. This conversion requires two steps: defining the constants in the VBA code and reformatting calls to subprocedures or functions. Let's tackle the constants first. Screen 2 contains the values xlCenter and xlBottom for the horizontal and vertical alignment modifications, respectively. These constants aren't available in native VBScript, and you can't use the Windows Scripting Host (WSH) 2.0 <reference object> Extensible Markup Language (XML) tag to include them; thus, you have to define them in your code. To learn the values of each constant, open the Macro Editor and choose View, Object Browser (or press F2). Then, select the <globals> entry from the Classes window and look at the constants to find the values. Next, use the form Const variablename = constantvalue in your VBScript to define the constants. In VBA, subprocedures and functions tell you what the values that you're passing represent to help you understand the code. To convert the code for use in VBScript, you need to remove the := and the parameter name that precedes it. VBScript code isn't as easy to understand as VBA, but after you define the constants in the script, this code is functional.