IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_VirtualMachineManager_Report_HostUtilizationReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_VirtualMachineManager_Report_HostUtilizationReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_VirtualMachineManager_Report_HostUtilizationReportDataGet]
@UTCStartDate1 datetime,
@UTCEndDate1 datetime,
@ObjectList xml,
@HostTypeId NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #TempReportObjectList
(
ManagedEntityRowid int
)
INSERT INTO @RecentHosts
(
ManagedEntityRowId,
ManagedEntityTypeRowId,
vME.DisplayName
)
(
SELECT
vME.[ManagedEntityRowId]
,vME.[ManagedEntityTypeRowId]
,vME.[DisplayName]
FROM vManagedEntity vME,
(
SELECT
vManagedEntity.[DisplayName],
[ManagedEntityTypeRowId],
MAX([DWCreatedDateTime]) as maxDateTime
FROM vManagedEntity
Inner join #TempReportObjectList
on #TempReportObjectList.ManagedEntityRowid = vManagedEntity.ManagedEntityRowId
GROUP BY vManagedEntity.[DisplayName], [ManagedEntityTypeRowId]
) maxDateResults
WHERE vME.[DisplayName] = maxDateResults.[DisplayName]
AND vME.[ManagedEntityTypeRowId] = maxDateResults.[ManagedEntityTypeRowId]
AND vME.[DWCreatedDateTime] = maxDateResults.maxDateTime
AND
(
vME.ManagedEntityTypeRowId IN
(
SELECT number
FROM Microsoft_SystemCenter_VirtualMachineManager_Function_ObjectRowIDList_ToTable(@HostTypeId) AS Base
)
)
)
SELECT
Perf.DisplayName,
Perf.ManagedEntityRowId,
Perf.ManagedEntityTypeRowId,
-- Available Memory collected needs to be converted to Used Memory
Properties.RAM - Perf.MemAvg1 AS MemAvg1,
Perf.DiskFreeMemAvg1,
Perf.DiskReadBytesAvg1 + Perf.DiskWriteBytesAvg1 as DiskBytesAvg1,
Perf.NetAvg1,
Perf.CPUAvg1,
Perf.CPUMax1,
Properties.DisplayName,
Properties.RAM,
Properties.NoOfVMsDeployed,
Properties.HostGroup,
Properties.NoOFVMsRunning
FROM
(
SELECT
DisplayName,
ManagedEntityRowId,
ManagedEntityTypeRowId,
MAX(CASE WHEN RName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.MemoryAvailableMBytes',
N'Microsoft.SystemCenter.VirtualMachineManager.ESXHost.MemoryAvailableMBytes.V2',
N'Microsoft.SystemCenter.VirtualMachineManager.XenServerHost.MemoryAvailableMBytes.V2'
) THEN Entry.AVG1 END) AS MemAvg1,
MAX(CASE WHEN RName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.DiskPercentFreeSpace',
N'Microsoft.SystemCenter.VirtualMachineManager.ESXHost.DiskPercentFreeSpace',
N'Microsoft.SystemCenter.VirtualMachineManager.XenServerHost.DiskPercentFreeSpace'
) THEN Entry.AVG1 END) AS DiskFreeMemAvg1,
MAX(CASE WHEN RName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.DiskReadBytesTotalPerSec',
N'Microsoft.SystemCenter.VirtualMachineManager.ESXHost.DiskReadBytesTotalPerSec.V2'
) THEN Entry.AVG1 END) AS DiskReadBytesAvg1,
MAX(CASE WHEN RName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.DiskWriteBytesTotalPerSec',
N'Microsoft.SystemCenter.VirtualMachineManager.ESXHost.DiskWriteBytesTotalPerSec.V2'
) THEN Entry.AVG1 END) AS DiskWriteBytesAvg1,
MAX(CASE WHEN RName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.ESXHost.NetworkAvgBytesTotalPerSec.V2',
N'Microsoft.SystemCenter.VirtualMachineManager.XenServerHost.NetworkAvgBytesTotalPerSec.V2',
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.NetworkAvgReceivedBytesTotalPerSec',
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.NetworkAvgSentBytesPerSec'
) THEN Entry.AVG1 END) AS NetAvg1,
MAX(CASE WHEN RName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.PercentProcessorTime',
N'Microsoft.SystemCenter.VirtualMachineManager.ESXHost.PercentProcessorTime.V2',
N'Microsoft.SystemCenter.VirtualMachineManager.XenServerHost.PercentProcessorTime.V2'
) THEN Entry.AVG1 END) AS CPUAvg1,
MAX(CASE WHEN RName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.PercentProcessorTime',
N'Microsoft.SystemCenter.VirtualMachineManager.ESXHost.PercentProcessorTime.V2',
N'Microsoft.SystemCenter.VirtualMachineManager.XenServerHost.PercentProcessorTime.V2'
) THEN Entry.MAX1 END) AS CPUMax1
FROM
(
SELECT
vRule.RuleSystemName AS RName, RecentHosts.DisplayName,
AVG((CASE WHEN (Perf.vPerfHourly.DateTime >= @UTCStartDate1) AND (Perf.vPerfHourly.DateTime <= @UTCEndDate1) THEN Perf.vPerfHourly.AverageValue END)) AS AVG1,
MIN((CASE WHEN (Perf.vPerfHourly.DateTime >= @UTCStartDate1) AND (Perf.vPerfHourly.DateTime <= @UTCEndDate1) THEN Perf.vPerfHourly.MinValue END)) AS MIN1,
MAX((CASE WHEN (Perf.vPerfHourly.DateTime >= @UTCStartDate1) AND (Perf.vPerfHourly.DateTime <= @UTCEndDate1) THEN Perf.vPerfHourly.MaxValue END)) AS MAX1,
vPerformanceRuleInstance.InstanceName,
RecentHosts.ManagedEntityRowId,
RecentHosts.ManagedEntityTypeRowId
FROM Perf.vPerfHourly
INNER JOIN vPerformanceRuleInstance
ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfHourly.PerformanceRuleInstanceRowId
INNER JOIN @RecentHosts AS RecentHosts
ON Perf.vPerfHourly.ManagedEntityRowId = RecentHosts.ManagedEntityRowId
INNER JOIN vManagedEntityType
ON vManagedEntityType.ManagedEntityTypeRowId = RecentHosts.ManagedEntityTypeRowId
INNER JOIN vRule
ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
LEFT OUTER JOIN vManagedEntityTypeImage
ON RecentHosts.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId
WHERE (Perf.vPerfHourly.DateTime >= @UTCStartDate1)
AND (Perf.vPerfHourly.DateTime <= @UTCEndDate1)
AND
(
vRule.RuleSystemName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.DiskPercentFreeSpace',
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.DiskReadBytesTotalPerSec',
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.DiskWriteBytesTotalPerSec',
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.MemoryAvailableMBytes',
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.NetworkAvgSentBytesPerSec',
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.NetworkAvgReceivedBytesTotalPerSec',
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.PercentProcessorTime',
N'Microsoft.SystemCenter.VirtualMachineManager.XenServerHost.DiskPercentFreeSpace',
N'Microsoft.SystemCenter.VirtualMachineManager.XenServerHost.PercentProcessorTime.V2',
N'Microsoft.SystemCenter.VirtualMachineManager.XenServerHost.MemoryAvailableMBytes.V2',
N'Microsoft.SystemCenter.VirtualMachineManager.XenServerHost.NetworkAvgBytesTotalPerSec.V2'
)
)
GROUP BY
RecentHosts.DisplayName, vPerformanceRuleInstance.InstanceName, RecentHosts.ManagedEntityRowId,
vRule.RuleSystemName, RecentHosts.ManagedEntityTypeRowId
) As Entry
GROUP BY DisplayName, ManagedEntityRowId, ManagedEntityTypeRowId
) AS Perf
INNER JOIN
(
SELECT
RecentHosts_1.DisplayName,
RecentHosts_1.ManagedEntityRowId,
RecentHosts_1.ManagedEntityTypeRowId,
MAX(CASE WHEN vManagedEntityTypeProperty.PropertySystemName = 'TotalRam' THEN CONVERT(BIGINT,vManagedEntityPropertySet.PropertyValue)/(1024*1024) END) AS RAM,
MAX(CASE WHEN vManagedEntityTypeProperty.PropertySystemName = 'NoOfVMsDeployed' THEN vManagedEntityPropertySet.PropertyValue END) AS NoOfVMsDeployed,
MAX(CASE WHEN vManagedEntityTypeProperty.PropertySystemName = 'HostGroup' THEN vManagedEntityPropertySet.PropertyValue END) AS HostGroup,
MAX(CASE WHEN vManagedEntityTypeProperty.PropertySystemName = 'NoOfVMsRunning' THEN vManagedEntityPropertySet.PropertyValue END) AS NoOFVMsRunning
FROM vManagedEntityPropertySet
INNER JOIN @RecentHosts AS RecentHosts_1
ON vManagedEntityPropertySet.ManagedEntityRowId = RecentHosts_1.ManagedEntityRowId
INNER JOIN vManagedEntityTypeProperty
ON vManagedEntityPropertySet.PropertyGuid = vManagedEntityTypeProperty.PropertyGuid
WHERE
(
RecentHosts_1.ManagedEntityTypeRowId IN
(
SELECT
number
FROM Microsoft_SystemCenter_VirtualMachineManager_Function_ObjectRowIDList_ToTable(@HostTypeId) AS ManagedEntityDerivedTypeHierarchy_1
)
)
AND (vManagedEntityPropertySet.ToDateTime IS NULL)
GROUP BY RecentHosts_1.DisplayName, RecentHosts_1.ManagedEntityRowId, RecentHosts_1.ManagedEntityTypeRowId
)
AS Properties
ON Properties.ManagedEntityRowId = Perf.ManagedEntityRowId
DROP TABLE #TempReportObjectList
SET NOCOUNT OFF
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_VirtualMachineManager_Report_HostUtilizationReportDataGet] TO OpsMgrReader
GO
Home