Last active
August 27, 2018 02:33
-
-
Save smaglio81/69dadb5eedebc66f34d1555e897fcb85 to your computer and use it in GitHub Desktop.
Determine # of New Connections through ALB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
use IisLogs | |
go | |
/****************************************************************** | |
AWS ALB DIMENSION: New Connections | |
******************************************************************/ | |
declare @dateStart varchar(10) = '2018-07-18', | |
@dateEnd varchar(10) = '2018-08-19', | |
@intervalInSeconds int = 1 | |
create table #data | |
( | |
[date] date, | |
[time] time, | |
[type] varchar(25), | |
[sum] int | |
) | |
insert into #data | |
select [date], [time], [type], [sum] | |
from ( | |
select [date], [time], [type], count(*) [sum] | |
from ( | |
select convert(date, EntryTime) [date] | |
,format(datepart(hour, EntryTime), '0#') + ':00:00' [time] | |
,min(EntryTime) [Start] | |
,cIp | |
,'not services conns' [type] | |
,count(*) [cnt] | |
from dbo.IisLog | |
where EntryTime >= @dateStart and EntryTime < @dateEnd | |
and not ( | |
csUriStem like '/webservices/%' | |
or ( | |
SiteName in ('blue.sa.ucsb.edu', 'isis.sa.ucsb.edu') | |
and csUriStem like '/services/%' | |
) | |
) | |
group by convert(date, EntryTime), | |
datepart(hour, EntryTime), | |
datepart(minute, EntryTime), | |
datepart(second, EntryTime) / @intervalInSeconds, | |
cIp | |
) f | |
group by [date], [time], [type] | |
union | |
select convert(date, EntryTime) [date] | |
,format(datepart(hour, EntryTime), '0#') + ':00:00' [time] | |
,'services conns' [type] | |
,count(*) [sum] | |
from dbo.IisLog | |
where EntryTime >= @dateStart and EntryTime < @dateEnd | |
and ( | |
csUriStem like '/webservices/%' | |
or ( | |
SiteName in ('site1.your.company.com', 'site2.your.company.com') | |
and csUriStem like '/services/%' | |
) | |
) | |
group by convert(date, EntryTime), | |
datepart(hour, EntryTime) | |
) d; | |
insert into #data | |
select [date], [time], 'total conns', sum([sum]) [sum] | |
from #data | |
group by [date], [time]; | |
insert into #data | |
select [date], [time], 'total conns per second', ([sum] / 3600) [sum] -- 60 m * 60 s = 3600 s | |
from #data | |
where [type] = 'total conns' | |
select [date], [time], [type], [sum] | |
from #data | |
where [type] = 'total conns per second' | |
order by [date], [time], [type] | |
select [sum], count(*) [cnt] | |
from #data | |
where [type] = 'total conns per second' | |
group by [sum] | |
order by [sum] asc | |
--drop table #data; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment