ODR.Report.Script.ManagedEntityTypeDataGet (DataWarehouseScript)

Element properties:

Source Code:

<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



				</Upgrade>

</DataWarehouseScript>