Res.ComTrade.Citrix.XenDesktop.Reports.ReportScript.TrendingStoredProcedure.Install (Resource)

Element properties:

File Content: ComTrade.Citrix.XenDesktop.Reports.ReportScript.TrendingStoredProcedure.Install.sql

IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ComTrade_XenDesktop_Trending_Perf_DataGet')

BEGIN

EXECUTE ('CREATE PROCEDURE dbo.[ComTrade_XenDesktop_Trending_Perf_DataGet] AS RETURN 1')

END

GO



ALTER PROCEDURE [dbo].[ComTrade_XenDesktop_Trending_Perf_DataGet] 

@StartDate_BaseValue  datetime,

@EndDate_BaseValue  datetime,

@EndDateTrend_BaseValue datetime,

@ObjectName varchar(256),

@CounterName varchar(256),

@ObjectList xml,

@AggregationType int, -- 0-hourly, 1-daily, 2-monthly

@BusinessTimeType int, -- 0-Regular, 1-Business

@BusinessDay1 int, --monday

@BusinessDay2 int, --tuesday

@BusinessDay3 int, --wednesday

@BusinessDay4 int, --thursday

@BusinessDay5 int, --friday

@BusinessDay6 int, --saturday

@BusinessDay7 int, --sunday

@StartDate_BaseValueGMT datetime



AS



DECLARE @StartDateTrend_BaseValue  datetime



DECLARE @ExecError int

CREATE TABLE #ManagedEntity (ManagedEntityRowId int)

INSERT INTO #ManagedEntity



EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]

@ObjectList = @ObjectList,

@StartDate = @StartDate_BaseValue ,

@EndDate = @EndDate_BaseValue 



SET DATEFIRST 1 --monday = 1



DECLARE @GMTDiff int

SELECT @GMTDiff = DATEDIFF(minute, @StartDate_BaseValueGMT, @StartDate_BaseValue)



DECLARE @BH_start datetime --business hour start time

DECLARE @BH_end datetime   --business hour end time

SELECT @BH_start = (@StartDate_BaseValue - CAST(FLOOR(CAST(@StartDate_BaseValue AS float)) AS datetime))

SELECT @BH_end = (@EndDate_BaseValue - CAST(FLOOR(CAST(@EndDate_BaseValue AS float)) AS datetime))

CREATE TABLE #PerfTable ("DateTime" datetime, "SampleCount" int, "AverageValue" float, "MinValue" float,

 "MaxValue" float, "StandardDeviation" float, "InstanceName" nvarchar(256) COLLATE database_default, "PerformanceRuleInstanceRowId" int, "Path" nvarchar(max) COLLATE database_default, "ManagedEntityDefaultName" nvarchar(max) COLLATE database_default, "ManagedEntityTypeDefaultName" nvarchar(256) COLLATE database_default, "ManagementGroupDefaultName" nvarchar(256) COLLATE database_default, "ManagedEntityRowId" int, 

 "ObjectName" nvarchar(256) COLLATE database_default, "CounterName" nvarchar(256) COLLATE database_default)

INSERT INTO #PerfTable

SELECT     

DATEADD(minute, @GMTDiff, vPerf.DateTime),

vPerf.SampleCount, 

vPerf.AverageValue,

vPerf.MinValue,

vPerf.MaxValue,

vPerf.StandardDeviation, 

vPerformanceRuleInstance.InstanceName, 

vPerformanceRuleInstance.PerformanceRuleInstanceRowId,

vManagedEntity.Path, 

vManagedEntity.ManagedEntityDefaultName, 

vManagedEntityType.ManagedEntityTypeDefaultName,

vManagementGroup.ManagementGroupDefaultName, 

vManagedEntity.ManagedEntityRowId,

vPerformanceRule.ObjectName, 

 vPerformanceRule.CounterName

FROM Perf.vPerfHourly AS vPerf INNER JOIN

 vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN

 vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN

 vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId INNER JOIN

 vManagementGroup ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId INNER JOIN

 vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId INNER JOIN

 #ManagedEntity ON vManagedEntity.ManagedEntityRowId = #ManagedEntity.ManagedEntityRowId

WHERE vPerf.DateTime >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)

and vPerf.DateTime < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)

AND (vPerformanceRule.ObjectName IN (@ObjectName))

AND (vPerformanceRule.CounterName IN (@CounterName))

ORDER BY  vPerf.DateTime



--select * from #PerfTable





CREATE TABLE #TrendTableFinal ("DateTime" datetime, "SampleCount" int, "AverageValue" float, "MinValue" float,

	 "MaxValue" float, "StandardDeviation" float, "InstanceName" nvarchar(256) COLLATE database_default, "PerformanceRuleInstanceRowId" int, "Path" nvarchar(max) COLLATE database_default, 

     "ManagedEntityDefaultName" nvarchar(max) COLLATE database_default, "ManagedEntityTypeDefaultName" nvarchar(256) COLLATE database_default, "ManagementGroupDefaultName" nvarchar(256) COLLATE database_default, "ManagedEntityRowId" int, 

	 "ObjectName" nvarchar(256) COLLATE database_default, "CounterName" nvarchar(256) COLLATE database_default, "Trend" float)

	



DECLARE @m_instID integer

DECLARE @m_entID integer

DECLARE cur INSENSITIVE CURSOR FOR

select [PerformanceRuleInstanceRowId], [ManagedEntityRowId] from #PerfTable GROUP BY [PerformanceRuleInstanceRowId], [ManagedEntityRowId]



OPEN cur



WHILE 1 = 1

BEGIN

FETCH cur INTO @m_instID, @m_entID

IF @@fetch_status <0

BREAK



	CREATE TABLE #PerfTablePart ("DateTime" datetime, "SampleCount" int, "AverageValue" float, "MinValue" float,

	 "MaxValue" float, "StandardDeviation" float, "InstanceName" nvarchar(256) COLLATE database_default, "PerformanceRuleInstanceRowId" int, "Path" nvarchar(max) COLLATE database_default, "ManagedEntityDefaultName" nvarchar(max) COLLATE database_default, "ManagedEntityTypeDefaultName" nvarchar(256) COLLATE database_default, "ManagementGroupDefaultName" nvarchar(256) COLLATE database_default, "ManagedEntityRowId" int, 

	 "ObjectName" nvarchar(256) COLLATE database_default, "CounterName" nvarchar(256) COLLATE database_default)

	

	IF @BusinessTimeType = 1  --business hours

		BEGIN

			INSERT INTO #PerfTablePart 

			SELECT * FROM #perfTable pt WHERE pt.[PerformanceRuleInstanceRowId] = @m_instID AND pt.[ManagedEntityRowId] = @m_entID

				and pt.[DateTime] - CAST(FLOOR(CAST(pt.[DateTime] AS float)) AS datetime) >= @BH_start --start hours

				and pt.[DateTime] - CAST(FLOOR(CAST(pt.[DateTime] AS float)) AS datetime) < @BH_end   --end hours

				and ((DATEPART(WEEKDAY,  pt.[DateTime]) = 1 and @BusinessDay1 = 1)        --monday

						OR (DATEPART(WEEKDAY,  pt.[DateTime]) = 2 and @BusinessDay2 = 1)  --tuesday

						OR (DATEPART(WEEKDAY,  pt.[DateTime]) = 3 and @BusinessDay3 = 1)  --wednesday

						OR (DATEPART(WEEKDAY,  pt.[DateTime]) = 4 and @BusinessDay4 = 1)  --thursday

						OR (DATEPART(WEEKDAY,  pt.[DateTime]) = 5 and @BusinessDay5 = 1)  --friday

						OR (DATEPART(WEEKDAY,  pt.[DateTime]) = 6 and @BusinessDay6 = 1)  --saturday

						OR (DATEPART(WEEKDAY,  pt.[DateTime]) = 7 and @BusinessDay7 = 1)) --sunday

		END

	ELSE

		BEGIN

			INSERT INTO #PerfTablePart 

			SELECT * FROM #perfTable pt WHERE pt.[PerformanceRuleInstanceRowId] = @m_instID AND pt.[ManagedEntityRowId] = @m_entID

		END	





	--at least 2 rows are needed to calculate trend

	IF cast((select COUNT(*) from #PerfTablePart) as int) > 1 

	BEGIN 

		  

		--calculate trend data from existing data

		DECLARE @m_A  float

		DECLARE @m_b  float



		DECLARE @m_InstanceName nvarchar(256)

		DECLARE @m_Path nvarchar(max)

		DECLARE @m_InstanceID int

		DECLARE @m_EntityID int

		DECLARE @m_ObjectName nvarchar(256)

		DECLARE @m_CounterName nvarchar(256)

		DECLARE @m_ManagedEntityDefaultName nvarchar(max)

		DECLARE @m_ManagedEntityTypeDefaultName nvarchar(256)

		DECLARE @m_ManagementGroupDefaultName nvarchar(256)



		SELECT @m_InstanceName = [InstanceName] FROM #PerfTablePart GROUP BY [InstanceName]

		SELECT @m_Path = [Path] FROM #PerfTablePart GROUP BY [Path]

		SELECT @m_InstanceID = [PerformanceRuleInstanceRowId] FROM #PerfTablePart GROUP BY [PerformanceRuleInstanceRowId]

		SELECT @m_EntityID = [ManagedEntityRowId] FROM #PerfTablePart GROUP BY [ManagedEntityRowId]

		SELECT @m_ObjectName = [ObjectName] FROM #PerfTablePart GROUP BY [ObjectName]

		SELECT @m_CounterName = [CounterName] FROM #PerfTablePart GROUP BY [CounterName]

		SELECT @m_ManagedEntityDefaultName = [ManagedEntityDefaultName] FROM #PerfTablePart GROUP BY [ManagedEntityDefaultName]

		SELECT @m_ManagedEntityTypeDefaultName = [ManagedEntityTypeDefaultName] FROM #PerfTablePart GROUP BY [ManagedEntityTypeDefaultName]

		SELECT @m_ManagementGroupDefaultName = [ManagementGroupDefaultName] FROM #PerfTablePart GROUP BY [ManagementGroupDefaultName]



		--max and min dates

		DECLARE @minDate  datetime

		SELECT @minDate = min([DateTime]) from #PerfTablePart

		DECLARE @maxDate  datetime

		SELECT @maxDate = max([DateTime]) from #PerfTablePart

		SET @StartDateTrend_BaseValue = @maxDate





		-- create temp table (float data for trending parameters calculation)

		CREATE TABLE	#ComTrade_XenDesktop_cf (x float, y float)

		INSERT INTO #ComTrade_XenDesktop_cf

		SELECT cast(([DateTime]-DATEADD(day,-1,(@minDate))) as float), cast([AverageValue] as float) FROM #PerfTablePart



		-- set trending parameters

		--------------------------------------------------------------------------------------------

		-- Curve fitting start - ComTrade_XenDesktop_CurveFitting() function replacement

		--------------------------------------------------------------------------------------------

		DECLARE	@cf_n float, @cf_x float, @cf_x2 float, @cf_y float, @cf_xy float, @cf_y2 float,

			@cf_d float, @cf_a float,	@cf_b float, @cf_r2 float



		SELECT	@cf_n =	COUNT(*),

			@cf_x =	SUM(x),

			@cf_x2 =	SUM(x * x),

			@cf_y =	SUM(y),

			@cf_xy =	SUM(x * y),

			@cf_y2 =	SUM(y * y),

			@cf_d =	@cf_n * @cf_x2 - @cf_x * @cf_x		

		FROM	#ComTrade_XenDesktop_cf



		IF @cf_d = 0

			BEGIN

				SELECT	 @m_A = 0.0

				SELECT   @m_b = 0.0

			END



		SELECT	@cf_a = (@cf_x2 * @cf_y - @cf_x * @cf_xy) / @cf_d,

			@cf_b = (@cf_n * @cf_xy - @cf_x * @cf_y) / @cf_d,

			@cf_r2 = (@cf_a * @cf_y + @cf_b * @cf_xy - @cf_y * @cf_y / @cf_n) / 

					CASE WHEN (@cf_y2 - @cf_y * @cf_y / @cf_n) = 0 THEN 0.000001

					ELSE (@cf_y2 - @cf_y * @cf_y / @cf_n) END



		SELECT	 @m_A = @cf_a

		SELECT   @m_b = @cf_b

		--------------------------------------------------------------------------------------------

		-- Curve fitting end

		--------------------------------------------------------------------------------------------



		-- create trending data table

		CREATE TABLE #TrendTable ([DateTime] datetime, [InstanceName] nvarchar(256) COLLATE database_default, [PerformanceRuleInstanceRowId] int, [Path] nvarchar(max) COLLATE database_default, [ManagedEntityDefaultName] nvarchar(max) COLLATE database_default, [ManagedEntityTypeDefaultName] nvarchar(256) COLLATE database_default, [ManagementGroupDefaultName] nvarchar(256) COLLATE database_default, [ManagedEntityRowId] int, [ObjectName] nvarchar(256) COLLATE database_default, [CounterName] nvarchar(256) COLLATE database_default, [Trend] float)

		INSERT INTO #TrendTable

		SELECT dateadd(ms,500-((datepart(ms,(cast(x as datetime) +DATEADD(day,-1,(@minDate))))+500)%1000),(cast(x as datetime) +DATEADD(day,-1,(@minDate))))

		as [DateTime],

		@m_InstanceName, @m_InstanceID, @m_Path, @m_ManagedEntityDefaultName, @m_ManagedEntityTypeDefaultName, @m_ManagementGroupDefaultName, @m_EntityID, @m_ObjectName, @m_CounterName, 

		@m_A+(@m_b*x) as [Trend]

		FROM #ComTrade_XenDesktop_cf

		UNION

		select A.[DateTime], @m_InstanceName, @m_InstanceID, @m_Path, @m_ManagedEntityDefaultName, @m_ManagedEntityTypeDefaultName, @m_ManagementGroupDefaultName, @m_EntityID, @m_ObjectName, @m_CounterName, A.[Trend] from dbo.ComTrade_XenDesktop_GetDatesTrend(@StartDateTrend_BaseValue, @EndDateTrend_BaseValue, @m_A, @m_b, @minDate, 

			   @AggregationType, @BusinessTimeType, @BusinessDay1,	@BusinessDay2, @BusinessDay3, @BusinessDay4, @BusinessDay5, @BusinessDay6, @BusinessDay7, @BH_start, @BH_end) A



		--add to final trend table

		IF @AggregationType = 0 --HOURLY

		BEGIN 

			INSERT INTO #TrendTableFinal

			SELECT B.[DateTime], A.[SampleCount], A.[AverageValue], A.[MinValue], A.[MaxValue], A.[StandardDeviation],

				   B.[InstanceName], B.[PerformanceRuleInstanceRowId], B.[Path], B.[ManagedEntityDefaultName], B.[ManagedEntityTypeDefaultName], B.[ManagementGroupDefaultName], B.[ManagedEntityRowId], B.[ObjectName], B.[CounterName], B.[Trend] 

			FROM #PerfTablePart A 

			RIGHT JOIN  #TrendTable B ON

			A.[DateTime] = B.[DateTime]

		END



		IF @AggregationType = 1 --DAILY

		BEGIN

			INSERT INTO #TrendTableFinal

			SELECT  convert(datetime,CONVERT(varchar,DATEPART(month, B.[DateTime]))+'/'+CONVERT(varchar,DATEPART(day, B.[DateTime]))+'/'+CONVERT(varchar,DATEPART(yyyy, B.[DateTime]))), 

					SUM(A.[SampleCount]), 

					SUM(A.[AverageValue]*A.[SampleCount])/SUM(A.[SampleCount]), 

					MIN(A.[MinValue]), MAX(A.[MaxValue]), STDEV(A.[AverageValue]),

				   B.[InstanceName], B.[PerformanceRuleInstanceRowId], B.[Path], B.[ManagedEntityDefaultName], B.[ManagedEntityTypeDefaultName], B.[ManagementGroupDefaultName], B.[ManagedEntityRowId], B.[ObjectName], B.[CounterName], AVG(B.[Trend]) 

			FROM #PerfTablePart A 

			RIGHT JOIN  #TrendTable B ON

			A.[DateTime] = B.[DateTime]

			GROUP BY DATEPART(yyyy, B.[DateTime]), DATEPART(month, B.[DateTime]), DATEPART(day, B.[DateTime]),

			B.[InstanceName], B.[PerformanceRuleInstanceRowId], B.[Path], B.[ManagedEntityDefaultName], B.[ManagedEntityTypeDefaultName], B.[ManagementGroupDefaultName], B.[ManagedEntityRowId], B.[ObjectName], B.[CounterName]

		END



		IF @AggregationType = 2 --MONTHLY

		BEGIN

			INSERT INTO #TrendTableFinal

			SELECT  convert(datetime,CONVERT(varchar,DATEPART(month, B.[DateTime]))+'/1/'+CONVERT(varchar,DATEPART(yyyy, B.[DateTime]))), 

					SUM(A.[SampleCount]), 

					SUM(A.[AverageValue]*A.[SampleCount])/SUM(A.[SampleCount]), 

					MIN(A.[MinValue]), MAX(A.[MaxValue]), STDEV(A.[AverageValue]),

				   B.[InstanceName], B.[PerformanceRuleInstanceRowId], B.[Path], B.[ManagedEntityDefaultName], B.[ManagedEntityTypeDefaultName], B.[ManagementGroupDefaultName], B.[ManagedEntityRowId], B.[ObjectName], B.[CounterName], AVG(B.[Trend]) 

			FROM #PerfTablePart A 

			RIGHT JOIN  #TrendTable B ON

			A.[DateTime] = B.[DateTime]

			GROUP BY DATEPART(yyyy, B.[DateTime]), DATEPART(month, B.[DateTime]),

			B.[InstanceName], B.[PerformanceRuleInstanceRowId], B.[Path], B.[ManagedEntityDefaultName], B.[ManagedEntityTypeDefaultName], B.[ManagementGroupDefaultName], B.[ManagedEntityRowId], B.[ObjectName], B.[CounterName]

		END



		IF @AggregationType = 3 --YEARLY

		BEGIN

			INSERT INTO #TrendTableFinal

			SELECT  convert(datetime,'1/1/'+CONVERT(varchar,DATEPART(yyyy, B.[DateTime]))), 

					SUM(A.[SampleCount]), 

					SUM(A.[AverageValue]*A.[SampleCount])/SUM(A.[SampleCount]), 

					MIN(A.[MinValue]), MAX(A.[MaxValue]), STDEV(A.[AverageValue]),

				   B.[InstanceName], B.[PerformanceRuleInstanceRowId], B.[Path], B.[ManagedEntityDefaultName], B.[ManagedEntityTypeDefaultName], B.[ManagementGroupDefaultName], B.[ManagedEntityRowId], B.[ObjectName], B.[CounterName], AVG(B.[Trend]) 

			FROM #PerfTablePart A 

			RIGHT JOIN  #TrendTable B ON

			A.[DateTime] = B.[DateTime]

			GROUP BY DATEPART(yyyy, B.[DateTime]),

			B.[InstanceName], B.[PerformanceRuleInstanceRowId], B.[Path], B.[ManagedEntityDefaultName], B.[ManagedEntityTypeDefaultName], B.[ManagementGroupDefaultName], B.[ManagedEntityRowId], B.[ObjectName], B.[CounterName]

		END





		DROP TABLE #ComTrade_XenDesktop_cf 

		DROP TABLE #TrendTable



	END

	DROP TABLE #PerfTablePart

END



DEALLOCATE cur



--return final table

SELECT * FROM #TrendTableFinal



DROP TABLE #TrendTableFinal

DROP TABLE #PerfTable

DROP TABLE #ManagedEntity 



GO



--===============================================================

-- PERMISSIONS

--===============================================================

GRANT EXECUTE ON dbo.[ComTrade_XenDesktop_Trending_Perf_DataGet] TO OpsMgrReader

GO