<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.ObjectListParse" Accessibility="Public"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]
@StartDate datetime,
@EndDate datetime,
@ObjectList xml,
@ContainmentLevelCount int = 0,
@ContainmentStartLevel int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
DECLARE @RowCount int
CREATE TABLE #ObjectList (ManagedEntityRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
CREATE TABLE #ContainmentObjectList (
ManagedEntityRowId int,
[Level] int
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
CREATE TABLE #RelationshipType (
RelationshipTypeRowId int,
[Level] int
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ObjectList (ManagedEntityRowId)
SELECT ObjectList.ManagedEntityRowId.value('.', 'int')
FROM @ObjectList.nodes('/Data/Objects/Object') AS ObjectList(ManagedEntityRowId)
WHERE ISNULL(ObjectList.ManagedEntityRowId.value('@Use', 'nvarchar(255)'), 'Self') = 'Self'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ContainmentObjectList (ManagedEntityRowId, [Level])
SELECT ObjectList.ManagedEntityRowId.value('.', 'int'), 0
FROM @ObjectList.nodes('/Data/Objects/Object') AS ObjectList(ManagedEntityRowId)
WHERE ISNULL(ObjectList.ManagedEntityRowId.value('@Use', 'nvarchar(255)'), 'Self') = 'Containment'
IF @RowCount > 0
BEGIN
DECLARE @ContainmentRelationshipTypeRowId int
SELECT @ContainmentRelationshipTypeRowId = RelationshipTypeRowId FROM vRelationshipType WHERE RelationshipTypeSystemName = 'System.Containment'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #RelationshipType(RelationshipTypeRowId, [Level])
SELECT RelationshipTypeRowId, [Level]
FROM dbo.RelationshipDerivedTypeHierarchy(@ContainmentRelationshipTypeRowId, 0)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
DECLARE @CurrentLevel int
SET @CurrentLevel = 1
WHILE (((@ContainmentLevelCount >= @CurrentLevel) OR (@ContainmentLevelCount = 0)) AND (@RowCount > 0))
BEGIN
INSERT INTO #ContainmentObjectList (ManagedEntityRowId, [Level])
SELECT DISTINCT r.TargetManagedEntityRowId, @CurrentLevel
FROM Relationship r
JOIN RelationshipManagementGroup rmg ON (r.RelationshipRowId = rmg.RelationshipRowId)
JOIN #RelationshipType rt ON (r.RelationshipTypeRowId = rt.RelationshipTypeRowId)
JOIN #ContainmentObjectList me ON (me.ManagedEntityRowId = r.SourceManagedEntityRowId) AND (me.[Level] = @CurrentLevel - 1)
WHERE (rmg.FromDateTime <= @EndDate) AND (ISNULL(rmg.ToDateTime, '99991231') >= @StartDate)
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
END
END
/* ------------------------------ */
SELECT ManagedEntityRowId
FROM #ObjectList
UNION
SELECT ManagedEntityRowId
FROM #ContainmentObjectList
WHERE [Level] >= @ContainmentStartLevel
SET @Error = @@ERROR
QuitError:
DROP TABLE #ObjectList
DROP TABLE #ContainmentObjectList
DROP TABLE #RelationshipType
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]
@StartDate datetime,
@EndDate datetime,
@ObjectList xml,
@ContainmentLevelCount int = 0,
@ContainmentStartLevel int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
DECLARE @RowCount int
CREATE TABLE #ObjectList (ManagedEntityRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
CREATE TABLE #ContainmentObjectList (
ManagedEntityRowId int,
[Level] int
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
CREATE TABLE #RelationshipType (
RelationshipTypeRowId int,
[Level] int
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ObjectList (ManagedEntityRowId)
SELECT ObjectList.ManagedEntityRowId.value('.', 'int')
FROM @ObjectList.nodes('/Data/Objects/Object') AS ObjectList(ManagedEntityRowId)
WHERE ISNULL(ObjectList.ManagedEntityRowId.value('@Use', 'nvarchar(255)'), 'Self') = 'Self'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ContainmentObjectList (ManagedEntityRowId, [Level])
SELECT ObjectList.ManagedEntityRowId.value('.', 'int'), 0
FROM @ObjectList.nodes('/Data/Objects/Object') AS ObjectList(ManagedEntityRowId)
WHERE ISNULL(ObjectList.ManagedEntityRowId.value('@Use', 'nvarchar(255)'), 'Self') = 'Containment'
IF @RowCount > 0
BEGIN
DECLARE @ContainmentRelationshipTypeRowId int
SELECT @ContainmentRelationshipTypeRowId = RelationshipTypeRowId FROM vRelationshipType WHERE RelationshipTypeSystemName = 'System.Containment'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #RelationshipType(RelationshipTypeRowId, [Level])
SELECT RelationshipTypeRowId, [Level]
FROM dbo.RelationshipDerivedTypeHierarchy(@ContainmentRelationshipTypeRowId, 0)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
DECLARE @CurrentLevel int
SET @CurrentLevel = 1
WHILE (((@ContainmentLevelCount >= @CurrentLevel) OR (@ContainmentLevelCount = 0)) AND (@RowCount > 0))
BEGIN
INSERT INTO #ContainmentObjectList (ManagedEntityRowId, [Level])
SELECT DISTINCT r.TargetManagedEntityRowId, @CurrentLevel
FROM Relationship r
JOIN RelationshipManagementGroup rmg ON (r.RelationshipRowId = rmg.RelationshipRowId)
JOIN #RelationshipType rt ON (r.RelationshipTypeRowId = rt.RelationshipTypeRowId)
JOIN #ContainmentObjectList me ON (me.ManagedEntityRowId = r.SourceManagedEntityRowId) AND (me.[Level] = @CurrentLevel - 1)
WHERE (rmg.FromDateTime <= @EndDate) AND (ISNULL(rmg.ToDateTime, '99991231') >= @StartDate)
Home
