This chapter marks the beginning of a new project case study in which you will get to observe how Molly Masterson, a new programmer at Intuit Mechanical Tools, uses VBScript to improve the reporting process for an in-house developed order/inventory system. As you work through this chapter and the four chapters that follow, you will get the opportunity to see how to develop VBScripts that perform a number of tasks, including:
Intuit Mechanical Tools is a small company that manufactures high-powered computerized mechanical tools and supplies. Its products are commonly used to build large chemical processing facilities. The company uses an in-house developed order/inventory application to track all product sales and returns. The system also tracks current inventory levels and maintains an error log where program and hardware error messages are recorded. The order/inventory system is a distributed application written mostly in C and C++.
Unfortunately, things have been rough for Intuit Mechanical Tools over the last five years. While the number of competing firms has steadily grown, profits have been steadily declining. As a result, many of the application programmers that developed the company's order/inventory system have left the company looking for greener pastures. Today, a limited staff of three programmers remains, including Molly Masterson.
Because of the small size of their group, the programmers at Intuit Mechanical Tools are kept very busy. Recently things have become so bad that they have started to fall behind in their workload. Management has requested that they look for ways to streamline their daily activities in order to free up time for a new application that the company wants to develop for a computerized cooling system.
One of the daily tasks that Molly has been assigned to perform is the manual review and consolidation each morning of four application files generated by the order/inventory application into a report for management to review. Although not particularly complicated, it takes Molly over an hour each day to perform this task. In order to help streamline her workload, she has suggested automating this process.
At first she thought about modifying the in-house application, but decided that it would take too much time, effort, and testing to automate the functionality that she was looking for using C and C++. What she wants is a quick and easy way to develop an add-on process that she can create with a minimum level of work and that will be easy for other members of her team to support. Ultimately, she decided that VBScript is the right tool for the job. Like the other programmers on her team, Molly is already somewhat familiar with Visual Basic. She figures that her learning curve for VBScript will be relatively small. She also knows that as a scripting language, VBScript is well-suited to rapid application development and for filling in holes in applications that do not justify a lot of development time (and hence often go undone).
Unfortunately for Molly, though management approved her new VBScript project, they did not take any work off of her plate in order to provide her with additional time to work on the project. This means that she will have to develop her solution quickly, so as not to backlog her current workload any further.
To begin, Molly decides to take a few moments to document the existing manual process that she goes through every day. By the time she is done, Molly has broken the process of analyzing and consolidating the order/inventory system's reports down into the following steps.
In addition, Molly keeps a one-month minimum history of log and report files. She does this by logging on the Windows 2000 Server, where the report and log files reside, on the first business day of each new month and deleting all files other than those for the current and previous months.
Examining the Error Log
As the order/inventory application runs each day, it reports any hardware- or software-related errors that are encountered to a log file called Error.log. This file, an example of which is shown in Figure 16.1, is simply a plain text file. All error messages are made up of several pieces of information, as outlined below.
Figure 16.1: Error messages written to Error.log are date- and time-stamped and include an error code and a description
When Molly reviews this report, she generally looks only for errors that have been assigned a code 1, 2, or 3 severity. She then cuts and pastes these errors as the first entries in the new daily report that she is responsible for creating.
Examining the Daily Sales Report
The first report that Molly usually examines is the Daily Sales report, which is named DailySales.txt. An example of a typical DailySales.txt report is shown in Figure 16.2.
Figure 16.2: The Daily Sales report consists of detailed and summary information
When reviewing this report, Molly copies the information located at the bottom of the report in the Sales summary by part number section to her new report.
Examining the Daily Returns Report
When reviewing the Daily Returns report, an example of which is shown in Figure 16.3, Molly focuses on the summary information provided at the bottom of the report. She copies this information into the summary report that she is creating.
Figure 16.3: The Daily Returns report provides both line-item details and a summary analysis
Examining the Daily Production Report
The fourth report that Molly reviews each morning is the Daily Production report, which is called DailyProduction.txt. This report breaks the previous day's production work down into two separate groups, Hardware and Supplies. Molly copies information from both sections into the new summary report.
Figure 16.4: The Daily Production report breaks information down into multiple categories
In order to ensure that management is completely aware of the scope of the project that she is about to start and to ensure that she has their complete approval, Molly decides to generate a list of requirements that her project would meet. This list is outlined below.
After completing this list, she e-mails it to her boss for approval. He then forwards it on to Hank, the operations manager. Although hesitant at first, Hank agrees to assign someone on his staff to the task of retrieving and printing the consolidated summary report. Hank then forwards the e-mail on to the night shift operations supervisor as notification that in a few weeks, this task will need to be added as a step to the nightly checklist that the operations staff perform on the server.
After getting the formal approval from her manager and from Hank, and after studying the process that she goes though each day to generate the summary sales report for the operations manager, Molly begins work on a high-level design. She determines that she can accomplish her assignment by developing a series of small VBScripts, each of which will be responsible for performing a distinct task. Molly then produces a list of tasks that will need to be performed and groups them together, as shown in Table 16.1.
Type of Task
Develop a single INI file that will store configuration information used to control the execution of scripts that process each of the order/inventory system's report and log files.
Develop a scripted process for reading and processing INI configuration file settings.
Develop a VBScript log analyzer to read and process the contents of the Error.log file.
Develop a VBScript log analyzer to read and process the contents of the DailySales.txt file.
Develop a VBScript log analyzer to read and process the contents of the DailyReturns.txt file.
Develop a VBScript log analyzer to read and process the contents of the DailyProduction.txt file.
Schedule the execution script that processes the reports and log files.
Schedule the execution of a monthly VBScript that maintains a month-long archive of report and log files.
Develop a VBScript that, when executed, ensures that a month's worth of log and report files are maintained on the Windows 2000 Server where they are generated.
Rather than embed script execution settings inside each VBScript that she will be developing, Molly wants to create a single INI file and store as many settings as possible there. This way, some of the changes to the scripts that operations might one day ask of her can be made by modifying the INI file instead of the scripts. This will help make configuration changes easier to perform and eliminate opportunities for accidentally introducing errors in scripts that would otherwise have to be manually edited.
Molly is still in the process of determining the exact format and content of the INI file. For now, she knows that she wants to create a section in the INI file for each of the log analyzer scripts, as demonstrated in Figure 16.5.
Figure 16.5: A rough outline of how the INI file that Molly is currently developing will look
As you can see, Molly has started adding settings in the first section in the INI file. These settings and their purpose will be covered in detail in Chapter 17, "Using Configuration Files to Control Script Execution."
In order to develop a VBScript procedure to process the INI file, Molly will have to learn how to work with the VBScript run-time FileSystemObject. In particular, she will need to learn how to use a number of its methods, including:
At first Molly toyed with the idea of creating a single script that could process each of the reports and log files created by the order/inventory application. However, distinct differences in each report would make this a cumbersome development task, so she has decided instead to develop a log analyzer for each file. One benefit of this approach is that she can come back and modify a single log analyzer script without running the risk of making a modification mistake that would disable all of the log analyzers. This approach provides the most flexibility should the report and log files later grow in complexity and require further modification.
For the log analyzer that will process the Error.log file, Molly wants to be able to read the report and to be able to pull out errors based on their error codes. She plans to use the INI file to specify the level of alerts that will be reported on. For now, she plans on reporting only on level 1, 2, or 3 error messages.
The report analyzers for the DailySales.txt and DailyReturns.txt reports will be almost identical. Both will by default only extract summary reporting information as specified by settings stored in the INI file. However, the report analyzers for these two files will also be designed to support the extraction of detailed information should it become necessary.
By default, the report analyzer that will process the DailyProduction.txt file will be set up to consolidate and report on the detailed information located in both the Hardware and Supplies sections. However, by modifying a setting located in its section of the INI file, Molly can limit reporting to either the Hardware or the Supplies sections or to disable the reporting of both sections from this file altogether.
In order to develop the log analyzer scripts, Molly will have to learn how to work with the VBScript run-time FileSystemObject. In particular, she will need to learn how to use a number of its methods, including:
Molly also plans to provide these scripts with the ability to write messages about their operation to the Windows application event log. To perform this task, she will have to learn to work with the WshShell object's LogEvent() method.
Figure 16.6 shows an example of the summary report that Molly manually creates each month. As you can see, it consists of statements that she manually copied and pasted from each of the four log and report files.
Figure 16.6: A sample of a consolidated summary report
By automating the creation of this report using VBScript, Molly will eliminate a daily chore that she has been performing. In addition, she will eliminate the occasional errors that are sometimes made when manually creating the report, such as pasting data twice or not at all.
The Windows Task Scheduler service will be used to set up the schedule execution of the first log analyzer file, which will then run and call upon the second analyzer when it is done. By single threading script execution in this manner, Molly ensures that each script will be able to access the summary report file that is to be generated, without having to worry about whether another log analyzer is currently accessing it. The first log analyzer will create a new summary report and the rest of the analyzers will append their data to it when they execute.
In addition to the log analyzers, Molly will create a VBScript that runs once a month under the control of the Task Scheduler service in order to clear out old log, report, and summary files. Since the schedule of these scripts is a one-time task that needs to be performed only on one server, Molly will manually set up their scheduled execution using the Windows 2000 Scheduled Task Wizard.
Approximately two years ago, Intuit Mechanical Tools underwent a thorough audit of all its manufacturing and related processes. One of the results of this audit was a suggestion that a minimum of a month's worth of order/inventory reports and log files be kept on hand (to supplement the nightly backup and archival of data files on the Windows 2000 Server). Since that time, Molly has been instructed to log on to the Windows 2000 Server on the first business day of each month and to delete all but the previous month's report and log files.
In order to automate the execution of this task, Molly will have to work with the VBScript run-time FileSystemObject. In particular she will need to learn how to use a number of its methods, including:
Molly also plans to provide this script with the ability to write messages about its operation to the Windows application event log. To perform this task, she will need to use the WshShell object's LogEvent() method.
In this chapter, you were introduced to Molly Masterson and the challenge presented to her to streamline the reporting process for the order/inventory application at Intuit Mechanical Tools. In preparing to tackle this assignment, Molly has decided that VBScript and the WSH will provide her with the best tools for rapidly developing a new report summary generation and archive management process.
Part I - Introducing Microsoft VBScriptBasics
Part II - Professional Project 1 Desktop Administration Using VBScript and the WSH
Part III - Professional Project 2 Analyzing Application Logs
Part IV - Professional Project 3 Creating a Centralized Report Management Station
Part V - Professional Project 4 Reporting Application Summary Data via the Web
Part VI - Introducing Microsoft VBScriptBasics