SQL scratch pad

SQL scratch pad.sql

DROP TABLE [dbo].[iislog]

CREATE TABLE [dbo].[iislog] (
	[date] [varchar] (50) NULL,
	[time] [varchar] (50) NULL ,
	[c-ip] [varchar] (50) NULL ,
	[cs-method] [varchar] (50) NULL ,
	[cs-uri-stem] [varchar] (255) NULL ,
	[cs-uri-query] [varchar] (2048) NULL ,
	[sc-status] [varchar] (50) NULL ,
	[sc-bytes] [varchar] (50) NULL ,
	[time-taken] [varchar] (50) NULL ,
	[cs(User-Agent)] [varchar] (2048) NULL ,
	[cs(Cookie)] [varchar] (2048) NULL ,
	[cs(Referer)] [varchar] (2048) NULL 
	)

BULK INSERT [dbo].[iislog] FROM 'c:\tmp\LogFiles\u_ex160314.log'
WITH (
    FIELDTERMINATOR = ' ',
    ROWTERMINATOR = '\n'
)

BULK INSERT [dbo].[iislog] FROM 'c:\tmp\LogFiles\u_ex160315.log'
WITH (
    FIELDTERMINATOR = ' ',
    ROWTERMINATOR = '\n'
)



SELECT count(*)
FROM [dbo].[iislog]
WHERE 
	[date] LIKE '2016-03-15'


SELECT *
FROM [dbo].[iislog]
WHERE 
	[date] LIKE '2016-03-15' 
	AND [time] > '07:00:00' 
	AND [time] < '10:40:00' 
	AND [cs-uri-stem] LIKE '%/autodiscover/autodiscover.xml%'



--Find worst offenders
SELECT [time-taken], count([time-taken]) AS FREQUENCY
FROM [dbo].[iislog]
WHERE 
	[date] LIKE '2016-03-15' 
	AND[time] > '07:00:00' 
	AND[time] < '10:40:00' 
	AND[cs-uri-stem] LIKE '%/autodiscover/autodiscover.xml%'
GROUP BY [time-taken]
ORDER BY FREQUENCY DESC



--Check they add up
SELECT COUNT([time-taken])
FROM [dbo].[iislog]
WHERE 
	[date] LIKE '2016-03-15' 
	AND [time] > '07:00:00' 
	AND [time] < '10:40:00' 
	AND [cs-uri-stem] LIKE '%/autodiscover/autodiscover.xml%'


SELECT [time], count([time]) AS FREQUENCY
FROM [dbo].[iislog]
WHERE 
	[date] LIKE '2016-03-17' 
	AND [time] > '10:00:00' 
	AND [time] < '11:00:00' 
	AND [cs-uri-stem] LIKE '%/autodiscover/autodiscover.xml%'
GROUP BY [time]
ORDER BY FREQUENCY DESC