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 here.  http://logparserplus.com/Functions

Examples of other parsing can be found here.

http://felipeferreira.net/?p=823
http://support.microsoft.com/kb/296085/EN-US

I didn’t use these examples, but may in the future
http://technet.microsoft.com/en-us/library/ee692659.aspx