Analyze your IIS Logs with hive

As you know, it's really easy to collect logs from a apache driven webfarm into a hive-cluster and analyze them. But how it'll work for IIS? 


Okay, lets do a view inside. IIS let us collect logs in W3C format by checking over the administraion console, register "website", "Active log format". Here you can setup the path where the logs will be stored, the fields you'll logging and much more. After a restart you should see the logs in the desired path. A good idea will be a split into hours, so you can run the jobs every hour on a fresh dataset.


A really easy way will be for a small farm to export the path as a windows shared drive, connect your hive server with the samba-utils:
mount -t cifs //Windows-Server/share -o user=name,password=passwd /mountpoint

Copy the file into hdfs:
hadoop dfs -copyFromLocal /mountpoint/filename <hdfs-dir> (we assume iislog)

Now you can proceed with analysis, we use hive here. Lets assume you want to know which IP has the most traffic.


First you have to describe your tables in hive:
hive> create TABLE iislog (sdate STRING, stime STRING, ssitename STRING,scomputername STRING,sip STRING,csmethod STRING,csuristem STRING,csuriquery STRING,sport INT,csusername STRING,cip STRING,csversion STRING,csuseragent STRING,csCookie STRING,csReferer STRING,scstatus INT,scsubstatus INT,scwin32status INT,scbyte INT,csbytes INT,timetaken INT) partitioned by (time STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '32' STORED AS TEXTFILE;

hive> CREATE TABLE iptraffic (sdate STRING, cip STRING, traffic INT,hits INT,appid STRING,scsuseragent STRING) partitioned by (time STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '124' STORED AS TEXTFILE;

hive> describe iptraffic;

OK

sdate string

cip string

traffic int

hits int

appid string

scsuseragent string

time string

Time taken: 0.07 seconds


The first statement create a table, the rows describes our logfiles, splittet in fields. The second one we need to get the traffic per minute.
Remind, we try to partition the data to get the last results much faster. For that test we need the date -2 mins to create the partition with the data we looking for. To do that write a small script like:

#!/bin/bash
TABLE=IPTRAFFICDATEPAR=`date -d '-2 Min' +"%Y%m%d%H%M"`

DATEPATH=`date -d '-2 Min' +"%Y-%m-%d/%H00/%M"`

SDATE=`date -d '-2 Min' +"%Y-%m-%d"`

STIME=`date -d '-2 Min' +"%H:%M"`
hive -e "ALTER TABLE iptraffic ADD IF NOT EXISTS PARTITION (time='$DATEPAR')"
if [ $? -ne 0 ] ; 

 then echo "Couldn't create partition" 

 exit 1

 else echo -e "\n ==> PARTITION (time='$DATEPAR') created" 

fi

hive -e "INSERT OVERWRITE TABLE iptraffic partition (time=$DATEPAR) \ select concat('$SDATE ','$STIME:00'), cip, sum(csbytes)/1024 counter, count(1) hits,ssitename,csuseragent \ from iislog where iislog.time=$DATEPAR and NOT(iislog.cip LIKE '192\.%')\ group by cip,concat('$SDATE ','$STIME:00'), csuseragent, ssitename"

if [ $? -ne 0 ] ; 

 then echo -e "\n ==> a error occured in analysis \n" 

 exit 1

 else echo -e "\n ==> Insert analysis sucessful" 

fi


What will that do?
First we define the date and format them into a standard format we can use for. Then we create the partition and use them in our hive-statement (where clause iptraffic.PARTITION), group with concat by our mainkey (cip = SourceIP) with date and time and copy that into our hive-warehouse-dir.

Let us take a look into the table:
hive> select * from iislog limit 10; 
OK
20110928130000 2011-09-28 10:59:06 W3SVC2 IISTEST xxx.xxx.xxx.xxx GET /images/bluebox.gif -80 - xxx.xxx.xxx.xxx HTTP/1.1 Mozilla/5.0+(compatible;+MSIE+9.0;+Windows+NT+6.1;+Trident/5.0) GUID=<deleted> 6313985 NULL 200 0 0 551 1689 201109281300

But we analyzed the data in a new partition:
hadoop dfs -cat /user/hive/warehouse/iptraffic/time=201110071059/* |less

2011-10-07 10:59:00|xxx.xxx.xxx.xxx|18|2|W3SVC5|Mozilla/5.0+(compatible;+MSIE+9.0;+Windows+NT+6.1;+Win64;+x64;+Trident/5.0;+MALC)
2011-10-07 10:59:00|xxx.xxx.xxx.xxx|1|2|W3SVC7|Mozilla/5.0+(Windows+NT+6.1;+WOW64)+AppleWebKit/535.1+(KHTML,+like+Gecko)+Chrome/14.0.835.202+Safari/535.1

you're done.