Analyze your IIS Log Files - Favorite Log Parser Queries
The other day I was asked if I knew about a tool that would allow users to easily analyze the IIS Log Files, to process and look for specific data that could easily be automated. My recommendation was that if they were comfortable with using a SQL-like language that they should use Log Parser. Log Parser is a very powerful tool that provides a generic SQL-like language on top of many types of data like IIS Logs, Event Viewer entries, XML files, CSV files, File System and others; and it allows you to export the result of the queries to many output formats such as CSV (Comma-Separated Values, etc), XML, SQL Server, Charts and others; and it works well with IIS 5, 6, 7 and 7.5.
To use it you just need to install it and use the LogParser.exe that is found in its installation directory (on my x64 machine it is located at: C:\Program Files (x86)\Log Parser 2.2).
I also thought on sharing some of my favorite queries. To run them, just execute LogParser.exe and make sure to specify that the input is an IIS Log file (-i:W3C) and for ease of use in this case we will export to a CSV file that can be then opened in Excel (-o:CSV) for further analysis:
| Purpose | Query | Sample Output | ||||||||||||||||||||||||
| Number of Hits per Client IP, including a Reverse DNS lookup (SLOW) | SELECT c-ip As Machine, REVERSEDNS(c-ip) As Name, COUNT(*) As Hits FROM c:\inetpub\logs\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:\inetpub\logs\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:\inetpub\logs\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:\inetpub\logs\LogFiles\W3SVC1\* WHERE date>'2010-03-01' and date<'2010-04-01' Group By Hour |
| ||||||||||||||||||||||||
| Number of hits per Method (GET, POST, etc) | SELECT cs-method As Method, COUNT(*) As Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY Method |
| ||||||||||||||||||||||||
| Number of requests made by user | SELECT TOP 25 cs-username As User, COUNT(*) as Hits FROM c:\inetpub\logs\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:\inetpub\logs\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:\inetpub\logs\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:\inetpub\logs\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:\inetpub\logs\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:\inetpub\logs\LogFiles\W3SVC1\* WHERE Win32-Status<>0 GROUP BY Win32-Status ORDER BY Win32-Status ASC |
|
A final 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 seem to be reported incorrectly.
If you need any help you can always visit the Log Parser Forums to find more information or ask specific questions.
Any other useful queries I missed?