Res.TheBackbone.SMP04.Reports.TheBackbone_SMP04_BI_InstanceAnalyze.Install (Resource)

Element properties:

Source Code:

<Resource ID="Res.TheBackbone.SMP04.Reports.TheBackbone_SMP04_BI_InstanceAnalyze.Install" Accessibility="Public" FileName="TheBackbone_SMP04_BI_InstanceAnalyze.Install.sql" HasNullStream="false"/>

File Content: TheBackbone_SMP04_BI_InstanceAnalyze.Install.sql

/* 


Verify if the stored procedure already exists, if not create the procedure 


*/


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


	BEGIN


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


    END





GO





SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO





ALTER PROC [dbo].[TheBackbone_SMP04_BI_InstanceAnalyze]


(


	@hoursHistory decimal (6,2) = 1


)


AS


  CREATE TABLE #ObjectList (


	ManagedEntityRowId int,


	InstanceName nvarchar(max),


	Location nvarchar(max)


  )





  INSERT INTO #ObjectList


  SELECT vME.ManagedEntityRowId,


	vME.DisplayName AS InstanceName,


	VMEPS.PropertyValue as Location


  FROM dbo.vManagedEntityType as vMET


	JOIN dbo.vManagedEntity vME on vME.ManagedEntityTypeRowId = vMET.ManagedEntityTypeRowId


	JOIN dbo.vManagedEntityPropertySet vMEPS on vMEPS.ManagedEntityRowId = vME.ManagedEntityRowId


	JOIN dbo.vManagedEntityTypeProperty vMETP on vMETP.PropertyGuid = vMEPS.PropertyGuid


  WHERE vMET.ManagedEntityTypeSystemName like 'TheBackbone.SMP04.Perspective.Instance.Class'


    AND vMETP.PropertySystemName = 'Location'


	AND vMEPS.ToDateTime IS NULL





;WITH Measures AS


(


	SELECT o.Location


		 , o.InstanceName


		 , o.ManagedEntityRowId


		 , 'Measured' AS RuleName


		 , p.SampleValue


		, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()),p.DateTime) AS TimeSampled --From UTC to Local time


	FROM dbo.vRule r


		JOIN dbo.vPerformanceRuleInstance pri ON r.RuleRowId = pri.RuleRowId


		JOIN Perf.vPerfRaw p ON pri.PerformanceRuleInstanceRowId = p.PerformanceRuleInstanceRowId


		JOIN #ObjectList o ON p.ManagedEntityRowId = o.ManagedEntityRowId


	WHERE DATEDIFF(mi, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()),p.DateTime), GETDATE()) < @hoursHistory * 60


		AND r.RuleSystemName = 'TheBackbone.SMP04.Monitoring.Perspective.Instance.TimeMeasured.rule'


),


Thresholds AS


(


	SELECT o.Location


		 , o.ManagedEntityRowId


		 , r.RuleSystemName


		 , MIN(p.SampleValue) AS SampleValue


	FROM dbo.vRule r


		JOIN dbo.vPerformanceRuleInstance pri ON r.RuleRowId = pri.RuleRowId


		JOIN Perf.vPerfRaw p ON pri.PerformanceRuleInstanceRowId = p.PerformanceRuleInstanceRowId


		JOIN #ObjectList o ON p.ManagedEntityRowId = o.ManagedEntityRowId


	WHERE r.RuleSystemName IN ('TheBackbone.SMP04.Monitoring.Perspective.Instance.LowerThreshold.rule'


							 , 'TheBackbone.SMP04.Monitoring.Perspective.Instance.UpperThreshold.rule'


							 )


	GROUP BY o.Location, o.ManagedEntityRowId, r.RuleSystemName


),


ThresholdsPivot AS


(


	SELECT Location


		 , ManagedEntityRowId


		 , [TheBackbone.SMP04.Monitoring.Perspective.Instance.LowerThreshold.rule] AS LowerThreshold


		 , [TheBackbone.SMP04.Monitoring.Perspective.Instance.UpperThreshold.rule] AS UpperThreshold


	FROM Thresholds


		PIVOT(SUM(SampleValue) 


			  FOR RuleSystemName IN ([TheBackbone.SMP04.Monitoring.Perspective.Instance.LowerThreshold.rule]


					               , [TheBackbone.SMP04.Monitoring.Perspective.Instance.UpperThreshold.rule])


			 ) AS piv


)


SELECT m.Location


	 , m.InstanceName AS Simulation


	 , DATEADD(mi, DATEDIFF(mi, GETDATE(), GETUTCDATE()),m.TimeSampled) AS DateTimeUTC


	 , m.TimeSampled AS DateTime


	 , CAST(m.TimeSampled as date) AS Date


	 , DATEADD(SECOND, -DATEPART(second, m.TimeSampled), CAST(m.TimeSampled as Time(0))) as Time --eliminate seconds


	 , m.SampleValue / 1000.00 AS 'Performance (sec)'


	 , t.LowerThreshold / 1000.00 AS 'Warning Threshold'


	 , t.UpperThreshold / 1000.00 AS 'Critical Threshold'


FROM Measures m


	JOIN ThresholdsPivot t ON m.Location = t.Location


							AND m.ManagedEntityRowId = t.ManagedEntityRowId





DROP TABLE #ObjectList