IIS Log Import

IIS Log Import

IIS Log Import 150 150 Carlos L Chacon

A client asked me about extracting some data from an IIS log.  I thought it would be straightforward; however, the trick was the file is delimited in two ways.  Most of the columns are tab delimited, but one column–cs-uri-query is delimited with an ampersand(&) and a column header.   Parsing was going to be difficult.

I think did a little searching and came across Microsoft’s LogParser Tool.   It seemed like the right fit; however, I wanted to load the data into a database as we would need to do other manipulations to it.  I also found that the LogParser tool was fairly snappy and I could import my file in about 20 minutes.  Other tools/ways I tried took much longer–one lasted over 12 hours.

At the end of the day, this is what my batch script looked like.  I will put in the detail below.  The items in [ ] should be replaced.

c:”program files””log parser 2.2″LogParser “SELECT TO_TIMESTAMP(date, time) AS LogDate, REVERSEDNS(c-ip), cs-method, sc-bytes, time-taken, cs-uri-stem, cs-username, cs(user-agent), Extract_Value(cs-uri-query,’DeviceId’) AS DeviceId, Extract_Value(cs-uri-query,’DeviceType’) AS DeviceType, Extract_Value(cs-uri-query,’Cmd’) AS Cmd, Extract_Value(cs-uri-query,’Log’) AS Log FROM [ex111231.log] TO IISLogsLP” -o:SQL -server:[SQLDB] -driver:”SQL Server” -database:[DBName] -username:[Carlos] -password:[pwd] -createtable:ON

the -o parameter allows me to insert the data directly into a table.  This is nice because I don’t have to double hop (export to another file and then import).

Date and Time are actually two seperate columns for some reason and the To_TimeStamp allow you to join the two and create ad datetime column in SQL Server.

The Extract_Value was the most valuable function for me.  I use that to extract from a column that looks like this:  User=carlos&DeviceId=XXXXXXXXXXXXXXXXXXX&DeviceType=iPhone&Cmd=Ping&Log=V121_LdapC0_LdapL0_RpcC10_RpcL123_Hb4321_Rto1_Pk987654321_S1_ 443

It then creates nice columns for me to be used for other reporting.

A list of the functions you can use with the LogParser tool can be found herehttp://logparserplus.com/Functions

Examples of other parsing can be found here.


I didn’t use these examples, but may in the future

Imagine what’s possible with a dedicated SQL specialist on your team.

Leave a Reply

Back to top