Basics of a Query

The most simple query that can be written with Log Parser specifies that all the Input Records generated by an Input Format are to be delivered to an Output Format with no intervening processing.

For example, let's assume that we want to visualize all the fields of all the events in the System Event Log. To perform this task, we first have to specify the EVT Input Format as the source of our input records, and we do so by using the "-i:EVT" command-line parameter.
Then, we can choose the NAT Output Format as the consumer of our output records, since this Output Format is specifically designed to print output records to the console window; we do so by using the "-o:NAT" command-line parameter. Finally, we specify the SQL query that performs the desired task; the complete command is as follows:

C:\>LogParser -i:EVT -o:NAT "SELECT * FROM System"

The query above contains the two basic building blocks of each possible query: the SELECT clause, and the FROM clause.

The SELECT clause is used to specify which input record fields we want to appear in the output records; in this example, the special "*" wildcard means "all the fields".

The FROM clause is used to specify which specific data source we want the Input Format to process. Different Input Formats interpret the value of the FROM clause in different ways; for instance, the EVT Input Format requires the value of the FROM clause to be the name of a Windows Event Log, which in our example is the "System" Event Log.

To be precise, the INTO clause should appear in every query as well. The INTO clause is used to specify the target we want the Output Format to write data to. In our example, we want the NAT Output Format to display results to the console window. This is accomplished by specifying "STDOUT" for the value of the INTO clause, as in the following example:

C:\>LogParser -i:EVT -o:NAT "SELECT * INTO STDOUT FROM System"

When a query does not specify an INTO clause, the NAT Output Format automatically selects "STDOUT" as its target, so in our example we can eliminate the INTO clause altogether.

Tip: When you use the NAT Output Format to display results to the console window, Log Parser prints 10 lines before pausing the printout and prompting the user to press a key to display the next 10 lines.
To override this behavior, you can use the "-rtp" parameter of the NAT Output Format to specify the number of lines to be printed before pausing; if you want to disable the pause altogether and have Log Parser display all the records in a single printout, use the "-1" value.

Selecting Specific Fields

When you execute the basic query above, Log Parser prints all the fields of all the events in the System Event Log to the console window.
Most of the times, a printout of all of the 14 fields of the Event Log records might not be desired. For example, we might only want to see the time at which each event was generated, the type of the event, and the name of the source of the event.
To accomplish this, we have to substitute the "*" wildcard in the SELECT clause with a comma-separated list of the names of the fields we wish to be displayed. We can see the names of the fields in the EVT Input Format records by typing the following help command:

C:\>LogParser -h -i:EVT

The output of this command gives a detailed overview of the EVT Input Format, including a "Fields" section describing the structure of the records produced:

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)

From the fields listing, we understand that the fields we are interested in are named "TimeGenerated", "EventTypeName", and "SourceName"; we can now rewrite our command as:

C:\>LogParser -i:EVT -o:NAT "SELECT TimeGenerated, EventTypeName, SourceName FROM System"

Tip: Field names are case-insensitive.

Tip: If a field name contains spaces, you need to enclose it in square brackets ('[' and ']') for Log Parser to be able to recognize it.

The output of this command contains three columns, one for each of the fields we have selected:

TimeGenerated	 EventTypeName	 SourceName
------------------- ----------------- -----------------------
2004-03-14 18:56:55 Warning event	 W32Time
2004-03-14 14:02:23 Information event Disk
2004-03-14 14:02:23 Information event Disk
2004-03-14 12:00:00 Information event EventLog
2004-03-14 00:41:47 Warning event	 W32Time
2004-03-13 22:17:00 Information event Service Control Manager
2004-03-13 22:06:48 Information event Service Control Manager
2004-03-13 22:06:48 Information event Service Control Manager
2004-03-13 12:00:00 Information event EventLog
2004-03-12 22:30:47 Information event Service Control Manager

This example illustrates the most simple transformation that you can achieve with the Log Parser SQL language: transforming an input record made up of a number of fields into an output record made up of a subset of these fields; in SQL terms, this transformation is called projection.

Using Functions

Functions are very powerful elements of the Log Parser SQL-Like language that take values as arguments, do some processing, and return a new value.
The Log Parser SQL-Like language supports a wide variety of functions, including arithmetical functions (e.g. ADD, SUB, MUL, DIV, MOD, QUANTIZE, etc.), string manipulation functions (e.g. SUBSTR, STRCAT, STRLEN, EXTRACT_TOKEN, etc.), and timestamp manipulation functions (e.g. TO_DATE, TO_TIME, TO_UTCTIME, etc.).

Considering the previous example, assume that for the "TimeGenerated" field we only need to retrieve the date when an event has been generated, ignoring all of the time elements.
To do this, we need to modify the "TimeGenerated" field with the TO_DATE function, which takes a value of type TIMESTAMP and returns a new value of type TIMESTAMP containing only the year, day, and month elements:

C:\>LogParser -i:EVT -o:NAT "SELECT TO_DATE(TimeGenerated), EventTypeName, SourceName FROM System"

The output of this command is:

TO_DATE(TimeGenerated) EventTypeName	 SourceName
---------------------- ----------------- -----------------------
2004-03-14			 Warning event	 W32Time
2004-03-14			 Information event Disk
2004-03-14			 Information event Disk
2004-03-14			 Information event EventLog
2004-03-14			 Warning event	 W32Time
2004-03-13			 Information event Service Control Manager
2004-03-13			 Information event Service Control Manager
2004-03-13			 Information event Service Control Manager
2004-03-13			 Information event EventLog
2004-03-12			 Information event Service Control Manager

Functions can also appear as arguments of other functions.
For example, instead of the event type name shown in the output above, we might want the first word only ("Warning", "Information", etc.), all in capital letters.
This task can be accomplished by first using the EXTRACT_TOKEN function, which extracts specific substrings from within a string, followed by the TO_UPPERCASE function, which transforms a string into a string with all uppercase characters:

C:\>LogParser -i:EVT -o:NAT "SELECT TO_DATE(TimeGenerated), TO_UPPERCASE( EXTRACT_TOKEN(EventTypeName, 0, ' ') ), SourceName FROM System"

TO_DATE(TimeGenerated) TO_UPPERCASE(EXTRACT_TOKEN(EventTypeName, 0, ' ')) SourceName
---------------------- -------------------------------------------------- -----------------------
2004-03-14			 WARNING											W32Time
2004-03-14			 INFORMATION										Disk
2004-03-14			 INFORMATION										Disk
2004-03-14			 INFORMATION										EventLog
2004-03-14			 WARNING											W32Time
2004-03-13			 INFORMATION										Service Control Manager
2004-03-13			 INFORMATION										Service Control Manager
2004-03-13			 INFORMATION										Service Control Manager
2004-03-13			 INFORMATION										EventLog
2004-03-12			 INFORMATION										Service Control Manager

Specifying Constants

So far we have written SELECT clauses that specify both fields and functions.
There is a third kind of item that we could use in our queries: constants.
Constants are special elements in the Log Parser language that represent fixed values; just like the field values, constant values can be one of the Log Parser types: INTEGER, REAL, STRING, TIMESTAMP, and NULL. Constants can be specified in queries in different ways, depending on their type.

Constant values of the INTEGER type are specified by simply typing their value; the following query:

SELECT 242, SourceName FROM SYSTEM

would produce the following output:

242 SourceName
--- ----------
242 W32Time
242 Disk
242 Disk
242 EventLog
242 W32Time

Constant values of the REAL type are specified exactly like the INTEGER values, but they are recognized as being of the REAL type by the presence of a decimal point:

SELECT 242.7, SourceName FROM SYSTEM

242.700000 SourceName
---------- ----------
242.700000 W32Time
242.700000 Disk
242.700000 Disk
242.700000 EventLog
242.700000 W32Time

STRING constants must be enclosed within single-quote characters:

SELECT 'MyConstant', SourceName FROM SYSTEM

'MyConstant' SourceName
------------ ----------
MyConstant   W32Time
MyConstant   Disk
MyConstant   Disk
MyConstant   EventLog
MyConstant   W32Time

Special characters in STRING constants can be specified by using character sequences preceded by the '\' character.
For example, a single-quote character can be specified as \', while a backslash character can be specified by \\:

SELECT 'Contains \' a quote', 'Contains \\ a backslash', SourceName FROM SYSTEM

'Contains 'a quote' 'Contains \ a backslash' SourceName
------------------- ------------------------ ----------
Contains ' a quote   Contains \ a backslash   W32Time
Contains ' a quote   Contains \ a backslash   Disk
Contains ' a quote   Contains \ a backslash   Disk
Contains ' a quote   Contains \ a backslash   EventLog
Contains ' a quote   Contains \ a backslash   W32Time

In addition, it is also possible to specify any UNICODE character using the \uxxxx notation, where xxxx is the 4-digit hexadecimal representation of the UNICODE character.
For example, to specify a tab character (whose UNICODE value is 0009), we could type:

SELECT 'Contains \u0009 a tab', SourceName FROM SYSTEM

A NULL constant can be specified with the "NULL" keyword:

SELECT NULL, SourceName FROM SYSTEM

TIMESTAMP constants are specified in the following way:

TIMESTAMP( 'timestamp value', 'timestamp format' )

For more information regarding timestamp values, constants, and format specifications, refer to the Timestamp Reference.

In the Log Parser SQL language, the three terms that can be specified in a SQL query (fields, functions, and constants) are collectively referred to as field-expressions.

Aliasing Field-Expressions

Consider again one of the examples seen in this section:

C:\>LogParser -i:EVT -o:NAT "SELECT TO_DATE(TimeGenerated), TO_UPPERCASE( EXTRACT_TOKEN(EventTypeName, 0, ' ') ), SourceName FROM System"

TO_DATE(TimeGenerated) TO_UPPERCASE(EXTRACT_TOKEN(EventTypeName, 0, ' ')) SourceName
---------------------- -------------------------------------------------- -----------------------
2004-03-14			 WARNING											W32Time
2004-03-14			 INFORMATION										Disk
2004-03-14			 INFORMATION										Disk
2004-03-14			 INFORMATION										EventLog
2004-03-14			 WARNING											W32Time
2004-03-13			 INFORMATION										Service Control Manager
2004-03-13			 INFORMATION										Service Control Manager
2004-03-13			 INFORMATION										Service Control Manager
2004-03-13			 INFORMATION										EventLog
2004-03-12			 INFORMATION										Service Control Manager

We can see that for each field in the output record, the NAT Output Format prints a column header with the name of that field.
By default, output record fields are named with the full field-expression text that generates them; in our example, the name of the first output record field is "TO_DATE(TimeGenerated)", which mirrors exactly the field-expression text used in the SELECT clause.

We can change the name of a field-expression in the SELECT clause by using an Alias.
In order to alias a field-expression in the SELECT clause, we can use the AS keyword followed by the new name:

C:\>LogParser -i:EVT -o:NAT "SELECT TO_DATE(TimeGenerated) AS DateGenerated, TO_UPPERCASE( EXTRACT_TOKEN(EventTypeName, 0, ' ') ) AS TypeName, SourceName FROM System"

DateGenerated TypeName	SourceName
------------- ----------- -----------------------
2004-03-14	WARNING	 W32Time
2004-03-14	INFORMATION Disk
2004-03-14	INFORMATION Disk
2004-03-14	INFORMATION EventLog
2004-03-14	WARNING	 W32Time
2004-03-13	INFORMATION Service Control Manager
2004-03-13	INFORMATION Service Control Manager
2004-03-13	INFORMATION Service Control Manager
2004-03-13	INFORMATION EventLog
2004-03-12	INFORMATION Service Control Manager

Aliasing a field-expression means assigning a name to it; as we will see later, this name can also be used anywhere else in the query as a shortcut that refers to the original field-expression.