Graphing Windows 2000 User Logons with Excel 2000

Editor's Note: This article is the last in a series about automating reports in Windows 2000 (Win2K). The first four articles were

• "Extending Active Directory's GUI" (Windows 2000 Magazine, February 2000), which discussed how to use context menus and property pages to modify the user interface (UI) in the Win2K shell and administrator tools
• "Generating Deployment Reports" (Windows 2000 Magazine, March 2000), which covered how to automate Microsoft Excel 2000 to dynamically create a spreadsheet of statistics for Computer objects in Win2K's Active Directory (AD) and how to create a pie chart
• "Automating Excel to Create Trend Charts" (Win32 Scripting Journal, March 2000), which described how to create a script that regularly gathers data, updates an Excel spreadsheet with that data, and creates charts showing trends
• "Using WSH with ADSI to Create Excel Spreadsheets for Debugging" (Win32 Scripting Journal, April 2000), which discussed how to use Windows Script Host (WSH) and Active Directory Service Interfaces (ADSI) to retrieve and examine an AD object's properties then write the desired properties to a spreadsheet

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

In Win2K, you can automate the creation of Excel graphs that report detailed information about user logons. For example, suppose your company is deploying Win2K. As the head systems administrator, you want to create scripts that gather and chart data showing the number of user logons across the Win2K domain controller server farm. You want these graphs to show how many employees are logging on to the new Win2K service (which suggests how the Win2K deployment is proceeding) and their usage patterns for the new service in a typical day. You also want to use these graphs in the long term to identify trends and usage patterns across the farm.

To gather the data, you need a script that regularly records the number of user logons across all servers. The script LogData.wsf accomplishes this task. LogData.wsf is designed to be run every 5 minutes with the Win2K Task Scheduler. (For information about the Task Scheduler, see Win2K's online Help.) LogData.wsf creates one log file each day and records three pieces of data:

• The time of the run
• The number of users who are currently logged on
• The maximum number of logged-on users so far for that day

After you run LogData.wsf for several days, you can use the gathered data to create almost any type of chart. Two informative charts that you can create are a graph depicting the user logon numbers for a particular day (from midnight to midnight) and a graph depicting the maximum number of user logons for each day during a particular time period.

The scripts that create these charts are DayUsageGraph.wsf and PeriodMaxUsageGraph.wsf, respectively. Both scripts prompt you for information (i.e., dates), use that information to retrieve the relevant records from the log files, pass that data to an Excel spreadsheet, and create a chart from the spreadsheet. For DayUsageGraph.wsf, you need to provide one date: the particular day of interest. For PeriodMaxUsageGraph.wsf, you need to provide two dates: a start date and an end date for the desired period.

LogData.wsf, DayUsageGraph.wsf, and PeriodMaxUsageGraph.wsf apply 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 all three scripts in the Code Library on the Win32 Scripting Journal Web site.

The LogData Script
LogData.wsf is a modified version of the script ShowUsers.vbs that I discussed in the article "An ADSI Primer, Part 8: More About Manipulating Persistent and Dynamic Objects," August 1999. However, LogData.wsf is a WSH 2.0 script. As a result, you can import subroutines from a library file rather than declaring them in the script. Putting subroutines in a library file is preferable because you can call them from any script by simply referencing that file.

To use LogData.wsf, you need to download the file called library.vbs from the Code Library on the Win32 Scripting Journal Web site and place it on the server from which you'll run the script. Library.vbs contains two subroutines (RemoveDuplicates and Quicksort) that LogData.wsf uses.

LogData.wsf performs five important tasks. Because LogData.wsf is similar to ShowUsers.vbs, I won't discuss the first three tasks in depth. For information about how to script these tasks, see "An ADSI Primer, Part 8." In addition, you can review the many comments in LogData.wsf. Here's what LogData.wsf does:

1. The script defines the array of servers in the server farm.
2. For each server in the server farm array, the script steps through each session and selects connected user sessions only (ignoring interserver logons) and stores the username of each connected user in an array.
3. The script sorts through the username array, removing duplicates. The script counts the number of remaining unique usernames to obtain the current number of user logons.
4. The script updates the maximum number of user logons. In the first run of any day, the maximum number is the same as the current number. For subsequent runs in the day, the script updates the maximum number every time the current number hits a new high.

   As Listing 1, page 6, shows, LogData.wsf updates the maximum number by first opening that day's log file, which uses the filename format C:\logs\usercount-ddmmyy.log. (To adapt this code to the US date format, you need to modify the scripts so that they use the mm/dd/yy format.) The script then reads the previously appended line (which is always the last line in the file) to find out the maximum number for the previous run and compares that number with the current number. (If the file doesn't exist yet because it's the first run of the day, the script sets the maximum number to the current number.) If the previous line's maximum number is higher than the current number, LogData.wsf uses the previous line's maximum number again in the present line. If the previous line's maximum number is lower than the current number, LogData.wsf uses the current number as the new maximum number in the present line.

5. The script appends the time, current number of user logons, and maximum number of user logons to the file, then closes the file.

Figure 1, page 6, contains a 40-minute excerpt from example output from LogData.wsf. (On the Win32 Scripting Journal Web site, you can find an entire log file called usercount-020200.log.) As Figure 1 shows, commas separate the time, the current number of user logons, and the maximum number of user logons.

The DayUsageGraph Script
After you've logged at least one day's worth of data, you can use DayUsageGraph.wsf to chart daily usage patterns. This script prompts you for the day you want to graph, uses this information to retrieve the relevant data from the file, passes the data to an Excel spreadsheet, and creates a chart from the spreadsheet.

You create DayUsageGraph.wsf in four sections:

The setup section. You begin this section by defining the constants and declaring the variables. The constants you need to define include Excel's Application constants. Although Excel's Application constants are automatically available in Visual Basic for Applications (VBA), you need to define them in VBScript code. In addition, you need to define the file access constants from the File System Object (FSO) model. This model provides the means for the VBScript code to read, write, and append local system files. (For information about the FSO constants, see Dino Esposito's column "Understanding VBScript: The TextStream Object," page 10.) The script also uses the USERLOGS_FILEPATH constant to specify the path to the folder containing the log files.

You complete the setup part of the script by creating instances of Excel's Application object and the FSO model's FileSystemObject object. These objects let you manipulate Excel 2000 and the log file, respectively.

The input section. In this section, you use VBScript's InputBox function to prompt the administrators running the script to specify the date (in the dd/mm/yy format) they want to graph. You then compare that date with a default date you specify. If the entered date is before the default date, an If...Then...Else statement selects the default date for use. Otherwise, the entered date is used. This process helps avoid errors if users type in an inappropriate date.

Next, you compile the log file's full path by using VBScript's Split function with a slash delimiter to separate the date into its three components. You then append these components to the USERLOGS_FILEPATH constant. After you create the path, you check to see whether the file exists and quit the script if it doesn't.

The data retrieval section. If the log file exists, you're ready to retrieve the data from the log file and fill a worksheet with that data. First, you use the Add method of the Application object's Workbooks method to create a new workbook in an Excel spreadsheet. In the workbook, you use the Value method of the Application object's Cell method to set three column headings (Time, Users, and Max Users) in row 1 of sheet 1.

Next, you open the log file with the FileSystemObject object's OpenTextFile method, which lets you access the TextStream object representing that log file. Using the TextStream object's ReadLine method, you read each line in the file into the arrTmp array's indexes. You then use the Value property of the Application object's Cell method to transfer the index values to the worksheet rows, starting at row 2.

After you transfer all the data, you set the strMaxUsers variable to the maximum number of user logons for that day and close the log file. You'll later use this variable's value in the chart's title.

The data charting section. In this last section, which Listing 2 shows, you chart the data you've transferred. First, you use Excel's Range object to select all the rows containing data. Then, you use the Add method of Excel's Chart object and the ChartType property of the Application object's ActiveChart method to add a new sheet that contains a line chart based on this data. You need to give this new chart sheet a meaningful name, such as Day Usage Graph.

The next task is to modify the chart so that it has a main title that includes the maximum number of user logons (i.e., the value in strMaxUsers) and a title for the primary axis (in this case, the horizontal axis). To create the main title, you set ActiveChart's HasTitle property to True and use its ChartTitle property to specify the title. To create the primary-axis title, you set the HasTitle property to True for ActiveChart's Axis method and use the AxisTitle property to specify the title.

In Listing 2, note the use of VBScript's With statement. You can use the With statement to help with text formatting when you're writing a script that includes multiple references to the same object within a section of code. This statement lets you execute several statements on an object without having to restate the object's name each time. For example, without the With statement, the code at callout A in Listing 2 would be like that in Listing 3.

After you've set the titles, you display the chart by setting the Application object's Visible property to True. On the Win32 Scripting Journal Web site, you can find the DayUsageGraph.xls Excel file, which shows the resulting graph when you run DayUsageGraph.wsf.

The PeriodMaxUsageGraph Script
You create PeriodMaxUsageGraph.wsf the same way in which you created DayUsageGraph.wsf, except for these differences:

1. You use the InputBox function twice instead of once. The first input box retrieves the start date of the period to graph; the second input box retrieves the end date.
2. You add the code in Listing 4 to swap the dates if administrators enter the end date in the first input box and the start date in the second input box.
3. Based on the entered start and end dates, you open each log file in chronological order. From each file, you retrieve only the maximum number of user logons. This number is the last number in the last line of each file.
4. Instead of creating three column headings (Time, Users, and Max Users), you create two column headings (Date and Max Users) in row 1 of sheet 1 in the workbook. You need to give the new chart sheet a meaningful name, such as Period Max Usage Graph.

On the Win32 Scripting Journal Web site, you can find the Excel file PeriodMaxUsageGraph.xls, which shows the graph that results when you run PeriodMaxUsageGraph.wsf.

Customize Your Graphs
You can easily customize PeriodMaxUsageGraph.wsf and DayUsageGraph.wsf. For example, if you want to use a bar, or column, chart instead of a line chart for the Period Max Usage Graph, you just need to specify the chart-type constant xlColumnClustered instead of xlLine in PeriodMaxUsageGraph.wsf. To find the constants for the chart type that you want, you can record a VBA macro following the process I discussed in "Generating Deployment Reports."

You can even include several chart-type constants in your script, then query administrators about which type they want to use. Their response dictates the constant that the script will use. For example, if you define the constants

Const xlLine = 4
Const xlColumnClustered = 51

in a script, you can then use the MsgBox function to ask administrators which chart to create. The message box doesn't have to be complex. For example, it can simply ask the administrators to answer Yes or No to the question, "Do you want to use columns?" If administrators select No, the script uses the xlLine constant. Otherwise, the script uses the xlColumnClustered constant. Listing 5 contains the code that generates this message box and determines which chart to create based on administrators' input.

I hope you found this five-part series useful. Next month, I'll start a WSH column that will show you how to use WSH to perform more great tasks.