Parsing and analysing IIS W3C log files
There are three methods I've used to parse and analyse IIS W3C log files in the last couple of weeks. AWStats, Log Parser, and SQL Server.
Option 1: Use AWStats
http://awstats.sourceforge.net/
Free real-time logfile analyzer to get advanced statistics (GNU GPL). This can be used on the command line and produces graphs and stats in webpages. To install and get stats from your first log file will probably take 1-2 hours.
Option 2: Use Log Parser 2.2 from Microsoft
http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en
"Most software is designed to accomplish a limited number of specific tasks. Log Parser is different... the number of ways it can be used is limited only by the needs and imagination of the user. The world is your database with Log Parser."
Microsoft, I can't believe you really just said that.
Anyway the Log parser is a handy tool, even more so because you can query log files themselves using SQL like syntax. This one only takes about 10 minutes to download and install, and you can be pulling useful numbers out of it within about 5 minutes.
Option 3: Use SQL Server Management Studio
Create a table similar to the following (depending on the format of your log)
CREATE TABLE [dbo].[tablename] (
[date] [datetime] NULL,
[time] [datetime] NULL ,
[c-ip] [varchar] (50) NULL ,
[cs-method] [varchar] (50) NULL ,
[cs-uri-stem] [varchar] (255) NULL ,
[cs-uri-query] [varchar] (2048) NULL ,
[sc-status] [int] NULL ,
[sc-bytes] [int] NULL ,
[time-taken] [int] NULL ,
[cs(User-Agent)] [varchar] (255) NULL ,
[cs(Cookie)] [varchar] (2048) NULL ,
[cs(Referer)] [varchar] (2048) NULL
)
Populate it using this command
BULK INSERT [dbo].[tablename] FROM 'c:\data\my.log'
WITH (
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n'
)
(code taken from http://support.microsoft.com/kb/296085)
If you already have SQL Server Management Studio installed this method is useful, especially due to the familiar syntax (SQL)
February 11th, 2010 - 02:02
Nice article and good resources.
Sometimes, one needs to write ones own customized parser (based on whatever new BI (Business Intelligence) craze has captured our top management
For that, I have been using this script – http://www.biterscripting.com/SS_WebLogParser.html . I start with that script and modify to parse the logs in my customized way. Try it out one of these days. It works pretty good.