TechBubbles Microsoft Technology BLOG

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 :

About the author

Kalyan Bandarupalli

My name is kalyan, I am a software architect and builds the applications using Microsoft .NET technologies. Here I am trying to share what I feel and what I think with whoever comes along wandering to Internet home of mine.I hope that this page and its contents will speak for me and that is the reason I am not going to say anything specially about my self here.

6 Comments

  • I need a log parser query that will run against a months worth of log files for several web sites and I would like the output to total by cs-host for the entire month…not for each daily log file. What do you recommend? Thank you, Adam

  • I have the same requirement as Adam. Do you think importing data to database and then querying it would be more faster than actually querying through the IIS log files?

  • You can use below query to list the users to specific page

    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

TechBubbles Microsoft Technology BLOG

Follow me

Archives

Tag Cloud