Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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?