Share via

Log Parser And IIS Log Analysis.

  • Article

First have  Log Parser installed. Then Start it from C:\Program Files (x86)\Log Parser 2.2 

Here are some Log Parser queries which can be used across  IIS logs for analysis

Request Type Distribution

"SELECT EXTRACT_EXTENSION(TO_UPPERCASE(cs-uri-stem)) AS ExtType, COUNT(*) INTO FrontEndRequestType.csv FROM *.log GROUP BY ExtType ORDER BY COUNT(*) DESC " -i:W3C -o:CSV

 

Top 20 Hits

"SELECT TOP 20 TO_UPPERCASE(cs-uri-stem) AS URI, COUNT(*) AS Hits INTO Top20Hits.csv FROM *.log WHERE URI LIKE '%%.ASPX' GROUP BY URI ORDER BY Hits DESC " -i:W3C -o:CSV

 

Top 20 Hits Overall

"SELECT TOP 20 TO_UPPERCASE(cs-uri-stem) AS URI, COUNT(*) AS Hits INTO Top20Hits.csv FROM *.log GROUP BY URI ORDER BY Hits DESC " -i:W3C -o:CSV

 

Top 20 Hits Chart

"SELECT TOP 20 cs-uri-stem, COUNT(*) AS Hits INTO Top20Hits.jpg FROM *.log GROUP BY cs-uri-stem ORDER BY Hits DESC" -i:W3C -o:CHART -charttype:ColumnClustered -groupsize:640x480 -chartTitle: Top 20 Hits Chart

 

Top 20 ASPX Hits

"SELECT TOP 20 TO_UPPERCASE(cs-uri-stem) AS URI, COUNT(*) AS Hits INTO Top20ASPX.csv FROM *.log WHERE URI LIKE '%.ASPX' GROUP BY URI ORDER BY Hits DESC " -i:W3C -o:CSV

 

Top 20 Slowest ASPX Pages

"SELECT TOP 20 cs-uri-stem, max(time-taken) as MaxTime, avg(time-taken) as AvgTime INTO Top20SlowASPX.csv FROM *.log WHERE extract_extension(to_lowercase(cs-uri-stem)) = ‘aspx’ GROUP BY cs-uri-stem ORDER BY MaxTime DESC" -i:W3C -o:CSV

 

Top 20 ASMX Hits

"SELECT TOP 20 TO_UPPERCASE(cs-uri-stem) AS URI, COUNT(*) AS Hits INTO Top20ASMX.csv FROM *.log WHERE URI LIKE '%.ASMX' GROUP BY URI ORDER BY Hits DESC " -i:W3C -o:CSV

 

Top 20 Client IP Addresses

"SELECT TOP 20 c-ip, COUNT(*) AS Hits INTO Top20ClientIP.csv FROM *.log GROUP BY c-ip ORDER BY Hits DESC " -i:W3C -o:CSV

 

Requests Per Hour

"SELECT TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) AS Hours, COUNT(*) AS Hits INTO RequestsPerHour.jpg FROM *.log GROUP BY Hours ORDER BY Hours " -i:W3C -o:CHART -charttype:ColumnClustered -groupsize:640x480 -chartTitle:"Requests Per Hour"

 

HTTP Status Counts

"SELECT DISTINCT sc-status AS Status, COUNT(*) AS Hits INTO HTTPStatusCount.csv FROM *.log GROUP BY Status ORDER BY Status ASC " -i:W3C -o:CSV

 

HTTP Status Distribution

"SELECT DISTINCT sc-status AS Status, COUNT(*) AS Percent INTO HTTPStatusDistribution.jpg FROM *.log GROUP BY Status ORDER BY Percent DESC " -i:W3C -o:CHART -chartType:PieExploded -chartTitle: "HTTP Status Distribution"

 

Top 20 HTTP 304 Errors

"SELECT TOP 20 TO_UPPERCASE(cs-uri-stem) AS URI, sc-substatus, sc-win32-status, COUNT(*) AS Hits INTO Top20Http304Errors.csv FROM *.log WHERE sc-status = 304 GROUP BY URI, sc-substatus, sc-win32-status ORDER BY Hits DESC" -i:W3C -o:CSV

 

Top 20 HTTP 404 Errors

"SELECT TOP 20 TO_UPPERCASE(cs-uri-stem) AS URI, sc-substatus, sc-win32-status, COUNT(*) AS Hits INTO Top20Http404Errors.csv FROM *.log WHERE sc-status = 404 GROUP BY URI, sc-substatus, sc-win32-status ORDER BY Hits DESC" -i:W3C -o:CSV

 

Top 20 HTTP 403 Errors

"SELECT TOP 20 TO_UPPERCASE(cs-uri-stem) AS URI, sc-substatus, sc-win32-status, COUNT(*) AS Hits INTO Top20Http403Errors.csv FROM *.log WHERE sc-status = 403 GROUP BY URI, sc-substatus, sc-win32-status ORDER BY Hits DESC" -i:W3C -o:CSV

 

Top 20 HTTP 500 Errors

"SELECT TOP 20 TO_UPPERCASE(cs-uri-stem) AS URI, sc-substatus, sc-win32-status, COUNT(*) AS Hits INTO Top20500Errors.csv FROM *.log WHERE sc-status = 500 GROUP BY URI, sc-substatus, sc-win32-status ORDER BY Hits DESC" -i:W3C -o:CSV

 

Top 20 HTTP 503 Errors

"SELECT TOP 20 TO_UPPERCASE(cs-uri-stem) AS URI, sc-substatus, sc-win32-status, COUNT(*) AS Hits INTOTop20503Errors.csv FROM *.log WHERE sc-status = 503 GROUP BY URI, sc-substatus, sc-win32-status ORDER BY Hits DESC" -i:W3C -o:CSV

 

Top 20 Longest Processing Requests

"SELECT TOP 20 cs-uri-stem AS URI, date AS Date, time AS Time, sc-status, time-taken as TimeTaken(ms) INTO

Top20LongRunningRequests.csv FROM *.log ORDER BY time-taken DESC" -i:W3C -o:CSV

 

 

To view results in Charts- this needs Office Web Components; there is a good post about this here https://blogs.msdn.com/b/carloc/archive/2008/08/07/charting-with-logparser.aspx

Comments

  • Anonymous
    January 09, 2014
    I'm a big fan of LogParser! It's a powerfull and awesome tool. This is a good list of some examples of using LogParser.

  • Anonymous
    February 18, 2014
    The time we are looking at is it GMT or reading the server time?