IIS_Log_Tmp_Load.sql

--
-- Ad Hoc IIS Log analysis
-- Based on code from http://support.microsoft.com/kb/296085
-- Updated to work with the IIS log column config from my 
-- particular inherited Windows 2008 server.
--

DROP TABLE #IISLogs
GO
 
CREATE TABLE #IISLogs (
      [date] [datetime] NULL,
      [time] [datetime] NULL ,
      [s-sitename] [varchar] (50) NULL ,
      [s-computername] [varchar] (50) NULL ,
      [s-ip] [varchar] (50) NULL ,
      [cs-method] [varchar] (50) NULL ,
      [cs-uri-stem] [varchar] (255) NULL ,
      [cs-uri-query] [varchar] (2048) NULL ,
      [s-port] [int] NULL ,
      [cs-username] [varchar] (50) NULL ,
      [c-ip cs-version] [varchar] (50) NULL ,
      [cs(User-Agent)] [varchar] (255) NULL ,
      [cs(Cookie)] [varchar] (2048) NULL ,
      [cs(Referer)] [varchar] (2048) NULL,
      [cs-host] [varchar] (2048) NULL ,
      [sc-status] [varchar] (50) NULL ,
      [sc-substatus] [varchar] (50) NULL ,
      [sc-win32-status] [varchar] (50) NULL ,
      [sc-bytes] [bigint] NULL ,
      [cs-bytes] [int] NULL ,
      [time-taken] [varchar] (50) NULL ,
);

BULK INSERT #IISLogs FROM 'F:\logs\W3SVC3\u_ex110104.log'
WITH (
    FIELDTERMINATOR = ' ',
    ROWTERMINATOR = '\n',
    MAXERRORS  = 1000
);
 
SELECT TOP 5 * FROM #IISLogs WHERE [cs-uri-stem] LIKE '%.ashx'
GO