Techbubbles

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 :


Related Posts:

  • No Related Posts
  • http://kelloggs.com Adam

    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

    • http://www.techbubbles.com Kalyan Bandarupalli

      I recommend you to import all your log files to database then you can write the queries based on dates to see the reports for individual months.

  • Vikas Arora

    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?

  • http://www.gs1uk.org Anil GIdla

    Hello

    Thanks for the valuable information on the site.
    I am looking for a query to find the users who have visited a particular URL.

    Say for ex – Anil -> http://www.test.gs1uk.org\default.aspx

    Please if possible kindly send the process with query.

    Many Thanks
    Anil

  • http://www.gs1uk.org Anil GIdla

    sorry, To be more clear i need the list of users who have visited a particular webpage

  • http://www.techbubbles.com Kalyan Bandarupalli

    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

%d bloggers like this: