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'
)
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.
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.
