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

Monday Mar 1st 2004 by William Pearson
Share:

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 see Tutorial 1: Create a Calculated Field with the Expression Builder.

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 statistics.

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 following:

  •   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 log.
  •   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 Security log.
  •   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.

Most 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

In 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.

The Event 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

The 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.

The 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.

Fortunately, 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.



Discussion



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.



Data Element

Description

 

Date

The date the event occurred, stored in Universal Time Coordinate (UTC).

 

Time

The time the event occurred, stored in UTC.

Source

The software logging the event, which can be a program name or the identifier of a system component / subcomponent of a larger program.

Type

Event severity classification:

Application and System Logs:

  • Error
  • Information
  • Warning

Security Log:

  • Success Audit
  • Failure Audit

Category

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.

Event

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 exercises).

User

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.

Computer

The name of the computer upon which the event took place.

Details

Additional message details that sometimes appear in the dumped log file, depending upon the event.


Table 1: Event Log Information

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 2.


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 3.


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 log.



Syntax



The command-line syntax for Elogdmp is simple, and is structured as follows:



elogdmp [-?] computername eventlogtype [> export filename]



The components of the syntax, together with amplifying comments, appear in Table 2.



Component

Description

 

[-?]

Prompts for display of command-line help

 

computername

The name of the computer against whose log files we are running the export process. Elogdmp accepts:

7  IP addresses

7  NetBIOS names,

7  Some DNS names, with preceding backslashes not required

 

eventlogtype

The event log type to display.

(If the name of the log contains a space, enclose it in quotation marks)

export filename

The file name and location to which we wish to redirect export output


Table 2: Elogdmp Syntax Components

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 > D:\EventLogs\021404_Security.txt

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:

1.  Go to the Start button on the PC, and then navigate to Programs --> Accessories --> Command Prompt, as shown in Illustration 5.


Illustration 5: Open a Command Prompt Window

Note: There are numerous ways of launching the Command Prompt. Select the way that you prefer.

2.  Click Command Prompt to open the prompt.

The Command Prompt window opens.

3.  From the 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   
 
> D:\temp\022004_app.txt

The command prompt window on my PC appears as depicted in Illustration 6.


Illustration 6: The Syntax at the Command Prompt

4.  Press ENTER.

The 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.

5.  Go to Windows Explorer and navigate to the folder into which you directed the Elogdmp output.

I 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

6.  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.

Conclusion ...

With 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

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