IIS Log Analysis

Some good things to use when trying to do analysis on IIS logs:

  • TXTCollector – This will make all your individual IIS log files into one large file.
  • Log Parser – Write SQL queries against your IIS Log files
  • Visual Log Parser – No command line (but sometimes a pain in the ass to install)!
  • Log Parser Lizard – Visual Log Parser doesn’t want to install anymore, so a new tool it is!
  • Log Parser Studio – Free from MS!

Some common Log Parser queries:

select cs-uri-stem as url,
cs-uri-query, cs-method,
 count(cs-uri-stem) as pagecount,
 sum(time-taken) as total-processing-time,
 avg(time-taken) as average,
 Max(time-taken) as Maximum
from <logfile>
group by cs-uri-stem,
 cs-uri-query,
 cs-method
order by average desc

 

select cs-uri-stem as url,
 cs-method,
 count(cs-uri-stem) as pagecount,
 sum(time-taken) as total-processing-time,
 avg(time-taken) as average
from <logfile>
where cs-uri-stem like '%.aspx'
group by cs-uri-stem,
 cs-method
order by pagecount desc

 

select top 500 cs-uri-stem as url,
 cs-uri-query,
 count(cs-uri-stem) as pagecount,
 sum(time-taken) as total-processing-time,
 avg(time-taken) as average
from <logfile>
where cs-uri-stem like '%.aspx'
group by cs-uri-stem,
 cs-uri-query
order by pagecount desc

 

select cs-uri-stem as url,
 cs-method,
 count(cs-uri-stem) as pagecount,
 sum(time-taken) as total-processing-time,
 avg(time-taken) as average,
 avg(sc-bytes),
 max(sc-bytes)
from <logfile>
where cs-uri-stem like '%.aspx'
group by cs-uri-stem,
 cs-method
order by pagecount desc

UpdateI’m just adding more queries I frequently use, and fixing the formatting.

select quantize(time-taken,5000) as 5seconds,
 count(cs-uri-stem) as hits,
 cs-uri-stem as url
from <logfile>
group by url, quantize(time-taken,5000)
order by quantize(time-taken,5000)

 

select
 quantize(time,3600) as dayHour,
 count(cs-uri-stem) as hits,
 avg(time-taken) as averageTime,
 cs-uri-stem as url
from <logfile>
where url like '%.svc'
group by url,
 dayHour
order by dayHour

select
TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time), 3600)) AS dayHour,
count(cs-uri-stem) as hits
from <logfile>
where cs-uri-stem like '%/page.aspx'
group by dayHour
order by dayHour Asc