The purpose of this article is to show the practical way of analyzing IIS logs, and using those parsed results for analytical using.
After installing the IIS Log Parser application, let’s assume in Windows OS PC, I’m now using below command to generate one CSV file for all of the traffic hits counted to the log file folder:
The LogParser.exe (v2.2) is by default installed at c:\Program Files (x86)\Log Parser 2.2\, go to this folder then running below –
1 | LogParser.exe -i:W3C "SELECT cs-uri-stem as Url, Count(*) As Hits FROM C:\Temp\W3SVC2\* GROUP BY cs-uri-stem ORDER BY Hits DESC" -o:CSV > c:\temp.csv |
Thanks Carlos Aguilar, he gives very nice guides here https://blogs.iis.net/carlosag/analyze-your-iis-log-files-favorite-log-parser-queries.
The syntax is: LogParse.exe -i:W3C "Query-From-The-Table-Below" -o:CSV > "destination CSV file path and name"
I’m digesting the useful queries here:
| Purpose | Query |
|---|---|
| Number of Hits per Client IP, including a Reverse DNS Lookup (SLOW) | SELECT c-ip AS Machine, REVERSEDNA(c-ip) As Name, COUNT(*) As Hits FROM c:\inetput\log\LogFiles\W3SVC1\* GROUP BY Machine ORDER BY Hits DESC |
| Top 25 File Types | SELECT TOP 25 EXTRACT_EXTENSION(cs-uri-stem) As Extension, COUNT(*) As Hits FROM c:\inetput\log\LogFiles\W3SVC1\* GROUP BY Extension ORDER BY Hits DESC |
| Top 25 URLs | SELECT TOP 25 cs-uri-stem as Url, COUNT(*) As Hits FROM c:\inetput\log\LogFiles\W3SVC1\* GROUP BY cs-uri-stem ORDER BY Hits DESC |
| Number of hits per hour for the month of March | SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(date, time)), 3600) AS Hour, COUNT(*) AS Hits FROM c:\inetput\log\LogFiles\W3SVC1\* WHERE date>'2022-03-01' and date<'2022-04-01' GROUP BY Hour |
| Number of hits per Method (GET, POST, etc) | SELECT cs-method As Method, COUNT(*) As Hits FROM c:\inetput\log\LogFiles\W3SVC1\* GROUP BY Method |
| Number of requests made by user | SELECT TOP 25 cs-username As User, COUNT(*) As Hits FROM c:\inetput\log\LogFiles\W3SVC1\* WHERE User Is Not Null GROUP BY User |
| Extract Values from Query String (d and t) and use them for Aggregation | SELECT TOP 25 EXTRACT_VALUE(cs-uri-query,'d') as Query_D, EXTRACT_VALUE(cs-uri-query,'t') as Query_T, COUNT(*) As Hits FROM c:\inetput\log\LogFiles\W3SVC1\* WHERE Query_D IS NOT NULL GROUP By Query_D, Query_T ORDER BY Hits DESC |
| Find the Slowest 25 URLs (in average) in the site | SELECT TOP 25 cs-uri-stem as URL, MAX(time-taken) AS Max, MIN(time-taken) AS Min, Avg(time-taken) As Average FROM c:\inetput\log\LogFiles\W3SVC1\* GROUP BY URL ORDER BY Average DESC |
| List the count of each Status and Substatus code | SELECT TOP 25 STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) As Status, COUNT(*) As Hits FROM c:\inetput\log\LogFiles\W3SVC1\* GROUP BY Status ORDER BY Status ASC |
| List all the requests by user agent | SELECT cs(User-Agent) As UserAgent, COUNT(*) as Hits FROM c:\inetput\log\LogFiles\W3SVC1\* GROUP BY UserAgent ORDER BY Hits DESC |
| \ List all the Win32 Error codes that have been logged | SELECT sc-win32-status As Win32-Status, WIN32_ERROR_DESCRIPTION(sc-win32-status) as Description, COUNT(*) As Hits FROM c:\inetput\log\LogFiles\W3SVC1\* GROUP BY Win32-Status ORDER BY Win32-Status ASC |
Note: Any time you deal with Date and Time, remember to use the TO_LOCALTIME function to convert the log times to your local time, otherwise you will find it very confusing when your entries seems to be reported incorrectly.