/******************************************************************
Impacted Report: Chargeback Report
Functionality: Get the charge of all VMs selected by user during the specified time range.
Including the following charge:
(1) Memory
(2) CPU
(3) Storage
(5) VM
Explanation of Parameters:
(1) @MemoryUnitCost - Cost Per GB of Memory Per Hour
(2) @CPUUnitCost - Cost of Per CPU Per Hour
(3) @StorageTypesUnitCost - Cost of Per GB of Storage Per Hour
(5) @BaseUnitCostPerVM - Base Cost Per VM Per Hour
Explanation of all costs:
(1) Memory cost: Memory used in GB hours * Cost per GB hour of memory
(2) CPU cost: Nos. of CPU hours * Cost per CPU Hour
(3) Storage cost: Nos of GB hours of storage used* Cost per GB hour of storage
(5) Base cost: Base cost per VM per hour * Nos of hours the VM is in operation
(from creation time of the VM to the end of the report time;
or from beginning time of report to end time of report
based on whether the VM was in existence longer than the report duration).
(6) Total cost = Sum (all the above individual costs)
******************************************************************/
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_VirtualMachineManager_Report_ChargebackReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_VirtualMachineManager_Report_ChargebackReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_VirtualMachineManager_Report_ChargebackReportDataGet]
@UTCStartDate1 DATETIME,
@UTCEndDate1 DATETIME,
@ObjectList XML,
@VMTypeID INT,
@MemoryUnitCost FLOAT,
@CPUUnitCost FLOAT,
@StorageTypesUnitCost NVARCHAR(MAX),
@BaseUnitCostPerVM FLOAT,
@HostNamePropertyGuid UNIQUEIDENTIFIER
AS
BEGIN
SET NOCOUNT ON
Declare @StorageTypeAndUnitCost NVARCHAR(MAX)
Declare @TempStorageTypeAndUnitCost NVARCHAR(MAX)
DECLARE @FoundIndex INT
DECLARE @UnitCostString NVARCHAR(MAX)
SET @StorageTypeAndUnitCost = ''
SET @TempStorageTypeAndUnitCost = ''
SET @UnitCostString = ''
-----------------Begin : The following scripts is used to get unit cost of each storage classification
-------------------------from StorageTypeUnitCost which is input by user via OM textbox control---------------------
CREATE TABLE #TempStorageTypesUnitCost
(
StorageType NVARCHAR(MAX),
UnitCost FLOAT
)
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT Data
FROM
(
-- @StorageTypesUnitCost stores one string like 'Gold: 3 Silver: 2 Bronze: 1 '
-- Storage types are seperated by 4 space chars.
-- it will be some issue when storage type name also contain 4 spaces.
-- TODO: find one better way to resolve 4 spaces issue.
--This query will return the rows like below .
--(1) Gold: 3
--(2) Silver: 2
--(3) Bronze: 1
SELECT * FROM Microsoft_SystemCenter_VirtualMachineManager_Function_StringSplit(@StorageTypesUnitCost, ' ')
) AS StorageTpyeUnitCost
OPEN @MyCursor
FETCH NEXT FROM @MyCursor INTO @StorageTypeAndUnitCost
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- @StorageTypeAndUnitCost here has value like 'Gold:3'
SET @TempStorageTypeAndUnitCost = @StorageTypeAndUnitCost
SET @FoundIndex = CHARINDEX(':',@TempStorageTypeAndUnitCost)
SET @UnitCostString = SUBSTRING(@TempStorageTypeAndUnitCost, @FoundIndex + 1, LEN(@TempStorageTypeAndUnitCost) - @FoundIndex)
-- only when @UnitCostString is numeric, can this storage type and its cost be insert into temp table.
IF ISNUMERIC(@UnitCostString) = 1
BEGIN
INSERT INTO #TempStorageTypesUnitCost (StorageType, UnitCost)
SELECT
StorageType = RTRIM(LTRIM(SUBSTRING(@TempStorageTypeAndUnitCost, 1, @FoundIndex - 1))),
UnitCost = CONVERT(FLOAT, @UnitCostString, 0)
END
-- Get the next row
FETCH NEXT FROM @MyCursor INTO @StorageTypeAndUnitCost
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
-----------------End ---------------------
CREATE TABLE #TempReportObjectList
(
ManagedEntityRowid INT
)
SELECT
Chargeback.VMName ,
Chargeback.VMRowId,
HostGroupAndCloud.HostGroupName,
HostGroupAndCloud.CloudName,
HostGroupAndCloud.HostName,
Chargeback.MemoryAllocatedInGB,
Chargeback.CPUCount,
Chargeback.ClassificationCount,
Chargeback.Classification,
-- Refer to Chaitanya's words for different storage classification
-- just put N/A for storage allocated and in red.
-- we can show storage allocated but its not important in this report when its cost cannot be calculated
CASE
WHEN Chargeback.ClassificationCount IS NULL THEN Chargeback.StorageAllocatedInGB
-- Only Single classification VMs are supported
WHEN Chargeback.ClassificationCount IS NOT NULL AND Chargeback.ClassificationCount < 2 THEN Chargeback.StorageAllocatedInGB
ELSE NULL END
AS StorageAllocatedInGB,
Chargeback.BaseCost,
CASE
-- Dont add storage cost if there is no storage if there is no classifications
WHEN Chargeback.ClassificationCount IS NULL
THEN (Chargeback.MemoryCost + Chargeback.CPUCost + Chargeback.BaseCost)
WHEN Chargeback.ClassificationCount IS NOT NULL AND Chargeback.ClassificationCount < 2
THEN (Chargeback.MemoryCost + Chargeback.CPUCost + Chargeback.StorageCost + Chargeback.BaseCost)
ELSE (Chargeback.MemoryCost + Chargeback.CPUCost + Chargeback.BaseCost) END AS TotalCost
FROM
(
SELECT
vME.ManagedEntityRowid AS VMRowId,
vME.DisplayName AS VMName,
perfTable.MemoryAllocatedInGB AS MemoryAllocatedInGB,
(CASE WHEN perfTable.MemoryAllocatedInGB * @MemoryUnitCost IS NOT NULL
THEN perfTable.MemoryAllocatedInGB * @MemoryUnitCost
ELSE 0 END) AS MemoryCost ,
perfTable.CPUCount AS CPUCount,
(CASE WHEN perfTable.CPUCount* @CPUUnitCost IS NOT NULL
THEN perfTable.CPUCount* @CPUUnitCost
ELSE 0 END) AS CPUCost,
(CASE WHEN perfTable.StorageAllocatedInGB IS NOT NULL
THEN perfTable.StorageAllocatedInGB
ELSE 0 END) AS StorageAllocatedInGB, -- in GB Hours
(CASE WHEN perfTable.StorageAllocatedInGB * VMVHDTable.StorageCostPerGBHour IS NOT NULL
THEN perfTable.StorageAllocatedInGB * VMVHDTable.StorageCostPerGBHour
ELSE 0 END) AS StorageCost,
(CASE WHEN VMDeployedLife.VMDeployedLifeInHour * @BaseUnitCostPerVM IS NOT NULL
THEN VMDeployedLife.VMDeployedLifeInHour * @BaseUnitCostPerVM
ELSE 0 END) AS BaseCost,
VMVHDTable.Classification AS Classification,
VMVHDTable.StorageCostPerGBHour AS StorageUnitCost,
VMVHDTable.ClassificationCount AS ClassificationCount
FROM
vManagedEntity AS vME
INNER JOIN #TempReportObjectList
ON #TempReportObjectList.ManagedEntityRowid = vME.ManagedEntityRowId
-- to only show Objects with data in the report
INNER JOIN
(
SELECT
VMPositiveDeployedLife.ManagedEntityRowid AS ManagedEntityRowid,
SUM(VMPositiveDeployedLife.DeployedLife/60.0) AS VMDeployedLifeInHour
FROM
(
SELECT
VMTempDeployedLife.ManagedEntityRowid AS ManagedEntityRowid,
VMTempDeployedLife.DeployedLife
FROM
(
-- the scripts below is used to calculate how long one VM is deployed on host.
SELECT
#TempReportObjectList.ManagedEntityRowid AS ManagedEntityRowid,
--If ToDateTime is NULL, it means the VM is deployed on one host now. so we should use GETDATE to get current time as ToDateTime
--If ToDateTime is not NULL, it means the VM was deployed on host at some time before.
--If FromDateTime is earlier than report start time(@UTCStartDate1), then we should use report star time (@UTCStartDate1) to calculate VM Cost hours.
--As we only show VM's costs during the report time period.
-- pick the intersection of report run interval and data existence interval
(CASE
WHEN ToDateTime IS NULL THEN
CASE WHEN FromDateTime < @UTCStartDate1 THEN
CASE WHEN @UTCEndDate1 < GETUTCDATE() THEN
DATEDIFF(MINUTE, @UTCStartDate1, @UTCEndDate1)
ELSE
DATEDIFF(MINUTE, @UTCStartDate1 , GETUTCDATE())
END
ELSE
CASE WHEN @UTCEndDate1 < GETUTCDATE() THEN
DATEDIFF(MINUTE, FromDateTime, @UTCEndDate1)
ELSE
DATEDIFF(MINUTE, FromDateTime, GETUTCDATE())
END
END
ELSE
CASE WHEN FromDateTime < @UTCStartDate1 THEN
CASE WHEN ToDateTime < @UTCEndDate1 THEN
DATEDIFF(MINUTE, @UTCStartDate1, ToDateTime)
ELSE
DATEDIFF(MINUTE, @UTCStartDate1, @UTCEndDate1)
END
ELSE
CASE WHEN ToDateTime < @UTCEndDate1 THEN
DATEDIFF(MINUTE, FromDateTime, ToDateTime)
ELSE
DATEDIFF(MINUTE, FromDateTime, @UTCEndDate1)
END
END
END) AS DeployedLife
FROM vRelationshipProperty
INNER JOIN vRelationship
ON vRelationship.RelationshipRowId = vRelationshipProperty.RelationshipRowId
INNER JOIN vRelationshipType
ON vRelationshipType.RelationshipTypeRowId = vRelationship.RelationshipTypeRowId
INNER JOIN #TempReportObjectList
ON #TempReportObjectList.ManagedEntityRowid = vRelationship.TargetManagedEntityRowId
WHERE vRelationshipType.RelationshipTypeSystemName = 'Microsoft.SystemCenter.VirtualMachineManager.VMHostContainsVirtualMachine'
)AS VMTempDeployedLife
-- Negative deployed life means, the lifespan of VM did not overlap Report run interval time
WHERE DeployedLife > 0
) AS VMPositiveDeployedLife
GROUP BY VMPositiveDeployedLife.ManagedEntityRowid
) AS VMDeployedLife
ON VMDeployedLife.ManagedEntityRowid = #TempReportObjectList.ManagedEntityRowid
LEFT JOIN
(
SELECT
ManagedEntityRowId,
MIN(DataPointDateTime) AS VMLifeStartDateTime,
SUM(CASE WHEN RName = N'Microsoft.SystemCenter.VirtualMachineManager.VirtualMachine.TotalRAM' THEN Entry.AVG1/1024 END) AS MemoryAllocatedInGB,
SUM(CASE WHEN RName = N'Microsoft.SystemCenter.VirtualMachineManager.VirtualMachine.CPUCount' THEN Entry.AVG1 END)AS CPUCount,
SUM(CASE WHEN RName = N'Microsoft.SystemCenter.VirtualMachineManager.VirtualMachine.TotalSizeOfVirtualDisks' THEN Entry.AVG1/(1024*1024*1024) END) AS StorageAllocatedInGB
FROM
(
SELECT
Perf.vPerfHourly.DateTime AS DataPointDateTime,
vRule.RuleSystemName AS RName,
AVG(Perf.vPerfHourly.AverageValue) AS AVG1,
vManagedEntity.ManagedEntityRowId
FROM
Perf.vPerfHourly
INNER JOIN
vPerformanceRuleInstance
ON
vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfHourly.PerformanceRuleInstanceRowId
INNER JOIN
vManagedEntity
ON
Perf.vPerfHourly.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN
vManagedEntityType
ON
vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId
INNER JOIN
vRule
ON
vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
INNER JOIN
(
SELECT DISTINCT
ManagedEntityRowId,
MAX(CASE WHEN vMEPS.PropertyGuid = @HostNamePropertyGuid THEN vMEPS.PropertyValue END) AS HostName
FROM
vManagedEntityPropertySet AS vMEPS
WHERE
(
PropertyGuid = @HostNamePropertyGuid
)
--To calculate cost of offline VMs during their deployed life (these rules dont run on offlineVM)
GROUP BY ManagedEntityRowId
)AS VMIdHostNameTable
ON vManagedEntity.ManagedEntityRowId = VMIdHostNameTable.ManagedEntityRowId
WHERE
(Perf.vPerfHourly.DateTime > @UTCStartDate1)
AND (Perf.vPerfHourly.DateTime <= @UTCEndDate1)
AND
(
vRule.RuleSystemName
IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.VirtualMachine.TotalSizeOfVirtualDisks',
N'Microsoft.SystemCenter.VirtualMachineManager.VirtualMachine.CPUCount',
N'Microsoft.SystemCenter.VirtualMachineManager.VirtualMachine.TotalRAM'
)
)
AND vManagedEntity.ManagedEntityTypeRowId = @VMTypeID
GROUP BY Perf.vPerfHourly.DateTime, vPerformanceRuleInstance.InstanceName, vManagedEntity.ManagedEntityRowId, vRule.RuleSystemName
) AS Entry
GROUP BY ManagedEntityRowId
) AS perfTable
ON vME.ManagedEntityRowId = perfTable.ManagedEntityRowId
LEFT JOIN
(
-- the scripts below is used to get the storage Classification and Classification count attached to VM's VHDs
-- if ClassificationCount > 1,
-- then chargeback report will show the error message like below.
-- "VMM could not calculate storage-related chargeback for some virtual machines because the disks that are attached to them have different storage classifications."
SELECT VMRowid,
ClassificationCount,
Classification,
StorageDurationHours,
UnitCost AS StorageCostPerGBHour
FROM
(
SELECT
VMRowid,
COUNT(DISTINCT Classification) AS ClassificationCount,
MAX(Classification) AS Classification,
SUM(StorageDurationHours) AS StorageDurationHours
FROM
(
SELECT * FROM
(
SELECT
VMVHDS.VMRowid,
VMVHDS.VHDRowid,
vManagedEntityPropertySet.PropertyValue AS Classification,
-- ToDateTime is NULL here so pick the earlier end date and the later start date
CASE WHEN @UTCStartDate1 < FromDateTime THEN
CASE WHEN @UTCEndDate1 < GETUTCDATE() THEN
DATEDIFF(HOUR, FromDateTime , @UTCEndDate1)
ELSE
DATEDIFF(HOUR, FromDateTime , GETUTCDATE())
END
ELSE
CASE WHEN @UTCEndDate1 < GETUTCDATE() THEN
DATEDIFF(HOUR, @UTCStartDate1 , @UTCEndDate1)
ELSE
DATEDIFF(HOUR, @UTCStartDate1 , GETUTCDATE())
END
END AS StorageDurationHours
FROM vManagedEntityPropertySet
INNER JOIN
(
SELECT
SourceManagedEntityRowId AS VMRowid,
TargetManagedEntityRowId AS VHDRowid
FROM vRelationship
INNER JOIN vRelationshipType
ON vRelationshipType.RelationshipTypeRowId = vRelationship.RelationshipTypeRowId
INNER JOIN #TempReportObjectList
ON #TempReportObjectList.ManagedEntityRowid = vRelationship.SourceManagedEntityRowId
INNER JOIN vManagedEntity
ON vManagedEntity.ManagedEntityRowId = #TempReportObjectList.ManagedEntityRowid
INNER JOIN vManagedEntityType
ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId
WHERE vRelationshipType.RelationshipTypeSystemName = N'Microsoft.SystemCenter.VirtualMachineManager.VirtualMachineContainsVirtualDiskDrive'
AND vManagedEntityType.ManagedEntityTypeRowId = @VMTypeId
) AS VMVHDS
ON VMVHDS.VHDRowid = vManagedEntityPropertySet.ManagedEntityRowId
WHERE vManagedEntityPropertySet.PropertyGuid IN
(
SELECT
vMETP.PropertyGuid
FROM vManagedEntityTypeProperty AS vMETP
INNER JOIN
(
SELECT vMET.ManagedEntityTypeRowId
FROM vManagedEntityType AS vMET
INNER JOIN vManagementPack AS vMP ON vMP.ManagementPackRowId = vMET.ManagementPackRowId
WHERE
(vMET.ManagedEntityTypeSystemName = N'Microsoft.SystemCenter.VirtualMachineManager.VirtualDiskDrive')
AND (vMP.ManagementPackSystemName = N'Microsoft.SystemCenter.VirtualMachineManager.Library')
AND (vMP.PublicKeyToken = '31bf3856ad364e35')
)
AS VMTypeID
ON vMETP.ManagedEntityTypeRowId = VMTypeID.ManagedEntityTypeRowId
WHERE (vMETP.PropertySystemName = N'Classification')
)
-- Cannot determine the classification of Library VMs when they were deployed on hosts
AND ToDateTime IS NULL
) AS VMVHD
WHERE VMVHD.StorageDurationHours > 0
) AS VMVHDPositive
GROUP BY VMRowid
)AS VMVHDClassification
Left JOIN #TempStorageTypesUnitCost
ON #TempStorageTypesUnitCost.StorageType = VMVHDClassification.Classification
) AS VMVHDTable
ON VMVHDTable.VMRowid = vME.ManagedEntityRowId
) AS Chargeback
LEFT JOIN
(
--Get HostGroup Name and Cloud Name for all VMs according the following relationships
--(1) Microsoft.SystemCenter.VirtualMachineManager.VMHostContainsVirtualMachine
--(5) Microsoft.SystemCenter.VirtualMachineManager.PrivateCloudContainsVirtualMachine
SELECT
HostGroupContainsVMHOst.VMName,
HostGroupContainsVMHOst.VMRowId,
HostGroupContainsVMHOst.HostGroupName,
HostGroupContainsVMHOst.HostGroupRowId,
HostGroupContainsVMHOst.HostName,
HostGroupContainsVMHOst.HostRowId,
PrivateCloudContainsVMHost.CloudName,
PrivateCloudContainsVMHost.CloudRowId
FROM
(
SELECT
vME.DisplayName AS HostGroupName,
VMHostGroup.HostGroupRowId AS HostGroupRowId,
NULL AS CloudRowId,
NULL AS CloudName,
VMHostGroup.HostName AS HostName,
VMHostGroup.HostRowId AS HostRowId,
VMHostGroup.VMName AS VMName,
VMHostGroup.VMRowId AS VMRowId
FROM
(
SELECT
VMHostsRowId.VMName AS VMName,
VMHostsRowId.VMRowId AS VMRowId,
RS.RelationshipTypeRowId,
MAX(RS.SourceManagedEntityRowId) AS HostGroupRowId,
RS.TargetManagedEntityRowId AS HostRowId,
HostEntity.DisplayName AS HostName,
RST.RelationshipTypeSystemName
FROM
vRelationship AS RS
INNER JOIN
(
SELECT
MAX(SourceManagedEntityRowId) AS VMHostRowId,
TargetManagedEntityRowId AS VMRowId,
vME.DisplayName AS VMName
FROM vRelationship
INNER JOIN #TempReportObjectList
ON #TempReportObjectList.ManagedEntityRowid = vRelationship.TargetManagedEntityRowId
INNER JOIN vRelationshipType
ON vRelationshipType.RelationshipTypeRowId = vRelationship.RelationshipTypeRowId
INNER JOIN vManagedEntity AS vME
ON vME.ManagedEntityRowId = vRelationship.TargetManagedEntityRowId
INNER JOIN
(
SELECT
ManagedEntityRowId,
MAX(CASE WHEN vMEPS.PropertyGuid = @HostNamePropertyGuid THEN vMEPS.PropertyValue END) AS HostName
FROM
vManagedEntityPropertySet AS vMEPS
WHERE
(
PropertyGuid = @HostNamePropertyGuid
)
AND (ToDateTime IS NULL)
GROUP BY ManagedEntityRowId
)AS HosTable
ON vME.ManagedEntityRowId = HosTable.ManagedEntityRowId
WHERE vRelationshipType.RelationshipTypeSystemName = 'Microsoft.SystemCenter.VirtualMachineManager.VMHostContainsVirtualMachine'
GROUP BY TargetManagedEntityRowId, vME.DisplayName
)AS VMHostsRowId
ON VMHostsRowId.VMHostRowId = RS.TargetManagedEntityRowId
INNER JOIN vRelationshipType AS RST
ON RST.RelationshipTypeRowId = RS.RelationshipTypeRowId
INNER JOIN vManagedEntity AS HostEntity
ON HostEntity.ManagedEntityRowId = VMHostsRowId.VMHostRowId
WHERE RST.RelationshipTypeSystemName = 'Microsoft.SystemCenter.VirtualMachineManager.HostGroupContainsVMHost'
GROUP BY VMHostsRowId.VMName, VMHostsRowId.VMRowId,RS.RelationshipTypeRowId, RS.TargetManagedEntityRowId,RST.RelationshipTypeSystemName, HostEntity.DisplayName
) AS VMHostGroup
INNER JOIN vManagedEntity AS vME
ON vME.ManagedEntityRowId = VMHostGroup.HostGroupRowId
UNION
SELECT
vME.DisplayName AS HostGroupName,
TempTable.HostGroupRowId AS HostGroupRowId,
TempTable.CloudName AS CloudName,
TempTable.CloudRowId AS CloudRowId,
TempTable.HostName AS HostName,
TempTable.HostRowId AS HostRowId,
TempTable.VMName AS VMName,
TempTable.VMRowId AS VMRowId
FROM
(
SELECT
HostClusters.VMName,
HostClusters.VMRowId,
MAX(vRS.SourceManagedEntityRowId) AS HostGroupRowId,
HostClusters.HostRowId AS HostRowId,
HostEntity.DisplayName AS HostName,
NULL AS CloudRowId,
NULL AS CloudName
FROM
(
SELECT
HostGroupContainsCluster.HostClusterRowId AS HostClusterRowId,
HostGroupContainsCluster.HostRowId AS HostRowId,
HostGroupContainsCluster.RelationshipTypeRowId AS RelationshipTypeRowId,
HostGroupContainsCluster.RelationshipTypeSystemName AS RelationshipTypeSystemName,
HostGroupContainsCluster.VMName AS VMName,
HostGroupContainsCluster.VMRowId AS VMRowId,
vME.DisplayName AS HostClusterName
FROM
(
SELECT
VMHostsRowId.VMName AS VMName,
VMHostsRowId.VMRowId AS VMRowId,
RS.RelationshipTypeRowId,
RS.SourceManagedEntityRowId AS HostClusterRowId,
MAX(RS.TargetManagedEntityRowId) AS HostRowId
,RST.RelationshipTypeSystemName AS RelationshipTypeSystemName
FROM
vRelationship AS RS
INNER JOIN
(
SELECT
DISTINCT
SourceManagedEntityRowId AS VMHostRowId,
TargetManagedEntityRowId AS VMRowId,
vME.DisplayName AS VMName,
vRelationship.DWCreatedDateTime AS DWCreatedDateTime
FROM vRelationship
INNER JOIN #TempReportObjectList
ON #TempReportObjectList.ManagedEntityRowid = vRelationship.TargetManagedEntityRowId
INNER JOIN vRelationshipType
ON vRelationshipType.RelationshipTypeRowId = vRelationship.RelationshipTypeRowId
INNER JOIN vManagedEntity AS vME
ON vME.ManagedEntityRowId = vRelationship.TargetManagedEntityRowId
INNER JOIN
(
SELECT
ManagedEntityRowId,
MAX(CASE WHEN vMEPS.PropertyGuid = @HostNamePropertyGuid THEN vMEPS.PropertyValue END) AS HostName
FROM
vManagedEntityPropertySet AS vMEPS
WHERE
(
PropertyGuid = @HostNamePropertyGuid
)
AND (ToDateTime IS NULL)
GROUP BY ManagedEntityRowId
)AS HosTable
ON vME.ManagedEntityRowId = HosTable.ManagedEntityRowId
WHERE vRelationshipType.RelationshipTypeSystemName = 'Microsoft.SystemCenter.VirtualMachineManager.VMHostContainsVirtualMachine'
)AS VMHostsRowId
ON VMHostsRowId.VMHostRowId = RS.TargetManagedEntityRowId
INNER JOIN vRelationshipType AS RST
ON RST.RelationshipTypeRowId = RS.RelationshipTypeRowId
WHERE RST.RelationshipTypeSystemName = 'Microsoft.SystemCenter.VirtualMachineManager.HostClusterContainsVMHost'
GROUP BY VMHostsRowId.VMName, VMHostsRowId.VMRowId, RS.RelationshipTypeRowId,RS.SourceManagedEntityRowId, RST.RelationshipTypeSystemName
) AS HostGroupContainsCluster
INNER JOIN vManagedEntity AS vME
ON vME.ManagedEntityRowId = HostGroupContainsCluster.HostClusterRowId
) AS HostClusters
INNER JOIN vRelationship vRS
ON vRS.TargetManagedEntityRowId = HostClusters.HostClusterRowId
INNER JOIN vRelationshipType vRST
ON vRST.RelationshipTypeRowId = vRS.RelationshipTypeRowId
INNER JOIN vManagedEntity HostEntity
ON HostEntity.ManagedEntityRowId = HostClusters.HostRowId
WHERE vRST.RelationshipTypeSystemName = 'Microsoft.SystemCenter.VirtualMachineManager.HostGroupContainsHostCluster'
GROUP BY HostClusters.VMName, HostClusters.VMRowId, HostClusters.HostRowId, HostEntity.DisplayName
) AS TempTable
INNER JOIN vManagedEntity vME
ON vME.ManagedEntityRowId = TempTable.HostGroupRowId
) AS HostGroupContainsVMHOst
LEFT JOIN
(
SELECT
CloudHostVM.VMName AS VMName,
CloudHostVM.VMRowId AS VMRowId,
NULL AS HostGroupName,
NULL AS HostGroupRowId,
CloudHostVM.CloudRowId AS CloudRowId,
vME.DisplayName AS CloudName
FROM
(
SELECT
DISTINCT
SourceManagedEntityRowId AS CloudRowId,
TargetManagedEntityRowId AS VMRowId,
vME.DisplayName AS VMName
FROM vRelationship
INNER JOIN #TempReportObjectList
ON #TempReportObjectList.ManagedEntityRowid = vRelationship.TargetManagedEntityRowId
INNER JOIN vRelationshipType
ON vRelationshipType.RelationshipTypeRowId = vRelationship.RelationshipTypeRowId
INNER JOIN vManagedEntity AS vME
ON vME.ManagedEntityRowId = vRelationship.TargetManagedEntityRowId
WHERE vRelationshipType.RelationshipTypeSystemName = 'Microsoft.SystemCenter.VirtualMachineManager.PrivateCloudContainsVirtualMachine'
)AS CloudHostVM
INNER JOIN vManagedEntity vME
ON vME.ManagedEntityRowId = CloudHostVM.CloudRowId
) AS PrivateCloudContainsVMHost
ON PrivateCloudContainsVMHost.VMRowId = HostGroupContainsVMHOst.VMRowId
-- We should include VMs which is stored library in this report.
-- (Refer to Chaitanya's words "you should be able to pick VMs that are currently offline also; so changing this filter type sounds good. ")
-- When one VMs is stored in Library, it costs zero, but when it is migrated into host, then it will costs
-- We can change one VM's migration between Library and Host.
UNION
SELECT
LibraryContainsVM.VMName,
LibraryContainsVM.VMRowId,
NULL AS HostGroupName,
NULL AS HostGroupRowId,
NULL AS HostName,
NULL AS HostRowId,
NULL AS CloudName,
NULL AS CloudRowId
FROM
(
SELECT
DISTINCT
TargetManagedEntityRowId AS VMRowId,
vME.DisplayName AS VMName
FROM vRelationship
INNER JOIN #TempReportObjectList
ON #TempReportObjectList.ManagedEntityRowid = vRelationship.TargetManagedEntityRowId
INNER JOIN vRelationshipType
ON vRelationshipType.RelationshipTypeRowId = vRelationship.RelationshipTypeRowId
INNER JOIN vManagedEntity AS vME
ON vME.ManagedEntityRowId = vRelationship.TargetManagedEntityRowId
WHERE vRelationshipType.RelationshipTypeSystemName = 'Microsoft.SystemCenter.VirtualMachineManager.Discovery.LibraryContainsOfflineVM'
)AS LibraryContainsVM
)AS HostGroupAndCloud
ON HostGroupAndCloud.VMRowId = Chargeback.VMRowId
ORDER BY Chargeback.ClassificationCount DESC
DROP TABLE #TempReportObjectList
DROP TABLE #TempStorageTypesUnitCost
SET NOCOUNT OFF
End
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_VirtualMachineManager_Report_ChargebackReportDataGet] TO OpsMgrReader
GO
Home