In my previous post I described how to query an IIS log as if it was a database table using SQL Servers OPENROWSET. This post is a short follow up on that describing how to query EPiServer (log4net) logs in the same way. In EPiServers logfiles, there are no character sequence that can be used as row delimiter, which means we need to preprocess the file in some way and inject a good row delimiter. I’ve been using RxFind to inject <logdelim/> after each log entry in the file.

rxfind mylogfile.txt /p:"\n([0-9]{4})-" /r:"<logdelim/>${1}-"

Then you can use a format 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="10"/>
        <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="15"/>
        <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="\r\n" MAX_LENGTH="20000"/>
        <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="&lt;logdelim/&gt;" MAX_LENGTH="20000"/>
    </RECORD>
    <ROW>
        <COLUMN SOURCE="1" NAME="date" xsi:type="SQLNVARCHAR"/>
        <COLUMN SOURCE="2" NAME="time" xsi:type="SQLNVARCHAR"/>
        <COLUMN SOURCE="3" NAME="type" xsi:type="SQLNVARCHAR"/>
        <COLUMN SOURCE="4" NAME="thread" xsi:type="SQLINT"/>
        <COLUMN SOURCE="5" NAME="description" xsi:type="SQLNVARCHAR" LENGTH="MAX"/>
        <COLUMN SOURCE="6" NAME="text" xsi:type="SQLNVARCHAR" LENGTH="MAX"/>
    </ROW>
</BCPFORMAT>

to query your log file like this:

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

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