Filtering Input Records

When retrieving data from an Input Format, it is often needed to filter out unneeded records and only keep those that match specific criteria.

For example, consider the simple command seen in the previous section, which returns selected fields from all of the events in the System event log:

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

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

Let's now assume that we are only interested in the events generated by the "Service Control Manager" source.
To accomplish this task, we can use another basic building block of the Log Parser SQL-Like language: the WHERE clause.

The WHERE clause is used to specify a boolean expression that must be satisfied by an input record for that record to be output. Input records that do not satisfy the condition will be discarded.
In SQL terms, filtering records with the WHERE clause is a transformation called selection.

Using the WHERE clause, we can rewrite the previous command as follows:

C:\>LogParser -i:EVT -o:NAT "SELECT TimeGenerated, EventTypeName, SourceName FROM System WHERE SourceName = 'Service Control Manager'"

Tip: The WHERE clause must immediately follow the FROM clause.

The output of this command is:

TimeGenerated	 EventTypeName	 SourceName
------------------- ----------------- -----------------------
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-12 22:30:47 Information event Service Control Manager
2004-03-12 22:12:32 Information event Service Control Manager
2004-03-12 21:09:14 Information event Service Control Manager

Let's analyze in detail the WHERE clause used in this example.
The boolean condition that we have used is a very simple one: we only want those input records whose "SourceName" field has the exact value of "Service Control Manager". To specify this condition, we have used the "=" relational operator, with the left operand being the "SourceName" field, and the right operand being a STRING constant.

Complex Conditions

Conditions specified in the WHERE clause can be more complex, making use of comparison operators (such as ">", "<=", "<>", "LIKE", "BETWEEN", etc.) and boolean operators (such as "AND", "OR", "NOT").

For example, we might only want to see two kinds of events:

Events generated by the "Service Control Manager" source whose EventID is greater than or equal 7024;
Events generated by the "W32Time" source.

To accomplish this, the query can be written as follows:

SELECT TimeGenerated, EventTypeName, SourceName FROM System
WHERE ( SourceName = 'Service Control Manager' AND EventID >= 7024) OR
	( SourceName = 'W32Time')

As another example, we might want to see all the events that have been logged in the past 24 hours.
Translated into WHERE terms, this means that we only want to see records whose "TimeWritten" field is greater than or equal the current local time minus 1 day:

SELECT * FROM System
WHERE TimeWritten >= SUB( TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02', 'yyyy-MM-dd') )

Tip: In Log Parser the origin of time is day 1 of month 1 of year zero. This means that a time span of one day can be specified as day 2 of month 1 of year zero, i.e. 24 hours after the origin of time.

To see security events whose "Message" field contains the word "logon", we can use the LIKE operator, which tests a STRING value for case-insensitive pattern matching:

SELECT * FROM Security
WHERE Message LIKE '%logon%'

If we want to retrieve events with an ID belonging to a specific set of values, we can use the IN operator followed by a list of the desired "EventID" values:

SELECT * FROM Security
WHERE EventID IN (547; 541; 540; 528)

Tip: With the IN operator, single values are separated by the semicolon character.

On the other hand, if we want to retrieve events with an ID belonging to a specific range of values, we can use the BETWEEN operator as follows:

SELECT * FROM Security
WHERE EventID BETWEEN 528 AND 547