<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 < 0 THEN -1 ELSE 1 END
CREATE TABLE #ObjectList (ManagedEntityRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 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 >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) AND
(vPerf.DateTime < 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 <> 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 < 0 THEN -1 ELSE 1 END
CREATE TABLE #ObjectList (ManagedEntityRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 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 >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) AND
(vPerf.DateTime < 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 <> 0 GOTO QuitError
QuitError:
DROP TABLE #ObjectList
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceTopInstanceReportDataGet] TO OpsMgrReader
GO