Import IIS logs into SQL Server table

There are lots of IIS log parsers out there, but I found a simple SQL script that will load them into your own SQL Server table for you to query how you like.

CREATE TABLE dbo.IISLOG (

 [DATE] [DATE] NULL,

 [TIME] [TIME] NULL,

 [s-ip] [VARCHAR] (48) NULL,

 [cs-method] [VARCHAR] (8) NULL,

 [cs-uri-stem] [VARCHAR] (255) NULL,

 [cs-uri-query] [VARCHAR] (2048) NULL,

 [s-port] [VARCHAR] (5) NULL,

 [s-username] [VARCHAR] (128) NULL,

 [c-ip] [VARCHAR] (48) NULL,

 [cs(User-Agent)] [VARCHAR] (1024) NULL,

 [cs(Referer)] [VARCHAR] (4096) NULL,

 [sc-STATUS] [BIGINT] NULL,

 [sc-substatus] [INT] NULL,

 [sc-win32-STATUS] [INT] NULL,

 [time-taken] [INT] NULL

)

BULK INSERT dbo.IISLOG

FROM 'c:\temp\u_ex171205.log'

WITH (

 FIRSTROW = 5,

 FIELDTERMINATOR = ' ',

 ROWTERMINATOR = '\n'

)

SELECT [cs-uri-stem], avg([time-taken])

FROM dbo.IISLOG
WHERE [cs-uri-stem] like '%.svc'
GROUP BY [cs-uri-stem]
ORDER BY avg([time-taken]) desc

This tip is based on this post, but contains fixed field lengths and bulk import statement.