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

Article
03/30/2016

[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 WHERE 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 WHERE 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 WHERE 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 WHERE 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 WHERE 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 20 - HTTP 404 Errors Per Hour
LogParser.exe "SELECT QUANTIZE(TO_TIMESTAMP(date, time),3600) AS Hours, COUNT(*) AS Errors INTO 20_IISHC_HTTP404ErrorsPerHour.gif FROM u_ex*.log WHERE sc-status = 404 GROUP BY Hours ORDER BY Hours" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"HTTP 404 Errors Per Hour" -stats:OFF
REM 21 - Top 20 HTTP 404 Errors
LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO '21_IISHC_Top_20HTTP404Errors.csv' FROM u_ex*.log WHERE sc-status = 404 GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF
REM 22 - Top 20 HTTP Status 5XX
LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem) as URI, sc-status as HTTPStatus, COUNT(*) AS Hits INTO '22_IISHC_Top_20HTTP5XXErrors.csv' FROM u_ex*.log WHERE 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 WHERE 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 WHERE 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 WHERE 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 WHERE 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 WHERE 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 WHERE 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.

Version française

[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 WHERE 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 WHERE 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 WHERE 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 WHERE 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 WHERE 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 20 - HTTP 404 Errors Per Hour
LogParser.exe "SELECT QUANTIZE(TO_TIMESTAMP(date, time),3600) AS Hours, COUNT(*) AS Errors INTO 20_IISHC_HTTP404ErrorsPerHour.gif FROM u_ex*.log WHERE sc-status = 404 GROUP BY Hours ORDER BY Hours" -i:W3C -config:"LogParserScript.js" -o:CHART -charttype:ColumnClustered -groupsize:800x600 -chartTitle:"HTTP 404 Errors Per Hour" -stats:OFF
REM 21 - Top 20 HTTP 404 Errors
LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem), COUNT(*) AS Hits INTO '21_IISHC_Top_20HTTP404Errors.csv' FROM u_ex*.log WHERE sc-status = 404 GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY Hits DESC" -i:W3C -o:CSV -stats:OFF
REM 22 - Top 20 HTTP Status 5XX
LogParser.exe "SELECT Top 20 TO_LOWERCASE(cs-uri-stem) as URI, sc-status as HTTPStatus, COUNT(*) AS Hits INTO '22_IISHC_Top_20HTTP5XXErrors.csv' FROM u_ex*.log WHERE 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 WHERE 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 WHERE 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 WHERE 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 WHERE 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 WHERE 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 WHERE 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.

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

Additional resources