Generating Deployment Reports

Automate Excel 2000 to generate reports from Active Directory

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 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 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.

Preparing the Script
To write a script that graphs OS versions in a pie chart, first declare the constants and variables that you'll use. Include the Option Explicits statement, which forces you to declare variables, as I do at callout A in Listing 2, page 95, so that you're less likely to make mistakes. I use a string of code in callout B to hold the attributes to search and retrieve so that I can easily add attributes later. I also set a constant in callout B to show the index of the OS version attribute that I'll need later to draw the pie chart (although it's the 12th item, the index value is 11 because the count starts at 0). After defining the constants and variables, I display a warning message about the length of time the script might take to run, then I use the AD search function at callout C to search AD for computer accounts and count the size of the result set so that I can use it as another index later.

Because I want to automate Excel in this script, I need to bind to the Excel.Application object. At callout D, I first create a reference to that object so that I have an Excel session available to use. Then, I open a new workbook, which comes with three default sheets. I want to store the returned result set in the first sheet, then use the second sheet to hold the results of calculations on that data from which I'll create my pie chart. To make the sheets more obvious, I rename them to more representative names. Next, I select the first sheet so that I can enter data into it. I also want to watch Excel perform its work, so I make the application visible on screen in the last line of callout D. This visibility is very useful for debugging.

At callout E, the code writes the 13 column headings into the first row of the spreadsheet and adjusts the column widths to a standard size. Then, the script goes on to modify various spreadsheet settings, such as including colors for row 1, using the Freeze Panes function on row 2, and centering column A. At callout F, page 96, I switch to the pie chart data sheet to perform similar setup tasks.

Tallying the OS Versions
Before populating the spreadsheet with the data from my variables, I want to compute the pie chart data and place it in an array. After I have the raw data from AD and the pie chart data, I enter the data into the two data sheets. To draw a pie chart, I need two sets of data, the names of the pie segments, and the values that represent the size of the pie. In this case, I need the OS versions and the totals for that OS version. The script iterates through the returned set of results (that the arrAttributes array holds) and checks the 12th attribute. Each result in the result set will have the OS version name as the 12th attribute. After I go through each result and tally the number of distinct OS version names, I'll have the data for my pie chart.

At callout G, page 96, I use the 2-D array arrOSVersionTotals to hold these values, where the arrOSVersionTotals(0,x) values represent the names of the pie segments and the arrOSVersionTotals(1,x) values represent the segment values. (In both cases, the value of x starts at 0.) To maintain a tally, I have to check the name of each new result against each of my existing arrOSVersionTotals(0,x) names. So, I need a loop that goes through every result and checks that result against all the names in my OS version array.

Because my OS version array is empty initially, I need to add at least one value to start with; otherwise, the comparisons won't work. The easiest way to add a value is to add the first result into my OS version array and set the count for that OS version to 1. To add this value, I use the ReDim command in callout G to expand the undefined array to hold the values. Then, the array can hold one name and one count. Next, I place the name of the first result's OS version into the array and set the count to 1. With that value in place, I can start a loop through the rest of the result set starting at index 1 (because I just completed 0) and compare the OS version names with those I recorded.

Some objects might not have a set OS version yet, especially if a user is still installing the client that this account represents and the client hasn't connected to the domain. If this situation is the case for any object, I modify the result set's 12th attribute to the string UNKNOWN. This string lets me keep a tally of UNKNOWN computer objects with no known version.

At callout H, page 99, I check each item against each name in the arrOSVersionTotals array. If the names match, I increase the tally by one. If the names don't match, I add the new name and a tally of 1 to my arrOSVersionTotals array. I use a Boolean variable, bolFound, to show whether the script found the name.

Populating the Spreadsheet
Having specified the worksheet properties and calculated the pie chart data, I can now populate the two spreadsheets with the data. The process is the same for both sheets, as you can see at callout I. First, I select the desired sheet, then loop through the array and write the values to the spreadsheet. The loop starts at 0, but because of column headings, I want to populate the spreadsheet in row 2 and higher. I use intRowIndex+2 to show the cell that I'm updating. The same procedure applies for the columns starting at A in the pie chart data sheet; I have to use intAttributeIndex+1 as the column count so that 0 becomes 1 and 1 becomes 2. An extra part to the computer data spreadsheet is the addition of the code that checks to determine whether the value of the cell is blank. If the resulting cell after the write is empty, the script replaces that value in the spreadsheet with the string UNKNOWN. The complete automatic report generation script also contains a Search Function in its subprocedures. For more information about the Search Function, see the sidebar "The Search Function."

Drawing the Pie Chart
Finally, at callout J, the script draws the pie chart. To draw the chart, I add a new sheet for the chart to the workbook. Then, the script sets the chart to be a pie chart, and specifies the source data for the pie chart data sheet, specifically columns A and B and rows 1 through the maximum number of OS versions that the script finds. Then, I specify the name of the new sheet, set a title for it, and select and modify the legend to show that I want to display the percentages. Finally, the script selects and displays the pie chart on the screen, which Screen 3 shows, at the end of callout J.

I used VBA to create many of the commands automatically. You can use the Object Browser to investigate these commands further and discover other methods and properties of the interfaces. For more information about VBA commands, refer to Stephen Bullen, John Green, and Felipe Martins, Excel 2000 VBA Programmer's Reference (Wrox Press, 1999) or Dwayne Gifford, John Green, Duncan MacKenzie, Office 2000 Programmer's Reference Kit (Wrox Press, 1999).

Other Suggestions
You can attach chart-generating scripts directly to the Win2K GUI, thus providing administrators and users access to any data that you want to make available. You could also create the data in HTML or XML (or save it as HTML or XML from Excel 2000) and open it in Microsoft Internet Explorer (IE).

I hope you now have some insight into creating scripts that can interface with the AD and produce useful reports. If you're interested in learning about more scripts and examples of this nature, I continue this AD series in the March 2000 issue of the Win32 Scripting Journal.