IIS Log Parser

This is an important tool for looking specifically into IIS Logs. Now this tool does not have a good GUI but has good documentation that can be used. In case you cannot use the SPDiag tool and are provided only with all log files, the IIS Log Parser is a good tool to use. There is however a bit of a learning curve.

You can download it from http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

Log Parser 2.2 is a powerful, versatile tool that provides universal query access to text-based data, such as log files, XML files and CSV files, as well as key data sources on the Windows operating system such as the Event Log, the Registry, the file system, and Active Directory. You tell Log Parser what information you need and how you want it processed. The results of your query can be custom-formatted in text based output, or they can be persisted to more specific targets like SQL, SYSLOG, or a chart. 

After installing Log Parser 2.2 you will see a help file with all the details of how to use it. We will see here some scenario-specific usage.

Example 1: You want to determine the top 10 slowest pages in a web site.

Solution: Create a file called Slowest10Files.sql with the following code:

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 E:\inetpub\logs\LogFiles\W3SVC1395779599\u_ex121213.log

Order by time-taken desc

Ensure the "From" value is your environment specific IIS Log file. Here, -i:IISW3C signifies that we are querying the IIS W3C logs. You can view the complete list of IISW3C input format fields in the documentation and frame your query accordingly.

-o:DataGrid implies that the output should be shown in a data grid.

On the command prompt window of the log parser run the following command:

E:\Program Files (x86)\Log Parser 2.2>LOGPARSER -i:IISW3C file:E:\LogParserQueries\Slowest10Files.sql -o:DataGrid -q:off

You will see the output as follows:



Figure  SEQ Figure \* ARABIC 1: IIS Log Parser

Now particularly in my case this gives the interesting observation that _layouts/15/templatepick.aspx took the maximum amount of time and hence by the time home.aspx is reached it took a lot of time.

Example 2: Finding the 10 most commonly used .aspx pages in your web site

Solution: Create a file called Most10Pages.sql with the following code:

Select Top 20

cs-uri-stem as [Request URI],

COUNT(*) AS Hits

INTO Most10PagesChart.png

FROM E:\inetpub\logs\LogFiles\W3SVC1395779599\u_ex121213.log

Group by cs-uri-stem ORDER BY Hits

DESC

Then from the command prompt run the following command:

E:\Program Files (x86)\Log Parser 2.2>LOGPARSER -i:IISW3C file:e:\LogParserQueries\Most10Files.sql -o:DataGrid -q:off

Here is how the output looks:



Figure  SEQ Figure \* ARABIC 2: Log Parser Output

If you want the output in chart format then you can specify –o:chart. The set of available chart types depend on the version of the Microsoft Office Web Components installed on the local computer.

Example 3: We want to generate a report containing the words "error" or "warning" that are present in the SharePoint logs.

Solution: Create a SQL file with the following command:

Select Process, Area, Category, Message FROM 'E:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\LOGS\SHAREPOINT-2013-20130224-1504.log' WHERE Message LIKE '%error%'

Run the following command:

E:\Program Files (x86)\Log Parser 2.2>logparser -i:tsv -headerrow:on file:e:\log parserqueries\allerrors.sql -o:Datagrid

You will get the first 10 rows. You can click "All Rows" and all the entries where the error is present in the message will be shown.



Figure  SEQ Figure \* ARABIC 3: Log Parser: View All Records

You can recursively do it for all SharePoint log files but it is not recommended since the log files can be months old and too large. Use your discretion when to run this command recursively.

You can similarly get all warnings from a log file.

You can copy all rows from this data grid to an Excel file for further analysis.