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
1: Create a Calculated Field with the
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
In this article, we will continue
with the steps required to import the file into MS Access, including the
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
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
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.
the Exported Log Data to MS Access
Let's start MS Access
and proceed, taking the following steps:
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.
Click the icon
to start MS Access.
Access opens, and may display the initial dialog. If so, close it.
-> New from the top menu.
Database... from the options that are available.
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
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.
into the File Name box of the dialog, after navigating to a place to put
The File New
Database dialog appears as depicted in Illustration 2.
Illustration 2: The File New Database Dialog
Click the Create
The new database is created, and appears as shown in Illustration
Illustration 3: The New Database Appears
--> Get External Data from the main menu.
... 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.
Select the Text
Files option in the Files of Type selector at the bottom of the Import
the location of the exported dump file. (I placed mine, which I called 022004_app.txt, into the D:\temp folder on my computer).
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
Click 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.
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.
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.
Click the Advanced
Specification dialog for our file appears. Here we can manage the data
type conversions that are enacted upon the text fields we import.
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.
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.
Specification dialog, with our modifications, appears as shown in Illustration
Illustration 8: The Import Specifications Dialog
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.
following into the Specification Name box of the dialog:
App Log Import Specification
Import / Export Specification dialog appears as depicted in Illustration
Illustration 9: Saving the Import Specification
returned to the Import Specification dialog, which now bears the name
that we have saved.
to return to the second dialog of the Import Text Wizard.
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
fourth dialog of the Import Text Wizard appears next, as shown in Illustration
Illustration 11: Fourth Dialog of the Import Text Wizard
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.
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.
event, we will not make alterations here, but can do a quick review of our
settings before pressing onward.
Select the No
Primary Key radio button on the fifth dialog of the Import Text Wizard
that next appears, as depicted in Illustration 12.
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.
following into the Import to Table box:
final dialog of the Import Text Wizard appears as shown in Illustration
Illustration 13: Final Dialog of the Import Text Wizard
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,
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
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
to close the message box and arrive at the database.
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
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.
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.
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
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
Select File -->
Exit to leave MS Access as appropriate.
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
See All Articles by Columnist William E. Pearson, III