Introduction
Log Parser is a program created by Microsoft specifically to query large text log files. Log Parser is a command line utility but there are GUIs for it. One is Log Parser Studio, the download link in the article is easy to miss so here it is.. Another GUI for the utility is Log Parser Lizard
Log Parser Studio opening screen
Log Parser Lizard opening screen
Simple Examples
Here are some very simple example queries. In these examples "-i:ncsa" specifies the Common Log Format used by web servers.
Show all records from a file:
logparser -i:ncsa "SELECT * FROM 'E:\web server logs\originals\brisray.com-access-20210704.log'"
Show first 10 records from a file:
logparser -i:ncsa "SELECT TOP 10 * FROM 'E:\web server logs\originals\brisray.com-access-20210704.log'"
Write the records between two dates into a newfile:
logparser -i:ncsa "SELECT INTO 'E:\web server logs\newfile.log' FROM 'E:\web server logs\originals\brisray.com-access-20210704.log' WHERE [DateTime] BETWEEN timestamp('2016/01/01', 'yyyy/MM/dd') AND timestamp('2016/02/01', 'yyyy/MM/dd')"
Show last 10 records from a file sorted by date:
logparser -i:ncsa "SELECT TOP 10 * FROM 'E:\web server logs\originals\brisray.com-access-20210704.log' ORDER BY DateTime DESC"
Log Parser pauses the screen output every 10 records with the instruction to "Press a key..." This behaviour can be changed by using the switch --rpt:<number> where number is the number of records to display before the "Press a key..." prompt is displayed, for example -rtp:20
To suppress the "Press a key..." prompt altogether use -rtp:-1
What I wanted to do was to look at a day's worth of log file records and output the top 10 user agents that were making the most requests because one of them was making a lot more requests than was normal. It took me a while to figure out how to output just 1 day's worth of records. What I finally came up with was to extract the log file's DateTime group as a date and then turn that into a string and test it against the date I wanted. Like this:
to_string(to_date(DateTime),'dd/MMM/yyyy') = '17/Mar/2023'
Here's a complete command line using it (all on one line):
logparser -i:ncsa "SELECT top 10 User-Agent, count (User-Agent) as requestcount FROM 'C:\Users\brisr\Documents\logtest\brisray-access-2023-03.log' where to_string(to_date(DateTime),'dd/MMM/yyyy') = '17/Mar/2023' GROUP BY User-Agent ORDER BY count(User-Agent) DESC"
Here's the output of the above command at prompt:
Output of the above command
As I suspected, a single user agent is making many, many more times requests than is normal
I used the same query in Log Parser Studio:
The same query as above in Log Parser Studio
Log Parser Studio comes with 181 example queries, but Mike Lichtenberg has over 50 more complex example queries on LichtenBytes.
Sources and Resources
A GUI for Log Parser? - A review of the Log Parser Lizard GUI for Log Parser
Analyse Logs using MS Log Parser
Introducing Log Parser Studio - A basic introduction to Log Parser
Getting Started with Log Parser Studio - Part 1 and Part 2
Log Parser download
Log Parser Lizard - a GUI for Log Parser
Log Parser Plus - Articles and example queries
Log Parser Rocks! More than 50 Examples! - Example queries from Mike Lichtenberg
Log Parser Studio - Microsoft's GUI for Log Parser - Immediate download!
Recommended LogParser Queries for IIS Monitoring - can also be used on other log files
Steve Fenton - Steve Fenton's blogs about using Log Parser
This page created April 29, 2023; last modified May 6, 2023
