Read an Excerpt
Microsoft Log Parser Toolkit
By Gabriele Giuseppini Mark Burnett
SyngressCopyright © 2004 Syngress Publishing, Inc.
All right reserved.
Chapter OneIntroducing Log Parser
Scripts and Samples in this Chapter:
* Retrieving fields from the Event Log * Searching for Information * Ordering the Output * Top-Ten Lists * Parsing Text Files * Querying System Information * Parsing NetMon Files * Implementing Custom Input Formats * Using the DATAGRID Output Format * Creating Custom Text Files * Creating Charts * Uploading Query Results to a SQL Database * Converting Log File Formats
In This Toolbox
Log files—records of events that have occurred in a system—often contain information deemed crucial to a business. However, most times log files can be so large that it becomes almost impossible to extract useful data or find meaningful patterns in the amount of information that they provide. The Log Parser tool allows users to treat log files and other information as SQL tables, the rows of which can be queried, processed, and formatted in different ways. The power of the SQL language, together with the variety of data that can be processed by Log Parser, make it possible to extract specific information, calculate statistics, and generate reports from large amounts of data.
A Brief Background
The Log Parser tool was born around 2000 as a utility to test the logging mechanisms of Microsoft's Internet Information Services (IIS). The first inception of the tool allowed users to retrieve and display all the fields from a single log file in any of the three text-logging formats supported by IIS.
As tests became more complicated, the need arose for more specialized tasks, including the possibility to filter log entries matching specific criteria and to sort the resulting entries according to values of specific fields. To provide a succinct and well-established way to specify these operations from a command-line shell, the tool underwent its first major makeover and began to support a very limited dialect of the SQL language. Log Parser 1.0 was born, and its use began spreading among internal Microsoft users and product support analysts.
After some time, the SQL language dialect processor was completely redesigned, enriched with functions, aggregate functions, and the GROUP BY clause, and with improved performance characteristics; at the same time, the tool underwent a second major architectural makeover, which separated the log file parsers and the console output formatter from the core SQL engine, making it possible for generic "input formats" and "output formats" to easily plug in with the new architecture. Just for fun, the first non-IIS input formats made their appearance, including the Event Log (EVT) input format and the File System (FS) input format, together with the first nonconsole output format, the SQL output format. It didn't take long for IIS program managers to notice that the tool's capabilities could be leveraged by end users as well, greatly simplifying most of the tasks related to processing of log files. In fact, in November 2001 Log Parser 2.0 made its public debut as a freely downloadable tool from the pages of www.microsoft.com.
The user response was so favorable that the new 2.1 version of the tool, which included many new input and output formats, was included in the IIS 6.0 Resource Kit Tools, published in April 2002.
The tool has been continuously improved since then, thanks especially to the feedback and suggestions of its many users; new input and output formats have been added, and the core SQL engine has been improved with new functions and better-performing algorithms.
This 2.2 version marks the latest release of the Log Parser tool, designed and engineered with the vision of helping users achieve their data-processing goals in a simple, fast, and powerful way.
With the Log Parser tool, users can run queries written in a dialect of the Structured Query Language (SQL) on data coming from different sources. The data processed by the queries is provided by input formats; these are data processors that enumerate information from different sources and provide the data as input records on which the queries operate. Once a query is executed, it generates output records, which are handed to output formats—data processors that consume output records, format data, and send them to different targets.
Retrieving Fields from the Event Log
For our first example of a Log Parser command, we'll start with a simple, basic task: retrieving some fields from the Event Log.
To run this command, start a command-line shell, navigate to the folder where the LogParser.exe command-line executable has been copied or make sure that the folder is in the system path, and type the following command:
C:\>LogParser -i:EVT -o:NAT "SELECT TimeGenerated, SourceName FROM System"
The output will look like this:
18:48:04 EventLog 2004-04-18 18:48:04 EventLog 2004-04-18 18:48:27 Service Control Manager 2004-04-18 18:48:27 Service Control Manager 2004-04-18 18:48:27 Service Control Manager 2004-04-18 18:48:27 Service Control Manager 2004-04-18 18:48:27 Service Control Manager 2004-04-18 18:48:27 Service Control Manager 2004-04-18 18:48:27 Service Control Manager 2004-04-18 18:48:27 Service Control Manager Press a key ...
Let's analyze this command in detail. The first argument is -i:EVT, which tells Log Parser that we want to use the EVT input format—that is, the input format that enumerates the events in the Windows Event Log. In fact, the -i parameter is used to indicate which input format we want our query to operate on.
The second argument is -o:NAT, which tells Log Parser that we want to use the NAT output format—that is, the output format that displays the query results in a readable, nicely formatted way. In fact, the -o parameter is used to indicate which output format we want our query results processed by.
The third argument is our SQL query. In this example, the query is a very simple one: The SELECT clause specifies which of the input record fields we want to be handed to the output format, and the FROM clause specifies where we want to retrieve the input records from.
In our example, we have specified that we want to retrieve two fields: the TimeGenerated field and the SourceName field. These are just two fields out of the 15 fields exported by the EVT input format. We can see what fields are exported by the EVT input format by typing the following help command:
C:\>LogParser -h -i:EVT
The output of this help command displays some useful information on the EVT input format, which includes a list of the exported fields:
EventLog (S) RecordNumber (I) TimeGenerated (T) TimeWritten (T) EventID (I) EventType (I) EventTypeName (S) EventCategory (I) EventCategoryName (S) SourceName (S) Strings (S) ComputerName (S) SID (S) Message (S) Data (S)
The FROM clause that we have used specifies System as the data source that we want the EVT input format to retrieve records from. This means that we want to retrieve records from the System Event Log; if we wanted to retrieve records from the Security Event Log, we could have specified Security in the FROM clause. If we wanted to retrieve events from both the System and Security Event Logs, we could have specified both in our FROM clause, using a comma character as a separator, this way:
C:\>LogParser -i:EVT -o:NAT "SELECT TimeGenerated, SourceName FROM System, Security"
When we execute commands using the NAT output format, we can see that the format displays 10 lines and then prompts the user to press a key before displaying the next 10 lines. This behavior can be overridden by specifying a value for the rtp parameter of the NAT output format.
Most of the Log Parser input and output formats accept parameters that are used to fine-tune their behaviors to match our needs. The rtp parameter of the NAT output format specifies the number of rows to print before prompting the user to press a key. If we want to change the value of this parameter to 5, for example, we would type the following command:
C:\>LogParser -i:EVT -o:NAT -rtp:5 "SELECT TimeGenerated, SourceName FROM System"
Specifying -1 for this parameter disables the prompt altogether and causes the NAT output format to display all the output records without interruption.
We can see a description of all the parameters supported by an input format by typing the input format help command that we have used before:
C:\>LogParser -h -i:EVT
We now have a command that displays selected fields from the Event Log to the console output. What if we want to write this output to a file instead?
Well, forget command-line redirection. In the Log Parser SQL-centric world, this can be accomplished using the INTO clause of the SQL language:
C:\>LogParser -i:EVT -o:NAT "SELECT TimeGenerated, SourceName INTO myoutput. Txt FROM System"
This command causes the NAT output format to write its output to the myoutput. Txt file instead of the console output. When an INTO clause is used, the NAT output format disables the user prompt between output records and writes all the output records to the file without interruption. When a query does not include an INTO clause, the NAT output formats assumes a default INTO clause with a value of STDOUT—that is, the console output.
The commands that we have used in these examples can be simplified by leveraging a couple of shortcuts. When the FROM clause in a query specifies a well-known value, Log Parser is able to automatically recognize the input format that should handle the FROM clause. This is true for the System or Security values we have used so far in our examples: Log Parser recognizes that System or Security are commonly used names of Event Logs and automatically selects the EVT input format for us.
The same applies for output formats: If a query does not include an INTO clause, Log Parser automatically selects the NAT output format. This means that we could rewrite our example command as follows:
logparser.exe file: "SELECT TimeGenerated, SourceName FROM System"
Thus we obtain the same effect as the fully specified command in our first example.
Retrieving Fields from IIS Logs
The simple command that we have used to display Event Log fields can be easily modified to display fields from an IIS Web log file. To do so, we need to first change the input format to the input format responsible for parsing the IIS logs in their currently configured format. Assuming that our IIS Web server is configured to log using the W3C Extended Log File Format, we need to pick the IISW3C input format.
Then we need to decide which of the IISW3C fields we want to retrieve. Again, we can see what fields are exported by this input format using the following help command:
Let's assume that we are interested in the date, time, and cs-uri-stem fields.
Finally, we need to change our FROM clause to specify the IIS log files that we are interested in parsing. We can do so in two ways: by specifying the paths to the log files or by specifying the ID number of a virtual IIS site whose logs we want to parse. Assuming that we want to parse the log files of the site with ID 1 (the default Web site), our command looks like the following:
C:\>LogParser -i:IISW3C -o:NAT "SELECT date, time, cs-uri-stem FROM <1>"
The output will look like this:
date time cs-uri-stem
2003-11-18 00:28:33 /Default.htm 2003-11-18 00:28:33 /style.css 2003-11-18 00:28:33 /images/address.gif 2003-11-18 00:28:33 /cgi-bin/counts.exe 2003-11-18 00:28:42 /gorice/rulesinfo.nsf 2003-11-18 00:28:42 /gorice/rulesinfo.nsf 2003-11-18 00:28:42 /gorice/rulesinfo.nsf 2003-11-18 00:28:42 /gorice/rulesinfo.nsf 2003-11-18 00:28:42 /gorice/rulesinfo.nsf 2003-11-18 00:28:42 /gorice/rulesinfo.nsf Press a key ...
Excerpted from Microsoft Log Parser Toolkit by Gabriele Giuseppini Mark Burnett Copyright © 2004 by Syngress Publishing, Inc.. Excerpted by permission of Syngress. All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.