IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_VirtualMachineManager_Report_VMUtilizationReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_VirtualMachineManager_Report_VMUtilizationReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_VirtualMachineManager_Report_VMUtilizationReportDataGet]
@UTCStartDate1 datetime,
@UTCEndDate1 datetime,
@ObjectList xml,
@VMTypeID int,
@HostNamePropertyID uniqueidentifier,
@TotalHardDiskSizePropertyID uniqueidentifier,
@TotalMemoryPropertyID uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
---------------------------------------------------------------------
Create Table #TempReportObjectList
(
ManagedEntityRowid int
)
SELECT
vME.DisplayName AS Name,
ROUND(perfTable.CPUAvg1,2) as CPUAvg1,
ROUND(perfTable.CPUMax1,2) as CPUMax1,
ROUND(perfTable.DiskSpaceAvg1,2) as DiskSpaceAvg1,
ROUND(perfTable.RAMAvg1,2) as RAMAvg1,
HostDiskMemTable.Memory AS RAM,
HostDiskMemTable.HardDiskSize AS HDDSize,
HostDiskMemTable.HostName, vME.ManagedEntityRowId
FROM
vManagedEntity AS vME
INNER JOIN #TempReportObjectList
ON
#TempReportObjectList.ManagedEntityRowid = vME.ManagedEntityRowId
INNER JOIN
(
SELECT
ManagedEntityRowId,
MAX(CASE WHEN vMEPS.PropertyGuid = @HostNamePropertyID THEN vMEPS.PropertyValue END) AS HostName,
MAX(CASE WHEN vMEPS.PropertyGuid = @TotalHardDiskSizePropertyID THEN vMEPS.PropertyValue END) AS HardDiskSize,
MAX(CASE WHEN vMEPS.PropertyGuid = @TotalMemoryPropertyID THEN vMEPS.PropertyValue END) AS Memory
FROM
vManagedEntityPropertySet AS vMEPS
WHERE
(
PropertyGuid = @HostNamePropertyID OR
PropertyGuid = @TotalHardDiskSizePropertyID OR
PropertyGuid = @TotalMemoryPropertyID
)
AND (ToDateTime IS NULL)
GROUP BY ManagedEntityRowId
)AS HostDiskMemTable
ON
vME.ManagedEntityRowId = HostDiskMemTable.ManagedEntityRowId
LEFT OUTER JOIN
(
SELECT
ManagedEntityRowId,
MAX(CASE WHEN RName = N'Microsoft.SystemCenter.VirtualMachineManager.VirtualMachine.PercentCPU' THEN Entry.AVG1 END) AS CPUAvg1,
MAX(CASE WHEN RName = N'Microsoft.SystemCenter.VirtualMachineManager.VirtualMachine.PercentCPU' THEN Entry.MAX1 END) AS CPUMax1,
MAX(CASE WHEN RName = N'Microsoft.SystemCenter.VirtualMachineManager.VirtualMachine.Memory' THEN Entry.AVG1 END)AS RAMAvg1 ,
MAX(CASE WHEN RName = N'Microsoft.SystemCenter.VirtualMachineManager.VirtualMachine.DiskTotalBytesPerSec' THEN Entry.AVG1 END) AS DiskSpaceAvg1
FROM
(
SELECT
vRule.RuleSystemName AS RName,
AVG(Perf.vPerfHourly.AverageValue) AS AVG1,
MAX(Perf.vPerfHourly.MaxValue) AS MAX1,
InstanceNameAndIds.ManagedEntityRowId,
vPerformanceRuleInstance.InstanceName
FROM
vPerformanceRuleInstance
INNER JOIN vRule ON
vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
INNER JOIN Perf.vPerfHourly ON
vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfHourly.PerformanceRuleInstanceRowId
INNER JOIN
(
SELECT DISTINCT
vME.DisplayName + '(' + [PropertyValue] + ')' as InstanceName,
vME.ManagedEntityRowId
FROM
[vManagedEntityPropertySet] AS vMEPS,
vManagedEntityTypeProperty AS vMETP,
vManagedEntityType AS vMET,
vManagedEntity AS vME
WHERE
vMEPS.PropertyGuid = vMETP.PropertyGuid
AND vMETP.PropertySystemName = 'VMMId'
AND vMET.ManagedEntityTypeRowId = vMETP.ManagedEntityTypeRowId
AND vMET.ManagedEntityTypeSystemName = N'Microsoft.SystemCenter.VirtualMachineManager.VirtualMachine'
AND vMEPS.ManagedEntityRowId = vME.ManagedEntityRowId
AND PropertyValue IS NOT NULL
AND vME.DisplayName != ''
) AS InstanceNameAndIds
ON vPerformanceRuleInstance.InstanceName = InstanceNameAndIds.InstanceName
WHERE
(Perf.vPerfHourly.DateTime >= @UTCStartDate1)
AND (Perf.vPerfHourly.DateTime <= @UTCEndDate1)
AND (Perf.vPerfHourly.AverageValue >= 0)
AND
(
vRule.RuleSystemName
IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.VirtualMachine.PercentCPU',
N'Microsoft.SystemCenter.VirtualMachineManager.VirtualMachine.Memory',
N'Microsoft.SystemCenter.VirtualMachineManager.VirtualMachine.DiskTotalBytesPerSec'
)
)
GROUP BY vPerformanceRuleInstance.InstanceName, InstanceNameAndIds.ManagedEntityRowId, vRule.RuleSystemName
) AS Entry
GROUP BY ManagedEntityRowId) AS perfTable
ON vME.ManagedEntityRowId = perfTable.ManagedEntityRowId
DROP TABLE #TempReportObjectList
SET NOCOUNT OFF
End
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_VirtualMachineManager_Report_VMUtilizationReportDataGet] TO OpsMgrReader
GO
Home