Productivity Tools

Parsing IIS logs with Log Parser 2.2

By Praveen Modi ⋅ January 2, 2009 ⋅  Email This Post ⋅  Print This Post ⋅ Post a comment
Visited 21960 times, 1 so far today

Last week on Windows Live we had a requirement to verify the number of page views on one specific post. Due to some mismatch in view count on third party tracking and our .net processing mechanism.

As a last resort, we decided to verify the page view count from IIS log.

I took the responsibility of downloading the IIS logs and parsing them but I had no clue how I am going to do it. After a bit of Googling, I landed on Microsoft IIS.NET website and came to know about “Log Parser”.

So in the next few paragraphs I will discuss the usage of Log Parser 2.2 and its various options to parse IIS logs.

As per IIS.NET website “Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®.”

I had 150GB of log files to parse and Log Parser did a great job. So I can confidently say it’s excellent tool to parse very large IIS log files.

Log Parser command line is really simple.  Here is the syntax:

LogParser –i:<inputFileFormat> -o:<output format> <Sql Query> | file:<text file
with SQL Query>
-i:<input_format>   :  one of IISW3C, NCSA, IIS, IISODBC, BIN, IISMSID,
                              HTTPERR, URLSCAN, CSV, TSV, W3C, XML, EVT, ETW,
                              NETMON, REG, ADS, TEXTLINE, TEXTWORD, FS, COM (if
                              omitted, will guess from the FROM clause)
-o:<output_format>  :  one of CSV, TSV, XML, DATAGRID, CHART, SYSLOG,
                              NEUROVIEW, NAT, W3C, IIS, SQL, TPL, NULL (if omitted,
                              will guess from the INTO clause)
<SQL Query> OR file:<text file that contains sql query>

So from above, we have: the command, LogParser, an input specification, an output specification and the query or file that contains the query we want to run.

Here are some sample queries which I used on log parser. Let use “default.aspx” as a filter to query the logs.

Get the number of hits to a web page

LogParser -i:IISW3C "SELECT cs-uri-stem, COUNT(*) As Hits
FROM C:\Logs\*.log
WHERE EXTRACT_FILENAME(cs-uri-stem) = 'default.aspx' AND sc-status = '200'
GROUP BY cs-uri-stem" -o:DataGrid

Get the number of hits to a web page in a 3d bar graph

LogParser -i:IISW3C "SELECT cs-uri-stem, COUNT(*) As Hits
FROM C:\Logs\*.log
WHERE EXTRACT_FILENAME(cs-uri-stem) = 'default.aspx' AND sc-status = '200'
GROUP BY cs-uri-stem" -chartType:Bar3D -ChartTitle "Number of hits"
-view:ON

Get the number of hits to a web page by IP address

LogParser -i:IISW3C "SELECT c-ip, COUNT(*) As Hits FROM C:\Logs\*.log
WHERE EXTRACT_FILENAME(cs-uri-stem) = 'default.aspx' AND sc-status = '200'
GROUP BY c-ip Order by Hits DESC" -o:DataGrid

Get the number of hits to a web page by Date

LogParser -i:IISW3C "SELECT date, COUNT(*) As Views FROM C:\Logs\*.log
WHERE cs-uri-stem LIKE 'default.aspx' AND sc-status = '200'
GROUP BY date" -o:DataGrid

Get the total number of hits to your site

LogParser -i:IISW3C "SELECT cs-uri-stem, COUNT(*) As Hits FROM C:\Logs\*.log  GROUP
BY cs-uri-stem" -q:ON –o:DataGrid

Get the average time taken to load the web pages

LogParser -i:IISW3C "SELECT cs-uri-stem, AVG(time-taken) As AvgTime FROM
C:\Logs\*.log GROUP BY cs-uri-stem" -q:ON -0:DataGrid

Get the info about the type of content takes most of the bandwidth on the IIS web server

LogParser -i:IISW3C "SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType,
SUM(sc-bytes) AS Bytes INTO TrafficDistribution.GIF FROM C:\Logs\*.log
GROUP BY PageType ORDER BY Bytes DESC" -chartType:PieExploded3d -view:ON

Get the requests between time frames

LogParser -i:IISW3C "SELECT date, time, cs-uri-stem, cs-uri-query, cs-username,
c-ip, sc-status, sc-substatus, sc-win32-status, time-taken FROM C:\Logs\*.log
WHERE  TO_TIME(time) BETWEEN TIMESTAMP('00:00:02','hh:mm:ss')
AND TIMESTAMP('00:00:08','hh:mm:ss')" -o:DataGrid

Remember that IIS Logs time are in GMT by default.

Useful information about Log Parser

Official Log Parser Forum on IIS.NET

Download link to Log Parser Documentation

Log Parser download link

Tags: IIS, Log Parser, Logs