<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.PerformanceTop" 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_PerformaceTopReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceTopReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceTopReportDataGet]
@StartDate datetime,
@EndDate datetime,
@RuleId uniqueidentifier,
@ManagementGroup xml,
@InstanceList xml,
@SortOrder int,
@TopCount int,
@LanguageCode varchar(3) = 'ENU'
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
SET @SortOrder = CASE WHEN @SortOrder < 0 THEN -1 ELSE 1 END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #GroupList (ManagementGroupGuid)
SELECT GroupList.ManagementGroupGuid.value('.', 'uniqueidentifier')
FROM @ManagementGroup.nodes('/Data/Value') AS GroupList(ManagementGroupGuid)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
IF @InstanceList IS NULL
BEGIN
INSERT INTO #InstanceList (PerformanceRuleInstanceRowId)
SELECT vPerformanceRuleInstance.PerformanceRuleInstanceRowId
FROM vPerformanceRuleInstance INNER JOIN
vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
WHERE vRule.RuleGuid = @RuleId
END
ELSE
BEGIN
INSERT INTO #InstanceList (PerformanceRuleInstanceRowId)
SELECT vPerformanceRuleInstance.PerformanceRuleInstanceRowId
FROM vPerformanceRuleInstance INNER JOIN
vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId INNER JOIN
@InstanceList.nodes('/Data/Value') AS InstanceList(InstanceName) ON InstanceList.InstanceName.value('.', 'nvarchar(256)') = vPerformanceRuleInstance.InstanceName
WHERE vRule.RuleGuid = @RuleId
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* ------------------------------ */
SELECT TOP (@TopCount) WITH TIES
vManagedEntity.ManagedEntityGuid,
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,
vManagedEntity.ManagedEntityRowId, vManagedEntity.ManagedEntityDefaultName, vManagedEntity.Path,
vRule.RuleGuid, vRule.RuleDefaultName,
vManagementGroup.ManagementGroupGuid, vManagementGroup.ManagementGroupDefaultName,
ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)AS DisplayName,
vManagedEntityTypeImage.Image
FROM Perf.vPerfDaily As vPerf INNER JOIN
vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagementGroup ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId INNER JOIN
#GroupList AS GroupList ON vManagementGroup.ManagementGroupGuid = GroupList.ManagementGroupGuid INNER JOIN
vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId INNER JOIN
vPerformanceRuleInstance ON vPerf.PerformanceRuleInstanceRowId = vPerformanceRuleInstance.PerformanceRuleInstanceRowId INNER JOIN
#InstanceList AS InstanceList ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = InstanceList.PerformanceRuleInstanceRowId INNER JOIN
vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId LEFT OUTER JOIN
vManagedEntityTypeImage ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId AND
vManagedEntityTypeImage.ImageCategory = N'u16x16Icon' LEFT OUTER JOIN
vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid AND
vDisplayString.LanguageCode = @LanguageCode
WHERE (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 vManagedEntity.ManagedEntityGuid, vManagedEntity.Path, vManagedEntityTypeImage.Image, vManagedEntity.ManagedEntityRowId,
vManagedEntity.ManagedEntityDefaultName, vRule.RuleGuid, vRule.RuleDefaultName, vManagementGroup.ManagementGroupGuid,
vManagementGroup.ManagementGroupDefaultName, vManagedEntityType.ManagedEntityTypeDefaultName, vDisplayString.Name
ORDER BY (SUM(vPerf.SampleCount * vPerf.AverageValue) / SUM(vPerf.SampleCount)) * @SortOrder
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
QuitError:
DROP TABLE #GroupList
DROP TABLE #InstanceList
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceTopReportDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceTopReportDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceTopReportDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceTopReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceTopReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceTopReportDataGet]
@StartDate datetime,
@EndDate datetime,
@RuleId uniqueidentifier,
@ManagementGroup xml,
@InstanceList xml,
@SortOrder int,
@TopCount int,
@LanguageCode varchar(3) = 'ENU'
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
SET @SortOrder = CASE WHEN @SortOrder < 0 THEN -1 ELSE 1 END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #GroupList (ManagementGroupGuid)
SELECT GroupList.ManagementGroupGuid.value('.', 'uniqueidentifier')
FROM @ManagementGroup.nodes('/Data/Value') AS GroupList(ManagementGroupGuid)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
IF @InstanceList IS NULL
BEGIN
INSERT INTO #InstanceList (PerformanceRuleInstanceRowId)
SELECT vPerformanceRuleInstance.PerformanceRuleInstanceRowId
FROM vPerformanceRuleInstance INNER JOIN
vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
WHERE vRule.RuleGuid = @RuleId
END
ELSE
BEGIN
INSERT INTO #InstanceList (PerformanceRuleInstanceRowId)
SELECT vPerformanceRuleInstance.PerformanceRuleInstanceRowId
FROM vPerformanceRuleInstance INNER JOIN
vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId INNER JOIN
@InstanceList.nodes('/Data/Value') AS InstanceList(InstanceName) ON InstanceList.InstanceName.value('.', 'nvarchar(256)') = vPerformanceRuleInstance.InstanceName
WHERE vRule.RuleGuid = @RuleId
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* ------------------------------ */
SELECT TOP (@TopCount) WITH TIES
vManagedEntity.ManagedEntityGuid,
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,
vManagedEntity.ManagedEntityRowId, vManagedEntity.ManagedEntityDefaultName, vManagedEntity.Path,
vRule.RuleGuid, vRule.RuleDefaultName,
vManagementGroup.ManagementGroupGuid, vManagementGroup.ManagementGroupDefaultName,
ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)AS DisplayName,
vManagedEntityTypeImage.Image
FROM Perf.vPerfDaily As vPerf INNER JOIN
vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagementGroup ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId INNER JOIN
#GroupList AS GroupList ON vManagementGroup.ManagementGroupGuid = GroupList.ManagementGroupGuid INNER JOIN
vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId INNER JOIN
vPerformanceRuleInstance ON vPerf.PerformanceRuleInstanceRowId = vPerformanceRuleInstance.PerformanceRuleInstanceRowId INNER JOIN
#InstanceList AS InstanceList ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = InstanceList.PerformanceRuleInstanceRowId INNER JOIN
vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId LEFT OUTER JOIN
vManagedEntityTypeImage ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId AND
vManagedEntityTypeImage.ImageCategory = N'u16x16Icon' LEFT OUTER JOIN
vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid AND
vDisplayString.LanguageCode = @LanguageCode
WHERE (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 vManagedEntity.ManagedEntityGuid, vManagedEntity.Path, vManagedEntityTypeImage.Image, vManagedEntity.ManagedEntityRowId,
vManagedEntity.ManagedEntityDefaultName, vRule.RuleGuid, vRule.RuleDefaultName, vManagementGroup.ManagementGroupGuid,
vManagementGroup.ManagementGroupDefaultName, vManagedEntityType.ManagedEntityTypeDefaultName, vDisplayString.Name
ORDER BY (SUM(vPerf.SampleCount * vPerf.AverageValue) / SUM(vPerf.SampleCount)) * @SortOrder
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
QuitError:
DROP TABLE #GroupList
DROP TABLE #InstanceList
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceTopReportDataGet] TO OpsMgrReader
GO
Home
