Being in the support industry is pretty much like a technician in a factory most times, except that we work on computers and technicians work on machines. As I see it, one of the common things between a support technician and other technicians is the usage of tools to fix problems. Support Engineers like me use software tools to troubleshoot problems. There are a ton of free tools out there on the internet that accomplish many tasks and provid valuable information to resolve computer related problems. My personal favorites are tools developed by Mark Russinovich, who, before joining Microsoft, wrote a lot of tools for the Windows platform. I use them every day in my job and without those tools it would be extremely difficult to scope and get to the root cause of the problem.

One of the tools that I use probably everyday is LogParser from Microsoft. It is a splendid tool if you want to parse very large log files and have knowledge of SQL statements. Please do not be disheartened if you do not know SQL. With a little practice, you can become an expert in using LogParser. I am going to put down some of the common commands that I use to look for specific information in IIS logs. Hopefull, this will help you get started if you are a novice. I will give you a small background and then show you how LogParser helps.

First, LogParser is my choice when I have to parse multiple IIS logs and IIS logs that are really large in size. The other tool that I use is Microsoft Excel, which is an excellent tool for parsing text files that contain data separated with delimiter.

IIS 6.0 and later have request logging enabled by default. The default location for these logs is:

IIS 6.0: %windir%\System32\LogFiles\W3SVC<SiteID>

IIS 7.0: %systemDrive%\Inetpub\logfiles

Also, to enable specific attributes to be logged, open IIS Manager and bring up the properties for the site you want to configure which attributes to log. Then on the WebSite tab, click on the Properties button under “Enable Logging” and then select the attributes from the Advanced tab.

LogParser download location: http://www.microsoft.com/downloads  

Search for Log Parser. As of this writing, the latest version is 2.2.

Listing 1: Log Parser Command Line

LogParser command line is really simple. 
LogParser –i:<inputFileFormat> -o:<output format> <Sql Query> | file:<text file 
with SQL Query>
-i:<input_format>   :  one of IISW3C, NCSA, IIS, IISODBC, BIN, IISMSID,
                              HTTPERR, URLSCAN, CSV, TSV, W3C, XML, EVT, ETW,
                              NETMON, REG, ADS, TEXTLINE, TEXTWORD, FS, COM (if
                              omitted, will guess from the FROM clause)
 
 -o:<output_format>  :  one of CSV, TSV, XML, DATAGRID, CHART, SYSLOG,
                              NEUROVIEW, NAT, W3C, IIS, SQL, TPL, NULL (if omitted,
                              will guess from the INTO clause)
 
<SQL Query> OR file:<text file that contains sql query>

So from above, we have: the command, LogParser, an input specification, an output specification and the query or file that contains the query we want to run.

This one is very common and I probably see this almost every day. Your Web site or web application is performing very slowly and you want to know which pages are taking the longest amount of time to be served. You can run the following query from the log parser command prompt.

Listing 2

LogParser "SELECT cs-uri-stem, time-taken FROM ex080620.log ORDER BY time-taken 
DESC" -i:IISW3C -q:ON

Now, let us say you want to view the Top 5 from this, you can then use:

Listing 3

LogParser "SELECT TOP 5 cs-uri-stem, time-taken INTO MyChart.GIF FROM ex080620.log 
ORDER BY time-taken DESC" -i:IISW3C -charttype:Bar3d -view:ON

Figure 1

OR create an exploded PIE:

Listing 4

LogParser "SELECT TOP 5 cs-uri-stem, time-taken INTO MyChart.GIF FROM ex080620.log 
ORDER BY time-taken DESC" -i:IISW3C -charttype:PieExploded3d -view:ON

Figure 2

Listing 5: Finding the average time taken for web pages

LogParser -i:IISW3C "SELECT cs-uri-stem, AVG(time-taken) As AvgTime FROM 
ex080620.log GROUP BY cs-uri-stem" -q:ON -0:DataGrid

Listing 6: Finding the number of hits to your web pages

LogParser -i:IISW3C "SELECT cs-uri-stem, COUNT(*) As Hits FROM ex080620.log GROUP 
BY cs-uri-stem" -q:ON –o:DataGrid

Listing 7: Finding the number of hits to web pages in a 3d Bar graph

LogParser -i:IISW3C "SELECT cs-uri-stem, COUNT(*) As Hits INTO Hits.Gif FROM 
ex080620.log GROUP BY cs-uri-stem" -chartType:Bar3D -ChartTitle "WebPage Hits" 
-view:ON

Figure 3

Listing 8: Finding the number of hits to a specific web page

LogParser -i:IISW3C "SELECT cs-uri-stem, COUNT(*) As Hits FROM ex080620.log WHERE 
EXTRACT_FILENAME(cs-uri-stem) = 'AdvancedSearch.aspx' GROUP BY cs-uri-stem" 
-o:DataGrid

Create a text file and name it as "HitsToPage.bat" and paste the following line in it. Then save it.

Listing 9: Scripting the above with batch file

LogParser -i:IISW3C "SELECT cs-uri-stem, COUNT(*) As Hits FROM %1 WHERE 
EXTRACT_FILENAME(cs-uri-stem) = '%2' GROUP BY cs-uri-stem" -o:DataGrid

Run it as example: HitsToPage C:\Logs\ex080620.log AdvancedSearch.aspx.

Listing 10: Finding what is the Hit rate for various types of content like GIF, HTML, etc.

LogParser -i:IISW3C "SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType, COUNT(*) 
AS Hits INTO PageDistribution.GIF FROM ex080620.log GROUP BY PageType ORDER BY 
Hits DESC" -chartType:PieExploded3d -view:ON

Figure 4

Listing 11: Finding what type of content takes most of the bandwidth on the IIS web server

NOTE: You must enable the field sc-bytes in IIS to get this.

LogParser -i:IISW3C "SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType, 
SUM(sc-bytes) AS Bytes INTO TrafficDistribution.GIF FROM ex080620.log GROUP BY 
PageType ORDER BY Bytes DESC" -chartType:PieExploded3d -view:ON

Now, let us say you want to trace requests from a specific client IP:

Listing 12

LogParser "SELECT * FROM ex080620.log WHERE c-ip='10.253.209.17'" -i:IISW3C -q:ON 
-o:datagrid

You can extend this further to trace requests from a specific client IP during a specific date range.

Listing 13

LogParser "SELECT * FROM ex080620.log WHERE date > '2008-06-19' AND date < 
'2008-07-22'" -i:IISW3C -q:ON -o:datagrid

Suppose you want to trace requests from a specific username (assuming that the request is not anonymous).

Listing 14

LogParser "SELECT * FROM ex080620.log WHERE cs-username='Domain\User'" -i:IISW3C 
-q:ON –o:Datagrid

Listing 15: Finding requests between specific time frames

NOTE: IIS Logs time in GMT

LogParser -i:IISW3C "SELECT date, time, cs-uri-stem, cs-uri-query, cs-username, 
c-ip, sc-status, sc-substatus, sc-win32-status, time-taken FROM ex080620.log WHERE 
TO_TIME(time) BETWEEN TIMESTAMP('00:00:02','hh:mm:ss') AND 
TIMESTAMP('00:00:08','hh:mm:ss')" -o:DataGrid

Scripting the above

Create a text file and name it as "RequestsInTime.bat" and paste the following line in it. Then save it.

Listing 16

LogParser -i:IISW3C "SELECT date, time, cs-uri-stem, cs-uri-query, cs-username, 
c-ip, sc-status, sc-substatus, sc-win32-status, time-taken FROM %1 WHERE 
TO_TIME(time) BETWEEN TIMESTAMP('%2','hh:mm:ss') AND TIMESTAMP('%3','hh:mm:ss')" 
-o:DataGrid

Run it as RequestsInTime C:\Logs\ex080620.log 00:00:02 00:00:10.

Now you wish to trace requests to a specific URL and want to get the protocol status and protocol sub-status combined.

Listing 17

LogParser -i:IISW3C -o:DataGrid "SELECT cs-uri-stem, STRCAT(TO_STRING(sc-status), 
STRCAT('.',TO_STRING(sc-substatus))) as Status FROM ex080620.log" -q:ON 
–o:DataGrid

This will find all requests that failed with a status code of HTTP 500.

Listing 18

LogParser -i:IISW3C -o:DataGrid "SELECT cs-uri-stem,  sc-status as Status FROM  
ex080620.log WHERE Status='500'" -q:ON

You wish to find out the requests with a specific cookie in the request headers.

Listing 19

LogParser -i:IISW3C -o:DataGrid "SELECT cs-uri-stem,  sc-status as Status FROM  
ex080620.log WHERE like cs-cookie '%ASPSESSIONID%'"  -q:ON

Listing 20: Find requests with extension PNG

LogParser -i:IISW3C -o:DataGrid "SELECT cs-uri-stem,  sc-status as Status FROM  
ex080620.log WHERE cs-uri-stem like '%png%'"  -q:ON

Get all entries from all HTTPErr logs on the computer.

Listing 21

Logparser -i:HTTPErr "Select * from C:\windows\system32\Logfiles\httperr\*.log" 
-q:ON

Suppose you are investigating a connection failure and you want to check if the connection was dropped from IIS or HTTP; you also want to find out the date, time, the URL requested and the reason for the failure. If this is the case, you can use the following log parser query that will not only give you the log file where the entry is, but also the data you need to further troubleshoot the problem.

Listing 22

LogParser -i:HTTPErr "Select logfilename, date, time, cs-uri, s-reason from 
C:\windows\system32\Logfiles\httperr\*.log" -q:ON -o:datagrid
LogParser -i:HTTPErr "SELECT date, time, c-ip, cs-uri, sc-status, s-reason FROM 
httperr1.log ORDER BY date,time" -o:DataGrid -q:ON

Listing 23: Sorting the output by reason for failure

LogParser -i:HTTPErr "SELECT date, time, c-ip, cs-uri, sc-status, s-reason FROM 
httperr1.log ORDER BY s-reason" -o:DataGrid -q:ON

Now suppose, you are investigating a specific problem, such as "Service Unavailable," and you want to find out details like the client IP, client port, and reason. You can filter this with the sc-status code of 503.

Listing 24

Logparser -i:HTTPErr "Select logfilename, date, time, cs-uri, c-port, s-reason 
from C:\windows\system32\Logfiles\httperr\*.log WHERE sc-status=503" -q:ON 
-o:datagrid

Your Web site or web application is performing very slowly and you want to know which pages are taking the longest amount of time.

Listing 25

Logparser -i:TEXTLINE "SELECT Text FROM Filemon.log WHERE Text LIKE 
'%SHARING VIOLATION%'" –o:datagrid
Logparser -i:TEXTLINE "SELECT Text FROM Filemon.log WHERE Text LIKE 
'%explorer.exe%'" -o:DataGrid

This helps in finding all Events from Application Event Log on the local computer.

Lisitng 26

LogParser -i:EVT "SELECT TimeGenerated, EventID, EventTypeName, EventCategoryName, 
SourceName, Message FROM APPLICATION" -o:DataGrid

Finding the Events from a specific source:

Listing 27

LogParser -i:EVT "SELECT TimeGenerated, EventID, EventTypeName, EventCategoryName, 
SourceName, Message  FROM APPLICATION WHERE SourceName='COM+'" -o:DataGrid

Finding Events from System Event Log with a source of Service Control Manager:

Listing 28

LogParser -i:EVT "SELECT TimeGenerated, EventID, EventTypeName, EventCategoryName, 
SourceName, Message FROM SYSTEM WHERE SourceName='Service Control Manager'" 
-o:DataGrid

Scripting the above

Create a text file and name it as "EventsFromSource.bat" and paste the following line in it. Then save it.

Listing 29

LogParser -i:EVT "SELECT TimeGenerated, EventID, EventTypeName, EventCategoryName, 
SourceName, Message FROM %1 WHERE SourceName='%2'" -o:DataGrid
Run it as example:      EventsFromSource SYSTEM DCOM
                        EventsFromSource APPLICATION  COM+

Using LogParser with saved Event logs

The following show how to get all events with a specific event ID and source. For example: Event ID of 37 and sourcename=W3SVC.

Listing 30

Logparser -i:EVT "SELECT * FROM system.evt WHERE sourcename='W3SVC' AND 
EventID=37" -o:DataGrid

Suppose you want to view only the events that were generated on and after July 07, 2008, listing the most recent. The query will be:

Listing 31

Logparser -i:EVT "SELECT * FROM system.evt WHERE TimeGenerated > 
'2008-07-07 00:00:00' Order by TimeGenerated DESC" -o:datagrid

Now, let us say you want to view only the warning events and error events that were generated on and after July 07, 2008.

Listing 32

Logparser -i:EVT "SELECT * FROM system.evt WHERE EventType=1 or EventType=2 and 
timegenerated > '2008-07-07 00:00:00'" -o:datagrid

You could do this with the Event logging from your machine too! The command below picks up all events with ID 101 from the Application event log into AppReport.txt.

Listing 33

<span lang=EN-IN>Logparser –i:EVT "SELECT TimeGenerated, EventID, EventTypeName, EventCategoryName, </span>
<span lang=EN-IN>SourceName, Message INTO AppReport.txt FROM APPLICATION WHERE EventID=101" </span>
<span lang=EN-IN>-o:DataGrid</span>

NOTE: If you are not sure about the column names, just use * to select all fields and then note down the specific fields you are interested in and use them in the query.

View network traffic from a netmon trace with the following.

Listing 34

LogParser -i:NETMON "SELECT Frame, SrcIP, SrcPort, DstIP, DstPort, TCPFlags, Seq, 
Ack, WindowSize, PayloadBytes FROM Netmon.cap" -o:DataGrid

Filter network traffic between 2 systems from a netmon trace:

Listing 35

LogParser -i:NETMON "SELECT Frame, SrcIP, SrcPort, DstIP, DstPort, TCPFlags, Seq, 
Ack, WindwSize, PayloadBytes FROM Netmon.cap WHERE (SrcIP='192.168.10.1' AND 
DstIP='192.168.10.8') OR (SrcIP='192.168.10.8' AND DstIP='192.168.10.1')" 
-o:DataGrid

Viewing an ETW Trace:

Listing 36

LogParser -i:ETW "SELECT EventName, EventTypeName, TimeStamp, UserData FROM 
Test_01.etl" -o:DataGrid

I would recommend checking out the log parser command line options. It can give you some more examples which include how to get statistics from a URL, Active Directory, etc. and many of the functions supported by LogParser. You will be amazed! Have fun using LogParser and as always, please send me your comments or suggestions about this article.

References

Description of IIS logging

Log Parser improvements, features and Functions