smaglio81

insert into [dbo].[ProxyWebAppCounts]
select		SiteName, count(*) [WebAppCount]
from			[dbo].[ProxyWebApps]

smaglio81

USE [IisLogs]
GO

/****** Object:  Table [dbo].[ProxyWebAppCounts]    Script Date: 9/2/2018 6:54:29 PM ******/
CREATE TABLE [dbo].[ProxyWebAppCounts](
	[SiteName] [varchar](255) NOT NULL,
	[WebAppCount] [int] NOT NULL,
 CONSTRAINT [PK_ProxyWebAppCounts] PRIMARY KEY CLUSTERED 
(
	[SiteName] ASC

smaglio81

select		s.[Date], s.[Time], s.[SiteName]
			,c.WebAppCount
			,((((c.WebAppCount/2) - 5) * 1.0 * s.RequestCounts) / 3600) [rule-evals-per-hour]
			,convert(decimal(4,2), ((((c.WebAppCount/2) - 5) * 1.0 * s.RequestCounts) / 3600) / 1000) [rule-eval-lcus]
			--,(((c.WebAppCount - 10) * s.RequestCounts) / 1000) * 0.008 [dollars-per-hour]
			,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), (convert(decimal(4,2), ((((c.WebAppCount/2) - 5) * 1.0 * s.RequestCounts) / 3600) / 1000)) as value(v)) as [max-lcus]
			,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (convert(decimal(4,2), ((((c.WebAppCount/2) - 5) * 1.0 * s.RequestCounts) / 3600) / 1000)) as value(v)) = convert(decimal(4,2), ((((c.WebAppCount/2) - 5) * 1.0 * s.RequestCounts) / 3600) / 1000) then 1 else 0 end as [rule-eval-used]

smaglio81

USE [IisLogs]
GO
/****** Object:  StoredProcedure [dbo].[usp_Calculate_ProxySiteActiveConnections_For_Date]    Script Date: 9/2/2018 6:59:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_Calculate_ProxySiteActiveConnections_For_Date]
	@Date date
as

smaglio81

USE [IisLogs]
GO
/****** Object:  StoredProcedure [dbo].[usp_Calculate_ProxySiteBandwidth_For_Date]    Script Date: 9/2/2018 7:00:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[usp_Calculate_ProxySiteBandwidth_For_Date]
	@Date date

smaglio81

USE [IisLogs]
GO
/****** Object:  StoredProcedure [dbo].[usp_Calculate_ProxySiteNewConnections_For_Date]    Script Date: 9/2/2018 7:01:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[usp_Calculate_ProxySiteNewConnections_For_Date]
	@Date date

smaglio81

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

smaglio81

create view vw_LCUComparison
with schemabinding
as
select		s.[Date], s.[Time], s.[SiteName]
			,e.WebAppCount
			,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]

smaglio81

create procedure usp_Regenerate_ProxySiteRequestStats
as

truncate table dbo.ProxySiteRequestStats;

insert into dbo.ProxySiteRequestStats
select		sitename
			,avg(RequestCounts) [avg]
			,stdev(RequestCounts) [stdev]

smaglio81

<# SEE SETTINGS BELOW #>

# pull in the data from the database
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
function Invoke-SqlCmd {
param (
	[string] $Server,
	[string] $Database,
	[string] $Query,
	[int] $Timeout = 30