Determine # of Active Connections through ALB

AWS_ALB_Active_Connections.sql

use IisLogs
go

/******************************************************************

AWS ALB DIMENSION: Active Connections per Minute by Hour

******************************************************************/

declare	@dateStart varchar(10) = '2018-07-18',
		@dateEnd varchar(10) = '2018-08-19',
		@includeAll bit = 0,
		@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)
										or	@includeAll = 1
									)
							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 minute', ([sum] / 60) [sum] -- 60 m * 60 s = 3600 s
from		#data
where		[type] = 'total conns'

select		[date], [time], [type], [sum],
		(convert(decimal(5,2), [sum] / 3000.0)) [lcus]
from			#data
where		[type] = 'total conns per minute'
	and		[sum] > 45
order by	[date], [time], [type]

select		[sum], count(*) [cnt]
from			#data
where		[type] = 'total conns per minute'
group by	[sum]
order by	[sum] asc

--drop table #data;