Understanding the USING clause in Log Parser

Added 9/3/2011 9:53:06 PM and updated 9/3/2011 9:54:07 PM

One easy way to simplify your Log Parser queries is to use the USING clause.

If you’ve been using Log Parser for any amount of time you’re probably well versed with SELECT, FROM, and WHERE.

Looking at a SELECT clause against IIS logs, you may find something that looks like the following:

SELECT TO_DATE(TO_LOCALTIME(TO_TIMESTAMP(date, time))) AS [LocalDate]
    , TO_TIME(TO_LOCALTIME(TO_TIMESTAMP(date, time))) AS [LocalTime]
    , c-ip, cs-uri-stem, cs-uri-query, sc-status, sc-bytes, cs-bytes, time-taken
    , cs(User-Agent), cs(Referer)
[...]

Here we’re taking the date and time stored in the W3C logs as UTC and converting them to local time. We repeat a couple of items, specifically TO_LOCALTIME(TO_TIMESTAMP(date, time)).

In addition, if we want to use LocalTimeDate or LocalTimeTime in our WHERE clause we’ll find that we can’t.

Let’s modify our query slightly by taking advantage of the USING clause. Since we have that expression we’d like to use in multiple places that’s the best place to start.

SELECT TO_DATE(localDateTime) AS [LocalDate]
    , TO_TIME(localDateTime) AS [LocalTime]
    , c-ip, cs-uri-stem, cs-uri-query, sc-status, sc-bytes, cs-bytes, time-taken
    , cs(User-Agent), cs(Referer)
USING TO_LOCALTIME(TO_TIMESTAMP(date, time)) AS [localDateTime]
[...]

We’ve started by adding a USING immediately after the SELECT (before INTO or FROM). We then declared an alias just like we’d do in the SELECT. In this case we’re creating an alias for our shared expression. With this done we can replace the corresponding expressions within the SELECT.

Now that we have our alias defined by the USING clause we can use it in our WHERE clause.

WHERE [localDateTime] &gt; TO_TIMESTAMP('2011-09-01', 'yyyy-MM-dd')

Putting complex expressions in the USING clause - like CASE cs-uri-query WHEN null THEN cs-uri-stem ELSE STRCAT(STRCAT(cs-uri-stem, '?'), cs-uri-query) END AS [fullUrl] - can help keep your SELECT nice and concise.

Generally, if you find yourself using an expression in more than one place in your query, ask yourself if it makes sense to move it into the USING clause.

How do you use the USING clause?

Do you have complex expressions you typically put within a USING clause? If so, please leave them in the comments, along with the log format.

Article provided by James Skemp.

Back to List