IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_VirtualMachineManager_Report_HostUtilizationGrowthReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_VirtualMachineManager_Report_HostUtilizationGrowthReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_VirtualMachineManager_Report_HostUtilizationGrowthReportDataGet]
@UTCStartDate1 datetime,
@UTCEndDate1 datetime,
@UTCStartDate2 datetime,
@UTCEndDate2 datetime,
@HostTypeId NVARCHAR(100),
@ObjectList xml
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #TempReportObjectList
(
ManagedEntityRowid int
)
INSERT INTO @RecentHosts (ManagedEntityRowId, ManagedEntityTypeRowId, DisplayName)
(
SELECT
vME.[ManagedEntityRowId]
,vME.[ManagedEntityTypeRowId]
,vME.[DisplayName]
FROM vManagedEntity vME,
(
SELECT
[DisplayName],
[ManagedEntityTypeRowId],
MAX([DWCreatedDateTime]) as maxDateTime
FROM vManagedEntity
WHERE [ManagedEntityTypeRowId] IN
(
SELECT number
FROM Microsoft_SystemCenter_VirtualMachineManager_Function_ObjectRowIDList_ToTable(@HostTypeId) AS Base
)
AND
[ManagedEntityRowId] IN
(
select distinct ManagedEntityRowid from #TempReportObjectList
)
GROUP BY [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,
Properties.RAM - Perf.MemAvg2 AS MemAvg2,
CASE WHEN Perf.MemAvg1 = 0 THEN (Perf.MemAvg2-1) * 100/1
ELSE (Perf.MemAvg1 - Perf.MemAvg2 ) * 100/(Properties.RAM - Perf.MemAvg1) END AS MemAvgGrowth,
Perf.NetAvg1 ,
Perf.NetAvg2,
CASE WHEN Perf.NetAvg1 = 0 THEN (Perf.NetAvg2-1) * 100/1
ELSE (Perf.NetAvg2-Perf.NetAvg1) * 100/Perf.NetAvg1 END AS NetAvgGrowth,
Perf.DiskFreeMemAvg1,
Perf.DiskFreeMemAvg2,
CASE WHEN Perf.DiskFreeMemAvg1 = 0 THEN (DiskFreeMemAvg2-1) * 100/1
ELSE (Perf.DiskFreeMemAvg2-Perf.DiskFreeMemAvg1) * 100/Perf.DiskFreeMemAvg1 END AS DiskFreeMemAvgGrowth,
Perf.DiskReadBytesAvg1 + Perf.DiskWriteBytesAvg1 AS DiskBytesAvg1,
Perf.DiskReadBytesAvg2 + Perf.DiskWriteBytesAvg2 AS DiskBytesAvg2,
CASE WHEN Perf.DiskReadBytesAvg1 + Perf.DiskWriteBytesAvg1 = 0 THEN (Perf.DiskReadBytesAvg2 + Perf.DiskWriteBytesAvg2-1) * 100/1
ELSE ((Perf.DiskReadBytesAvg2 + Perf.DiskWriteBytesAvg2)- (Perf.DiskReadBytesAvg1 + Perf.DiskWriteBytesAvg1)) * 100/(Perf.DiskReadBytesAvg1 + Perf.DiskWriteBytesAvg1) END AS DiskBytesAvgGrowth,
Perf.CPUAvg1,
Perf.CPUAvg2,
CASE WHEN Perf.CPUAvg1 = 0 THEN (CPUAvg2-1) * 100/1
ELSE (Perf.CPUAvg2-Perf.CPUAvg1) * 100/Perf.CPUAvg1 END AS CPUAvgGrowth,
Perf.CPUMax1,
Perf.CPUMax2,
CASE WHEN Perf.CPUMax1 = 0 THEN (CPUMax2-1) * 100/1
ELSE (Perf.CPUMax2-Perf.CPUMax1) * 100/Perf.CPUMax1 END AS CPUMaxGrowth,
FROM
(
SELECT
DisplayName,
ManagedEntityRowId,
ManagedEntityTypeRowId,
----------------------------------------------------------------------------------
-- First interval Host utilization parameters
----------------------------------------------------------------------------------
MAX
(
CASE WHEN RName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.MemoryAvailableMBytes',
N'Microsoft.SystemCenter.VirtualMachineManager.XenServerHost.MemoryAvailableMBytes.V2',
N'Microsoft.SystemCenter.VirtualMachineManager.ESXHost.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.HyperVHost.NetworkAvgSentBytesPerSec',
N'Microsoft.SystemCenter.VirtualMachineManager.XenServerHost.NetworkAvgBytesTotalPerSec.V2',
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.NetworkAvgReceivedBytesTotalPerSec',
N'Microsoft.SystemCenter.VirtualMachineManager.ESXHost.NetworkAvgBytesTotalPerSec.V2'
)
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,
-- Second interval Host utilization parameters
---------------------------------------------
MAX
(
CASE WHEN RName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.MemoryAvailableMBytes',
N'Microsoft.SystemCenter.VirtualMachineManager.XenServerHost.MemoryAvailableMBytes.V2',
N'Microsoft.SystemCenter.VirtualMachineManager.ESXHost.MemoryAvailableMBytes.V2'
)
THEN Entry.AVG2 END
) AS MemAvg2,
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.AVG2 END
) AS DiskFreeMemAvg2,
MAX
(
CASE WHEN RName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.DiskReadBytesTotalPerSec',
N'Microsoft.SystemCenter.VirtualMachineManager.ESXHost.DiskReadBytesTotalPerSec.V2'
)
THEN Entry.AVG2 END
) AS DiskReadBytesAvg2,
MAX
(
CASE WHEN RName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.DiskWriteBytesTotalPerSec',
N'Microsoft.SystemCenter.VirtualMachineManager.ESXHost.DiskWriteBytesTotalPerSec.V2'
)
THEN Entry.AVG2 END
) AS DiskWriteBytesAvg2,
MAX
(
CASE WHEN RName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.NetworkAvgSentBytesPerSec',
N'Microsoft.SystemCenter.VirtualMachineManager.HyperVHost.NetworkAvgReceivedBytesTotalPerSec',
N'Microsoft.SystemCenter.VirtualMachineManager.ESXHost.NetworkAvgBytesTotalPerSec.V2',
N'Microsoft.SystemCenter.VirtualMachineManager.XenServerHost.NetworkAvgBytesTotalPerSec.V2'
)
THEN Entry.AVG2 END
) AS NetAvg2,
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.AVG2 END
) AS CPUAvg2,
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.MAX2 END
) AS CPUMax2
FROM
(
SELECT
vRule.RuleSystemName AS RName,
RecentHosts.DisplayName,
-- First interval AVG and MAX
-----------------------------------
AVG((CASE WHEN (Perf.vPerfHourly.DateTime >= @UTCStartDate1) AND (Perf.vPerfHourly.DateTime <= @UTCEndDate1)
THEN Perf.vPerfHourly.AverageValue END)) AS AVG1,
MAX((CASE WHEN (Perf.vPerfHourly.DateTime >= @UTCStartDate1) AND (Perf.vPerfHourly.DateTime <= @UTCEndDate1)
THEN Perf.vPerfHourly.MaxValue END)) AS MAX1,
-- Second interval AVG and MAX
-----------------------------------
AVG((CASE WHEN (Perf.vPerfHourly.DateTime >= @UTCStartDate2) AND (Perf.vPerfHourly.DateTime <= @UTCEndDate2)
THEN Perf.vPerfHourly.AverageValue END)) AS AVG2,
MAX((CASE WHEN (Perf.vPerfHourly.DateTime >= @UTCStartDate2) AND (Perf.vPerfHourly.DateTime <= @UTCEndDate2)
THEN Perf.vPerfHourly.MaxValue END)) AS MAX2,
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)
OR (Perf.vPerfHourly.DateTime >= @UTCStartDate2
AND Perf.vPerfHourly.DateTime <= @UTCEndDate2)
)
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.DisplayName = Perf.DisplayName
DROP TABLE #TempReportObjectList
SET NOCOUNT OFF
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_VirtualMachineManager_Report_HostUtilizationGrowthReportDataGet] TO OpsMgrReader
GO
Home