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 here.

The CHART output requires the Microsoft Office Web Components. It can be downloaded here.

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 email address will not be published. Required fields are marked *