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.