Cool Tools Part XI: Log Parser

FWIW, I suggested we change "Cool Tools" to "Kewl Tewls" to capture the strong ALI tween demographic, but I got over-ruled.  I guess the kids these days are too busy playing with their Twitters and their Facebooks to care about our old friend ALI.  Maybe if BEA would just rename "Aqualogic User Interaction" to something a little more Web 2.0, like Twinte or Youzoom, then the kids would come running.  But instead of just holding my breath for the next month waiting for Oracle to re-brand Plumtree yet again, I thought I'd share a decidely un-hip, un-Web 2.0, but none-the-less really useful, tool with you: Log Parser. 

So what is this mysterious kewl tewl...a new micro-blogging social tag mash-up platform?  Nay, it's actually just a....log parser :(  But it's awesome, and  here's why...

Log Parser let's you use SQL syntax to quickly get meaningful information out of Gigabytes of log data.  The tool is designed to parse arbitrary log formats, meaning that you can run it against things like: IIS access and error logs, Apache access and error logs, Windows event logs, Tomcat logs, etc.  It's even designed to be extensible, so, if you're motivated, you can write custom parsers for just about anything.  If, like me, you've ever had the misfortune of trying to manually make sense out of 50,000 lines of web server access logs, you can probably appreciate the utility of a tool like Log Parser.

Let's take a minute to consider a concrete example.  A few weeks ago, I was working with an ALI client who had a total of 300 portal users, of which, maybe 150 used the portal at all.  Oddly, Analytics was reporting about 10,000 daily hits to their portal.  This seemed abnormally high, which lead me to believe either a) somebody is scanning/attacking the portal, or b) some monitoring tool is hitting the portal too often.  So we decided to take a look at the portal IIS access logs to get some more granular information, and used Log Parser to answer the following questions:

• What IPs are generating the most traffic to the portal? (If the bulk of the traffic is coming from one IP, we probably have an over-active monitoring tool)
• What are the most popular URLs in the portal? (Monitoring would likely only hit one URL, but somebody doing a scan would hit many)
• Are we seeing a bunch of 404s returned from the Web Server (A scanner would likely generate hits to a bunch of pages that don't exist)

 Here's how we did it:

1. Opened up a handy DOS prompt to the IIS log file directory at: c:\windows\system32\Logfiles\W3SVC1
2. Ran Log Parser from the command line as follows:

//Traffic by IP

logparser "SELECT c-ip, Count(*) as Hits FROM ex*.log GROUP BY c-ip"

c-ip            Hits
--------------- -------------------
192.168.0.1     8,920
192.168.0.8     54
192.168.1.93    37
...

 

//Popular URLs

logparser "SELECT cs-uri-stem, Count(*) as Hits FROM ex*.log GROUP BY cs-uri-stem"

cs-uri-stem           Hits
--------------------- -------------------
/portal/server.pt     9,312
/portal/server.pt?... 137
/portal/server.pt?... 83
...

 

//404s

logparser "SELECT count(*) as Errors FROM ex*.log WHERE sc-status=404"

Errors
------
32

 

So, in about 2 minutes, Log Parser told us that most of the client's portal traffic was coming from one IP address, and that it was all going to /portal/server.pt, not some unknown URL.  A quick nslookup of 192.168.0.1 confirmed that the traffic was coming from an over-active health monitor, all our questions were answered, and the world was at peace.  It would have probably taken an hour or more to gather the same information by hand.  So Log Parser saved me some headache, and our client some money...not bad for an old-fashioned command-line tool.  Of course, Log Parser didn't auto-blog this post for me...but maybe in the next release, when it gets a slick AJAX interface and re-named LogPress.