Log Parser is one of those great tools that makes your IIS logs analysis easier.
Software
Software used in this article:
- Log Parser 2.2
- Office 2003 Web Components
- 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