Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows operating system such as the Event Log, the Registry, the file system, and Active Directory. This is how a IIS web server log looks like which is hard to read when you want to gather information to either diagnose an issue or analyze the logs.
Parsing IIS logs
Log Parser tool allows you to analyze web server logs and event logs to provide you the snippet of information you are looking for .Download the tool and install the MSI package of the tool.
- Usually the tool is installed on C:\Program Files\LogParser or C:\Program Files (x86)\LogParser locally when running it from your client machine.
- Another option is to upload LogParser.exe onto web app file server ( under D:\home) and run it using Azure web app KUDU debug console.
Here are some sample queries or command you can use to gather information about IPs , number of hits etc . To run these you need to execute LogParser.exe with this command format
LogParser.exe -i:W3C Query -o:CSV
Where -i:W3C is the input is an IIS Log file and output in the format of CSV for ease of use but you can also use XML . When running the query please update the path to your IIS log files downloaded form Azure storage or file storage for your web app
- To get the number of Hits per Client IP, including a Reverse DNS lookup use this query
SELECT c-ip As Machine,REVERSEDNS(c-ip) As Name,COUNT(*) As Hits FROM c:\iislogs\* GROUP BY Machine ORDER BY Hits DESC
- To get the Top 25 file types
SELECT TOP 25 EXTRACT_EXTENSION(cs-uri-stem) As Extension, COUNT(*) As Hits FROM c:\iislogs\*
\* GROUP BY Extension ORDER BY Hits DESC
- To get the top 25 URLs
SELECT TOP 25 cs-uri-stem as Url, COUNT(*) As Hits FROM c:\iislogs\* GROUP BY cs-uri-stem ORDER By Hits DESC
- To get the 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:\iislogs\* WHERE date>’2010-03-01′ and date<‘2010-04-01’ Group By Hour
- To get the number of hits per Method (GET, POST, etc)
SELECT cs-method As Method, COUNT(*) As Hits FROM c:\iislogs\* GROUP BY Method
- To get the number of requests made by user
SELECT TOP 25 cs-username As User,COUNT(*) as Hits FROM c:\iislogs\* WHERE User Is Not Null GROUP BY User
- To extract the 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:\iislogs\* WHERE Query_D IS NOT NULL GROUP BY Query_D, Query_T ORDER By Hits DESC
- To 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:\iislogs\* GROUP BY URL ORDER By Average DESC
- To 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:\iislogs\* GROUP BY Status ORDER BY Status ASC
- To list all the requests by user agent
SELECT cs(User-Agent) As UserAgent, COUNT(*) as Hits FROM c:\iislogs\* GROUP BY UserAgent ORDER BY Hits DESC
Parsing Event Logs
You can find the event log under D:\home\Logfiles . Check out this blog post on using Log parser to parse event logs.
Using System Center Advisor
This blog post show how to parser IIS Logs from your Operations Manager agents into System Center Advisor.