The benefits of using a file for Log Parser SQL queries
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.
- Review: Empires of EVE: A History of the Great Wars of EVE Online
- Review: Ghost in the Shell README: 1995-2017
- Review: Fitbit Charge 2 Bands,Fitbit Bands Milanese Loop Stainless Steel Metal Replacement Bracelet Strap, Wristbands Accessories for Charge 2 with Stronger Magnetic Clasp
- Review: Wickedly Prime Organic Roasted Seaweed Snacks, Korean Kalbi
- Review: 5-Minute Dungeon
Support This Site
If my blog was helpful to you, then please consider visiting my Amazon Wishlist.