<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.GetRulesByObjectsAndCounters" Accessibility="Public"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_GetRulesByObjectsAndCounters')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_GetRulesByObjectsAndCounters] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_DataWarehouse_Report_GetRulesByObjectsAndCounters]
@OptionList xml,
@ManagementPackSystemName nvarchar(512)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
DECLARE @xmldoc int
DECLARE @ManagementPackRowId int
Select @ManagementPackRowId = ManagementPackRowId From vManagementPack Where ManagementPackSystemName = @ManagementPackSystemName
EXEC @ExecError = sp_xml_preparedocument @xmldoc OUTPUT, @OptionList
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SELECT
1 AS Tag
,NULL AS Parent
,'' AS [Rules!1]
,NULL AS [Rule!2!RuleGuid]
,NULL AS [Rule!2!ObjectName]
,NULL AS [Rule!2!CounterName]
,NULL AS [Rule!2!InstanceName]
Union
SELECT
2 AS Tag
,1 AS Parent
,NULL AS [Rules!1!N]
,vRule.RuleGuid AS [Rule!2!RuleGuid]
,vPerformanceRule.ObjectName AS [Rule!2!ObjectName]
,vPerformanceRule.CounterName AS [Rule!2!CounterName]
,vPerformanceRuleInstance.InstanceName AS [Rule!2!InstanceName]
FROM OPENXML(@xmldoc, '/Data/Values/Value', 2) WITH
(ObjectName nvarchar(512) 'PerformanceObject',
CounterName nvarchar(512) 'PerformanceCounter',
InstanceName nvarchar(512) 'Instance') As InstanceList
INNER JOIN vPerformanceRule
ON vPerformanceRule.ObjectName = InstanceList.ObjectName
AND vPerformanceRule.CounterName = InstanceList.CounterName
INNER JOIN vRule
ON vRule.RuleRowId = vPerformanceRule.RuleRowId
AND vRule.ManagementPackRowId = @ManagementPackRowId
INNER JOIN vPerformanceRuleInstance
ON vRule.RuleRowId = vPerformanceRuleInstance.RuleRowId
FOR XML EXPLICIT
QuitError:
-- remove xml document if opened
IF @xmldoc IS NOT NULL EXEC sp_xml_removedocument @xmldoc
IF ((@Error = 0) AND (@ExecError <> 0)) SET @Error = @ExecError
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_GetRulesByObjectsAndCounters] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_GetRulesByObjectsAndCounters')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_GetRulesByObjectsAndCounters]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_GetRulesByObjectsAndCounters')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_GetRulesByObjectsAndCounters] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_DataWarehouse_Report_GetRulesByObjectsAndCounters]
@OptionList xml,
@ManagementPackSystemName nvarchar(512)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
DECLARE @xmldoc int
DECLARE @ManagementPackRowId int
Select @ManagementPackRowId = ManagementPackRowId From vManagementPack Where ManagementPackSystemName = @ManagementPackSystemName
EXEC @ExecError = sp_xml_preparedocument @xmldoc OUTPUT, @OptionList
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SELECT
1 AS Tag
,NULL AS Parent
,'' AS [Rules!1]
,NULL AS [Rule!2!RuleGuid]
,NULL AS [Rule!2!ObjectName]
,NULL AS [Rule!2!CounterName]
,NULL AS [Rule!2!InstanceName]
Union
SELECT
2 AS Tag
,1 AS Parent
,NULL AS [Rules!1!N]
,vRule.RuleGuid AS [Rule!2!RuleGuid]
,vPerformanceRule.ObjectName AS [Rule!2!ObjectName]
,vPerformanceRule.CounterName AS [Rule!2!CounterName]
,vPerformanceRuleInstance.InstanceName AS [Rule!2!InstanceName]
FROM OPENXML(@xmldoc, '/Data/Values/Value', 2) WITH
(ObjectName nvarchar(512) 'PerformanceObject',
CounterName nvarchar(512) 'PerformanceCounter',
InstanceName nvarchar(512) 'Instance') As InstanceList
INNER JOIN vPerformanceRule
ON vPerformanceRule.ObjectName = InstanceList.ObjectName
AND vPerformanceRule.CounterName = InstanceList.CounterName
INNER JOIN vRule
ON vRule.RuleRowId = vPerformanceRule.RuleRowId
AND vRule.ManagementPackRowId = @ManagementPackRowId
INNER JOIN vPerformanceRuleInstance
ON vRule.RuleRowId = vPerformanceRuleInstance.RuleRowId
FOR XML EXPLICIT
QuitError:
-- remove xml document if opened
IF @xmldoc IS NOT NULL EXEC sp_xml_removedocument @xmldoc
IF ((@Error = 0) AND (@ExecError <> 0)) SET @Error = @ExecError
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_GetRulesByObjectsAndCounters] TO OpsMgrReader
GO
Home
