Microsoft.SystemCenter.DataWarehouse.Report.Script.PerformanceTopInstance (DataWarehouseScript)

Element properties:

Source Code:

<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.PerformanceTopInstance" Accessibility="Public" DataWarehouseDataSet="DataWarehouse!Microsoft.SystemCenter.DataWarehouse.DataSet.Performance">

  <Install> 

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

	BEGIN

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

	END

GO



ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceTopInstanceReportDataGet]

  @StartDate datetime,

  @EndDate datetime,

  @RuleId uniqueidentifier,

  @ObjectList xml,

  @SortOrder int,

  @TopCount int,

  @LanguageCode varchar(3) = 'ENU'

AS

BEGIN

  SET NOCOUNT ON



  DECLARE @Error int

  DECLARE @ExecError int



  SET @SortOrder = CASE WHEN @SortOrder &lt; 0 THEN -1 ELSE 1 END



  CREATE TABLE #ObjectList (ManagedEntityRowId int)



  SET @Error = @@ERROR

  IF @Error &lt;&gt; 0 GOTO QuitError



  INSERT INTO #ObjectList (ManagedEntityRowId)

  EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]

    @ObjectList = @ObjectList,

    @StartDate = @StartDate,

    @EndDate = @EndDate



  SET @Error = @@ERROR

  IF @Error &lt;&gt; 0 OR @ExecError &lt;&gt; 0 GOTO QuitError

		

/* ------------------------------ */



  SELECT TOP (@TopCount) WITH TIES

        vPerformanceRuleInstance.InstanceName,

        SUM(vPerf.SampleCount * vPerf.AverageValue) / SUM(vPerf.SampleCount) AS TotalAverageValue,

        SUM(vPerf.SampleCount) AS TotalSampleCount, MIN(vPerf.MinValue) AS TotalMinValue, 

        MAX(vPerf.MaxValue) AS TotalMaxValue,

        SQRT(SUM(vPerf.SampleCount * POWER(vPerf.StandardDeviation, 2)) / SUM(vPerf.SampleCount)) AS TotalStandardDeviation,

        vRule.RuleGuid, ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS RuleDisplayName

  FROM  Perf.vPerfDaily as vPerf INNER JOIN

        #ObjectList As ObjectList ON vPerf.ManagedEntityRowId = ObjectList.ManagedEntityRowId INNER JOIN

        vPerformanceRuleInstance ON vPerf.PerformanceRuleInstanceRowId = vPerformanceRuleInstance.PerformanceRuleInstanceRowId INNER JOIN

        vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId LEFT OUTER JOIN

		vDisplayString vDisplayStringRule ON vRule.RuleGuid = vDisplayStringRule.ElementGuid AND

		vDisplayStringRule.LanguageCode = @LanguageCode

  WHERE (vRule.RuleGuid = @RuleId) AND

		(vPerf.DateTime &gt;= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) AND

		(vPerf.DateTime &lt; DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112)))

  GROUP BY vPerformanceRuleInstance.InstanceName, vRule.RuleGuid, vRule.RuleDefaultName, vDisplayStringRule.Name

  ORDER BY (SUM(vPerf.SampleCount * vPerf.AverageValue) / SUM(vPerf.SampleCount)) * @SortOrder



  SET @Error = @@ERROR

  IF @Error &lt;&gt; 0 GOTO QuitError



QuitError:

  DROP TABLE #ObjectList

 

  RETURN @Error

END

GO



GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceTopInstanceReportDataGet] TO OpsMgrReader

GO



                </Install>

  <Uninstall>

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

	BEGIN

        DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceTopInstanceReportDataGet]

	END

GO



                </Uninstall>

  <Upgrade> 

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

	BEGIN

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

	END

GO



ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceTopInstanceReportDataGet]

  @StartDate datetime,

  @EndDate datetime,

  @RuleId uniqueidentifier,

  @ObjectList xml,

  @SortOrder int,

  @TopCount int,

  @LanguageCode varchar(3) = 'ENU'

AS

BEGIN

  SET NOCOUNT ON



  DECLARE @Error int

  DECLARE @ExecError int



  SET @SortOrder = CASE WHEN @SortOrder &lt; 0 THEN -1 ELSE 1 END



  CREATE TABLE #ObjectList (ManagedEntityRowId int)



  SET @Error = @@ERROR

  IF @Error &lt;&gt; 0 GOTO QuitError



  INSERT INTO #ObjectList (ManagedEntityRowId)

  EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]

    @ObjectList = @ObjectList,

    @StartDate = @StartDate,

    @EndDate = @EndDate



  SET @Error = @@ERROR

  IF @Error &lt;&gt; 0 OR @ExecError &lt;&gt; 0 GOTO QuitError

		

/* ------------------------------ */



  SELECT TOP (@TopCount) WITH TIES

        vPerformanceRuleInstance.InstanceName,

        SUM(vPerf.SampleCount * vPerf.AverageValue) / SUM(vPerf.SampleCount) AS TotalAverageValue,

        SUM(vPerf.SampleCount) AS TotalSampleCount, MIN(vPerf.MinValue) AS TotalMinValue, 

        MAX(vPerf.MaxValue) AS TotalMaxValue,

        SQRT(SUM(vPerf.SampleCount * POWER(vPerf.StandardDeviation, 2)) / SUM(vPerf.SampleCount)) AS TotalStandardDeviation,

        vRule.RuleGuid, ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS RuleDisplayName

  FROM  Perf.vPerfDaily as vPerf INNER JOIN

        #ObjectList As ObjectList ON vPerf.ManagedEntityRowId = ObjectList.ManagedEntityRowId INNER JOIN

        vPerformanceRuleInstance ON vPerf.PerformanceRuleInstanceRowId = vPerformanceRuleInstance.PerformanceRuleInstanceRowId INNER JOIN

        vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId LEFT OUTER JOIN

		vDisplayString vDisplayStringRule ON vRule.RuleGuid = vDisplayStringRule.ElementGuid AND

		vDisplayStringRule.LanguageCode = @LanguageCode

  WHERE (vRule.RuleGuid = @RuleId) AND

		(vPerf.DateTime &gt;= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) AND

		(vPerf.DateTime &lt; DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112)))

  GROUP BY vPerformanceRuleInstance.InstanceName, vRule.RuleGuid, vRule.RuleDefaultName, vDisplayStringRule.Name

  ORDER BY (SUM(vPerf.SampleCount * vPerf.AverageValue) / SUM(vPerf.SampleCount)) * @SortOrder



  SET @Error = @@ERROR

  IF @Error &lt;&gt; 0 GOTO QuitError



QuitError:

  DROP TABLE #ObjectList

 

  RETURN @Error

END

GO



GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceTopInstanceReportDataGet] TO OpsMgrReader

GO



                </Upgrade>

</DataWarehouseScript>