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.

The output of this query would look like the following sample output:

Username
--------
testusr1
testusr1
testusr2
testusr3