Determine the Bandwidth through ALB

AWS_ALB_Bandwidth.sql

use IisLogs
go

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

AWS ALB DIMENSION: Mbps per Hour

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

declare	@dateStart varchar(10) = '2018-07-18',
		@dateEnd varchar(10) = '2018-07-19',
		@includeAll bit = 0,
		@intervalInSeconds int = 1

create table #data
(
	[date] date,
	[time] time,
	[avg-in-bits-per-second-per-hour] decimal
)

insert into #data
select		[date], [time], sum([sum-in-bits-per-second]) / 3600 [avg-bits-per-second-per-hour]
from			(
					select		convert(date, EntryTime) [date]
								,format(datepart(hour, EntryTime), '0#') + ':00:00' [time]
								,sum(convert(decimal, csBytes + scBytes) * 8) [sum-in-bits-per-second]
					from			dbo.IisLog
					where		(EntryTime >= @dateStart and EntryTime < @dateEnd)
						or		@includeAll = 1
					group by	convert(date, EntryTime),
								datepart(hour, EntryTime),
								datepart(minute, EntryTime),
								datepart(second, EntryTime) / @intervalInSeconds
				)		f
group by	[date], [time]

-- 2327838.72 = 2.22 Mbps
select	[date], [time], [avg-in-bits-per-second-per-hour],
		convert(decimal(4,2), ([avg-in-bits-per-second-per-hour] / 2327838.72)) [lcus]
from		#data
order by	[date], [time]

select	avg(convert(decimal(4,2), ([avg-in-bits-per-second-per-hour] / 2327838.72))) [avg-lcus]
from		#data

--drop table #data;