Report from or analyze your Windows Event Log data from MS Access.
Author Bill Pearson provides a step-by-step approach for preparing Event Log
data for import into Access.
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.
NOTE: To derive the most benefit from this
article, obtain Elogdmp.exe, from the Windows 2000 Resource Kit (it
works for Server or Professional). The file is also available from other
sources, the CD can be ordered from Microsoft, and many files can be downloaded
from the Microsoft (and other) ftp sites. We will discuss the details of
accessing the utility within the article at the appropriate time.
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
The objective of this two-part article
is to discuss the creation and loading of an MS Access database with Event
Log data. Most of us have used the Event Viewer to view and
manipulate the sometimes-critical messages that accumulate in the Event Log,
regarding many aspects of our Windows 2000 network and machine operations.
While the Viewer is suitable for online follow-up of a specific event, as well
as a great starting point for troubleshooting of errors, system messages that
take us unawares, and any number of other daily as well as infrequent, occurrences,
it doesn't lend itself to easy analysis, or to the collection and reporting of
In this article, we will examine
one approach for moving the potentially valuable storehouse of data in the
Event Log to a data source from which we can report upon it or perform in-depth
analysis from a number of dimensions. As in all the articles within our series,
our intent is to examine ways that we can use MS Access to provide
sophisticated results to organizational information consumers. While we will be
diverting from our typical focus of financial information, it is easy to
understand how operational statistics can be useful to the organization as
well. Our article will address a means to this end, and accomplish the
Discuss the usefulness of manipulating Event Log data
within a database;
Discuss the Elogdmp.exe utility as an easy-to-use option
for exporting Event Log data to an MS Access database;
Perform a hands-on exercise using the utility to dump an Event
Log in preparation for its import into MS Access for Analysis and Reporting.
We will perform the steps involved in the creation of an Event
Log database, along with the import of the file we have prepared in Part I, in Part II of this lesson.
There are, as many of us know, several ways to accomplish
the objectives we are setting for Parts I
and II of this article. We will be using a straightforward approach
that can certainly be automated. The main idea is to gain an appreciation for
the concepts involved, after which the mechanics can be managed with the
relevance and finesse that only local customization can bring.
Preparing to Create a Database for Event Analysis and Reporting
Event Logs compose a highly valuable weapon in the arsenal of
Windows 2000 administrators. By default, a computer running a Windows
Server 2000 family operating system records events in three kinds of logs:
Application log: The Application log contains events logged
by applications or programs. For example, a database program might record a file
error in the application log. Application developers decide which events to
Security log: The Security log records events such as
valid and invalid logon attempts, as well as events related to resource use
such as creating, opening, or deleting files or other objects. For example, if
logon auditing is enabled, attempts to log on to the system are recorded in the
System log: The System log contains events logged by
Windows system components. For example, the failure of a driver or other system
component to load during startup is recorded in the System log. The
event types logged by system components are predetermined by the server.
Other logs can appear on a computer, depending upon the
services that are hosted. Domain controllers carry two additional logs, the Directory
Service and File Replication logs, and Domain Naming System (DNS)
servers host an additional DNS Server log.
We will focus primarily on the Application, Security
log in this article, as it resides on virtually any Windows 2000 Server family
machine. From the perspective of this article, however, the procedures we
undertake to export the Application log would be the same for additional logs,
should they exist and be populated.
would agree that recurring scans of the logs, at a bare minimum, are another "cost
of doing business," in maintaining a well-tuned, trouble-free computing
and network environment. But the Event Viewer's interface, the Microsoft
Management Console, makes cursory browsing somewhat tedious, which often
causes administrators to focus on the serious "error" items, whose "red-circle-and-x"
icons cannot help but demand attention. At best, a few of the yellow "warning"
class might also be reviewed, given the time to drill down on each to read the
data presented. Informational events might be overlooked, or deferred,
particularly in the hectic climates of today, and where other serious
conditions might well be in progress
addition to its importance in alerting us to existing or potential problems,
the Event Log often provides essential guidance in troubleshooting
servers, clients, networks, applications, and a host of other areas within
which we might experience difficulties in operations. The logs, when examined
and analyzed as a whole, enable us to see, and investigate, security issues
such as system attack attempts, audit results and many others vital statistics.
However, when it comes time to perform serious analysis of the data in the log,
practitioners are often frustrated with the unwieldy approach of the Event
Viewer in the Management Console.
Viewer provides a reasonable interface from which to view, filter and
search event logs. However, it does not provide the capability to automate
the export of a complete event log to another application, such as a database. We
can export the different log types individually in a manual process, but this
presents a time consuming effort. Illustration 1 depicts the Event
Viewer interface on a combined domain controller / DNS Server, with the Properties
page for an example event assuming the focus after the event is double-clicked
in the Viewer.
Illustration 1: All Dressed Up with Nowhere to Go ...
Limited Export Features in Event Viewer
menu clicks necessary to export the contents of a log are straightforward
enough: We simply highlight the (single) log we wish to export, and then
select Action --> Export List, whereupon we are prompted for
the location to which we wish to export the file. (The clipboard icon on the Properties
window saves the associated event's details to the clipboard, for an even more
manual process of pasting elsewhere). This is simple, but requires manual
intervention at any time we require export to take place.
capability to fully export log files might be particularly useful when we need
to explore the logs in detail for troubleshooting. Support teams for many
applications request that you send them the user.dmp and drwtsn32.log files, in
addition to parts or all of the Event Log, often filtered for a certain
window of time before a crash, etc. or when tracking down a potential security
breach. It is also useful for generating reports. Automation of log export
would truly be a luxury, but, somewhat surprisingly, the Event Viewer
offers no way to schedule the export process.
Microsoft has seen fit to address this need with various tools in the past and
today. For Windows 2000, the tool specifically designed for this purpose is the
Event Log Query tool.
The Event Log Query Tool
Microsoft Windows 2000 Server Resource Kit is the official home for the Event Log Query tool, otherwise known as Elogdmp.exe ("Elogdmp"). Elogdump requires a bit of practice to make it fit the needs of some, but it readily fills our immediate objective of exporting the data to a dump file we can easily import to MS Access. With the investment of a little development time, a macro / script can be assembled to call the tool for regularly scheduled exports of the logs to meet the needs of the organization.
Elogdmp is a command-line tool that sends its output,
the contents of the Event Log, to a PC Screen or to a file. The logs of
a remote or local computer, whose identity is designated in the utility's
syntax (see the next section for details) can be "dumped" using the
tool, to a location specified in the syntax. Search of the output data is easy,
as it is generated as a comma-delimited text file - a fact that also makes it
readily importable into other applications.
The data that appears in the Elogdmp file includes
the information depicted in Table 1.
Table 1: Event Log Information
The date the
event occurred, stored in Universal Time Coordinate (UTC).
time the event occurred, stored in UTC.
The software logging the event, which can be a program
name or the identifier of a system component / subcomponent of a larger
Event severity classification:
Application and System Logs:
- Success Audit
- Failure Audit
Primarily used in the Security log, the Category
classifies events by event source. Often used as a grouping mechanism
for event types that can be subjected to success / failure auditing in
Windows 2000 Group Policy.
NOTE: Dumped log files contain "Something"
or "None" in this field, and are thus not very useful.
The particular event type for the respective source,
identified by a number. The name of the event type is typically included in
the first line of the description (often useful to support representatives,
etc., using Source and Event data together, in troubleshooting
The name of the user on whose behalf the event occurred: the
client ID if the event was caused by a server process or the primary ID if
impersonation is not taking place. Where appropriate, Security log
entries contain both primary and impersonation IDs, when the server allows a
process to assume the security attributes of another.
The name of the computer upon which the event took place.
Additional message details that sometimes appear in the
dumped log file, depending upon the event.
For those of us using it for the first time, Elogdmp.exe
can be found in a couple of places, depending upon whether you installed the Windows
2000 Resource Kit, or if you simply have the CD and do not wish to install
the full kit on the computer. In the former case, the file can be located on
the hard drive of the computer upon which the Resource Kit was installed
(the search facility can be used, obviously, if you do not recall the location),
in the location chosen at installation time. An example is partially shown in Illustration
Illustration 2: Locating Elogdmp.exe on a Computer with the
Resource Kit Installed
Elogdmp.exe can also be extracted from a file on the Windows
2000 Resource Kit, called compmgmt.cab, depicted in Illustration
Illustration 3: The Compmgmt.cab on the CD, Home of Elogdmp.exe
We can be easily access our target in this compressed file
via the more recent versions of WinZip, Once the contents of the .cab file are
exposed, as partially depicted in Illustration 4, we can extract the
file to any destination we choose.
Illustration 4: Preparing to Extract Elogdmp.exe from Compmgmt.cab
on the CD
While any user on the network can use Elogdmp to view
the contents of the Application log on any remote computer on the
network (assuming basic access, etc., privileges), membership within the Domain
Administrators / Administrators group on the computer is required to take
advantage of opportunities to use Elogdmp as a remote administration tool
to view the contents of a remote computer's System or Security
The command-line syntax for Elogdmp is simple, and is
structured as follows:
elogdmp [-?] computername eventlogtype [> export
The components of the syntax, together with amplifying
comments, appear in Table 2.
Table 2: Elogdmp Syntax Components
Prompts for display of command-line
The name of the computer against
whose log files we are running the export process. Elogdmp accepts:
Some DNS names, with preceding
backslashes not required
The event log type to display.
(If the name of the log contains a
space, enclose it in quotation marks)
The file name and location to which we wish to
redirect export output
The following example would export the contents of the Security
log, as it exists upon a server named ELIAS; the output file, named 021404_Security.txt
would be redirected to the EventLogs directory on the D: drive.
elogdmp ELIAS Security
For the details surrounding additional functionality with Elogdmp,
such as its filtering and error reporting capabilities, consult the
documentation that ships with the Windows 2000 Resource Kit. For now, we
will practice with using Elogdmp to create a dump file, whose ultimate
destination will be an MS Access database.
Practice: Using the Tool to Export the Event Log
To put Elogdmp into action, we have only to open a Command
Prompt and issue the appropriate syntax. We will create a dump file for
our destination database by taking the following steps:
Go to the Start button on the PC, and then navigate to Programs
--> Accessories --> Command Prompt, as shown in Illustration
Illustration 5: Open a Command Prompt Window
Note: There are numerous ways of launching the Command
Prompt. Select the way that you prefer.
Prompt to open the prompt.
Prompt window opens.
directory housing Elogdmp.exe (the location depends upon where you chose
to install the Windows 2000 Resource Kit, or where you placed the
individual file after extracting it or otherwise placing it), type the
following into the Command Prompt:
[Directory Housing the Event Log Tool]> Elogdmp [ComputerName] application
> [Full file name you wish for the file]
I used the
following syntax on my computer.
D:\Program File\Windows 2000 Resource Kit>Elogdmp ELIAS application
The command prompt window on my PC
appears as depicted in Illustration 6.
Illustration 6: The Syntax at the Command Prompt
dump file is created instantaneously. If this is not the case, go back and
check your typing, particularly the validity of the information that is
contextually specific to your local computer.
Go to Windows
Explorer and navigate to the folder into which you directed the Elogdmp output.
specified that my file be called 022004_app.txt, and that it be placed within the D:\temp folder
on my machine. Upon navigating to the folder, I see that the file has indeed
been created, as shown in Illustration 7.
Illustration 7: The Output File Appears
Close the Command
Prompt when ready.
If we wish to do so, we
can certainly open the file with Notepad.exe, or any number of other text
editors. The file we have created will become the data source for our new Event
Log database, and will be used in both its creation and population, as we
will see when we move into the import stage in Part II of this article.
this, Part I of a two-article lesson, we diverted from our typical focus
of working with financial information in MS Access, and set our sights on using
the RDBMS in a different role: the support of operational analysis and
reporting with the wealth of statistics that can be obtained from the Event
Log of the Windows operating system. After introducing the Event Log
and discussing the data that it contains, we set about meeting our objective of
creating an MS Access database and populating it with Event Log data.
We discussed the usefulness of manipulating Event Log
data within a database, and then introduced the Elogdmp utility as an
easy-to-use option for exporting Event Log data. Finally, we performed a
hands-on exercise using the utility to dump an Application log in
preparation for its import, in Part II, to an MS Access database for
Analysis and Reporting.
See All Articles by Columnist William E. Pearson, III