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:EVTThe 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.
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 ManagerThis 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 ManagerFunctions 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 SYSTEMwould 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 W32TimeSpecial 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 W32TimeIn 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 ManagerWe 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.