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.

WCF .svc handler mapping not working in IIS

Problem:
When trying to access a WCF .svc endpoint hosted by IIS I receive the following error "The resource you are looking for does not have a handler associated with it."

Solution:

1.    Run Server Manager (on task bar and start menu)

2.    Choose the server to administer (probably local server)

3.    Scroll down to "Roles and Features" section.

4.    Choose "Add Role or Feature" from Tasks drop down

5.    On "Add Role or Feature Wizard" dialog, click down to "Features" in list of pages on the left.

6.    Expand ".Net 3.5" or ".Net 4.5", depending on what you have installed. (you can go back up to "roles" screen to add if you don't have.

7.    Under "WCF Services", check the box for "HTTP-Activation". You can also add non-http types if you know you need them (tcp, named pipes, etc).

8.    Click "Install" Button.

Works on Windows Server 2008-2012