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.