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 ManagerLet'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 ManagerLet'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.
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