Skip to main content

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;
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:

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"`
if [ $? -ne 0 ] ; 
 then echo "Couldn't create partition" 
 exit 1
 else echo -e "\n ==> PARTITION (time='$DATEPAR') created" 

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" 

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; 
20110928130000 2011-09-28 10:59:06 W3SVC2 IISTEST GET /images/bluebox.gif -80 - 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||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||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.


Popular posts from this blog

Deal with corrupted messages in Apache Kafka

Under some strange circumstances it can happen that a message in a Kafka topic is corrupted. This happens often by using 3rd party frameworks together with Kafka. Additionally, Kafka < 0.9 has no lock at at the consumer read level, but has a lock on Log.write(). This can cause a rare race condition, as described in KAKFA-2477 [1]. Probably a log entry looks like: ERROR Error processing message, stopping consumer: ($) kafka.message.InvalidMessageException: Message is corrupt (stored crc = xxxxxxxxxx, computed crc = yyyyyyyyyy Kafka-Tools Kafka stores the offset of every consumer in Zookeeper. To read out the offsets, Kafka provides handy tools [2]. But also can be used, at least to display the consumer and the stored offsets. First we need to find the consumer for a topic (> Kafka 0.9): bin/ --zookeeper management01:2181 --describe --group test Prior to Kafka 0.9 the only possibility to get this inform

Hive query shows ERROR "too many counters"

A hive job face the odd " Too many counters:"  like Ended Job = job_xxxxxx with exception 'org.apache.hadoop.mapreduce.counters.LimitExceededException(Too many counters: 201 max=200)' FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.MapRedTask Intercepting System.exit(1) These happens when operators are used in queries ( Hive Operators ). Hive creates 4 counters per operator, max upto 1000, plus a few additional counters like file read/write, partitions and tables. Hence the number of counter required is going to be dependent upon the query.  To avoid such exception, configure " mapreduce.job.counters.max " in mapreduce-site.xml to a value above 1000. Hive will fail when he is hitting the 1k counts, but other MR jobs not. A number around 1120 should be a good choice. Using " EXPLAIN EXTENDED " and " grep -ri operators | wc -l " print out the used numbers of operators. Use this value to tweak the MR s

GPT & GenAI for Startup Storytelling

OpenAI and Bard   are the most used GenAI tools today; the first one has a massive Microsoft investment, and the other one is an experiment from Google. But did you know that you can also use them to optimize and hack your startup?  For startups, creating pitch scripts, sales emails, and elevator pitches with generative AI (GenAI) can help you not only save time but also validate your marketing and wording. Curious? Here are a few prompt hacks for startups to create,improve, and validate buyer personas, your startup's mission/vision statements, and unique selling proposition (USP) definitions. First Step: Introduce yourself and your startup Introduce yourself, your startup, your website, your idea, your position, and in a few words what you are doing to the chatbot: Prompt : I'm NAME and our startup NAME, with website URL, is doing WHATEVER. With PRODUCT NAME, we aim to change or disrupt INDUSTRY. Bard is able to pull information from your website. I'm not sure if ChatGPT