Skip to content

Instantly share code, notes, and snippets.

@snerpton

snerpton/SQL scratch pad.sql

Last active October 15, 2019 09:49
Show Gist options
  • Save snerpton/dff5ec299d623d631d24 to your computer and use it in GitHub Desktop.
Save snerpton/dff5ec299d623d631d24 to your computer and use it in GitHub Desktop.
Download ZIP
SQL scratch pad
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment