The benefits of using a file for Log Parser SQL queries

• September 26, 2010
• James Skemp
• tutorials / guides

One of the things I always forget about when I'm using Microsoft Log Parser is that you can define your SQL query in a file and run it, instead of putting it in the command line.

For example, the following will run whatever SQL is defined in strivinglife.robots.sql.

logparser -i:w3c -o:xml file:strivinglife.robots.sql

Where this comes in handy is for something like the Log Parser query I defined today, that parses IIS logs for requests to the robots.txt file and outputs the ip address and user-agent of the request, with a total count from that combination.

SELECT c-ip AS [ClientIp], cs(user-agent) AS [ClientUserAgent], COUNT(*) AS [Requests]
--USING
INTO strivinglife.robots.xml
FROM \\server1\projects\logs\server2008\w3svc5\u_ex1009*.log
WHERE cs-uri-stem = '/robots.txt'
GROUP BY ClientIp, ClientUserAgent
--HAVING
ORDER BY Requests DESC

Because my output is XML, and I don't want the parens to turn into underscores (as well as so that I know what my element names are) I alias the columns to particular names. (The commented lines are from my template file.)

This then gives me an output that I can parse and display, for example, on a Web page.

• log parser
• xml