Using Log parser 2.2 to parse IIS logs for Azure web apps

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.

image

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.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s