Besoin d’analyser vos logs IIS ? LogParser est votre meilleur ami ! / Need to analyse your IIS log files? LogParser is your best friend !


[MAJ 01/03/2017] Est-il encore besoin de présenter LogParser ? Cet outil en ligne de commandes est un veritable couteau suisse et permet d’analyser une multitude de formats de fichier (Log IIS, fichier XML, Traces venant de Network Monitor, Traces ETW, …). Certes il existe d’excellents outils tel que LogParserStudio auquel mes collègues de l’équipe support IIS/Azure France ont même consacré un article mais il faut l’avouer je suis assez « old school » et je préfére directement utiliser l’outil de base plutôt qu’une surcouche (même si je le répète l’outil semble très bien fait).

Dans le cadre d’une analyse des logs IIS le temps consacré pour se familiariser avec LogParser (j’estime à moins de 4 heures en partant de zéro) me semble dérisoire par rapport au retour sur investissement lors de vos phases de troubleshooting. De manière à encore accélerer votre apprentissage, j’ai décidé de vous livrer les requêtes LogParser que j’utilise le plus fréquement lors de mes visites clients dédiées à des problématiques IIS. Pour cela vous aurez besoin de LogParser 2.2 et de Office 2003 Add-in: Office Web Components (Et oui on va générer des graphiques avec LogParser !). Je recommande d’ailleur d’installer LogParser 2.2 sur tous vos serveurs IIS de manière à pouvoir sortir rapidement un état des lieux des requêtes en cas de problème. Pour Office 2003 Add-in: Office Web Components  par contre, installez le uniquement sur votre poste de travail (sans oublier LogParser 2.2) car il est préférable de génerer vos graphiques sur votre poste de travail et d’installer le minimum de composants sur un serveur de production.

Nous supposerons pour des facilités de lecture que Logparser.exe est accessible depuis n’importe où (ie. le chemin d’installation est présent dans votre variable d’environnement PATH)

REM 01 - Total Requests Count LogParser.exe "SELECT COUNT(*) AS Hits FROM u_ex*.log TO '01_IISHC_TotalRequestsCount.csv'" -i:W3C -o:CSV -stats:OFF REM 02 - Total Distinct Client IP Count LogParser.exe "SELECT COUNT(DISTINCT c-ip) AS Counts FROM u_ex*.log TO '02_IISHC_TotalDistinctClientIP.csv'" -i:W3C -o:CSV -stats:OFF REM 03 - Top 20 Hits LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO '03_IISHC_Top_20Hits.csv' FROM u_ex*.log GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF REM 04 - Top 20 Hits Chart LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO 04_IISHC_Top_20Hits.gif FROM u_ex*.log GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"Top 20 Hits" -stats:OFF REM 05 - Top 20 ASPX Hits LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO '05_IISHC_Top_20ASPXHits.csv' FROM u_ex*.log AND TO_LOWERCASE(cs-uri-stem) LIKE '%%.aspx' GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF REM 06 - Top 20 ASPX Hits Chart LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO 06_IISHC_Top_20ASPXHits.gif FROM u_ex*.log AND TO_LOWERCASE(cs-uri-stem) LIKE '%%.aspx' GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"Top 20 ASPX Hits" -stats:OFF REM 07 - Top 20 ASP Hits LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO '07_IISHC_Top_20ASPHits.csv' FROM u_ex*.log AND TO_LOWERCASE(cs-uri-stem) LIKE '%%.asp' GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF REM 08 - Top 20 ASP Hits Chart LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO 08_IISHC_Top_20ASPHits.gif FROM u_ex*.log AND TO_LOWERCASE(cs-uri-stem) LIKE '%%.asp' GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"Top 20 ASP Hits" -stats:OFF REM 09 - Top 20 Client IP Addresses LogParser.exe "SELECT Top 20 c-ip, COUNT(*) AS Hits INTO '09_IISHC_Top_20ClientIP.csv' FROM u_ex*.log GROUP BY c-ip ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF REM 10 - Top 20 Client IP Addresses Chart LogParser.exe "SELECT Top 20 c-ip, COUNT(*) AS Hits INTO 10_IISHC_Top_20ClientIP.gif FROM u_ex*.log GROUP BY c-ip ORDER BY Hits DESC" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"Top 20 Client IP Addresses" -stats:OFF REM 11 - Top 20 Time Consuming Hits (Sum over the period reviewed) LogParser.exe "SELECT TOP 20 cs-uri-stem, count(*) AS Hits, SUM(time-taken) AS Milliseconds, AVG(time-taken) AS AvgMilliseconds INTO '11_IISHC_Top20TimeConsumingHits.csv' FROM u_ex*.log GROUP BY cs-uri-stem ORDER BY Milliseconds DESC" -i:W3C -o:CSV -stats:OFF REM 12 - Top 20 Outbound Bytes Consuming Hits (Sum over the period reviewed) LogParser.exe "SELECT TOP 20 cs-uri-stem, count(*) AS Hits, SUM(sc-bytes) AS OutboundBytes, AVG(sc-bytes) AS AvgOutboundBytes INTO '12_IISHC_Top20OutboundBytesConsumingHits.csv' FROM u_ex*.log GROUP BY cs-uri-stem ORDER BY OutboundBytes DESC" -i:W3C -o:CSV -stats:OFF REM 13 - Requests Per Hour LogParser.exe "SELECT TO_LocalTime(QUANTIZE(TO_TIMESTAMP(date, time),3600)) AS Hours, COUNT(*) AS Hits INTO 13_IISHC_RequestsPerHour.gif FROM u_ex*.log GROUP BY Hours ORDER BY Hours" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"Requests Per Hour" -stats:OFF REM 14 - Outbound Bytes Per Hour LogParser.exe "SELECT TO_LocalTime(QUANTIZE(TO_TIMESTAMP(date, time),3600)) AS Hours, SUM(sc-bytes) AS Bytes INTO 14_IISHC_OutboundBytesPerHour.gif FROM u_ex*.log GROUP BY Hours ORDER BY Hours" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"Outbound Bytes Per Hour" -stats:OFF REM 15 - Distinct Client IP Addresses Per Hour (Users Per Hour) LogParser.exe "SELECT TO_LocalTime(QUANTIZE(TO_TIMESTAMP(date, time),3600)) AS Hours, c-ip AS CIP INTO '15_IISHC_DistinctClientIPPerHour.csv' FROM u_ex*.log GROUP BY Hours, CIP" -i:W3C -o:CSV -stats:OFF REM 16 - Distinct Client IP Addresses Per Hour (Users Per Hour) Chart LogParser.exe "SELECT Hours, COUNT(*) as Counts FROM '15_IISHC_DistinctClientIPPerHour.csv' TO 16_IISHC_DistinctClientIPPerHour.gif GROUP BY Hours ORDER BY Hours" -i:CSV -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"Distinct Client IP Addresses Per Hour (Users Per Hour)" -stats:OFF REM 17 - HTTP Status Counts LogParser.exe "SELECT DISTINCT sc-status AS Status, COUNT(*) AS Hits INTO '17_IISHC_HTTPStatusCounts.csv' FROM u_ex*.log GROUP BY Status ORDER BY Status ASC" -i:W3C -o:CSV -stats:OFF REM 18 - HTTP Status Distribution LogParser.exe "SELECT DISTINCT sc-status AS Status, COUNT(*) AS Percent INTO 18_IISHC_HTTPStatusDistribution.gif FROM u_ex*.log GROUP BY Status ORDER BY Percent DESC" -i:W3C -config:"LogParserScript.js" -o:CHART -chartType:PieExploded3D -chartTitle:"HTTP Status Distribution" -stats:OFF REM 19 - Top 20 HTTP Status 4XX LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem) as URI, sc-status as HTTPStatus, COUNT(*) AS Hits INTO '19_IISHC_Top_20HTTP4XXErrors.csv' FROM u_ex*.log AND sc-status >= 400 AND sc-status = 500 AND sc-status < 600 GROUP BY URI, sc-status ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF REM 23 - HTTP 500 Errors/Hour LogParser.exe "SELECT TO_LocalTime(QUANTIZE(TO_TIMESTAMP(date, time),3600)) AS Hours, COUNT(*) AS Errors INTO 23_IISHC_HTTP500ErrorsPerHour.gif FROM u_ex*.log AND sc-status = 500 GROUP BY Hours ORDER BY Hours" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"HTTP 500 Errors Per Hour" -stats:OFF REM 24 - Top 20 HTTP 500 Errors LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO '24_IISHC_Top_20HTTP500Errors.csv' FROM u_ex*.log AND sc-status = 500 GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF REM 25 - Top 20 Average Longest Processing Requests LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem) AS URL, COUNT(TO_LOWERCASE(cs-uri-stem)) AS Hits, AVG(sc-bytes) AS sc-bytes, AVG(cs-bytes) AS cs-bytes, MAX(time-taken) as Max, MIN(time-taken) as Min, AVG(time-taken) as Avg INTO '25_IISHC_Top_20AvgLongestProcRequests.csv' FROM u_ex*.log GROUP BY URL ORDER BY AVG(time-taken) DESC" -i:W3C -o:CSV -stats:OFF REM 26 - Top 20 Longest Processing Requests LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem) AS URL, TO_DATE( TO_LOCALTIME( TO_TIMESTAMP(date, time))) AS date, TO_TIME( TO_LOCALTIME( TO_TIMESTAMP(date, time))) AS time, sc-bytes, cs-bytes, sc-status, time-taken INTO '26_IISHC_Top_20LongestProcRequests.csv' FROM u_ex*.log ORDER BY time-taken DESC" -i:W3C -o:CSV -stats:OFF REM 27 - Average/Max Processing Time Per Hour LogParser.exe "SELECT TO_LocalTime(QUANTIZE(TO_TIMESTAMP(date, time),3600)) AS Hours, AVG(time-taken) AS Avg, MAX(time-taken) AS Max INTO 27_IISHC_AvgMaxProcTimePerHour.gif FROM u_ex*.log AND sc-status = 200 GROUP BY Hours ORDER BY Hours" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:Line -groupsize:800x600 -chartTitle:"Average/Max Processing Time Per Hour (ms)" -stats:OFF REM 28 - Aggregated Average Processing Time Per Hour LogParser.exe "SELECT TO_LocalTime(quantize(time,3600)) AS Hours, AVG(time-taken) AS Avg INTO 28_IISHC_AggregatedAvgProcTimePerHourRadar.gif FROM u_ex*.log AND sc-status = 200 GROUP BY Hours ORDER BY Hours" -i:IISW3C -recurse:-1 -o:CHART -charttype:RadarLineFilled -groupsize:800x600 -chartTitle:"Aggregated Average Processing Time Per Hour (ms)" -stats:OFF REM 29 - Processing Time Per Extension LogParser.exe "SELECT EXTRACT_EXTENSION(TO_LOWERCASE(cs-uri-stem)) AS Extension, MUL(PROPSUM(time-taken),100.0) AS ProcessingTime INTO 29_IISHC_ProcTimePerExt.gif FROM u_ex*.log GROUP BY Extension ORDER BY ProcessingTime DESC" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:PieExploded3D -groupsize:800x600 -chartTitle:"Processing Time Per Extension" -categories:off -stats:OFF REM 30 - Outbound BandWidth In MBps LogParser.exe "SELECT DIV(DIV(MUL(1.0, SUM(sc-bytes)),1048576), 86399) AS OutboundBandWidthInMBps INTO '30_IISHC_OutboundBandWidthInMBps.csv' FROM u_ex*.log" -i:W3C -o:CSV -stats:OFF REM 31 - Requests Per Second LogParser.exe "SELECT DIV(MUL(1.0, COUNT(*)), 86399) AS RequestsPerSecond FROM u_ex*.log TO '31_IISHC_RequestsPerSecond.csv'" -i:W3C -o:CSV -stats:OFF REM 32 - HTTP 400 Errors/Hour LogParser.exe "SELECT TO_LocalTime(QUANTIZE(TO_TIMESTAMP(date, time),3600)) AS Hours, COUNT(*) AS Errors INTO 32_IISHC_HTTP400ErrorsPerHour.gif FROM u_ex*.log AND sc-status = 400 GROUP BY Hours ORDER BY Hours" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"HTTP 400 Errors Per Hour" -stats:OFF REM 33 - Top 20 HTTP 400 Errors LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO '33_IISHC_Top_20HTTP400Errors.csv' FROM u_ex*.log AND sc-status = 400 GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF

 

Le fichier LogParserScript.js référencé dans quelques lignes qui précédent est disponible ici (il faut changer l’extension en .js –  ne sert que pour les graphiques)

Toutes les requêtes peuvent être téléchargées ici.


[Updated 03/01/2017] Does it still need to present LogParser? This command line tool is a real Swiss Army knife and analyzes a multitude of file formats (IIS Log files, XML files, Network Monitor traces, ETW traces, …). There are excellent tools such as LogParserStudio for which my colleagues in the French IIS/Azure Support Team have even devoted an article but I must admit I’m pretty « old school » and I prefer use directly this tool rather than an wrapper (although I repeat the tool seems very well done).

As part of an IIS logs analysis the time spent to learn LogParser (I estimate at less than 4 hours from scratch) seems paltry compared to the return on investment when you will troubleshoot your IIS servers. In order to further accelerate your learning, I have decided to give you the LogParser queries that I use most often during my customer onsites related to IIS issues. For this you will need LogParser 2.2 and Office 2003 Add-in: Office Web Components (And yes we will generate graphs with LogParser!). BTW I recommend installing LogParser 2.2 on all of your IIS servers so you can quickly generate an IIS report of the requests in case of problems. For Office 2003 Add-in: Office Web Components I recommend to install only on your computer (don’t forget to install also LogParser 2.2) because it is preferable to generate your charts on your desktop and install the minimum components on a production server.

We assume to facilitate the reading that LogParser.exe is accessible from anywhere (ie. The installation path is in your PATH environment variable)

REM 01 - Total Requests Count LogParser.exe "SELECT COUNT(*) AS Hits FROM u_ex*.log TO '01_IISHC_TotalRequestsCount.csv'" -i:W3C -o:CSV -stats:OFF REM 02 - Total Distinct Client IP Count LogParser.exe "SELECT COUNT(DISTINCT c-ip) AS Counts FROM u_ex*.log TO '02_IISHC_TotalDistinctClientIP.csv'" -i:W3C -o:CSV -stats:OFF REM 03 - Top 20 Hits LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO '03_IISHC_Top_20Hits.csv' FROM u_ex*.log GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF REM 04 - Top 20 Hits Chart LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO 04_IISHC_Top_20Hits.gif FROM u_ex*.log GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"Top 20 Hits" -stats:OFF REM 05 - Top 20 ASPX Hits LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO '05_IISHC_Top_20ASPXHits.csv' FROM u_ex*.log AND TO_LOWERCASE(cs-uri-stem) LIKE '%%.aspx' GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF REM 06 - Top 20 ASPX Hits Chart LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO 06_IISHC_Top_20ASPXHits.gif FROM u_ex*.log AND TO_LOWERCASE(cs-uri-stem) LIKE '%%.aspx' GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"Top 20 ASPX Hits" -stats:OFF REM 07 - Top 20 ASP Hits LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO '07_IISHC_Top_20ASPHits.csv' FROM u_ex*.log AND TO_LOWERCASE(cs-uri-stem) LIKE '%%.asp' GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF REM 08 - Top 20 ASP Hits Chart LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO 08_IISHC_Top_20ASPHits.gif FROM u_ex*.log AND TO_LOWERCASE(cs-uri-stem) LIKE '%%.asp' GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"Top 20 ASP Hits" -stats:OFF REM 09 - Top 20 Client IP Addresses LogParser.exe "SELECT Top 20 c-ip, COUNT(*) AS Hits INTO '09_IISHC_Top_20ClientIP.csv' FROM u_ex*.log GROUP BY c-ip ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF REM 10 - Top 20 Client IP Addresses Chart LogParser.exe "SELECT Top 20 c-ip, COUNT(*) AS Hits INTO 10_IISHC_Top_20ClientIP.gif FROM u_ex*.log GROUP BY c-ip ORDER BY Hits DESC" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"Top 20 Client IP Addresses" -stats:OFF REM 11 - Top 20 Time Consuming Hits (Sum over the period reviewed) LogParser.exe "SELECT TOP 20 cs-uri-stem, count(*) AS Hits, SUM(time-taken) AS Milliseconds, AVG(time-taken) AS AvgMilliseconds INTO '11_IISHC_Top20TimeConsumingHits.csv' FROM u_ex*.log GROUP BY cs-uri-stem ORDER BY Milliseconds DESC" -i:W3C -o:CSV -stats:OFF REM 12 - Top 20 Outbound Bytes Consuming Hits (Sum over the period reviewed) LogParser.exe "SELECT TOP 20 cs-uri-stem, count(*) AS Hits, SUM(sc-bytes) AS OutboundBytes, AVG(sc-bytes) AS AvgOutboundBytes INTO '12_IISHC_Top20OutboundBytesConsumingHits.csv' FROM u_ex*.log GROUP BY cs-uri-stem ORDER BY OutboundBytes DESC" -i:W3C -o:CSV -stats:OFF REM 13 - Requests Per Hour LogParser.exe "SELECT TO_LocalTime(QUANTIZE(TO_TIMESTAMP(date, time),3600)) AS Hours, COUNT(*) AS Hits INTO 13_IISHC_RequestsPerHour.gif FROM u_ex*.log GROUP BY Hours ORDER BY Hours" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"Requests Per Hour" -stats:OFF REM 14 - Outbound Bytes Per Hour LogParser.exe "SELECT TO_LocalTime(QUANTIZE(TO_TIMESTAMP(date, time),3600)) AS Hours, SUM(sc-bytes) AS Bytes INTO 14_IISHC_OutboundBytesPerHour.gif FROM u_ex*.log GROUP BY Hours ORDER BY Hours" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"Outbound Bytes Per Hour" -stats:OFF REM 15 - Distinct Client IP Addresses Per Hour (Users Per Hour) LogParser.exe "SELECT TO_LocalTime(QUANTIZE(TO_TIMESTAMP(date, time),3600)) AS Hours, c-ip AS CIP INTO '15_IISHC_DistinctClientIPPerHour.csv' FROM u_ex*.log GROUP BY Hours, CIP" -i:W3C -o:CSV -stats:OFF REM 16 - Distinct Client IP Addresses Per Hour (Users Per Hour) Chart LogParser.exe "SELECT Hours, COUNT(*) as Counts FROM '15_IISHC_DistinctClientIPPerHour.csv' TO 16_IISHC_DistinctClientIPPerHour.gif GROUP BY Hours ORDER BY Hours" -i:CSV -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"Distinct Client IP Addresses Per Hour (Users Per Hour)" -stats:OFF REM 17 - HTTP Status Counts LogParser.exe "SELECT DISTINCT sc-status AS Status, COUNT(*) AS Hits INTO '17_IISHC_HTTPStatusCounts.csv' FROM u_ex*.log GROUP BY Status ORDER BY Status ASC" -i:W3C -o:CSV -stats:OFF REM 18 - HTTP Status Distribution LogParser.exe "SELECT DISTINCT sc-status AS Status, COUNT(*) AS Percent INTO 18_IISHC_HTTPStatusDistribution.gif FROM u_ex*.log GROUP BY Status ORDER BY Percent DESC" -i:W3C -config:"LogParserScript.js" -o:CHART -chartType:PieExploded3D -chartTitle:"HTTP Status Distribution" -stats:OFF REM 19 - Top 20 HTTP Status 4XX LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem) as URI, sc-status as HTTPStatus, COUNT(*) AS Hits INTO '19_IISHC_Top_20HTTP4XXErrors.csv' FROM u_ex*.log AND sc-status >= 400 AND sc-status = 500 AND sc-status < 600 GROUP BY URI, sc-status ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF REM 23 - HTTP 500 Errors/Hour LogParser.exe "SELECT TO_LocalTime(QUANTIZE(TO_TIMESTAMP(date, time),3600)) AS Hours, COUNT(*) AS Errors INTO 23_IISHC_HTTP500ErrorsPerHour.gif FROM u_ex*.log AND sc-status = 500 GROUP BY Hours ORDER BY Hours" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"HTTP 500 Errors Per Hour" -stats:OFF REM 24 - Top 20 HTTP 500 Errors LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO '24_IISHC_Top_20HTTP500Errors.csv' FROM u_ex*.log AND sc-status = 500 GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF REM 25 - Top 20 Average Longest Processing Requests LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem) AS URL, COUNT(TO_LOWERCASE(cs-uri-stem)) AS Hits, AVG(sc-bytes) AS sc-bytes, AVG(cs-bytes) AS cs-bytes, MAX(time-taken) as Max, MIN(time-taken) as Min, AVG(time-taken) as Avg INTO '25_IISHC_Top_20AvgLongestProcRequests.csv' FROM u_ex*.log GROUP BY URL ORDER BY AVG(time-taken) DESC" -i:W3C -o:CSV -stats:OFF REM 26 - Top 20 Longest Processing Requests LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem) AS URL, TO_DATE( TO_LOCALTIME( TO_TIMESTAMP(date, time))) AS date, TO_TIME( TO_LOCALTIME( TO_TIMESTAMP(date, time))) AS time, sc-bytes, cs-bytes, sc-status, time-taken INTO '26_IISHC_Top_20LongestProcRequests.csv' FROM u_ex*.log ORDER BY time-taken DESC" -i:W3C -o:CSV -stats:OFF REM 27 - Average/Max Processing Time Per Hour LogParser.exe "SELECT TO_LocalTime(QUANTIZE(TO_TIMESTAMP(date, time),3600)) AS Hours, AVG(time-taken) AS Avg, MAX(time-taken) AS Max INTO 27_IISHC_AvgMaxProcTimePerHour.gif FROM u_ex*.log AND sc-status = 200 GROUP BY Hours ORDER BY Hours" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:Line -groupsize:800x600 -chartTitle:"Average/Max Processing Time Per Hour (ms)" -stats:OFF REM 28 - Aggregated Average Processing Time Per Hour LogParser.exe "SELECT TO_LocalTime(quantize(time,3600)) AS Hours, AVG(time-taken) AS Avg INTO 28_IISHC_AggregatedAvgProcTimePerHourRadar.gif FROM u_ex*.log AND sc-status = 200 GROUP BY Hours ORDER BY Hours" -i:IISW3C -recurse:-1 -o:CHART -charttype:RadarLineFilled -groupsize:800x600 -chartTitle:"Aggregated Average Processing Time Per Hour (ms)" -stats:OFF REM 29 - Processing Time Per Extension LogParser.exe "SELECT EXTRACT_EXTENSION(TO_LOWERCASE(cs-uri-stem)) AS Extension, MUL(PROPSUM(time-taken),100.0) AS ProcessingTime INTO 29_IISHC_ProcTimePerExt.gif FROM u_ex*.log GROUP BY Extension ORDER BY ProcessingTime DESC" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:PieExploded3D -groupsize:800x600 -chartTitle:"Processing Time Per Extension" -categories:off -stats:OFF REM 30 - Outbound BandWidth In MBps LogParser.exe "SELECT DIV(DIV(MUL(1.0, SUM(sc-bytes)),1048576), 86399) AS OutboundBandWidthInMBps INTO '30_IISHC_OutboundBandWidthInMBps.csv' FROM u_ex*.log" -i:W3C -o:CSV -stats:OFF REM 31 - Requests Per Second LogParser.exe "SELECT DIV(MUL(1.0, COUNT(*)), 86399) AS RequestsPerSecond FROM u_ex*.log TO '31_IISHC_RequestsPerSecond.csv'" -i:W3C -o:CSV -stats:OFF REM 32 - HTTP 400 Errors/Hour LogParser.exe "SELECT TO_LocalTime(QUANTIZE(TO_TIMESTAMP(date, time),3600)) AS Hours, COUNT(*) AS Errors INTO 32_IISHC_HTTP400ErrorsPerHour.gif FROM u_ex*.log AND sc-status = 400 GROUP BY Hours ORDER BY Hours" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"HTTP 400 Errors Per Hour" -stats:OFF REM 33 - Top 20 HTTP 400 Errors LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO '33_IISHC_Top_20HTTP400Errors.csv' FROM u_ex*.log AND sc-status = 400 GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF

 

The file LogParserScript.js (used in some lines above) is available here (you have to change the extension into .js –  it only used for the chart generation)

All the requests can be downloaded here.

 

Laurent.

Comments (0)

Skip to main content