Project Case Study Creating a Centralized Report Management Station

Highlights

This chapter begins a new project case study. In this project, you'll observe as Molly Masterson develops a new centralized report management station for the order/inventory system at Intuit Mechanical Tools. Through the completion of this project, you will gain further experience in automating many of the tasks that were covered in the previous project. In addition, you will learn how to automate a number of new tasks, including:

• The storage and retrieval of configuration data in the Windows registry
• The remote administration of files stored on network drives
• The creation of text and Microsoft Word files
• The sending of notification pop-up messages over a network

Project Overview

It has been one month since Molly finished the development and deployment of her order/inventory report and log analyzer scripts. Since that time, an exciting new development has occurred at Intuit Mechanical Tools. Last week, the company was awarded a five-year contract with the local state government that will require it to almost double its production output. The first orders for this new contract will begin arriving in a week. Because this new contract was somewhat of a surprise, everyone at Intuit Mechanical Tools has to scramble in order to get things ready.

As a short-term solution to handling the increasing IT processing workload, the company has purchased a new Windows 2000 server and plans to install the order/inventory system on it. This server will then be used to handle all incoming sales and returns originating from the new government contract. Meanwhile, the current Windows 2000 server will continue to process all sales and returns from all current customers. At a later date (once more programmers have been hired), the company will work toward modifying the order/inventory system and consolidating all of the data managed from both the Windows 2000 servers. This means that a number of departments within Intuit Mechanical Tools will have to work harder in order to consolidate sales, returns, and production information from two different sources.

As a result of her success in completing her last project, the computer operations department has requested that Molly develop an automated process that will retrieve copies of the summary reports from both Windows 2000 servers and store them on a dedicated Windows 2000 Professional workstation located within the operations control center. In addition, the operations department would like for Molly to automate the processing of the summary reports from both servers and to create a single consolidated report, for which they would like to keep a rolling three-month archive.

Collecting Project Requirements

The first step that Molly took upon beginning this new project was to sit down with several members of the operations staff to determine exactly what they were looking for in regards to the creation of a consolidated summary report. At this meeting, she was asked to develop a daily report based on the following sample report that operations had manually created.

*******************************************************************************

Master Consolidated Summary report for 5/01/2003

*******************************************************************************

Errors:

Date Time Svr Code Description
12:15:44 3/01/03 Sr2 001 Unable to access card reader on device wkstn442
14:00:14 3/01/03 Sr2 001 No inventory for part # 58692 - unable to fill order
 39312
16:16:46 3/01/03 Sr2 003 Unable to print summary rpt on master printer (no
 paper)
17:42:23 3/01/03 Sr1 002 Failure executing job # 434, sched 55 - code 0004534
18:19:19 3/01/03 Sr1 001 No inventory for part # 58655 - unable to fill order
 39312
18:22:21 3/01/03 Sr1 001 Unable to access card reader on device wkstn113

-------------------------------------------------------------------------------

Sales summary:

Government:

Part # Qty Description

58694 19 Cordless temp reader
45643 3 200hp magnetic pump
17443 15 20 lb box of pump clips
10344 35 48 ounce solvent bottle
19365 2 3 speed electric drill

Other Customers:

Part # Qty Description

58694 23 Cordless temp reader
45643 2 200hp magnetic pump
17443 10 20 lb box of pump clips
10344 33 48 ounce solvent bottle
19365 5 3 speed electric drill

-------------------------------------------------------------------------------

Return summary:

Government:

Part # Qty Description

58694 2 Cordless temp reader
17443 7 20 lb box of pump clips
10344 4 48 ounce solvent bottle
45643 1 200hp magnetic pump
19365 1 3 speed electric drill

Other Customers:

Part # Qty Description

58694 1 Cordless temp reader
17443 3 20 lb box of pump clips
10344 4 48 ounce solvent bottle
45643 3 200hp magnetic pump
19365 2 3 speed electric drill

-------------------------------------------------------------------------------

Daily Production Summary

Part # Qty Description In Stock

58694 20 Cordless temp reader 45
45643 4 200hp magnetic pump 20
19365 12 3 speed electric drill 20
17443 42 20 lb box of pump clips 200
10344 240 48 ounce solvent bottle 375

The report represents information extracted from the summary reports that will be generated on both Windows 2000 servers. The current Windows 2000 server is named Serv0001. The new server, which is still being configured, will be named Serv0002. The report itself is not substantially different from the summary reports already generated locally on the servers.

For starters, this new master consolidated summary report is still organized into four sections. The Errors: section now reports on errors from both Windows 2000 servers. A new field will need to be inserted that specifies the server from which the error was reported, in abbreviated form (Sr1 for Serv0001 and Sr2 for Serv0002).

The data presented in the Sales summary: and Return summary: sections is broken down into two sets of data. The first set of data lists government sales or returns collected from the summary reports on Serv0001, and the second set of data represents sales or returns data collected from the summary reports on the Serv0002 server for all other customers.

Finally, the information presented in the Daily Production Summary section presents totals collected from both servers.

Requirements Analysis

After meeting with the operations staff, Molly returned to her desk and wrote up a list of project requirements, which she then e-mailed to both her manager and the operations manager for approval. This list is shown below.

• Automate the collection of summary reports from both Serv0001 and Serv0002
• Read and process the summary reports from both servers and generate a master consolidated summary report
• Maintain a three-month archive of master consolidated reports on the Windows 2000 workstation located in the operations control center
• Log event messages to the Windows 2000 applications event log to facilitate the auditing and tracking of script activity
• Notify operations staff by means of a pop-up dialog box when the master consolidated summary report is available
• Create both a text and a Microsoft Word version of the master consolidated summary report
• Complete the development and testing of the project within 30 days

By requiring that both her manager and the operations manager confirm their acceptance of these requirements, Molly hopes to ensure that the new automation system she'll be developing will meet the needs of the operations department. In addition, by formalizing project requirements up front and getting everyone's approval, she can prevent feature creep (that is, the ad hoc addition of new requirements during the development of the project). Once approved, any changes to the project's requirements will have to be addressed at a later date. This will help to ensure that Molly does not miss her project deadline by having to provide for additional project requirements that she had not planned for.

Performing a High Level Design

As part of her preliminary design and to help management and the operations staff better understand the overall scope of the project, Molly drew the sketch shown in Figure 21.1.

Figure 21.1: An overview of the different components involved in developing a master consolidated reporting system

As the sketch shows, Molly is planning on creating a centralized management workstation by copying the summary files from each Windows 2000 server and storing them on the Windows 2000 Professional workstation. The master consolidated summary reports will be stored on this workstation, as well.

For her own clarification, she depicted the INI files on each of the remote Windows servers where she stores VBScript reports and log analyzer configuration settings. Since everything is working well on the remote Windows 2000 servers, and for the sake of expediency, she plans to keep the INI files in place on the remote servers. However, she has decided to use the Windows 2000 registry on the Windows 2000 Professional workstation as the central repository for all the configuration settings for the scripts that she will develop as part of this new project.

Once she received formal approval of the project requirements that she had distributed and answered everyone's questions, Molly began work on a high-level design. Once again, she decided to use VBScript and the WSH and to tackle this project by developing a collection of small scripts, rather than trying to write one large all-inclusive program.

Molly ultimately produced the following list of tasks as shown in Table 21.1, each of which she plans to accomplish with an individual VBScript.

Table 21.1: Consolidated Order/inventory Summary Report Tasks

Type of Task	Description
Registry Setup	Create a VBScript that will create a registry key for the project and then populate that key with values, each of which stores a particular configuration setting for the scripts involved in this project
Remote Report Retrieval	Create a script that connects to each of the Windows 2000 servers, copies the daily summary reports to the Windows 2000 workstation, and notifies operations if the summary reports are not available
Report Consolidation	Create a VBScript that reads both of the summary reports generated each day, consolidates their information into a single report based on the criteria specified by operations, and notifies operations when the master consolidated report is ready
Archive Management	Create a VBScript that maintains a three-month archive of master consolidated summary reports and execute this script on the first day of each month in order to delete old report files

In addition to writing these scripts, Molly will need to set up their scheduled execution. Completion of this task will also include the one-time creation of a special service account on the Windows 2000 Professional workstation in order to facilitate the execution of scheduled tasks.

Creating the Registry Setup Script

Rather than embedding script settings within each VBScript or storing them in one or more INI files, Molly has decided to learn how to store them within the Windows registry. This way her scripts will run faster, because they will not have to perform any file I/O in order to retrieve their settings, and she won't have to manually edit her scripts and risk an accidental typo when modifying script settings. In fact, using the Regedt32 utility supplied with Windows 2000 or Windows XP, she'll be able to remotely modify registry settings for the scripts from the comfort of her own desktop, as demonstrated in Figure 21.2.

Figure 21.2: Using the Regedt32 utility to remotely modify registry keys and values

Molly is still in the process of determining exactly which types of configuration data she will externalize from her scripts and store within the Windows registry. She does know that she plans to create a key under the HKEY_LOCAL_MACHINE root key called Intuit. Under this key she will create a subkey called VBScripts, and within this subkey she will create one more key called MstSumRpts. Within the MstSumRpts subkey, she will store all the configuration settings for each of the VBScripts that will run on the Windows 2000 Professional workstation.

Note

For detailed information about the structure of the Registry and how it operates, refer to Chapter 22, "Developing the Setup Script."

In order to develop the setup script that will create the registry keys and store her script's configuration settings, Molly will need to learn how to work with the following WshShell object methods:

• RegWrite(). Provides the ability to create and change registry keys and values
• RegRead(). Provides the ability to retrieve registry keys and values
• RegDelete(). Provides the ability to delete registry keys and values

Creating the Remote Summary Report Retrieval Script

In order to read and process the summary reports from each Windows 2000 server, Molly plans on setting up the folders where the summary reports are stored as shared folders. She then plans on writing a script that will remotely connect to shared folders on each server and copy the daily summary reports to the Windows 2000 Professional workstation for further processing.

In order to remotely connect to the shared folders where the summary reports are stored on each Windows 2000 server, Molly will need to learn how to work with the WshNetwork object. This object provides access to properties that contain network information and to methods that allow you to connect to network drives and printers. In particular, Molly will have to learn how to work with the following WshNetwork object methods:

• MapNetworkDrive. Establishes a connection to a shared network drive or folder
• RemoveNetworkDrive. Disconnects a connection from a shared network drive or folder

Creating the Report Consolidation Script

The processing of the summary reports collected from the two Windows 2000 servers will be performed in much the same way that the report and log analyzer script processed the four report and log files described in Chapter 18, "Developing Script Log Analyzers." However, there are some differences. For one thing, Molly will have to add a new column to the Errors: section when writing the consolidated summary report in order to identify on which server each error occurred.

Another new requirement of the consolidated summary report is the accumulation of production inventory totals at the end of the report. This accumulation of totals will require the VBScript to match up line items based on matching part numbers, in order to calculate the total inventory on hand for each item.

In order to develop the VBScript that produces the consolidated summary report file, Molly will need to use each of the following FileSystemObject object methods:

• FileExists(). To validate whether a log or report file exists before trying to open it
• OpenTextFile(). To be able to work with the contents of the log or report file
• ReadLine(). To be able to read a line at a time from the INI file
• SkipLines(). To be able to skip header lines when reading files
• Close(). To close the INI file when done processing it
• WriteLine(). To be able to write a line of output to the summary file
• WriteBlankLines(). To be able to write blank lines and format the presentation of data in the summary file

Creating the Archive Management Script

The last VBScript that Molly will create for this project is one that manages three separate file archives, as outlined below.

• D:Order_InventorySr1_SummaryRpts. Stores a minimum of 90 days' worth of summary reports retrieved from Serv0001
• D:Order_InventorySr2_SummaryRpts. Stores a minimum of 90 days' worth of summary reports retrieved from Serv0002
• D:Order_InventoryConsolidatedRpts. Stores a minimum of 90 days' worth of consolidated summary reports created from the individual summary reports retrieved from Serv0001 and Serv0002

Molly will use the Windows Task Scheduler service to automate the execution of this script on a monthly basis. Because the scheduling of this script is a one-time task that only needs to be performed on the Windows 2000 Professional workstation, Molly will manually set up its scheduled execution using the Scheduled Task Wizard.

In order to automate the execution of the archive management script, Molly will have to work with the following FileSystemObject object methods:

• FileExists(). To validate whether a log or report file exists before trying to open it
• MoveFile(). To move one or more files to an archive folder for a minimum one-month retention
• DeleteFile(). To delete one or more files stored in the archive folder after their 30-day retention period has passed

Molly also plans to provide this script with the ability to notify the operations staff when it has completed its execution and the consolidated summary report is ready for printing. Notification techniques implemented by this script will include the posting of messages to the Windows 2000 Professional application event log and the creation of pop-up messages on the Windows 2000 Professional workstation. In order to perform these two tasks, Molly will have to use the WshShell object's LogEvent() method and the Windows Net Send command.

Summary

In this chapter, you were introduced to a new project case study. You were provided with an overview of the information that Molly collected from the operations department, as well as the list of project requirements. A preliminary high-level design for the project was developed, and the major VBScript language constructs that will be required to develop each of the VBScripts that make up this project were briefly outlined and explained. In the four chapters that follow, you will get the chance to work on the development of all of the VBScripts.