Improving Query Readability
The functions available in the Log Parser SQL language make it possible to write complex queries operating on a very large number of possible transformations of the input fields; however, these complex queries might sometimes be cumbersome to write.
As an example, consider the task of writing a query that extracts from the Security event
log all the users belonging to a specific domain that logged on this computer.
For the purpose of the example, let's also assume that we want the user names as lowercase
strings, and that we are writing the query as a SQL file that takes a lowercase domain name
as an input parameter.
At first thought, the query would look like this:
SELECT EXTRACT_TOKEN( TO_LOWERCASE( RESOLVE_SID(Sid) ), 1, '\\') AS Username FROM Security WHERE EventID IN (540; 528) AND EXTRACT_TOKEN( TO_LOWERCASE( RESOLVE_SID(Sid) ), 0, '\\') = '%domainname%'To execute this query, we can use the "file:" command-line argument, specifying a value for the "domainname" parameter:
C:\>LogParser file:myquery.sql?domainname=tstdomain -i:EVT
When typing the query above, we had to repeat twice the whole expression that transforms
the Sid input record field into a lowercase fully-qualified account name:
TO_LOWERCASE( RESOLVE_SID(Sid) )It would be easier if we could, in a certain sense, "assign" this expression to a "variable", and then use the variable when needed.
We could definitely do that by aliasing the expression in the SELECT clause:
SELECT TO_LOWERCASE( RESOLVE_SID(Sid) ) AS FQAccount, EXTRACT_TOKEN( FQAccount, 1, '\\') AS Username FROM Security WHERE EventID IN (540; 528) AND EXTRACT_TOKEN( FQAccount, 0, '\\') = '%domainname%'However, the output of this query now contains an extraneous field - the fully-qualified account name:
FQAccount Username ------------------ --------------- tstdomain\testusr1 testusr1 tstdomain\testusr1 testusr1 tstdomain\testusr2 testusr2 tstdomain\testusr3 testusr3
To obviate this problem, the Log Parser SQL language supports the USING clause.
The USING clause, a non-standard SQL language element, is used to declare aliases in the
same way as we would in the SELECT clause, with the difference that expressions
in the USING clause will not appear in the output records (unless explicitly referenced
in the SELECT clause).
With the USING clause, the query above can be written as follows:
SELECT EXTRACT_TOKEN( FQAccount, 1, '\\') AS Username USING TO_LOWERCASE( RESOLVE_SID(Sid) ) AS FQAccount FROM Security WHERE EventID IN (540; 528) AND EXTRACT_TOKEN( FQAccount, 0, '\\') = '%domainname%'
Tip: The USING clause must immediately follow the SELECT clause.
Username -------- testusr1 testusr1 testusr2 testusr3