IIS logs are a good source of information when you want to analyze the behavior of your site. There are many tools and log parsers around, but I tend to prefer SQL Servers OPENROWSET to parse and query log files.

To use OPENROWSET you need to map the format of the file to a virtual database table using an Xml Format file. I use a file like this one:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <RECORD>
        <FIELD ID="1" xsi:type="CharTerm" TERMINATOR=", " MAX_LENGTH="15"/>
        <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=", " MAX_LENGTH="20"/>
        <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=", " MAX_LENGTH="20"/>
        <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=", " MAX_LENGTH="20"/>
        <FIELD ID="5" xsi:type="CharTerm" TERMINATOR=", " MAX_LENGTH="20"/>
        <FIELD ID="6" xsi:type="CharTerm" TERMINATOR=", " MAX_LENGTH="20"/>
        <FIELD ID="7" xsi:type="CharTerm" TERMINATOR=", " MAX_LENGTH="20"/>
        <FIELD ID="8" xsi:type="CharTerm" TERMINATOR=", " MAX_LENGTH="20"/>
        <FIELD ID="9" xsi:type="CharTerm" TERMINATOR=", " MAX_LENGTH="20"/>
        <FIELD ID="10" xsi:type="CharTerm" TERMINATOR=", " MAX_LENGTH="20"/>
        <FIELD ID="11" xsi:type="CharTerm" TERMINATOR=", " MAX_LENGTH="20"/>
        <FIELD ID="12" xsi:type="CharTerm" TERMINATOR=", " MAX_LENGTH="20"/>
        <FIELD ID="13" xsi:type="CharTerm" TERMINATOR=", " MAX_LENGTH="20"/>
        <FIELD ID="14" xsi:type="CharTerm" TERMINATOR=", " MAX_LENGTH="2000"/>
        <FIELD ID="15" xsi:type="CharTerm" TERMINATOR=",\r\n" MAX_LENGTH="2000"/>
    </RECORD>
    <ROW>
        <COLUMN SOURCE="1" NAME="clientip" xsi:type="SQLNVARCHAR"/>
        <COLUMN SOURCE="2" NAME="username" xsi:type="SQLNVARCHAR"/>
        <COLUMN SOURCE="3" NAME="date" xsi:type="SQLNVARCHAR"/>
        <COLUMN SOURCE="4" NAME="time" xsi:type="SQLNVARCHAR"/>
        <COLUMN SOURCE="5" NAME="sitename" xsi:type="SQLNVARCHAR"/>
        <COLUMN SOURCE="6" NAME="servername" xsi:type="SQLNVARCHAR"/>
        <COLUMN SOURCE="7" NAME="serverip" xsi:type="SQLNVARCHAR"/>
        <COLUMN SOURCE="8" NAME="timetaken" xsi:type="SQLINT"/>
        <COLUMN SOURCE="9" NAME="clientbytes" xsi:type="SQLINT"/>
        <COLUMN SOURCE="10" NAME="serverbytes" xsi:type="SQLINT"/>
        <COLUMN SOURCE="11" NAME="httpstatus" xsi:type="SQLINT"/>
        <COLUMN SOURCE="12" NAME="windowsstatus" xsi:type="SQLINT"/>
        <COLUMN SOURCE="13" NAME="httpmethod" xsi:type="SQLNVARCHAR"/>
        <COLUMN SOURCE="14" NAME="url" xsi:type="SQLNVARCHAR"/>
        <COLUMN SOURCE="15" NAME="params" xsi:type="SQLNVARCHAR"/>
    </ROW>
</BCPFORMAT>

With this format file in place, it’s easy to query a log file using standard SQL statements like this one:

select top 25 *
from
    openrowset(
        bulk 'c:\mylogfile.log',
        formatfile = 'c:\iislog-format.xml'
    ) as logfile

I have written some useful queries that are available in the Code section along with the format file. These are:

• min and max date in log file
• requests ordered by time
• requests grouped by http-status
• requests grouped by windows-status
• requests grouped by http-method
• requests grouped by username
• requests grouped by clientip
• requests grouped by file extension
• requests grouped by minute
• 25 most requested urls (without querystring)
• 25 most requested urls (with querystring)
• 25 slowest requested urls
• 25 slowest requested urls (in average)
• requested urls with file extension ordered by time

This post was first published on EPiServer World, available here.