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 here. http://logparserplus.com/Functions
Examples of other parsing can be found here.
I didn’t use these examples, but may in the future