<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.License" Accessibility="Public"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_LicenseReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_LicenseReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_LicenseReportDataGet]
@StartDate datetime,
@EndDate datetime,
@GroupList xml
AS
BEGIN
SET NOCOUNT ON
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
DECLARE @ManagedComputerTypeRowId int
SELECT @ManagedComputerTypeRowId = ManagedEntityTypeRowId
FROM vManagedEntityType
WHERE vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.ManagedComputer'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ManagementGroupList
SELECT GroupList.ManagementGroup.value('.', 'uniqueidentifier')
FROM @GroupList.nodes('/Data/Value') AS GroupList(ManagementGroup)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* Get all computers */
INSERT INTO #ComputerList
SELECT vManagedEntityType.ManagedEntityTypeSystemName,
vManagedEntityType.ManagedEntityTypeRowId,
vManagedEntityManagementGroup.ManagedEntityRowId
FROM ManagedEntityDerivedTypeHierarchy (@ManagedComputerTypeRowId, 0) AS DerivedTypes
INNER JOIN vTypedManagedEntity ON vTypedManagedEntity.ManagedEntityTypeRowId = DerivedTypes.ManagedEntityTypeRowId
INNER JOIN vManagedEntity ON vManagedEntity.ManagedEntityRowId = vTypedManagedEntity.ManagedEntityRowId
INNER JOIN vManagedEntityManagementGroup ON vManagedEntityManagementGroup.ManagedEntityRowId = vTypedManagedEntity.ManagedEntityRowId
INNER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = DerivedTypes.ManagedEntityTypeRowId
INNER JOIN vManagementGroup ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId
INNER JOIN #ManagementGroupList ON #ManagementGroupList.ManagementGroupGuid = vManagementGroup.ManagementGroupGuid
WHERE vManagedEntityManagementGroup.FromDateTime <= @EndDate AND @StartDate < ISNULL(vManagedEntityManagementGroup.ToDateTime, '99991231') AND
vTypedManagedEntity.FromDateTime <= @EndDate AND @StartDate < ISNULL(vTypedManagedEntity.ToDateTime, '99991231')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* Add all client licences */
INSERT INTO #ComputerLicenseList
SELECT DISTINCT ManagedEntityRowId, 1, 0 ,0
FROM #ComputerList WHERE ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.ManagedComputerClient'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* Add all enterprise and standard licences */
INSERT INTO #ComputerLicenseList
SELECT DISTINCT #ComputerList.ManagedEntityRowId,
CASE vManagedEntityType.ManagedEntityTypeSystemName
WHEN 'Microsoft.SystemCenter.License.Standard' THEN 2
WHEN 'Microsoft.SystemCenter.License.Enterprise' THEN 3
END,
0 ,0
FROM vTypedManagedEntity
INNER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vTypedManagedEntity.ManagedEntityTypeRowId
INNER JOIN vManagedEntity ON vManagedEntity.ManagedEntityRowId = vTypedManagedEntity.ManagedEntityRowId
INNER JOIN #ComputerList ON #ComputerList.ManagedEntityRowId = vManagedEntity.TopLevelHostManagedEntityRowId
WHERE #ComputerList.ManagedEntityRowId NOT IN (SELECT ManagedEntityRowId FROM #ComputerLicenseList) AND
vManagedEntityType.ManagedEntityTypeSystemName in
(
'Microsoft.SystemCenter.License.Enterprise',
'Microsoft.SystemCenter.License.Standard'
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* Add rest of computers as enterprise licences */
INSERT INTO #ComputerLicenseList
SELECT DISTINCT #ComputerList.ManagedEntityRowId, 3, 0 ,0
FROM #ComputerList
WHERE #ComputerList.ManagedEntityRowId NOT IN (SELECT ManagedEntityRowId FROM #ComputerLicenseList)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* ------------------------------------------------------ */
/* Mark virtual computers */
UPDATE #ComputerLicenseList
SET IsVirtual = 1
FROM vManagedEntityProperty
INNER JOIN #ComputerLicenseList AS ComputerLicenseList ON ComputerLicenseList.ManagedEntityRowId = vManagedEntityProperty.ManagedEntityRowId
CROSS APPLY vManagedEntityProperty.PropertyXml.nodes('/Root/Property') AS PropertyList(Property)
WHERE
vManagedEntityProperty.FromDateTime < @EndDate AND @EndDate <= ISNULL(vManagedEntityProperty.ToDateTime, '99991231') AND
PropertyList.Property.value('@Guid','uniqueidentifier')
IN
(
SELECT PropertyGuid
FROM vManagedEntityTypeProperty WHERE
vManagedEntityTypeProperty.PropertySystemName IN
(
'IsVirtualMachine',
'IsVirtualNode',
'Virtual_Server_Type'
)
)
AND PropertyList.Property.value('.','nvarchar(256)') = '1'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* Mark clusters */
UPDATE #ComputerLicenseList
SET IsCluster = 1
FROM #ComputerList AS ComputerList
INNER JOIN vTypedManagedEntity ON vTypedManagedEntity.ManagedEntityRowId = ComputerList.ManagedEntityRowId
INNER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vTypedManagedEntity.ManagedEntityTypeRowId
INNER JOIN #ComputerLicenseList AS ComputerLicenseList ON ComputerLicenseList.ManagedEntityRowId = ComputerList.ManagedEntityRowId
WHERE vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Cluster.VirtualServer'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* return results */
SELECT ManagedEntity.ManagedEntityDefaultName, ManagedEntity.ManagedEntityGuid,
MAX(LicenseType) As LicenseType, IsVirtual, IsCluster
FROM #ComputerLicenseList AS List
INNER JOIN ManagedEntity ON ManagedEntity.ManagedEntityRowId = List.ManagedEntityRowId
INNER JOIN ManagementGroup ON ManagementGroup.ManagementGroupRowId = ManagedEntity.ManagementGroupRowId
GROUP BY ManagedEntity.ManagedEntityDefaultName, ManagedEntity.ManagedEntityGuid, IsVirtual, IsCluster
SET @Error = @@ERROR
QuitError:
DROP TABLE #ComputerList
DROP TABLE #ComputerLicenseList
DROP TABLE #ManagementGroupList
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_LicenseReportDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_LicenseReportDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_LicenseReportDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_LicenseReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_LicenseReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_LicenseReportDataGet]
@StartDate datetime,
@EndDate datetime,
@GroupList xml
AS
BEGIN
SET NOCOUNT ON
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
DECLARE @ManagedComputerTypeRowId int
SELECT @ManagedComputerTypeRowId = ManagedEntityTypeRowId
FROM vManagedEntityType
WHERE vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.ManagedComputer'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ManagementGroupList
SELECT GroupList.ManagementGroup.value('.', 'uniqueidentifier')
FROM @GroupList.nodes('/Data/Value') AS GroupList(ManagementGroup)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* Get all computers */
INSERT INTO #ComputerList
SELECT vManagedEntityType.ManagedEntityTypeSystemName,
vManagedEntityType.ManagedEntityTypeRowId,
vManagedEntityManagementGroup.ManagedEntityRowId
FROM ManagedEntityDerivedTypeHierarchy (@ManagedComputerTypeRowId, 0) AS DerivedTypes
INNER JOIN vTypedManagedEntity ON vTypedManagedEntity.ManagedEntityTypeRowId = DerivedTypes.ManagedEntityTypeRowId
INNER JOIN vManagedEntity ON vManagedEntity.ManagedEntityRowId = vTypedManagedEntity.ManagedEntityRowId
INNER JOIN vManagedEntityManagementGroup ON vManagedEntityManagementGroup.ManagedEntityRowId = vTypedManagedEntity.ManagedEntityRowId
INNER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = DerivedTypes.ManagedEntityTypeRowId
INNER JOIN vManagementGroup ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId
INNER JOIN #ManagementGroupList ON #ManagementGroupList.ManagementGroupGuid = vManagementGroup.ManagementGroupGuid
WHERE vManagedEntityManagementGroup.FromDateTime <= @EndDate AND @StartDate < ISNULL(vManagedEntityManagementGroup.ToDateTime, '99991231') AND
vTypedManagedEntity.FromDateTime <= @EndDate AND @StartDate < ISNULL(vTypedManagedEntity.ToDateTime, '99991231')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* Add all client licences */
INSERT INTO #ComputerLicenseList
SELECT DISTINCT ManagedEntityRowId, 1, 0 ,0
FROM #ComputerList WHERE ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.ManagedComputerClient'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* Add all enterprise and standard licences */
INSERT INTO #ComputerLicenseList
SELECT DISTINCT #ComputerList.ManagedEntityRowId,
CASE vManagedEntityType.ManagedEntityTypeSystemName
WHEN 'Microsoft.SystemCenter.License.Standard' THEN 2
WHEN 'Microsoft.SystemCenter.License.Enterprise' THEN 3
END,
0 ,0
FROM vTypedManagedEntity
INNER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vTypedManagedEntity.ManagedEntityTypeRowId
INNER JOIN vManagedEntity ON vManagedEntity.ManagedEntityRowId = vTypedManagedEntity.ManagedEntityRowId
INNER JOIN #ComputerList ON #ComputerList.ManagedEntityRowId = vManagedEntity.TopLevelHostManagedEntityRowId
WHERE #ComputerList.ManagedEntityRowId NOT IN (SELECT ManagedEntityRowId FROM #ComputerLicenseList) AND
vManagedEntityType.ManagedEntityTypeSystemName in
(
'Microsoft.SystemCenter.License.Enterprise',
'Microsoft.SystemCenter.License.Standard'
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* Add rest of computers as enterprise licences */
INSERT INTO #ComputerLicenseList
SELECT DISTINCT #ComputerList.ManagedEntityRowId, 3, 0 ,0
FROM #ComputerList
WHERE #ComputerList.ManagedEntityRowId NOT IN (SELECT ManagedEntityRowId FROM #ComputerLicenseList)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* ------------------------------------------------------ */
/* Mark virtual computers */
UPDATE #ComputerLicenseList
SET IsVirtual = 1
FROM vManagedEntityProperty
INNER JOIN #ComputerLicenseList AS ComputerLicenseList ON ComputerLicenseList.ManagedEntityRowId = vManagedEntityProperty.ManagedEntityRowId
CROSS APPLY vManagedEntityProperty.PropertyXml.nodes('/Root/Property') AS PropertyList(Property)
WHERE
vManagedEntityProperty.FromDateTime < @EndDate AND @EndDate <= ISNULL(vManagedEntityProperty.ToDateTime, '99991231') AND
PropertyList.Property.value('@Guid','uniqueidentifier')
IN
(
SELECT PropertyGuid
FROM vManagedEntityTypeProperty WHERE
vManagedEntityTypeProperty.PropertySystemName IN
(
'IsVirtualMachine',
'IsVirtualNode',
'Virtual_Server_Type'
)
)
AND PropertyList.Property.value('.','nvarchar(256)') = '1'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* Mark clusters */
UPDATE #ComputerLicenseList
SET IsCluster = 1
FROM #ComputerList AS ComputerList
INNER JOIN vTypedManagedEntity ON vTypedManagedEntity.ManagedEntityRowId = ComputerList.ManagedEntityRowId
INNER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vTypedManagedEntity.ManagedEntityTypeRowId
INNER JOIN #ComputerLicenseList AS ComputerLicenseList ON ComputerLicenseList.ManagedEntityRowId = ComputerList.ManagedEntityRowId
WHERE vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Cluster.VirtualServer'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* return results */
SELECT ManagedEntity.ManagedEntityDefaultName, ManagedEntity.ManagedEntityGuid,
MAX(LicenseType) As LicenseType, IsVirtual, IsCluster
FROM #ComputerLicenseList AS List
INNER JOIN ManagedEntity ON ManagedEntity.ManagedEntityRowId = List.ManagedEntityRowId
INNER JOIN ManagementGroup ON ManagementGroup.ManagementGroupRowId = ManagedEntity.ManagementGroupRowId
GROUP BY ManagedEntity.ManagedEntityDefaultName, ManagedEntity.ManagedEntityGuid, IsVirtual, IsCluster
SET @Error = @@ERROR
QuitError:
DROP TABLE #ComputerList
DROP TABLE #ComputerLicenseList
DROP TABLE #ManagementGroupList
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_LicenseReportDataGet] TO OpsMgrReader
GO
Home
