Database to determine AWS ALB pricing model for replacement of an on-premise IIS/ARR proxy server.

IisLogs-Database-Objects.sql

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProxySiteBandwidth]'
GO
CREATE TABLE [dbo].[ProxySiteBandwidth]
(
[Date] [date] NOT NULL,
[Time] [time] NOT NULL,
[SiteName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[bits-per-second] [decimal] (18, 6) NULL,
[lcus] [decimal] (4, 2) NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_ProxySiteBandwidth] on [dbo].[ProxySiteBandwidth]'
GO
ALTER TABLE [dbo].[ProxySiteBandwidth] ADD CONSTRAINT [PK_ProxySiteBandwidth] PRIMARY KEY CLUSTERED  ([Date], [Time], [SiteName])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ALBGrouping]'
GO
CREATE TABLE [dbo].[ALBGrouping]
(
[GroupId] [int] NOT NULL,
[SiteName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_ALBGrouping] on [dbo].[ALBGrouping]'
GO
ALTER TABLE [dbo].[ALBGrouping] ADD CONSTRAINT [PK_ALBGrouping] PRIMARY KEY CLUSTERED  ([GroupId], [SiteName])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[vw_ALBBandwidth]'
GO
create view [dbo].[vw_ALBBandwidth] as
select		b.[Date], b.[Time], g.GroupId
			,sum(b.[bits-per-second]) [bits-per-second-group]
			,convert(decimal(4,2), ((sum(b.[bits-per-second]) * 1.0) / 2327838.72)) [lcus]
			,count(*) [site-cnt]
from			dbo.ProxySiteBandwidth			b
	inner join	dbo.ALBGrouping					g	on	b.[SiteName] = g.[SiteName]
group by	b.[Date], b.[Time], g.GroupId
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProxySiteRuleEvaluations]'
GO
CREATE TABLE [dbo].[ProxySiteRuleEvaluations]
(
[Date] [date] NOT NULL,
[Time] [time] NOT NULL,
[SiteName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[WebAppCount] [int] NULL,
[RequestCounts] [bigint] NULL,
[rule-evals-per-second] [decimal] (18, 2) NULL,
[lcus] [decimal] (4, 2) NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_ProxySiteRuleEvaluations] on [dbo].[ProxySiteRuleEvaluations]'
GO
ALTER TABLE [dbo].[ProxySiteRuleEvaluations] ADD CONSTRAINT [PK_ProxySiteRuleEvaluations] PRIMARY KEY CLUSTERED  ([Date], [Time], [SiteName])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProxySiteRequestCounts]'
GO
CREATE TABLE [dbo].[ProxySiteRequestCounts]
(
[Date] [date] NOT NULL,
[Time] [time] NOT NULL,
[SiteName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[requests-per-hour] [bigint] NOT NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_ProxyWebAppRequestCounts] on [dbo].[ProxySiteRequestCounts]'
GO
ALTER TABLE [dbo].[ProxySiteRequestCounts] ADD CONSTRAINT [PK_ProxyWebAppRequestCounts] PRIMARY KEY CLUSTERED  ([Date], [Time], [SiteName])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[vw_ALBRuleEvaluations]'
GO
create view [dbo].[vw_ALBRuleEvaluations]
as
select		s.[Date], s.[Time], g.GroupId
			,sum(s.[requests-per-hour]) [requests-per-hour-group]
			,sum(e.WebAppCount) [rule-count-group]
			,((((sum(e.WebAppCount) / 2) - 5) * 1.0 * sum(s.[requests-per-hour])) / 3600) [requests-per-second-group]
			,convert(decimal(4,2), ((((sum(e.WebAppCount) / 2) - 5) * 1.0 * sum(s.[requests-per-hour])) / 3600) / 1000) [lcus]
			,count(*) [site-cnt]
from			dbo.ProxySiteRequestCounts		s
	inner join	dbo.ALBGrouping					g	on	s.[SiteName] = g.[SiteName]
	inner join	dbo.ProxySiteRuleEvaluations	e	on	s.[Date] = e.[Date] and s.[Time] = e.[Time] and s.[SiteName] = e.[SiteName]
group by	s.[Date], s.[Time], g.GroupId
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProxySiteNewConnections]'
GO
CREATE TABLE [dbo].[ProxySiteNewConnections]
(
[Date] [date] NOT NULL,
[Time] [time] NOT NULL,
[SiteName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[total-connections] [int] NULL,
[connections-per-second] [decimal] (18, 6) NULL,
[lcus] [decimal] (4, 2) NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_ProxySiteNewConnections] on [dbo].[ProxySiteNewConnections]'
GO
ALTER TABLE [dbo].[ProxySiteNewConnections] ADD CONSTRAINT [PK_ProxySiteNewConnections] PRIMARY KEY CLUSTERED  ([Date], [Time], [SiteName])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[vw_ALBNewConnections]'
GO
create view [dbo].[vw_ALBNewConnections] as
select		n.[Date], n.[Time], g.GroupId
			,sum(n.[total-connections]) [total-connections-group]
			,(sum(n.[total-connections]) * 1.0 / 3600) [connections-per-second-group]
			,convert(decimal(4,2), ((sum(n.[total-connections]) * 1.0 / 3600) / 25.0)) [lcus]
			,count(*) [site-cnt]
from			dbo.ProxySiteNewConnections		n
	inner join	dbo.ALBGrouping					g	on	n.[SiteName] = g.[SiteName]
group by	n.[Date], n.[Time], g.GroupId
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProxySiteActiveConnections]'
GO
CREATE TABLE [dbo].[ProxySiteActiveConnections]
(
[Date] [date] NOT NULL,
[Time] [time] NOT NULL,
[SiteName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[total-connections] [int] NULL,
[connections-per-minute] [decimal] (18, 6) NULL,
[lcus] [decimal] (4, 2) NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_ProxySiteActiveConnections] on [dbo].[ProxySiteActiveConnections]'
GO
ALTER TABLE [dbo].[ProxySiteActiveConnections] ADD CONSTRAINT [PK_ProxySiteActiveConnections] PRIMARY KEY CLUSTERED  ([Date], [Time], [SiteName])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[vw_ALBActiveConnections]'
GO
create view [dbo].[vw_ALBActiveConnections] as
select		a.[Date], a.[Time], g.GroupId
			,sum(a.[total-connections]) [total-connections-group]
			,(sum(a.[total-connections]) * 1.0 / 60) [connections-per-minute-group]
			,convert(decimal(4,2), ((sum(a.[total-connections]) * 1.0 / 60) / 3000.0)) [lcus]
			,count(*) [site-cnt]
from			dbo.ProxySiteActiveConnections	a
	inner join	dbo.ALBGrouping					g	on	a.[SiteName] = g.[SiteName]
	--inner join	dbo.ProxySiteActiveConnections	e	on	s.[Date] = e.[Date] and s.[Time] = e.[Time] and s.[SiteName] = e.[SiteName]
group by	a.[Date], a.[Time], g.GroupId
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[vw_ALBLCUComparison]'
GO
create view [dbo].[vw_ALBLCUComparison] as
select		n.[Date], n.[Time], n.[GroupId]
			,e.[site-cnt]
			,e.[rule-count-group]
			,e.lcus [rule-eval-lcus]
			,n.lcus [new-conn-lcus]
			,a.lcus [active-conn-lcus]
			,b.lcus [bandwidth-lcus]
			,(select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (e.lcus)) as value(v)) as [max-lcus]
			,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (e.lcus)) as value(v)) = e.lcus then 1 else 0 end as [rule-eval-used]
			,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (e.lcus)) as value(v)) = n.lcus then 1 else 0 end as [new-conn-used]
			,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (e.lcus)) as value(v)) = a.lcus then 1 else 0 end as [active-conn-used]
			,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (e.lcus)) as value(v)) = b.lcus then 1 else 0 end as [bandwidth-used]
from			dbo.vw_ALBNewConnections		n
	left join	dbo.vw_ALBActiveConnections		a	on	n.[Date] = a.[Date] and n.[Time] = a.[Time] and n.[GroupId] = a.[GroupId]
	left join	dbo.vw_ALBBandwidth				b	on	n.[Date] = b.[Date] and n.[Time] = b.[Time] and n.[GroupId] = b.[GroupId]
	left join	dbo.vw_ALBRuleEvaluations		e	on	n.[Date] = e.[Date] and n.[Time] = e.[Time] and n.[GroupId] = e.[GroupId]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[usp_Aggregate_ALBLCUComparison_For_DateRange]'
GO
create procedure [dbo].[usp_Aggregate_ALBLCUComparison_For_DateRange]
	@dateStart date,
	@dateEnd date
as
select		GroupId
			,max([site-cnt]) [site-cnt-mth]
			,max([rule-count-group]) [rule-count-group-mth]
			,sum([rule-eval-lcus]) [rule-eval-lcus-mth]
			,sum([new-conn-lcus]) [new-conn-lcus-mth]
			,sum([active-conn-lcus]) [active-conn-lcus-mth]
			,sum([bandwidth-lcus]) [bandwidth-lcus-mth]
			,sum([max-lcus]) [max-lcus-mth]
			,sum([max-lcus]) * .008 [max-lcus-cost-mth]
from			vw_ALBLCUComparison
where		[Date] >= @dateStart and [Date] < @dateEnd
group by	GroupId
order by	[GroupId]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[IisLog]'
GO
CREATE TABLE [dbo].[IisLog]
(
[EntryTime] [datetime] NOT NULL,
[LogFilename] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RowNumber] [int] NOT NULL,
[SiteName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sIp] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[csMethod] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[csUriStem] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[csUriQuery] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sPort] [int] NULL,
[csUsername] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cIp] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[csUserAgent] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[csReferer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[scStatus] [int] NULL,
[scSubstatus] [int] NULL,
[scWin32Status] [int] NULL,
[scBytes] [int] NULL,
[csBytes] [int] NULL,
[timeTaken] [int] NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_IisLog] on [dbo].[IisLog]'
GO
ALTER TABLE [dbo].[IisLog] ADD CONSTRAINT [PK_IisLog] PRIMARY KEY CLUSTERED  ([EntryTime], [LogFilename], [RowNumber])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_csUriStem] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_csUriStem] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [csUriStem])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_csUriStem_timeTaken] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_csUriStem_timeTaken] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [csUriStem], [timeTaken])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_siteName_cIp_csUriStem] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_siteName_cIp_csUriStem] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [SiteName], [cIp], [csUriStem])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [SiteName], [csUriStem])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem_scStatus] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem_scStatus] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [SiteName], [csUriStem], [scStatus])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem_scStatus_scSubstatus_timeTaken] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem_scStatus_scSubstatus_timeTaken] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [SiteName], [csUriStem], [scStatus], [scSubstatus], [timeTaken])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem_scStatus_timeTaken] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem_scStatus_timeTaken] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [SiteName], [csUriStem], [scStatus], [timeTaken])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_siteName_ServerName_csUriStem] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_siteName_ServerName_csUriStem] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [SiteName], [ServerName], [csUriStem])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[usp_CleanIisLog]'
GO

CREATE
procedure	[dbo].[usp_CleanIisLog]
	@Days_To_Keep	int
/*
 *	description:	removes all log entries which are not within the number
 *					of days to keep.
 *	created by:		steven maglio
 *	created:		2018-08-21
 */
	
as

delete		
from			[dbo].[IisLog]
where		EntryTime <= DATEADD( D, -1 * @Days_To_Keep, GETDATE() )

--DBCC DBREINDEX ( '[dbo].[IisLog]' );

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[usp_Calculate_ProxySiteBandwidth_For_Date]'
GO

CREATE procedure [dbo].[usp_Calculate_ProxySiteBandwidth_For_Date]
	@Date date
as

	declare		@dateStart date = @Date,
				@dateEnd date = dateadd(d, 1, @Date),
				@intervalInSeconds int = 1

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

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[usp_Calculate_ProxySiteActiveConnections_For_Date]'
GO
CREATE procedure [dbo].[usp_Calculate_ProxySiteActiveConnections_For_Date]
	@Date date
as

	declare	@dateStart varchar(10) = @Date,
			@dateEnd varchar(10) = dateadd(d, 1, @Date),
			@includeAll bit = 0,
			@intervalInSeconds int = 1

	insert into dbo.ProxySiteActiveConnections
	select		[date], [time], [SiteName]
				,sum([connections]) [total-connections]
				,((sum([connections]) * 1.0) / 60) [connections-per-minute]
				,convert(decimal(4,2), (((sum([connections]) * 1.0) / 60) / 3000.0)) [lcus]
	from		(
		select		[date], [time], [SiteName], [type], count(*) [connections]
		from			(
							select		convert(date, EntryTime) [date]
										,format(datepart(hour, EntryTime), '0#') + ':00:00' [time]
										,[SiteName]
										,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),
										[SiteName],
										datepart(minute, EntryTime),
										datepart(second, EntryTime) / @intervalInSeconds,
										cIp
						)		f
		group by	[date], [time], [SiteName], [type]

		union

		select		convert(date, EntryTime) [date]
					,format(datepart(hour, EntryTime), '0#') + ':00:00' [time]
					,[SiteName]
					,'services conns' [type]
					,count(*) [connections]
		from			dbo.IisLog
		where		(
							(EntryTime >= @dateStart and EntryTime < @dateEnd)
						or	@includeAll = 1
					)
			and		(
							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),
					[SiteName]
	)		d
	group by	[date], [time], SiteName;

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[usp_Calculate_ProxySiteNewConnections_For_Date]'
GO

CREATE procedure [dbo].[usp_Calculate_ProxySiteNewConnections_For_Date]
	@Date date
as

	declare	@dateStart varchar(10) = @Date,
			@dateEnd varchar(10) = dateadd(d, 1, @Date),
			@includeAll bit = 0,
			@intervalInSeconds int = 1

	insert into dbo.ProxySiteNewConnections
	select		[date], [time], [SiteName]
				,sum([connections]) [total-connections]
				,((sum([connections]) * 1.0) / 3600) [connections-per-second]
				,convert(decimal(4,2), (((sum([connections]) * 1.0) / 3600) / 25.0)) [lcus]
	from		(
		select		[date], [time], [SiteName], [type], count(*) [connections]
		from			(
							select		convert(date, EntryTime) [date]
										,format(datepart(hour, EntryTime), '0#') + ':00:00' [time]
										,SiteName
										,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),
										[SiteName],
										datepart(minute, EntryTime),
										datepart(second, EntryTime) / @intervalInSeconds,
								cIp
						)		f
		group by	[date], [time], [SiteName], [type]

		union

		select		convert(date, EntryTime) [date]
					,format(datepart(hour, EntryTime), '0#') + ':00:00' [time]
					,SiteName
					,'services conns' [type]
					,count(*) [sum]
		from			dbo.IisLog
		where		(
							EntryTime >= @dateStart and EntryTime < @dateEnd
						or	@includeAll = 1
					)
			and		(
							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),
					[SiteName]
	)		d
	group by	[date], [time], SiteName;


GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProxyWebAppCounts]'
GO
CREATE TABLE [dbo].[ProxyWebAppCounts]
(
[SiteName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[WebAppCount] [int] NOT NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_ProxyWebAppCounts] on [dbo].[ProxyWebAppCounts]'
GO
ALTER TABLE [dbo].[ProxyWebAppCounts] ADD CONSTRAINT [PK_ProxyWebAppCounts] PRIMARY KEY CLUSTERED  ([SiteName])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[usp_Calculate_ProxySiteRuleEvaluations_For_Date]'
GO

CREATE procedure [dbo].[usp_Calculate_ProxySiteRuleEvaluations_For_Date]
	@Date date
as

	declare	@dateStart varchar(10) = @Date,
			@dateEnd varchar(10) = dateadd(d, 1, @Date),
			@includeAll bit = 0

	insert into dbo.ProxySiteRequestCounts
	select		convert(date, EntryTime) [date]
				,format(datepart(hour, EntryTime), '0#') + ':00:00' [time]
				,SiteName
				,count(*) [requests-per-hour]
	from			dbo.IisLog
	where		(EntryTime >= @dateStart and EntryTime < @dateEnd)
		or		@includeAll = 1
	group by	convert(date, EntryTime),
				datepart(hour, EntryTime),
				SiteName
	order by	[date], [time], SiteName;


	insert into dbo.ProxySiteRuleEvaluations
	select		s.[Date], s.[Time], s.[SiteName]
				,c.WebAppCount
				,s.[requests-per-hour]
				,((((c.WebAppCount/2) - 5) * 1.0 * s.[requests-per-hour]) / 3600) [rule-evals-per-hour]
				,convert(decimal(4,2), ((((c.WebAppCount/2) - 5) * 1.0 * s.[requests-per-hour]) / 3600) / 1000) [lcus]
	from			dbo.ProxySiteRequestCounts		s
		inner join	dbo.ProxyWebAppCounts			c	on	s.SiteName = c.SiteName
	where		s.[Date] >= @dateStart and s.[Date] < @dateEnd
		or		@includeAll = 1
	order by	[date], [time], SiteName;
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProxySiteRequestStats]'
GO
CREATE TABLE [dbo].[ProxySiteRequestStats]
(
[SiteName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Avg] [decimal] (18, 0) NULL,
[Stdev] [decimal] (18, 0) NULL,
[95th] [decimal] (18, 0) NULL,
[min] [bigint] NULL,
[max] [bigint] NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_ProxySiteRequestStats] on [dbo].[ProxySiteRequestStats]'
GO
ALTER TABLE [dbo].[ProxySiteRequestStats] ADD CONSTRAINT [PK_ProxySiteRequestStats] PRIMARY KEY CLUSTERED  ([SiteName])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[usp_Regenerate_ProxySiteRequestStats]'
GO

CREATE procedure [dbo].[usp_Regenerate_ProxySiteRequestStats]
as

truncate table dbo.ProxySiteRequestStats;

insert into dbo.ProxySiteRequestStats
select		sitename
			,avg([requests-per-hour]) [avg]
			,stdev([requests-per-hour]) [stdev]
			,ceiling(avg([requests-per-hour]) + (2 * stdev([requests-per-hour]))) [95th]
			,min([requests-per-hour]) [min]
			,max([requests-per-hour]) [max]
from			dbo.ProxySiteRequestCounts
group by	sitename

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[vw_ProxySiteLCUComparison]'
GO
create view [dbo].[vw_ProxySiteLCUComparison]
with schemabinding
as
select		s.[Date], s.[Time], s.[SiteName]
			,e.WebAppCount
			,s.[requests-per-hour]
			,e.lcus [rule-eval-lcus]
			,n.lcus [new-conn-lcus]
			,a.lcus [active-conn-lcus]
			,b.lcus [bandwidth-lcus]
			,(select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (e.lcus)) as value(v)) as [max-lcus]
			,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (e.lcus)) as value(v)) = e.lcus then 1 else 0 end as [rule-eval-used]
			,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (e.lcus)) as value(v)) = n.lcus then 1 else 0 end as [new-conn-used]
			,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (e.lcus)) as value(v)) = a.lcus then 1 else 0 end as [active-conn-used]
			,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (e.lcus)) as value(v)) = b.lcus then 1 else 0 end as [bandwidth-used]
from			dbo.ProxySiteRequestCounts		s
	inner join	dbo.ProxySiteNewConnections		n	on	s.[Date] = n.[Date] and s.[Time] = n.[Time] and s.[SiteName] = n.[SiteName]
	inner join	dbo.ProxySiteActiveConnections	a	on	s.[Date] = a.[Date] and s.[Time] = a.[Time] and s.[SiteName] = a.[SiteName]
	inner join	dbo.ProxySiteBandwidth			b	on	s.[Date] = b.[Date] and s.[Time] = b.[Time] and s.[SiteName] = b.[SiteName]
	inner join	dbo.ProxySiteRuleEvaluations	e	on	s.[Date] = e.[Date] and s.[Time] = e.[Time] and s.[SiteName] = e.[SiteName]

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[AWSLambdaPricing]'
GO
CREATE TABLE [dbo].[AWSLambdaPricing]
(
[MemoryMB] [int] NULL,
[FreeSecondsPerMonth] [int] NULL,
[PricePer100ms] [decimal] (18, 10) NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[ProxyWebApps]'
GO
CREATE TABLE [dbo].[ProxyWebApps]
(
[SiteName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AppPath] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MemoryMB] [int] NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_ProxyWebApps] on [dbo].[ProxyWebApps]'
GO
ALTER TABLE [dbo].[ProxyWebApps] ADD CONSTRAINT [PK_ProxyWebApps] PRIMARY KEY CLUSTERED  ([SiteName], [AppPath])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
END
GO