usp_Calculate_ProxySiteRuleEvaluations_For_Date.sql

USE [IisLogs]
GO
/****** Object:  StoredProcedure [dbo].[usp_Calculate_ProxySiteRuleEvaluations_For_Date]    Script Date: 9/2/2018 7:28:25 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER 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(*) [RequestCounts]
	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.RequestCounts
				,((((c.WebAppCount/2) - 5) * 1.0 * s.RequestCounts) / 3600) [rule-evals-per-second]
				,convert(decimal(4,2), ((((c.WebAppCount/2) - 5) * 1.0 * s.RequestCounts) / 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;


/*
USE [IisLogs]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ProxySiteRuleEvaluations](
	[Date] [date] NOT NULL,
	[Time] [time](7) NOT NULL,
	[SiteName] [varchar](255) NOT NULL,
	[WebAppCount] [int] NULL,
	[RequestCounts] [bigint] NULL,
	[rule-evals-per-second] [decimal](18, 6) NULL,
	[lcus] [decimal](4, 2) NULL,
 CONSTRAINT [PK_ProxySiteRuleEvaluations] PRIMARY KEY CLUSTERED 
(
	[Date] ASC,
	[Time] ASC,
	[SiteName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO




*/