Using SQL Server to analyze IIS logs

> Thursday, June 24, 2010 |

Rather than using LogParser to analyze IIS logs, you can import your log files in an instance of SQL Server Express.

First, create a table that has the same columns as your log file. Depending on what data you were capturing in your log files, you will need a different structure.

For example, on my Windows XP IIS 5.1 machine, I had the following fields:

CREATE TABLE [dbo].[iis_logtable_1] (
        [time] [datetime] NULL ,
        [c-ip] [varchar] (50) NULL ,
        [cs-method] [varchar] (50) NULL ,
        [cs-uri-stem] [varchar] (255) NULL ,
        [sc-status] [int] NULL
        )





For an IIS7 server instance, I had these fields captured:




CREATE TABLE [dbo].[iis_logtable_2] (        
[date] [datetime] NULL,        
[time] [datetime] NULL ,        
[s-ip] [varchar] (50) NULL ,        
[cs-method] [varchar] (50) NULL ,        
[cs-uri-stem] [varchar] (255) NULL ,        
[cs-uri-query] [varchar] (2048) NULL ,        
[s-port] [varchar] (50) NULL ,        
[cs-username] [varchar] (50) NULL ,        
[c-ip] [varchar] (50) NULL ,        
[cs(User-Agent)] [varchar] (2048) NULL ,        
[sc-status] [int] NULL ,        
[sc-substatus] [int] NULL ,        
[sc-win32-status] [varchar] (255) NULL ,        
[time-taken] [int] NULL        
)







You may need to modify depending on which fields are being logged on your web server.



You could do a BULK INSERT now to pull all the data into SQL Server. However, your log file probably has comments in it, which IIS typically adds each time the server restarts… these lines start with hash (#) and cause BULK INSERT to choke. If you have enough of these in your log file, the insert will fail.



You can remove comments from your log file by using the PrepWebLog utility. Command will be something like this:




preplog.exe c:\temp\iislogs\mylogfile.log > c:\temp\iislogs\mylogfile_new.log





Finally, the newly un-commented file can be read by SQL Server.




BULK INSERT [dbo].[iis_logtable_2]
FROM 'C:\temp\iislogs\mylogfile_new.log'
WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '\n')







For more info, see How To Use SQL Server to Analyze Web Logs.