<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.CustomConfigurationColumn" Accessibility="Public"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportColumnList')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportColumnList] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportColumnList]
@StartDate datetime,
@EndDate datetime,
@ObjectList xml,
@LanguageCode varchar(3)= 'ENU'
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
---------------------------------------------------------------------
--Create a list of Managed Entities
---------------------------------------------------------------------
CREATE TABLE #ObjectList (ManagedEntityRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create list of all relationships derived from 'System.Hosting'
-- Assuming A MAX depth (level = 0)
---------------------------------------------------------------------
CREATE TABLE #RelationshipList(RelationshipTypeRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #RelationshipList
SELECT RelationshipTypeRowId
from RelationshipDerivedTypeHierarchy
(
(
SELECT RelationshipTypeRowId
FROM dbo.vRelationshipType
WHERE RelationshipTypeSystemName = 'System.Hosting'
)
,0
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create list of all entities above the selected objects in their respective hosting chains
---------------------------------------------------------------------
CREATE TABLE #Entity_Tree(ManagedEntityRowId int not null, Level int not null default 0)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #Entity_Tree
SELECT ManagedEntityRowId, 0
FROM #ObjectList
declare @count int
declare @level int
set @count = 1
set @level = 0
while @count > 0
begin
INSERT #Entity_Tree
SELECT r.SourceManagedEntityRowId, et.Level + 1
FROM vRelationship AS r
JOIN #Entity_Tree AS et ON r.TargetManagedEntityRowId = et.ManagedEntityRowId
JOIN #RelationshipList rl ON r.RelationshipTypeRowId = rl.RelationshipTypeRowId
WHERE et.Level = @level
set @count = @@ROWCOUNT
set @level = @level + 1
end
---------------------------------------------------------------------
--Create List of Selected Properties for selected Objects
---------------------------------------------------------------------
SELECT
vManagedEntityTypeProperty.PropertyGuid as PropertyGuid,
TypeList.ManagedEntityTypeRowId as ManagedEntityTypeRowId,
vManagedEntityTypeProperty.PropertySystemName as PropertySystemName,
ISNULL(vDisplayString.Name,vManagedEntityTypeProperty.PropertyDefaultName) as PropertyDefaultName
FROM
(
SELECT Distinct t.ManagedEntityTypeRowId
FROM
#Entity_Tree Inner Join
vManagedEntity ON vManagedEntity.ManagedEntityRowId = #Entity_Tree.ManagedEntityRowId Inner Join
vTypedManagedEntity ON vManagedEntity.ManagedEntityRowId = vTypedManagedEntity.ManagedEntityRowId
CROSS APPLY ManagedEntityBaseTypeHierarchy(vTypedManagedEntity.ManagedEntityTypeRowId,0) t
) AS TypeList
INNER JOIN
vManagedEntityTypeProperty ON vManagedEntityTypeProperty.ManagedEntityTypeRowId = TypeList.ManagedEntityTypeRowId
LEFT OUTER JOIN
vDisplayString ON vManagedEntityTypeProperty.PropertyGuid = vDisplayString.ElementGuid
AND
vDisplayString.LanguageCode = @LanguageCode
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Error Handling & Cleanup code
---------------------------------------------------------------------
QuitError:
IF ((@Error = 0) AND (@ExecError <> 0)) SET @Error = @ExecError
DROP TABLE #ObjectList
DROP TABLE #RelationshipList
DROP TABLE #Entity_Tree
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportColumnList] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = '[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportColumnList]')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportColumnList]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportColumnList')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportColumnList] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportColumnList]
@StartDate datetime,
@EndDate datetime,
@ObjectList xml,
@LanguageCode varchar(3)= 'ENU'
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
---------------------------------------------------------------------
--Create a list of Managed Entities
---------------------------------------------------------------------
CREATE TABLE #ObjectList (ManagedEntityRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create list of all relationships derived from 'System.Hosting'
-- Assuming A MAX depth (level = 0)
---------------------------------------------------------------------
CREATE TABLE #RelationshipList(RelationshipTypeRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #RelationshipList
SELECT RelationshipTypeRowId
from RelationshipDerivedTypeHierarchy
(
(
SELECT RelationshipTypeRowId
FROM dbo.vRelationshipType
WHERE RelationshipTypeSystemName = 'System.Hosting'
)
,0
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create list of all entities above the selected objects in their respective hosting chains
---------------------------------------------------------------------
CREATE TABLE #Entity_Tree(ManagedEntityRowId int not null, Level int not null default 0)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #Entity_Tree
SELECT ManagedEntityRowId, 0
FROM #ObjectList
declare @count int
declare @level int
set @count = 1
set @level = 0
while @count > 0
begin
INSERT #Entity_Tree
SELECT r.SourceManagedEntityRowId, et.Level + 1
FROM vRelationship AS r
JOIN #Entity_Tree AS et ON r.TargetManagedEntityRowId = et.ManagedEntityRowId
JOIN #RelationshipList rl ON r.RelationshipTypeRowId = rl.RelationshipTypeRowId
WHERE et.Level = @level
set @count = @@ROWCOUNT
set @level = @level + 1
end
---------------------------------------------------------------------
--Create List of Selected Properties for selected Objects
---------------------------------------------------------------------
SELECT
vManagedEntityTypeProperty.PropertyGuid as PropertyGuid,
TypeList.ManagedEntityTypeRowId as ManagedEntityTypeRowId,
vManagedEntityTypeProperty.PropertySystemName as PropertySystemName,
ISNULL(vDisplayString.Name,vManagedEntityTypeProperty.PropertyDefaultName) as PropertyDefaultName
FROM
(
SELECT Distinct t.ManagedEntityTypeRowId
FROM
#Entity_Tree Inner Join
vManagedEntity ON vManagedEntity.ManagedEntityRowId = #Entity_Tree.ManagedEntityRowId Inner Join
vTypedManagedEntity ON vManagedEntity.ManagedEntityRowId = vTypedManagedEntity.ManagedEntityRowId
CROSS APPLY ManagedEntityBaseTypeHierarchy(vTypedManagedEntity.ManagedEntityTypeRowId,0) t
) AS TypeList
INNER JOIN
vManagedEntityTypeProperty ON vManagedEntityTypeProperty.ManagedEntityTypeRowId = TypeList.ManagedEntityTypeRowId
LEFT OUTER JOIN
vDisplayString ON vManagedEntityTypeProperty.PropertyGuid = vDisplayString.ElementGuid
AND
vDisplayString.LanguageCode = @LanguageCode
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Error Handling & Cleanup code
---------------------------------------------------------------------
QuitError:
IF ((@Error = 0) AND (@ExecError <> 0)) SET @Error = @ExecError
DROP TABLE #ObjectList
DROP TABLE #RelationshipList
DROP TABLE #Entity_Tree
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportColumnList] TO OpsMgrReader
GO
Home
