Automating Excel to Create Trend Charts

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

Both articles are available online. Go to http://www.win2000mag.com, and access the appropriate month.

In Win2K, you can create scripts that dynamically generate two types of automated charts: snapshot and evolution. A snapshot chart takes a picture of a system so that you know the system's current status. Evolution charts record a series of snapshots over time so that you can see trends.

In the article "Generating Deployment Reports," I provided a script in Listing 2 that generates a snapshot chart detailing, at that moment, how many Computer objects are in the AD store and what build version of Win2K is running. Although you could regularly run this script to obtain a series of snapshots, this script doesn't record the data, so you wouldn't be able to see trends. Instead, you need a script that regularly takes snapshots of the system, stores the data, then charts the data for easy review.

If you regularly gather and store data about your environment, you can generate evolution charts to help you with many system administration tasks. For example, you can use these charts to keep track of how a deployment is going, justify additional resources (e.g., hardware, software, staff), or troubleshoot problems in systems.

The best practice is to store your data in a relational database, such as a Microsoft SQL Server or Microsoft Access database. However, if you don't have the time or resources to set up a relational database, you can store your data in spreadsheets you've created with Excel 2000. Storing data in spreadsheets is better than storing data in a text file because Excel has good built-in charting capabilities that you can use. In addition, you can easily transform Excel data into a format for import into a relational database if you later decide to set up such a system.

Let's look at how you can create a script that regularly gathers data, updates an Excel spreadsheet with that data, and creates charts showing the trends. You can break down the process of creating this script into three discrete steps:

Now let's see these steps in practice. Suppose you're introducing a Win2K AD system to an organization. The staff is currently using an older system that you'll slowly phase out. Although you have a cutoff date when the old system will cease to work, the timescale for the deployment is user-driven because the staff members get to specify when they want you to update their machines. For this reason, you want to keep an eye on the deployment to make sure that you have a steady conversion rate. You don't want to leave too many conversions for the last few weeks. You want to gather and store data that lets you take control of the situation so that you can fix any problems as soon as they become apparent.

The staff members are split into two categories: temporary and permanent. Because all the staff members are in one of three departments (Sales, Marketing, and Finance), you structure the AD store into three organizational units (OUs) corresponding to those departments. Each OU holds Computer and User objects. Each User and Computer object is a member of only one of two groups: temporary staff or permanent staff.

Step 1: Decide on the Data and the Update Interval
You want to create a daily report that records the number of deployments that you've made for the temporary and permanent staff in each department. Thus, you decide to record daily the number of User and Computer objects you've created in each OU in the AD store. You also decide to divide these objects into two groups (permanent staff and temporary staff) based on their group membership.

Step 2: Create the Spreadsheet
You begin this step by creating a new spreadsheet and saving it as computer and user count.xls. Then, right-click the Sheet1 tab and select Rename. Type Staff User Data, and press Enter.

In row 1 of the Staff User Data sheet, create the 10 column headings that you see in Screen 1. Column A contains the date. Columns B, C, and D contain the temporary staff count, permanent staff count, and total count (the sum of the other two columns), respectively, for the Sales OU. Similarly, columns E, F, and G contain the temporary, permanent, and total staff counts, respectively, for the Marketing OU and columns H, I, and J contain these counts for the Finance OU. You can use bold colors to make the columns more distinctive.

To force row 1 (i.e., the column headings) to stay on screen while the other rows scroll freely, highlight row 2 by clicking 2 and select Freeze Panes under the Windows menu. Screen 1 shows a snapshot of row 1 frozen and row 38 through 61 displayed.

Next, right-click the Sheet2 tab and rename the sheet Staff Computer Data. In the Staff Computer Data sheet, create 10 columns as you did in the Staff User Data sheet, except substitute the word Computers for Users in each of the column headings. Use Freeze Panes to stop row 1 from scrolling.

After you've created the data sheets, you can create the sheets for two evolution charts: One chart will show the trends for users in the Sales OU, and the other will show the trends for the computers in the Marketing OU. Although you're creating only two charts in this exercise, you can make as many graphs as you need to make effective comparisons.

To begin, delete Sheet3 by right-clicking the Sheet3 tab and choosing Delete. Next, go to Insert, Chart to bring up the Chart Wizard. Follow the step-by-step instructions that the Chart Wizard provides to create a line chart. In Step 4, Chart Location, make sure you select As new sheet and not As object in. Name the chart Totals for Sales Users. Repeat this process for the second chart, naming it Totals for Marketing Computers. Screen 2 shows an example of what the Totals for Sales Users chart will look like after you populate the Staff User Data sheet with data.

Finally, make a copy of the computer and user count.xls spreadsheet to use in step 3. That way, you won't have to recreate the spreadsheet when you run the script in production.

Step 3: Create the Script
The code that you're creating is a Windows Script Host (WSH) 2.0 script called ADDeploy.wsh. This script uses Extensible Markup Language (XML), so you need to use the .wsh extension and not the .vbs extension. You can find ADDeploy.wsh in the Code Library on the Win32 Scripting Journal Web site at http://www.win32scripting.com.

As the excerpt in Listing 1 shows, you begin ADDeploy.wsh by importing the library library.vbs that contains the SearchAD function. This library of useful routines resides on my machine. By importing this library, I can use the SearchAD routine without having to declare it in my code. To use ADDeploy.wsh, you need to download the SearchAD routine from the Code Library and create a similar library.

Next, you declare and initialize the constants and variables. Because the script performs counts and calculates totals, most of the variables are integers. You need to initialize all integer variables that represent a total to the value 0 because you haven't counted any AD objects yet. After initializing the counts, you bind to the two groups representing temporary and permanent staff (i.e., adsTempGroup and adsPermGroup) so that you can test for membership of these groups later.

Now, you can proceed to the heart of the script, which has two distinct parts. The first part searches the AD store for the relevant objects and counts those objects; the second part updates the spreadsheet with the collected totals.

Part 1. This part of the script contains six sections of almost identical code: Three sections search for and count User objects, and three sections search for and count Computer objects.

In the User object sections, you use the SearchAD function to search each OU for User objects. For example, Listing 2 shows the section of code that searches for and counts the number of User objects in the Sales OU. You have to use the code (!(objectClass=computer)) to explicitly state not to retrieve Computer objects because computers are a type of user and the search would otherwise return them. You then iterate through the returned array of ADsPaths so that you can use them as parameters to two calls to the IADsGroup::IsMember method. This method returns True or False, which lets you increment the count for either the temporary staff or permanent staff group. (For more information about the IADsGroup::IsMember method, see the article "An ADSI Primer, Part 2: Manipulating Active Directory Objects," February 1999.) The code doesn't count users who aren't members of either group.

The three sections for computers are basically identical to the User object sections, except you're searching each OU for Computer objects. For example, Listing 3 shows the section of code that searches for and counts the number of Computer objects in the Sales OU.

Part 2. The second part of the script updates the spreadsheet with the counts that Part 1 collects. Listing 4, page 12, shows this part of the script. You begin Part 2 by binding to the Excel.Application object and opening the computer and user count.xls spreadsheet. After accessing the Staff User Data sheet, you start an index from row 2 (missing the headings in row 1) and look at the current value for the cell in column A in that row. If the cell has a value, you move to the next row, and so on.

To check the cell's value, you use the Cells property of the Excel.Application object. The Cells property is a Range class object and is unique in that it has a property called Value, which returns the value of the cell you specify. You simply pass in the row (e.g., intUserRowIndex) and column (e.g., 1) as parameters, and the Cells property retrieves that specific cell from the currently selected sheet.

To obtain the code for the next part of the script, you need to record and translate a VBA macro. (If you're unfamiliar with how to record and translate a VBA macro, see the Windows 2000 Magazine article "Generating Deployment Reports.") In the copy of the computer and user count.xls spreadsheet, go the Staff User Data sheet and follow these steps:

After you've converted the VBA code into VBScript, the code will look similar to that in callout A in Listing 4, except for the first two lines. You need to add this code to fill the blank cell (intUserRowIndex, 1) with the current date.

To populate this cell with the date, you use VBScript's DatePart function (which returns a specified part of a given date) to obtain the day, month, and year of the current date that the Now function retrieves. (To adapt this code to the US date format, you need to switch the DatePart("d",Now) and DatePart("m",Now) entries.) You then use the concatenation operator (&) to put slashes between the day and month and between the month and year. Finally, you pass the completed date into the first cell of the row.

With the Staff User Data sheet completed, you can proceed to the Staff Computer Data sheet and repeat steps 1 through 10 to obtain and convert the VBA macro. Then, add the code that populates the blank cell (intCompRowIndex, 1) with the current date.

Next, you need to record a macro to update the spreadsheet's charts so that you can capture the VBA code and modify it for your VBScript script. To record the macro, select each relevant chart sheet and update the source data to include the new row. Begin by right-clicking the chart, and select Source Data on the context menu that pops up. Excel will display the chart's current source of data, which in this case, might read something like

'Staff User Data'!$A$1:$D$50

In plain English, this line says that the data is from the Staff User Data sheet of this spreadsheet in columns A through D and rows 1 through 50. To extend the chart down a row, increment the final row number by one (in this example, 50 to 51) and click OK. Now, stop the macro and bring up the macro editor to see how to modify the source data to include a new row of data programmatically.

After you convert the macro to VBScript, you'll obtain code similar to that in callout B in Listing 4. In this code, you use the SetSourceData method of the ActiveChart object. This method takes two parameters: the range of cells that you're charting and a constant specifying whether you want to chart the data by columns or rows. In this case, the Totals for Sales Users chart takes the range parameter ("A1:D" & intUserRowIndex) and the Totals for Marketing Computers chart takes the range parameter ("A1:D" & intCompRowIndex). The column index is static, so you can specify the value of "A1:D". However, the row index isn't static, so you need to use a row index variable, such as intUserRowIndex. For the second parameter, you use the xlColumns constant because you're charting by columns. (You use the constant xlRows if you're charting by rows.)

The Results
After you run ADDeploy.wsh several times, you'll obtain a trend chart like that in Screen 2. As you can see, you've steadily created new User objects for the Sales OU's permanent staff but not its temporary staff. That means the temporary staff aren't contacting you to upgrade their machines. Spotting trends like this one is the whole point of this statistical exercise. If you had caught this problematic trend at the beginning of July when it started, you could have nipped the problem in the bud rather than waiting until the end of July and losing a month of deployment time.

I hope this example has given you ideas on how to automate Excel to create evolution charts that will help you with your system administration tasks. Next month, I'll show you how to automate Excel to produce graphs that show the usage of a system over hours, days, weeks, or months.