<DataWarehouseScript ID="ODR.Report.Script.ManagementGroupsReportWorkloadClassesDataGet" Accessibility="Public"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ODR_Report_Library_ManagementGroupReportWorkloadClassesDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ODR_Report_Library_ManagementGroupReportWorkloadClassesDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[ODR_Report_Library_ManagementGroupReportWorkloadClassesDataGet]
@StartDate datetime,
@EndDate datetime,
@LanguageCode varchar(10)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
----------------------------------------------------------------------------------
-- Populate Computer list with instances of classes derived from 'System.Computer'
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
INSERT INTO #ComputerList
SELECT
vManagedEntityType.ManagedEntityTypeSystemName,
vManagedEntityType.ManagedEntityTypeRowId,
vManagedEntity.ManagedEntityDefaultName,
vManagedEntity.ManagedEntityRowId
FROM dbo.ManagedEntityDerivedTypeHierarchy
(
(
Select ManagedEntityTypeRowId
FROM vManagedEntityType
WHERE vManagedEntityType.ManagedEntityTypeSystemName = 'System.Computer'
)
,3
) AS DerivedTypes
INNER JOIN vManagedEntity
ON vManagedEntity.ManagedEntityTypeRowId = DerivedTypes.ManagedEntityTypeRowId
INNER JOIN vManagedEntityType
ON vManagedEntityType.ManagedEntityTypeRowId = DerivedTypes.ManagedEntityTypeRowId
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
------------------------------------------------------------------------------
SELECT MAX(Class) AS Class,IsVirtual,IsCluster
FROM
(
SELECT
#ComputerList.ManagedEntityDefaultName AS ComputerName,
#ComputerList.ManagedEntityTypeSystemName AS ComputerType,
Class = CASE
WHEN Licenses.TargetLicenseType IS NULL THEN 0
WHEN #ComputerList.ManagedEntityTypeSystemName ='Microsoft.Windows.Client.Computer' THEN 1
WHEN #ComputerList.ManagedEntityTypeSystemName ='System.Mom.BackwardCompatibility.Computer.Client' THEN 1
WHEN #ComputerList.ManagedEntityTypeSystemName ='Microsoft.SystemCenter.ManagedComputerClient' THEN 1
WHEN Licenses.TargetLicenseType ='Microsoft.SystemCenter.License.Enterprise' THEN 3
WHEN Licenses.TargetLicenseType ='Microsoft.SystemCenter.License.Standard' THEN 2
END,
IsVirtual = CASE( PropertyList.Property.value('.','nvarchar(256)') )
WHEN '1' THEN 1
ELSE 0
END,
IsCluster = CASE( #ComputerList.ManagedEntityTypeSystemName )
WHEN 'Microsoft.Windows.Cluster.VirtualServer' THEN 1
ELSE 0
END
FROM
#ComputerList INNER JOIN
vManagedEntityProperty
ON vManagedEntityProperty.ManagedEntityRowId = #ComputerList.ManagedEntityRowId
CROSS APPLY vManagedEntityProperty.PropertyXml.nodes('/Root/Property') AS PropertyList(Property)
INNER JOIN
vManagedEntityTypeProperty
ON vManagedEntityTypeProperty.PropertyGuid = PropertyList.Property.value('@Guid','uniqueidentifier')
AND
(
vManagedEntityTypeProperty.PropertySystemName = 'IsVirtualMachine'
OR vManagedEntityTypeProperty.PropertySystemName = 'IsVirtualNode'
OR vManagedEntityTypeProperty.PropertySystemName = 'Virtual_Server_Type'
)
INNER JOIN vManagedEntityManagementGroup AS ComputerManagementGroup
ON ComputerManagementGroup.ManagedEntityRowId = #ComputerList.ManagedEntityRowId
AND ComputerManagementGroup.FromDateTime <= @EndDate
AND (ComputerManagementGroup.ToDateTime IS NULL OR ComputerManagementGroup.ToDateTime <= @StartDate)
AND @StartDate <= @EndDate
AND @StartDate <= getdate()
-- enumerate hosted licenses
LEFT OUTER JOIN
(
SELECT
LicenseRelationship.SourceManagedEntityRowId
,TargetLicenseType.ManagedEntityTypeSystemName AS TargetLicenseType
FROM
vRelationship AS LicenseRelationship INNER JOIN
vManagedEntity AS TargetLicense
ON LicenseRelationship.TargetManagedEntityRowId = TargetLicense.ManagedEntityRowId INNER JOIN
vManagedEntityType AS TargetLicenseType
ON TargetLicense.ManagedEntityTypeRowId = TargetLicenseType.ManagedEntityTypeRowId INNER JOIN
vManagedEntityManagementGroup AS ComputerManagementGroup
ON ComputerManagementGroup.ManagedEntityRowId = TargetLicense.ManagedEntityRowId
AND ComputerManagementGroup.FromDateTime < @EndDate
AND (ComputerManagementGroup.ToDateTime IS NULL OR ComputerManagementGroup.ToDateTime <= @StartDate)
AND @StartDate <= @EndDate
WHERE LicenseRelationship.RelationshipTypeRowId =
(
SELECT RelationshipTypeRowId
from vRelationshipType
where vRelationshipType.RelationshipTypeSystemName = 'Microsoft.SystemCenter.WindowsComputerHostsSystemCenterLicense'
)
) AS Licenses
ON SourceManagedEntityRowId = #ComputerList.ManagedEntityRowId
)AS SourceTable
GROUP BY ComputerType,ComputerName,IsVirtual,IsCluster
SET @Error = @@ERROR
QuitError:
DROP TABLE #ComputerList
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[ODR_Report_Library_ManagementGroupReportWorkloadClassesDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ODR_Report_Library_ManagementGroupReportWorkloadClassesDataGet')
BEGIN
DROP PROCEDURE dbo.[ODR_Report_Library_ManagementGroupReportWorkloadClassesDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ODR_Report_Library_ManagementGroupReportWorkloadClassesDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ODR_Report_Library_ManagementGroupReportWorkloadClassesDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[ODR_Report_Library_ManagementGroupReportWorkloadClassesDataGet]
@StartDate datetime,
@EndDate datetime,
@LanguageCode varchar(10)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
----------------------------------------------------------------------------------
-- Populate Computer list with instances of classes derived from 'System.Computer'
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
INSERT INTO #ComputerList
SELECT
vManagedEntityType.ManagedEntityTypeSystemName,
vManagedEntityType.ManagedEntityTypeRowId,
vManagedEntity.ManagedEntityDefaultName,
vManagedEntity.ManagedEntityRowId
FROM dbo.ManagedEntityDerivedTypeHierarchy
(
(
Select ManagedEntityTypeRowId
FROM vManagedEntityType
WHERE vManagedEntityType.ManagedEntityTypeSystemName = 'System.Computer'
)
,3
) AS DerivedTypes
INNER JOIN vManagedEntity
ON vManagedEntity.ManagedEntityTypeRowId = DerivedTypes.ManagedEntityTypeRowId
INNER JOIN vManagedEntityType
ON vManagedEntityType.ManagedEntityTypeRowId = DerivedTypes.ManagedEntityTypeRowId
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
------------------------------------------------------------------------------
SELECT MAX(Class) AS Class,IsVirtual,IsCluster
FROM
(
SELECT
#ComputerList.ManagedEntityDefaultName AS ComputerName,
#ComputerList.ManagedEntityTypeSystemName AS ComputerType,
Class = CASE
WHEN Licenses.TargetLicenseType IS NULL THEN 0
WHEN #ComputerList.ManagedEntityTypeSystemName ='Microsoft.Windows.Client.Computer' THEN 1
WHEN #ComputerList.ManagedEntityTypeSystemName ='System.Mom.BackwardCompatibility.Computer.Client' THEN 1
WHEN #ComputerList.ManagedEntityTypeSystemName ='Microsoft.SystemCenter.ManagedComputerClient' THEN 1
WHEN Licenses.TargetLicenseType ='Microsoft.SystemCenter.License.Enterprise' THEN 3
WHEN Licenses.TargetLicenseType ='Microsoft.SystemCenter.License.Standard' THEN 2
END,
IsVirtual = CASE( PropertyList.Property.value('.','nvarchar(256)') )
WHEN '1' THEN 1
ELSE 0
END,
IsCluster = CASE( #ComputerList.ManagedEntityTypeSystemName )
WHEN 'Microsoft.Windows.Cluster.VirtualServer' THEN 1
ELSE 0
END
FROM
#ComputerList INNER JOIN
vManagedEntityProperty
ON vManagedEntityProperty.ManagedEntityRowId = #ComputerList.ManagedEntityRowId
CROSS APPLY vManagedEntityProperty.PropertyXml.nodes('/Root/Property') AS PropertyList(Property)
INNER JOIN
vManagedEntityTypeProperty
ON vManagedEntityTypeProperty.PropertyGuid = PropertyList.Property.value('@Guid','uniqueidentifier')
AND
(
vManagedEntityTypeProperty.PropertySystemName = 'IsVirtualMachine'
OR vManagedEntityTypeProperty.PropertySystemName = 'IsVirtualNode'
OR vManagedEntityTypeProperty.PropertySystemName = 'Virtual_Server_Type'
)
INNER JOIN vManagedEntityManagementGroup AS ComputerManagementGroup
ON ComputerManagementGroup.ManagedEntityRowId = #ComputerList.ManagedEntityRowId
AND ComputerManagementGroup.FromDateTime <= @EndDate
AND (ComputerManagementGroup.ToDateTime IS NULL OR ComputerManagementGroup.ToDateTime <= @StartDate)
AND @StartDate <= @EndDate
AND @StartDate <= getdate()
-- enumerate hosted licenses
LEFT OUTER JOIN
(
SELECT
LicenseRelationship.SourceManagedEntityRowId
,TargetLicenseType.ManagedEntityTypeSystemName AS TargetLicenseType
FROM
vRelationship AS LicenseRelationship INNER JOIN
vManagedEntity AS TargetLicense
ON LicenseRelationship.TargetManagedEntityRowId = TargetLicense.ManagedEntityRowId INNER JOIN
vManagedEntityType AS TargetLicenseType
ON TargetLicense.ManagedEntityTypeRowId = TargetLicenseType.ManagedEntityTypeRowId INNER JOIN
vManagedEntityManagementGroup AS ComputerManagementGroup
ON ComputerManagementGroup.ManagedEntityRowId = TargetLicense.ManagedEntityRowId
AND ComputerManagementGroup.FromDateTime < @EndDate
AND (ComputerManagementGroup.ToDateTime IS NULL OR ComputerManagementGroup.ToDateTime <= @StartDate)
AND @StartDate <= @EndDate
WHERE LicenseRelationship.RelationshipTypeRowId =
(
SELECT RelationshipTypeRowId
from vRelationshipType
where vRelationshipType.RelationshipTypeSystemName = 'Microsoft.SystemCenter.WindowsComputerHostsSystemCenterLicense'
)
) AS Licenses
ON SourceManagedEntityRowId = #ComputerList.ManagedEntityRowId
)AS SourceTable
GROUP BY ComputerType,ComputerName,IsVirtual,IsCluster
SET @Error = @@ERROR
QuitError:
DROP TABLE #ComputerList
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[ODR_Report_Library_ManagementGroupReportWorkloadClassesDataGet] TO OpsMgrReader
GO
Home