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
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