MS Access for the Business Environment: Analyze and Report from the Windows Event Log, Part II

Monday Apr 5th 2004 by William Pearson
Share:

Import Event Log data into an MS Access Database for Analysis and Reporting. Bill Pearson continues a step-by-step approach for creating an MS Access database populated with Windows Event Log data.

About the Series ...

This article continues the series, MS Access for the Business Environment. The primary focus of this series is an examination of business uses for the MS Access relational database management system. The series is designed to provide guidance in the practical application of data and database concepts to meet specific needs in the business world. The majority of the procedures I demonstrate in this article and going forward will be undertaken within MS Access 2003, although most of the concepts that we explore in the series will apply to earlier versions of MS Access, as well.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see Tutorial 1: Create a Calculated Field with the Expression Builder.

Introduction to this Tutorial

This article, the second of a two-part lesson, continues the topic of creating and loading an MS Access database with the data contained in the Windows 2000 Event Log. We discussed in Part I the fact that the Event Viewer, the interface from which we typically view and manipulate sometimes critical messages regarding many aspects of our Windows 2000 PCs and server operations, doesn't lend itself to easy analysis or the collection and reporting of statistics.

In this lesson, we will pick up where we left off in Part I, with the import of an example log into an MS Access database. In Part I, we examined the usefulness of an export utility supplied in the Windows 2000 Resource Kit for dumping a selected component of the Event Log to a text file, after discussing the nature of the Event Log and the logs it contains, as well as the data contained in the entries that accumulate in those logs. We discussed the Elogdmp utility as an easy-to-use option for exporting Event Log data to an MS Access database, examining aspects of its use. Finally, we performed a hands-on exercise using the utility to dump an Application log.

In this article, we will continue with the steps required to import the file into MS Access, including the following:

  • Import of the Application log that we exported in Part I with the Elogdmp utility;
  • Establishment of specifications for handling the data types of various components in the dump file;
  • Creation and population of an MS Access database in a multi-step process;
  • Discussion of the use of the error table generated by MS Access as a part of the import operation, together with options for avoiding errors that might be found;
  • Discussion of potential uses for the new Event Log database, as well as options for automation of the concepts involved in its creation.

Creating a Database for Event Analysis and Reporting

We concluded Part I of this article with an exported text file, containing the data from the Application log, which we had generated using the Elogdmp utility within a command prompt window. We now have a data source to import, and upon which to form a database within MS Access.

Practice: Loading the Exported Log Data to MS Access

Let's start MS Access and proceed, taking the following steps:

1.  Go to the Start button on the PC, and then navigate to the Microsoft Office Access icon, as we did in the first lesson of this series, Create a Calculated Field with the Expression Builder.

2.  Click the icon to start MS Access.

MS Access opens, and may display the initial dialog. If so, close it.

3.  Select File -> New from the top menu.

4.  Select Blank Database... from the options that are available.

The selection can be made from the Task Pane in MS Access 2003, as shown in Illustration 1, among other ways. Depending upon your version of Access, this may differ.


Illustration 1: Select Blank Database ...

The File New Database dialog appears. Here we give the new database a name and designate where we wish to place it.

5.  Type Event_Log into the File Name box of the dialog, after navigating to a place to put the database.

The File New Database dialog appears as depicted in Illustration 2.


Illustration 2: The File New Database Dialog

6.  Click the Create button.

The new database is created, and appears as shown in Illustration 3.


Illustration 3: The New Database Appears

7.  Select File --> Get External Data from the main menu.

8.  Select Import ... from the cascading context menu, as depicted in Illustration 4.


Illustration 4: Select Import ...

The Import dialog appears, from which we can select the file to import into the new database.

9.  Select the Text Files option in the Files of Type selector at the bottom of the Import dialog.

10.  Navigate to the location of the exported dump file. (I placed mine, which I called 022004_app.txt, into the D:\temp folder on my computer).

11.  Select the exported log file in the File Name box of the Import dialog, as shown in Illustration 5.


Illustration 5: The Import Dialog - Dump File Selected

12.  Click the Import button.

The Import Text Wizard dialog appears, with part of the contents of our log file appearing as a sample, and the Delimited radio button selected by default, as depicted in Illustration 6.

Click for larger image

Illustration 6: The Import Text Wizard with Sample Data

13.  Click Next.

We arrive at the second dialog of the Import Text Wizard, where we can view a preemptory layout of the file, with the default selection of Comma as delimiter type, and the quotation mark (") as the Text Qualifier. We can scroll over and down to ascertain that the delimiting scheme is adequate for our immediate needs. The second dialog of the Import Text Wizard appears as shown in Illustration 7.

Click for larger image

Illustration 7: Second Dialog of the Import Text Wizard - Delimitation Scheme

Because we wish to be able to use "dates as dates" and "times as times" in our analysis of the log data later, we can ensure that "translation" of the imported data to the correct data types occurs here. This will save us the task of changing the data types (they would largely be imported as text, by default) after creation of the table.

14.  Click the Advanced button.

The Import Specification dialog for our file appears. Here we can manage the data type conversions that are enacted upon the text fields we import.

15.  In the Field Information table in the lower half of the Import Specification dialog, type the titles from each Data Element column of Table 1 above into the respective Field Name column.

16.  Change the Data Type to Date/Time for the first two rows, Date and Time, using the selector in the respective Data Type fields. Leave the settings for this field at their defaults for the remaining rows.

The Import Specification dialog, with our modifications, appears as shown in Illustration 8.


Illustration 8: The Import Specifications Dialog

17.  Click Save As.

The Save Import / Export Specification dialog appears. This offers us the opportunity to save our data type specifications for re-use in subsequent imports of the log file we are importing.

18.  Type the following into the Specification Name box of the dialog:

       App Log Import Specification

The Save Import / Export Specification dialog appears as depicted in Illustration 9.


Illustration 9: Saving the Import Specification

19.  Click OK.

We are returned to the Import Specification dialog, which now bears the name that we have saved.

20.  Click OK to return to the second dialog of the Import Text Wizard.

21.  Click Next.

22.  Ensure that the In a New Table radio button is selected in the third dialog of the Import Text Wizard that next appears, as shown in Illustration 10.


Illustration 10: Preparing for Creation of a New Table

23.  Click Next.

The fourth dialog of the Import Text Wizard appears next, as shown in Illustration 11.


Illustration 11: Fourth Dialog of the Import Text Wizard

At this point, we could have selected data types and field names, had we not created the specification in advance. The point at which we did our alterations allowed us to save the specification, whereas this dialog serves better as a place to do this on a "one-off" basis. Indeed, the Advanced button here takes us back to the Import Specification dialog where we accomplished our settings earlier.

The fourth dialog of the Import Text Wizard might also serve as a means to "make exceptions" in future imports by allowing us to make data type changes, leave fields out altogether, and so forth, "on the fly" for the duration of that single import action.

In any event, we will not make alterations here, but can do a quick review of our settings before pressing onward.

24.  Click Next.

25.  Select the No Primary Key radio button on the fifth dialog of the Import Text Wizard that next appears, as depicted in Illustration 12.

Click for larger image

Illustration 12: Fifth Dialog of the Import Text Wizard, with Setting

26.  Click Next.

We arrive at the sixth, and final, dialog of the Import Text Wizard. Here we are given an opportunity to name the new table we are about to create, all within the import process.

27.  Type the following into the Import to Table box:

Event Log

The final dialog of the Import Text Wizard appears as shown in Illustration 13.


Illustration 13: Final Dialog of the Import Text Wizard

While we are importing only the Application log in our practice example, we might consider combining all imported logs into a single table for more integrated event analysis. This would involve modifications to the table to make allowance for slight differences in log layouts, as well as to accommodate and identify the different logs through the inclusion of a field that would identify the source log (Application, Security, System, etc.)

On the other hand, we might have a reason for creating separate tables based upon the source log involved. The needs analysis that we would perform prior to design and creation of the database would dictate the considerations specific to our environment.

28.  Click Finish.

The log file is rapidly imported, and we next see a message box warning us of errors. The message box also describes the presence of a second newly-created table, where we can find further information about the import errors. The message box appears as shown in Illustration 14.


Illustration 14: Message Box Warning of Import Errors

29.  Click OK to close the message box and arrive at the database.

We see that, in addition to our new Event Log table, an ImportErrors table has been created (mine is called 022004_app_ImportErrors, as shown in Illustration 15). The table name is created by MS Access, and is composed of a composite of the original file name and the term "ImportErrors."


Illustration 15: The Two New Tables in the Database

The Errors table reveals that the issues lie with the last field in the dump file. A review of a few dump file rows, selected from those identified in the Errors table as having issues, reveals that the primary problem is "nonparseability" of the right-most field in the file, Details. This is due, in some cases, to the fact that it is empty, although the quotation marks appear with nothing between them; more commonly, the often lengthy Details field contains characters beyond the final "closing" quotation marks, which thus cannot be "seen" correctly by MS Access.

We might work around this by writing a custom macro or script to handle this facet of the data in subsequent imports, perhaps even in a multi-step approach. Alternatively, we might not need the characters that we determine are almost universally omitted in our current import process. If this is the case, the results we have obtained will be sufficient to meet our needs.

Another consideration might be to leave out the Details column entirely, and thus eliminate the cause of the error, by simply telling the Import Wizard, at the point of completing the Import Specification dialog, that we wish to Skip the field entirely in the import. This could be accomplished by placing a checkmark in the "Skip" checkbox of the Import Specification dialog, as shown in Illustration 16.


Illustration 16: Skipping Import of an Unneeded Field in the Dump File

Whatever our solution for the above, we have successfully created an MS Access database containing our Event Log data at this stage. I have used databases such as this as a data source for creating reports (using Excel, MSSQL Server Reporting Services, Crystal, Cognos Impromptu, MicroStrategy and many others), as well as for cube models in Microsoft Analysis Services, Cognos PowerPlay, and others. The Event Log data source can serve our analysis needs either standalone, as we leave it here, or as part of a much larger analysis database, that contains tables that house data from a host of other logs and sources. The concept of importing these logs is the same; get them to a file format that is accessible to Access and import them.

Automation can make the combination / recurring import of the individual Event Logs an automatically recurring, scheduled evolution. Moreover, the potential uses of the data that we might access this way can be extended to include not only troubleshooting and review processes, but also monitoring system performance within the context of load-balancing, isolation of areas that might need software / hardware upgrades, and many other procedures.

30.  Select File --> Exit to leave MS Access as appropriate.

Conclusion ...

In this article, the second of a two-part lesson, we continued the process we began in Part I, continuing our objective of creating and loading an MS Access database with the data contained in the Windows Event Log. We began with the import of the Application log that we exported in Part I with the Elogdmp utility that we obtained from the Windows 2000 Resource Kit. We established specifications for handling the data types of various components in the dump file as we created and populated an MS Access database in a multi-step process. We discussed the use of the error table generated by MS Access as a part of the import operation, and touched upon options for avoiding the errors we obtained. Finally, we again discussed potential uses for the new Event Log database, as well as the fact that the steps we took in our practice example focused on concepts that can be automated for recurring, scheduled operations with local customization.

» See All Articles by Columnist William E. Pearson, III

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved