<DataWarehouseScript ID="ODR.Report.Script.ManagedEntityTypeDataGet" Accessibility="Public"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ODR_Report_Library_ManagedEntityTypeDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ODR_Report_Library_ManagedEntityTypeDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[ODR_Report_Library_ManagedEntityTypeDataGet]
AS
BEGIN
SET NOCOUNT ON
SELECT COUNT(vManagedEntityType.ManagedEntityTypeRowID) AS TotalTypes,
vManagedEntityType.ManagedEntityTypeDefaultName,vManagedEntityType.ManagedEntityTypeSystemName,
vManagementPack.ManagementPackSystemName, vManagementGroup.ManagementGroupDefaultName, vManagementGroup.ManagementGroupRowId
FROM vManagedEntity INNER JOIN
vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId INNER JOIN
vManagementPack on vmanagedEntityType.ManagementPackRowId = vManagementPack.ManagementPackRowID INNER JOIN
vManagementGroup ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId
WHERE EXISTS (SELECT *
FROM vManagedEntityManagementGroup
WHERE (vManagedEntityManagementGroup.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId) AND
(vManagedEntityManagementGroup.ToDateTime IS NULL)) AND
vManagementPack.SealedInd = 1
GROUP BY vManagedEntityType.ManagedEntityTypeRowId,
vManagedEntityType.ManagedEntityTypeSystemName,
vManagedEntityType.ManagedEntityTypeDefaultName,
vManagementGroup.ManagementGroupDefaultName,
vManagementPack.ManagementPackSystemName,
vManagementGroup.ManagementGroupRowId
ORDER BY COUNT(vManagedEntityType.ManagedEntityTypeRowID) DESC
END
GO
GRANT EXECUTE ON dbo.[ODR_Report_Library_ManagedEntityTypeDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ODR_Report_Library_ManagedEntityTypeDataGet')
BEGIN
DROP PROCEDURE dbo.[ODR_Report_Library_ManagedEntityTypeDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ODR_Report_Library_ManagedEntityTypeDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ODR_Report_Library_ManagedEntityTypeDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[ODR_Report_Library_ManagedEntityTypeDataGet]
AS
BEGIN
SET NOCOUNT ON
SELECT COUNT(vManagedEntityType.ManagedEntityTypeRowID) AS TotalTypes,
vManagedEntityType.ManagedEntityTypeDefaultName,vManagedEntityType.ManagedEntityTypeSystemName,
vManagementPack.ManagementPackSystemName, vManagementGroup.ManagementGroupDefaultName, vManagementGroup.ManagementGroupRowId
FROM vManagedEntity INNER JOIN
vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId INNER JOIN
vManagementPack on vmanagedEntityType.ManagementPackRowId = vManagementPack.ManagementPackRowID INNER JOIN
vManagementGroup ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId
WHERE EXISTS (SELECT *
FROM vManagedEntityManagementGroup
WHERE (vManagedEntityManagementGroup.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId) AND
(vManagedEntityManagementGroup.ToDateTime IS NULL)) AND
vManagementPack.SealedInd = 1
GROUP BY vManagedEntityType.ManagedEntityTypeRowId,
vManagedEntityType.ManagedEntityTypeSystemName,
vManagedEntityType.ManagedEntityTypeDefaultName,
vManagementGroup.ManagementGroupDefaultName,
vManagementPack.ManagementPackSystemName,
vManagementGroup.ManagementGroupRowId
ORDER BY COUNT(vManagedEntityType.ManagedEntityTypeRowID) DESC
END
GO
GRANT EXECUTE ON dbo.[ODR_Report_Library_ManagedEntityTypeDataGet] TO OpsMgrReader
GO