Parsing IIS Server Logs with Log Parser

Log Parser is one of those great tools that makes your IIS logs analysis easier.

Software

Software used in this article:

  1. Log Parser 2.2
  2. Office 2003 Web Components
  3. IIS 8 logs

You can download LogParser 2.2 from http://www.microsoft.com/en-us/download/details.aspx?id=24659.

The CHART output requires the Microsoft Office Web Components. It can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=22276.

This article assumes that all sql files are stored in the default Log Parser’s installation directory and that IIS log files have been copied to C:\W3SVC1\.

Using Log Parser

Get Average Page Load Time Between Time Frames (Data Grid)

> LogParser -i:IISW3C -o:DataGrid -e:1 file:avg.sql

Content of the avg.sql file:

SELECT cs-uri-stem, 
AVG(time-taken) As AvgTime 
FROM C:\W3SVC1\*.log
WHERE TO_TIME(time) 
BETWEEN TIMESTAMP('00:00:01','hh:mm:ss') AND TIMESTAMP('23:59:59','hh:mm:ss')
AND DATE > '2015-02-08' AND date < '2015-02-10'
GROUP BY cs-uri-stem
ORDER by AvgTime DESC

Get Top 10 Slowest aspx Pages Between Time Frames (CSV)

> LogParser -i:IISW3C -o:DataGrid -e:1 file:slowest.sql

Content of the slowest.sql file:

SELECT TOP 10 cs-uri-stem,
max(time-taken) as MaxTime,
avg(time-taken) as AvgTime
INTO C:\Temp\slowest.csv
FROM C:\W3SVC1\*.log
WHERE extract_extension(to_lowercase(cs-uri-stem)) = 'aspx'
GROUP BY cs-uri-stem 
ORDER BY MaxTime DESC

Get Hit Frequency per Hour Between Time Frames (Chart)

> LogParser -i:IISW3C -o:CHART -chartType:ColumnStacked -e:1 -view:ON file:hits.sql

Content of the hits.sql file:

SELECT TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)), 
COUNT(*) AS Hit_Frequency 
INTO C:\Temp\hits.jpg 
FROM C:\W3SVC1\*.log 
WHERE TO_TIME(time) 
BETWEEN TIMESTAMP('00:00:01','hh:mm:ss') AND TIMESTAMP('23:59:59','hh:mm:ss')
AND DATE > '2015-02-08' AND date < '2015-02-10'
GROUP BY TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) 
ORDER BY TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) 
DESC

Get Top 10 Client IP Addresses Between Time Frames (Chart)

> LogParser -i:IISW3C -o:CHART -chartType:ColumnStacked -e:1 -view:ON file:top10IPs.sql

Content of the top10IPs.sql file:

SELECT top 10 c-ip 
AS Unique_IPs,count(c-ip)
FROM C:\W3SVC1\*.log 
TO C:\Temp\top10IPs.jpg
WHERE TO_TIME(time)  
BETWEEN TIMESTAMP('00:00:01','hh:mm:ss') AND TIMESTAMP('23:59:59','hh:mm:ss')
AND DATE > '2015-02-08' AND date < '2015-02-10'
GROUP BY c-ip
ORDER BY count(c-ip) DESC

Get Hourly Bandwidth Between Time Frames (Chart)

> LogParser -i:IISW3C -o:CHART -chartType:ColumnStacked -chartTitle:"Bandwidth" -e:1 -view:ON file:bandwidth.sql

Content of the bandwidth.sql file:

Select TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time), 3600)) 
As Hourly_Bandwidth, 
Div(Sum(cs-bytes),1048576) As Incoming(MB), 
Div(Sum(sc-bytes),1048576) As Outgoing(MB) 
INTO C:\Temp\bandwidth.jpg  
FROM C:\W3SVC1\*.log 
WHERE TO_TIME(time) 
BETWEEN TIMESTAMP('00:00:01','hh:mm:ss') AND TIMESTAMP('23:59:59','hh:mm:ss')
AND DATE > '2015-02-08' AND date < '2015-02-10'
GROUP BY TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) 
ORDER BY TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600))
DESC

Get Status Codes Summary Between Time Frames (CSV)

> LogParser -i:IISW3C file:status.sql

Content of the status.sql file:

SELECT sc-status, sc-substatus,
COUNT(*) 
FROM C:\W3SVC1\*.log
TO C:\Temp\status.csv
WHERE TO_TIME(time)
BETWEEN TIMESTAMP('00:00:01','hh:mm:ss') AND TIMESTAMP('23:59:59','hh:mm:ss')
AND DATE > '2015-02-08' AND date < '2015-02-10'
GROUP BY sc-status, sc-substatus 
ORDER BY sc-status

Get 400 and 404 Errors with IPs Between Time Frames (CSV)

> LogParser -i:IISW3C file:404.sql

Content of the 404.sql file:

SELECT date, time, sc-status, cs-uri-stem, c-ip
FROM C:\W3SVC1\*.log 
TO C:\Temp\404.csv
WHERE TO_TIME(time)
BETWEEN TIMESTAMP('00:00:01','hh:mm:ss') AND TIMESTAMP('23:59:59','hh:mm:ss')
AND DATE > '2015-02-08' AND date < '2015-02-10'
AND (sc-status = 400 OR sc-status = 404)
ORDER BY date, time, sc-status

Get Content Usage by Bytes Between Time Frames (Chart)

> LogParser -i:IISW3C -chartType:PieExploded3d -view:ON file:content.sql

Content of the content.sql file:

SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType,
SUM(sc-bytes) AS Bytes 
INTO C:\Temp\content.jpg
FROM C:\W3SVC1\*.log
WHERE TO_TIME(time)
BETWEEN TIMESTAMP('00:00:01','hh:mm:ss') AND TIMESTAMP('23:59:59','hh:mm:ss')
AND DATE > '2015-02-08' AND date < '2015-02-10'
GROUP BY PageType 
ORDER BY Bytes DESC

Leave a Reply

Your e-mail address will not be published. Required fields are marked *