How To Use SQL Server to Analyze Web Logs
Very useful article BUT this one is old maybe because the table have different schema. By the way I have IIS 6.0 on my web server. So I did some workarounds and successfully imported the Log. Now I am posting here in case someone might be trying to do the same thing.
Step #1: Create a table in Database
CREATE TABLE [dbo].[IISLog](
[date] [datetime] NULL,
[time] [datetime] NULL,
[site-name] [varchar](255) NULL,
[s-computername] [varchar](255) NULL,
[s-ip] [varchar](50) NULL,
[cs-uri-stem] [varchar](255) NULL,
[cs-uri-query] [varchar](2048) NULL,
[c-ip] [varchar](50) NULL,
[cs(User-Agent)] [varchar](2048) NULL,
[cs(Cookie)] [varchar](2048) NULL,
[cs(Referer)] [varchar](2048) NULL,
[sc-status] [int] NULL,
[sc-bytes] [int] NULL,
[cs-bytes] [int] NULL,
[time-taken] [int] NULL
)
Step #2: Prepare the Log file (since it contains some description lines on top)
So this is a tricky step. As the Log file contains some description lines on top starting with "#" sign. Therefore SQL Server will NOT be able to import it. One more thing is that these log files can be large (or very large). The Log file I had was of size aprox. 216 MB. So obviously we cannot open it in NotePad etc.
The same article provides a small utility which removes the line, but I think there is bug in this utility, cause it is limiting file upto 43 MB. So I decided to write my own version.
PrepIISLogFileForImport.zip
As I started to create this application for IIS Log import but then it ends up with a generic utility. Which actually displays the text file content and have an option to skip number of lines from start. Therefore we can use it as,
C:\>PrepIISLogFileForImport C:\LogFile.Log skip=4 >newlogfile.txt
Step #3: Bulk import the Log file into SQL Server Table
Hence the final step is to import the log file in the same table. Which can be done by,
BULK INSERT [dbo].[IISLog] FROM 'C:\newlogfile.txt'
WITH (
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n'
)
