AWS ALB Price Planning w/ IIS : Active Connections

This post continues the series about from AWS ALB Price Planning w/ IIS : New Connections. Here are a couple things to note about the query to grab Active Connections:

• This query is largely based on the same query used in AWS ALB Price Planning w/ IIS : New Connections [link needed].
• It’s slightly modified by getting the number of connections per minute rather than per second. But, all the same problems that were outlined in the last post are still true.
• AWS does their pricing based on average usage per hour. So, the sql will aggregate the data into hour increments in order to return results.

Active Connections SQL Script

	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;

view raw AWS_ALB_Active_Connections.sql hosted with ❤ by GitHub

Graphing the output from the script shows:

• # of Active Connections per Minute by Hour (for a month)
• The jump in the average number of new connections in the beginning of the month corresponds to a return of students to campus. During the beginning of the month, school was not in session and then the students returned.
• The dip at the end of the month has to do with a mistake I made loading some data. There is one day of IIS logs that I forgot to import, but I don’t really want to go back and correct the data. It will disappear from the database in about a month.
• # of Active Connections per Second by Hour Frequency
• This doesn’t help visualize it as well as I would have hoped. But, it does demonstrate that usually the number of active connections per minutes will be less than 3000; so it will be less than 1 LCU (1 LCU = 3000 active connections per minute).

Next Up, AWS ALB Price Planning w/ IIS : Bandwidth.