Import IIS Log files to SQL Server table without a wizard

 

     IIS Servers create log files which contains all kind of information about the visitors, traffic, and statuses. Sometimes you might need to look at those files to find some information or analyze it for marketing/security puposes.  Log files can get large and it is not easy to open and find what you are looking for. SQL Server can help you in this situation. Don't worry, I am not going to talk about converting files to CSV and importing them using some kind of wizard here.
 
     First let's look at the IIS Log file standard. As you can see in this link, there is all kind of documentation about the log file structure.  Here is the standard header information of IIS Intranet site from Microsoft.

Table 10.2 Example of a W3C Extended Log File Entry from an Intranet Site
Field	Appears As	Description
date	2002-05-02	This log file entry was recorded on May 2, 2002.
time	17:42:15	This log file entry was recorded at 5:42 P.M. UTC. Entries are recorded to the log file when the send completion for the last IIS send occurs.
c-ip	172.22.255.255	The IP address of the client.
cs-username	-	The user was anonymous.
s-ip	172.30.255.255	The IP address of the server.
s-port	80	The server port.
cs-method	GET	The user issued a GET, or download, command.
cs-uri-stem	/images/picture.jpg	The user wanted to download the picture.jpg file from the Images folder.
cs-uri-query	-	The URI query did not occur. (URI queries are necessary only for dynamic pages, such as ASP pages, so this field usually contains a hyphen for static pages.)
sc-status	200	The request was fulfilled with no errors.
cs(User-Agent)	Mozilla/4.0+ (compatible;MSIE+5.5;+Windows+2000+Server)	The type of browser that the client used, as represented by the browser.

Now I need a sample IIS Log file. I am going to use one of my IIS servers log file for this. Here is the top part of my log file. As you can see, 4th line includes the headers, and I am going to use these header information to create my columns.

Here is my table, columns match the header columns.

CREATE TABLE dbo.IISLOG (
 [DATE] [DATE] NULL,
 [TIME] [TIME] NULL,
 [s-ip] [VARCHAR] (16) NULL,
 [cs-method] [VARCHAR] (8) NULL, 
 [cs-uri-stem] [VARCHAR] (255) NULL,
 [cs-uri-query] [VARCHAR] (2048) NULL,
 [s-port] [VARCHAR] (4) NULL,
 [s-username] [VARCHAR] (16) NULL,
 [c-ip] [VARCHAR] (16) NULL,
 [cs(User-Agent)] [VARCHAR] (1024) NULL,
 [cs(Referer)] [VARCHAR] (4096) NULL, 
 [sc-STATUS] [INT] NULL,
 [sc-substatus] [INT] NULL,
 [sc-win32-STATUS] [INT] NULL,
 [time-taken] [INT] NULL,
 INDEX cstore CLUSTERED COLUMNSTORE
)

Log files can be large, To analyze them faster, I have created a columnstore index in this table. Check out my columnstore blog if you like to learn about them more. Columnstore index will help us to make the table file size smaller since it compresses the data. Also it will speed up the analytic queries. Now, It's time to import the file into this table.There are many wizards out there to import files to SQL Server. I am going to use BULK INSERT command to import this file. First we put this file somewhere that SQL server can reach. Then, I run the following statement in SSMS.

BULK INSERT dbo.IISLogs
FROM 'D:\myiislogfile.log'
WITH (
 FIRSTROW = 5,
 FIELDTERMINATOR = ' ',
 ROWTERMINATOR = '\n'
)

Here is my results.