Log Parser usage

Log parser is a free tool from Microsoft where developers and support technicians can use to parse the IIS logs, Event Logs and Active Directory logs. This tool helps the developers to find the root cause for the site related issues. This post explains common commands that we use to look for specific information in IIS logs.

The tool can be downloaded from here

The default location for IIS logs is %windir%\System32\LogFiles\W3SVC<SiteID>.

The websiteID for your web site can be found from IIS as follows

• Open IIS and right click on your website then see the logging path. In IIS 6.0 it gives a unique id for your website for logging.

Log parser is a command line tool where you need to write the SQL queries for parsing the log files. There is a GUI tool for Log parser named which can be downloaded from here

1. The most common requirement is to find the pages in your site which are running very slowly. You can write the following SQL query to see the pages that are running slow

Select Top 10 LogRow as [Line Number],

date as [Date], time as [Time],

c-ip as [Client-IP], s-ip as [Server IP], 

s-port as [Server Port], cs-method as [Request Verb],

cs-uri-stem as [Request URI], sc-bytes as [Bytes sent],

sc-status as [Status], sc-substatus as [Sub-status],

sc-win32-status as [Win 32 Status], time-taken as [Time Taken]

From C:\ex100212.log

Order by time-taken desc

2. If you want to determine how many internal server errors and other HTTP status code failures on your server then you can write the following command

SELECT      STRCAT (TO_STRING(sc-status),
STRCAT('.', TO_STRING(sc-substatus))) AS Status, 

COUNT(*) AS Total FROM C:\ex100212.log

GROUP BY Status ORDER BY Total DESC

3. To know the Number of hits to specific page in your website then write the following  sql command

SELECT cs-uri-stem AS [Request URI], COUNT(*) As Hits FROM C:\ex100212.log WHERE 

EXTRACT_FILENAME(cs-uri-stem) like 'referrals.aspx'

GROUP BY cs-uri-stem

4. To display the Number of hits to specific page by specific user then use the following command

SELECT cs-uri-stem AS [Request URI], COUNT(*) As Hits,

cs-username AS[User Name] FROM C:\ex100212.log WHERE 

EXTRACT_FILENAME(cs-uri-stem) like '%referrals.aspx' and

cs-username like '%JohnSmith%' GROUP BY cs-uri-stem,cs-username

5. The following query can be used to get the TOP 10 URL’s requested from IIS log

SELECT  TOP 10  cs-uri-stem AS Url,
COUNT(*) AS Hits FROM C:\ex100212.log
GROUP BY Url ORDER BY Hits DESC

6. write the following query in order to retrieve the total number of different browsers and number of different client IP addresses that requested pages from IIS server for particular web site.

SELECT     COUNT(DISTINCT cs(User-Agent)) AS Browsers, 
COUNT(DISTINCT c-ip) AS Clients
FROM C:\ex100212.log

7. To show the percentages against the pages that user requested, write the following query

SELECT EXTRACT_FILENAME(cs-uri-stem) AS PageType, 
MUL( PROPCOUNT(*), 100.0 ) AS PageTypeHits
FROM ex040528.log GROUP BY PageType

8. Using Log parser with Event log.

SELECT TimeGenerated, EventID, EventTypeName,
EventCategoryName, SourceName, Message 
FROM APPLICATION"

Share this post :