HostResourcesPerformanceData.SP.2016.sql (Resource)

Element properties:

Source Code:

<Resource ID="HostResourcesPerformanceData.SP.2016.sql" Accessibility="Internal" FileName="HostResourcesPerformanceData.SP.2016.sql"/>

File Content: HostResourcesPerformanceData.SP.2016.sql

/******************************************************************


    Impacted Report: 


    (1) Host Group Forecasting 


    (2) Capacity Utilization


    


    Functionality: Get the following performance data of all the Hosts selected by user during the specified time range


    (1) Disk read bytes per second


    (2) Disk write bytes per second


    (3) Net Received bytes per second


    (4) Net send bytes per second


    (5) CPU usage


    (6) Disk storage usage percent


    (7) Disk used space


    (8) Disk total space


    (9) Memory usage percent


    (10) Total memory


    (11) Used memory


    


******************************************************************/


IF NOT EXISTS (SELECT * FROM sysobjects WHERE type IN ('IF', 'TF') AND name = 'Microsoft_SystemCenter_VirtualMachineManager_2016_Function_HostResourcesPerformanceData')


BEGIN


EXECUTE ('CREATE FUNCTION dbo.[Microsoft_SystemCenter_VirtualMachineManager_2016_Function_HostResourcesPerformanceData]() RETURNS @TempReportDataGetTable TABLE (id int) AS BEGIN RETURN; END')


END


GO








ALTER FUNCTION [dbo].[Microsoft_SystemCenter_VirtualMachineManager_2016_Function_HostResourcesPerformanceData]


(


    @UTCStartDate1 DATETIME,


    @StartDate1 DATETIME,


    @UTCEndDate1 DATETIME,


    @ObjectRowidList NVARCHAR(MAX),


    @TotalMemoryPropertyID UNIQUEIDENTIFIER,


    @VMHostTypeRowId NVARCHAR(100),


    @ForecastGranularity TINYINT = 0


) 


RETURNS @TempReportDataGetTable TABLE


(


    DataPointDateTime NVARCHAR(32),


    DiskReadBytes FLOAT,


    DiskWriteBytes FLOAT,


    NetReceived FLOAT,


    NetSent FLOAT,    


    CPUUsage FLOAT,


    DiskStorage FLOAT,


    MemUsagePercent FLOAT,


    TotalMem FLOAT,


    UsedMem FLOAT,


    UsedSpace FLOAT,


    TotalSpace FLOAT


)


AS


    BEGIN


    


        ---------------------------------------------------------------


        -- calculate the time difference


        ---------------------------------------------------------------


        DECLARE @DateDiff AS INT;


        SET @DateDiff = DATEDIFF(MINUTE , @UTCStartDate1, @StartDate1);


    


    


        --Hourly Aggregation


        IF @ForecastGranularity = 0    


            INSERT @TempReportDataGetTable 


            SELECT


                DataPointDateTime,


                DiskReadBytes,


                DiskWriteBytes,


                NetReceived,


                NetSent,


                CPUUsage,


                DiskStorage,


                MemUsagePercent,


                TotalMem,


                MemUsagePercent*0.01*TotalMem AS UsedMem,


                ((FreeMBytes*100/NULLIF(FreeSpacePercentage, 100)) - FreeMBytes) AS UsedSpace,


                (FreeMBytes*100/NULLIF(FreeSpacePercentage, 100)) AS TotalSpace


            FROM


            (


                SELECT  


                    CONVERT(NVARCHAR(32), DataPointDateTime, 22) AS DataPointDateTime,





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskReadBytesTotalPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskReadBytesTotalPerSec.V2') THEN (Entry.AVG1/1024) END) AS DiskReadBytes,


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskWriteBytesTotalPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskWriteBytesTotalPerSec.V2') THEN (Entry.AVG1/1024) END) AS DiskWriteBytes,





                    AVG(CASE WHEN RName =   N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgReceivedBytesTotalPerSec' THEN Entry.AVG1/1024 END) AS NetReceived,


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgSentBytesPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.NetworkAvgBytesTotalPerSec.V2', 


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.NetworkAvgBytesTotalPerSec.V2') THEN Entry.AVG1/1024 END) AS NetSent,





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2') THEN Entry.AVG1 END) AS CPUUsage,





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace') THEN (100-Entry.AVG1) END) AS DiskStorage, 


                                    


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2') 


                                            THEN 100-((Entry.AVG1*1024*1024/NULLIF(CONVERT(BIGINT,HostMemTable.Memory), 0))*100) END) AS MemUsagePercent,   





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace', 


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace') THEN AVG1 END) AS FreeSpacePercentage,


                    AVG(CASE WHEN RName IN   (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskFreeMegabytes',


                                              N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskFreeMegabytes',


                                              N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskFreeMegabytes') THEN AVG1 END) AS FreeMBytes,





                    SUM(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2') THEN CONVERT(BIGINT,HostMemTable.Memory)/(1024*1024) END) AS TotalMem


                FROM            


                (


                    SELECT        


                    DISTINCT


                        vRule.RuleSystemName AS RName,


                        DATEADD(Minute, @DateDiff, Perf.vPerfHourly.DateTime) as DataPointDateTime,


                        vManagedEntity.ManagedEntityRowId AS ManagedEntityRowId,


                        MAX(Perf.vPerfHourly.AverageValue) AS AVG1


                    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 Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@VMHostTypeRowId) AS VMHostTypeRowIds


                    ON VMHostTypeRowIds.number = vManagedEntityType.ManagedEntityTypeRowId


                    INNER JOIN


                        vRule 


                    ON 


                        vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId


                    INNER JOIN 


                        Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@ObjectRowidList) AS ObjectRowidTable


                    ON ObjectRowidTable.number = Perf.vPerfHourly.ManagedEntityRowId 


                    WHERE        


                    (Perf.vPerfHourly.DateTime >= @UTCStartDate1) 


                    AND (Perf.vPerfHourly.DateTime <= @UTCEndDate1)


                    AND


                    (


                        vRule.RuleSystemName 


                        IN 


                        (    


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskReadBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskReadBytesTotalPerSec.V2' ,


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskWriteBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskWriteBytesTotalPerSec.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgReceivedBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgSentBytesPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.NetworkAvgBytesTotalPerSec.V2', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.NetworkAvgBytesTotalPerSec.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskFreeMegabytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskFreeMegabytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskFreeMegabytes'


                        )


                     )


                     GROUP BY Perf.vPerfHourly.DateTime,vManagedEntity.ManagedEntityRowId,vRule.RuleSystemName                    


                )   AS Entry


                INNER JOIN 


                (


                    SELECT        


                        ManagedEntityRowId, 


                        MAX(CASE WHEN vMEPS.PropertyGuid = @TotalMemoryPropertyID THEN vMEPS.PropertyValue END) AS Memory


                    FROM            


                        vManagedEntityPropertySet AS vMEPS


                    WHERE        


                    (


                        PropertyGuid = @TotalMemoryPropertyID


                    ) 


                    AND (ToDateTime IS NULL)


                    GROUP BY ManagedEntityRowId


                )AS HostMemTable


                ON Entry.ManagedEntityRowId = HostMemTable.ManagedEntityRowId


                GROUP BY DataPointDateTime  


            ) AS PerfmanceDataTable


                


        --Daily Aggregation


        ELSE IF @ForecastGranularity = 1    


            INSERT @TempReportDataGetTable 


            SELECT


                DataPointDateTime,


                DiskReadBytes,


                DiskWriteBytes,


                NetReceived,


                NetSent,


                CPUUsage,


                DiskStorage,


                MemUsagePercent,


                TotalMem,


                MemUsagePercent*0.01*TotalMem AS UsedMem,


                ((FreeMBytes*100/NULLIF(FreeSpacePercentage, 100)) - FreeMBytes) AS UsedSpace,


                (FreeMBytes*100/NULLIF(FreeSpacePercentage, 100)) AS TotalSpace


            FROM


            (


                SELECT  


                    CONVERT(NVARCHAR(32), DataPointDateTime, 101) AS DataPointDateTime,


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskReadBytesTotalPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskReadBytesTotalPerSec.V2') THEN (Entry.AVG1/1024) END) AS DiskReadBytes,


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskWriteBytesTotalPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskWriteBytesTotalPerSec.V2') THEN (Entry.AVG1/1024) END) AS DiskWriteBytes,





                    AVG(CASE WHEN RName =   N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgReceivedBytesTotalPerSec' THEN Entry.AVG1/1024 END) AS NetReceived,


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgSentBytesPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.NetworkAvgBytesTotalPerSec.V2', 


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.NetworkAvgBytesTotalPerSec.V2') THEN Entry.AVG1/1024 END) AS NetSent,





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2') THEN Entry.AVG1 END) AS CPUUsage,





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace') THEN (100-Entry.AVG1) END) AS DiskStorage, 


                                    


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2') 


                                            THEN 100-((Entry.AVG1*1024*1024/NULLIF(CONVERT(BIGINT,HostMemTable.Memory), 0))*100) END) AS MemUsagePercent,   





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace', 


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace') THEN AVG1 END) AS FreeSpacePercentage,


                    AVG(CASE WHEN RName IN   (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskFreeMegabytes',


                                              N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskFreeMegabytes',


                                              N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskFreeMegabytes') THEN AVG1 END) AS FreeMBytes,





                    SUM(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2') THEN CONVERT(BIGINT,HostMemTable.Memory)/(1024*1024) END) AS TotalMem





                FROM            


                (


                    SELECT        


                    DISTINCT


                        vRule.RuleSystemName AS RName,


                        DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime) as DataPointDateTime,


                        vManagedEntity.ManagedEntityRowId AS ManagedEntityRowId,


                        MAX(Perf.vPerfDaily.AverageValue) AS AVG1


                    FROM            


                        Perf.vPerfDaily 


                    INNER JOIN


                        vPerformanceRuleInstance 


                    ON


                        vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId 


                    INNER JOIN                


                        vManagedEntity 


                    ON 


                        Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId 


                    INNER JOIN


                        vManagedEntityType 


                    ON 


                        vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId 


                    INNER JOIN Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@VMHostTypeRowId) AS VMHostTypeRowIds


                    ON VMHostTypeRowIds.number = vManagedEntityType.ManagedEntityTypeRowId


                    INNER JOIN


                        vRule 


                    ON 


                        vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId


                    INNER JOIN 


                        Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@ObjectRowidList) AS ObjectRowidTable


                    ON ObjectRowidTable.number = Perf.vPerfDaily.ManagedEntityRowId 


                    WHERE        


                    (Perf.vPerfDaily.DateTime >= @UTCStartDate1) 


                    AND (Perf.vPerfDaily.DateTime <= @UTCEndDate1)        


                    AND


                    (


                        vRule.RuleSystemName 


                        IN 


                        (                            


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskReadBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskReadBytesTotalPerSec.V2' ,


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskWriteBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskWriteBytesTotalPerSec.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgReceivedBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgSentBytesPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.NetworkAvgBytesTotalPerSec.V2', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.NetworkAvgBytesTotalPerSec.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskFreeMegabytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskFreeMegabytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskFreeMegabytes'


                        )


                     )


                     GROUP BY Perf.vPerfDaily.DateTime,vManagedEntity.ManagedEntityRowId,vRule.RuleSystemName                    


                )  AS Entry


                INNER JOIN 


                (


                        SELECT        


                            ManagedEntityRowId, 


                            MAX(CASE WHEN vMEPS.PropertyGuid = @TotalMemoryPropertyID THEN vMEPS.PropertyValue END) AS Memory


                        FROM            


                            vManagedEntityPropertySet AS vMEPS


                        WHERE        


                        (


                            PropertyGuid = @TotalMemoryPropertyID


                        ) 


                        AND (ToDateTime IS NULL)


                        GROUP BY ManagedEntityRowId


                )AS HostMemTable


                ON Entry.ManagedEntityRowId = HostMemTable.ManagedEntityRowId


                GROUP BY DataPointDateTime


            ) AS PerformanceDataTable


               


        --Weekly Aggregation


        ELSE IF @ForecastGranularity = 2


            INSERT @TempReportDataGetTable 


            SELECT


                DataPointDateTime,


                DiskReadBytes,


                DiskWriteBytes,


                NetReceived,


                NetSent,


                CPUUsage,


                DiskStorage,


                MemUsagePercent,


                TotalMem,


                MemUsagePercent*0.01*TotalMem AS UsedMem,


                ((FreeMBytes*100/NULLIF(FreeSpacePercentage, 100)) - FreeMBytes) AS UsedSpace,


                (FreeMBytes*100/NULLIF(FreeSpacePercentage, 100)) AS TotalSpace


             FROM


             (


                 SELECT  


                        MAX(CONVERT(NVARCHAR(32), DataPointDateTime, 101)) AS DataPointDateTime,


                        DataPointWeek,


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskReadBytesTotalPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskReadBytesTotalPerSec.V2') THEN (Entry.AVG1/1024) END) AS DiskReadBytes,


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskWriteBytesTotalPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskWriteBytesTotalPerSec.V2') THEN (Entry.AVG1/1024) END) AS DiskWriteBytes,





                    AVG(CASE WHEN RName =   N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgReceivedBytesTotalPerSec' THEN Entry.AVG1/1024 END) AS NetReceived,


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgSentBytesPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.NetworkAvgBytesTotalPerSec.V2', 


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.NetworkAvgBytesTotalPerSec.V2') THEN Entry.AVG1/1024 END) AS NetSent,





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2') THEN Entry.AVG1 END) AS CPUUsage,





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace') THEN (100-Entry.AVG1) END) AS DiskStorage, 


                                    


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2') 


                                            THEN 100-((Entry.AVG1*1024*1024/NULLIF(CONVERT(BIGINT,HostMemTable.Memory), 0))*100) END) AS MemUsagePercent,   





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace', 


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace') THEN AVG1 END) AS FreeSpacePercentage,


                    AVG(CASE WHEN RName IN   (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskFreeMegabytes',


                                              N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskFreeMegabytes',


                                              N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskFreeMegabytes') THEN AVG1 END) AS FreeMBytes,





                    SUM(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2') THEN CONVERT(BIGINT,HostMemTable.Memory)/(1024*1024) END) AS TotalMem


                    FROM            


                    (


                        SELECT        


                        DISTINCT


                            vRule.RuleSystemName AS RName,


                            DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime) as DataPointDateTime,


                            DATEPART(WEEK, DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime)) AS DataPointWeek,


                            vManagedEntity.ManagedEntityRowId AS ManagedEntityRowId,


                            MAX(Perf.vPerfDaily.AverageValue) AS AVG1


                        FROM            


                            Perf.vPerfDaily 


                        INNER JOIN


                            vPerformanceRuleInstance 


                        ON


                            vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId 


                        INNER JOIN                


                            vManagedEntity 


                        ON 


                            Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId 


                        INNER JOIN


                            vManagedEntityType 


                        ON 


                            vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId 


                        INNER JOIN Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@VMHostTypeRowId) AS VMHostTypeRowIds


                        ON VMHostTypeRowIds.number = vManagedEntityType.ManagedEntityTypeRowId


                        INNER JOIN


                            vRule 


                        ON 


                            vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId


                        INNER JOIN 


                            Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@ObjectRowidList) AS ObjectRowidTable


                        ON ObjectRowidTable.number = Perf.vPerfDaily.ManagedEntityRowId 


                        WHERE        


                        (Perf.vPerfDaily.DateTime >= @UTCStartDate1) 


                        AND (Perf.vPerfDaily.DateTime <= @UTCEndDate1)          


                        AND


                        (


                            vRule.RuleSystemName 


                            IN 


                            (


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskReadBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskReadBytesTotalPerSec.V2' ,


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskWriteBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskWriteBytesTotalPerSec.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgReceivedBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgSentBytesPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.NetworkAvgBytesTotalPerSec.V2', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.NetworkAvgBytesTotalPerSec.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskFreeMegabytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskFreeMegabytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskFreeMegabytes'


                            )


                         )


                         GROUP BY Perf.vPerfDaily.DateTime,vManagedEntity.ManagedEntityRowId,vRule.RuleSystemName                    


                    )  AS Entry


                    INNER JOIN 


                    (


                            SELECT        


                                ManagedEntityRowId, 


                                MAX(CASE WHEN vMEPS.PropertyGuid = @TotalMemoryPropertyID THEN vMEPS.PropertyValue END) AS Memory


                            FROM            


                                vManagedEntityPropertySet AS vMEPS


                            WHERE        


                                (


                                    PropertyGuid = @TotalMemoryPropertyID


                                ) 


                                AND (ToDateTime IS NULL)


                            GROUP BY ManagedEntityRowId


                    )AS HostMemTable


                    ON Entry.ManagedEntityRowId = HostMemTable.ManagedEntityRowId


                    GROUP BY DataPointWeek 


               )AS DiskNetTransfer


        


        --Monthly Aggregation


        ELSE IF @ForecastGranularity = 3


            INSERT @TempReportDataGetTable      


            SELECT


                DataPointDateTime,


                DiskReadBytes,


                DiskWriteBytes,


                NetReceived,


                NetSent,


                CPUUsage,


                DiskStorage,


                MemUsagePercent,


                TotalMem,


                MemUsagePercent*0.01*TotalMem AS UsedMem,


                ((FreeMBytes*100/NULLIF(FreeSpacePercentage, 100)) - FreeMBytes) AS UsedSpace,


                (FreeMBytes*100/NULLIF(FreeSpacePercentage, 100)) AS TotalSpace


             FROM


             (


                SELECT  


                    MAX(CONVERT(NVARCHAR(32), DataPointDateTime, 101)) AS DataPointDateTime,


                    DataPointMonth,


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskReadBytesTotalPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskReadBytesTotalPerSec.V2') THEN (Entry.AVG1/1024) END) AS DiskReadBytes,


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskWriteBytesTotalPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskWriteBytesTotalPerSec.V2') THEN (Entry.AVG1/1024) END) AS DiskWriteBytes,





                    AVG(CASE WHEN RName =   N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgReceivedBytesTotalPerSec' THEN Entry.AVG1/1024 END) AS NetReceived,


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgSentBytesPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.NetworkAvgBytesTotalPerSec.V2', 


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.NetworkAvgBytesTotalPerSec.V2') THEN Entry.AVG1/1024 END) AS NetSent,





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2') THEN Entry.AVG1 END) AS CPUUsage,





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace') THEN (100-Entry.AVG1) END) AS DiskStorage, 


                                    


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2') 


                                            THEN 100-((Entry.AVG1*1024*1024/NULLIF(CONVERT(BIGINT,HostMemTable.Memory), 0))*100) END) AS MemUsagePercent,   





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace', 


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace') THEN AVG1 END) AS FreeSpacePercentage,


                    AVG(CASE WHEN RName IN   (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskFreeMegabytes',


                                              N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskFreeMegabytes',


                                              N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskFreeMegabytes') THEN AVG1 END) AS FreeMBytes,





                    SUM(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2') THEN CONVERT(BIGINT,HostMemTable.Memory)/(1024*1024) END) AS TotalMem


                FROM            


                (


                    SELECT        


                    DISTINCT


                        vRule.RuleSystemName AS RName,


                        DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime) as DataPointDateTime,


                        DATEPART(MONTH, DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime)) AS DataPointMonth,


                        vManagedEntity.ManagedEntityRowId AS ManagedEntityRowId,


                        MAX(Perf.vPerfDaily.AverageValue) AS AVG1


                    FROM            


                        Perf.vPerfDaily 


                    INNER JOIN


                        vPerformanceRuleInstance 


                    ON


                        vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId 


                    INNER JOIN                


                        vManagedEntity 


                    ON 


                        Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId 


                    INNER JOIN


                        vManagedEntityType 


                    ON 


                        vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId 


                    INNER JOIN Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@VMHostTypeRowId) AS VMHostTypeRowIds


                    ON VMHostTypeRowIds.number = vManagedEntityType.ManagedEntityTypeRowId


                    INNER JOIN


                        vRule 


                    ON 


                        vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId


                    INNER JOIN 


                        Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@ObjectRowidList) AS ObjectRowidTable


                    ON ObjectRowidTable.number = Perf.vPerfDaily.ManagedEntityRowId 


                    WHERE        


                    (Perf.vPerfDaily.DateTime >= @UTCStartDate1) 


                    AND (Perf.vPerfDaily.DateTime <= @UTCEndDate1)          


                    AND


                    (


                        vRule.RuleSystemName 


                        IN 


                        (


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskReadBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskReadBytesTotalPerSec.V2' ,


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskWriteBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskWriteBytesTotalPerSec.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgReceivedBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgSentBytesPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.NetworkAvgBytesTotalPerSec.V2', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.NetworkAvgBytesTotalPerSec.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskFreeMegabytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskFreeMegabytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskFreeMegabytes'


                        )


                     )


                     GROUP BY Perf.vPerfDaily.DateTime,vManagedEntity.ManagedEntityRowId,vRule.RuleSystemName                    


                )  AS Entry


                INNER JOIN 


                (


                        SELECT        


                            ManagedEntityRowId, 


                            MAX(CASE WHEN vMEPS.PropertyGuid = @TotalMemoryPropertyID THEN vMEPS.PropertyValue END) AS Memory


                        FROM            


                            vManagedEntityPropertySet AS vMEPS


                        WHERE        


                            (


                                PropertyGuid = @TotalMemoryPropertyID


                            ) 


                            AND (ToDateTime IS NULL)


                        GROUP BY ManagedEntityRowId


                )AS HostMemTable


                ON Entry.ManagedEntityRowId = HostMemTable.ManagedEntityRowId


                GROUP BY DataPointMonth 


           )AS DiskNetTransfer


        


        --Quarterly Aggregation


        ELSE IF @ForecastGranularity = 4


            INSERT @TempReportDataGetTable   


            SELECT


                DataPointDateTime,


                DiskReadBytes,


                DiskWriteBytes,


                NetReceived,


                NetSent,


                CPUUsage,


                DiskStorage,


                MemUsagePercent,


                TotalMem,


                MemUsagePercent*0.01*TotalMem AS UsedMem,


                ((FreeMBytes*100/NULLIF(FreeSpacePercentage, 100)) - FreeMBytes) AS UsedSpace,


                (FreeMBytes*100/NULLIF(FreeSpacePercentage, 100)) AS TotalSpace


            FROM


            (


                SELECT  


                    CONVERT(NVARCHAR(2), DataPointMonth)+ '/' + CONVERT(NVARCHAR(4), DataPointYear)   AS DataPointDateTime, 


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskReadBytesTotalPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskReadBytesTotalPerSec.V2') THEN (Entry.AVG1/1024) END) AS DiskReadBytes,


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskWriteBytesTotalPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskWriteBytesTotalPerSec.V2') THEN (Entry.AVG1/1024) END) AS DiskWriteBytes,





                    AVG(CASE WHEN RName =   N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgReceivedBytesTotalPerSec' THEN Entry.AVG1/1024 END) AS NetReceived,


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgSentBytesPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.NetworkAvgBytesTotalPerSec.V2', 


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.NetworkAvgBytesTotalPerSec.V2') THEN Entry.AVG1/1024 END) AS NetSent,





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2') THEN Entry.AVG1 END) AS CPUUsage,





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace') THEN (100-Entry.AVG1) END) AS DiskStorage, 


                                    


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2') 


                                            THEN 100-((Entry.AVG1*1024*1024/NULLIF(CONVERT(BIGINT,HostMemTable.Memory), 0))*100) END) AS MemUsagePercent,   





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace', 


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace') THEN AVG1 END) AS FreeSpacePercentage,


                    AVG(CASE WHEN RName IN   (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskFreeMegabytes',


                                              N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskFreeMegabytes',


                                              N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskFreeMegabytes') THEN AVG1 END) AS FreeMBytes,





                    SUM(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2') THEN CONVERT(BIGINT,HostMemTable.Memory)/(1024*1024) END) AS TotalMem





                FROM            


                (


                    SELECT


                        DataPointYear,


                        DataPointMonth,


                        RName, 


                        ManagedEntityRowId,


                        MAX(AVG1) AS AVG1


                    FROM


                    (        


                        SELECT        


                            DISTINCT


                            vRule.RuleSystemName AS RName, 


                            DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime) as DataPointDateTime,


                            CONVERT(NVARCHAR(4), DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime), 20) AS DataPointYear,


                            CAST(DATEPART(QUARTER, DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime)) AS Decimal(2,0)) * 3 AS DataPointMonth,


                            Perf.vPerfDaily.AverageValue AS AVG1,


                            vManagedEntity.ManagedEntityRowId AS ManagedEntityRowId


                        FROM            


                            Perf.vPerfDaily 


                        INNER JOIN


                            vPerformanceRuleInstance 


                        ON


                            vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId 


                        INNER JOIN                


                            vManagedEntity 


                        ON 


                            Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId 


                        INNER JOIN


                            vManagedEntityType 


                        ON 


                            vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId


                        INNER JOIN Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@VMHostTypeRowId) AS VMHostTypeRowIds


                        ON VMHostTypeRowIds.number = vManagedEntityType.ManagedEntityTypeRowId 


                        INNER JOIN


                            vRule 


                        ON 


                            vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId


                        INNER JOIN 


                            Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@ObjectRowidList) AS ObjectRowidTable


                        ON ObjectRowidTable.number = Perf.vPerfDaily.ManagedEntityRowId 


                        WHERE        


                        (Perf.vPerfDaily.DateTime >= @UTCStartDate1) 


                        AND (Perf.vPerfDaily.DateTime <= @UTCEndDate1)         


                        AND


                        (


                            vRule.RuleSystemName 


                            IN 


                            (


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskReadBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskReadBytesTotalPerSec.V2' ,


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskWriteBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskWriteBytesTotalPerSec.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgReceivedBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgSentBytesPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.NetworkAvgBytesTotalPerSec.V2', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.NetworkAvgBytesTotalPerSec.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskFreeMegabytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskFreeMegabytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskFreeMegabytes'


                            )


                        )


                    )AS TempPerfDataTable


                    GROUP BY DataPointYear, DataPointMonth, RName,ManagedEntityRowId                   


                )  AS Entry


                INNER JOIN 


                (


                    SELECT        


                        ManagedEntityRowId, 


                        MAX(CASE WHEN vMEPS.PropertyGuid = @TotalMemoryPropertyID THEN vMEPS.PropertyValue END) AS Memory


                    FROM            


                        vManagedEntityPropertySet AS vMEPS


                    WHERE        


                        (


                            PropertyGuid = @TotalMemoryPropertyID


                        ) 


                        AND (ToDateTime IS NULL)


                    GROUP BY ManagedEntityRowId


                )AS HostMemTable


                ON Entry.ManagedEntityRowId = HostMemTable.ManagedEntityRowId


                GROUP BY DataPointMonth, DataPointYear


            ) AS PerformanceDataTable


            


        --Yearly Aggregation


        ELSE IF @ForecastGranularity = 5


            INSERT @TempReportDataGetTable   


            SELECT


                DataPointDateTime,


                DiskReadBytes,


                DiskWriteBytes,


                NetReceived,


                NetSent,


                CPUUsage,


                DiskStorage,


                MemUsagePercent,


                TotalMem,


                MemUsagePercent*0.01*TotalMem AS UsedMem,


                ((FreeMBytes*100/NULLIF(FreeSpacePercentage, 100)) - FreeMBytes) AS UsedSpace,


                (FreeMBytes*100/NULLIF(FreeSpacePercentage, 100)) AS TotalSpace


            FROM


            (


                SELECT  


                    CONVERT(NVARCHAR(2), MAX(DataPointMonth))+ '/' + CONVERT(NVARCHAR(4), DataPointYear)    AS DataPointDateTime, 


                                        AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskReadBytesTotalPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskReadBytesTotalPerSec.V2') THEN (Entry.AVG1/1024) END) AS DiskReadBytes,


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskWriteBytesTotalPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskWriteBytesTotalPerSec.V2') THEN (Entry.AVG1/1024) END) AS DiskWriteBytes,





                    AVG(CASE WHEN RName =   N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgReceivedBytesTotalPerSec' THEN Entry.AVG1/1024 END) AS NetReceived,


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgSentBytesPerSec',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.NetworkAvgBytesTotalPerSec.V2', 


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.NetworkAvgBytesTotalPerSec.V2') THEN Entry.AVG1/1024 END) AS NetSent,





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2') THEN Entry.AVG1 END) AS CPUUsage,





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace') THEN (100-Entry.AVG1) END) AS DiskStorage, 


                                    


                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2') 


                                            THEN 100-((Entry.AVG1*1024*1024/NULLIF(CONVERT(BIGINT,HostMemTable.Memory), 0))*100) END) AS MemUsagePercent,   





                    AVG(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace', 


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace') THEN AVG1 END) AS FreeSpacePercentage,


                    AVG(CASE WHEN RName IN   (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskFreeMegabytes',


                                              N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskFreeMegabytes',


                                              N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskFreeMegabytes') THEN AVG1 END) AS FreeMBytes,





                    SUM(CASE WHEN RName IN (N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2') THEN CONVERT(BIGINT,HostMemTable.Memory)/(1024*1024) END) AS TotalMem


                FROM            


                (


                    SELECT        


                    DISTINCT


                        vRule.RuleSystemName AS RName,


                        DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime) as DataPointDateTime,


                        CONVERT(NVARCHAR(4), DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime), 20) AS DataPointYear, 


                        DATEPART(MONTH, DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime)) AS DataPointMonth,


                        vManagedEntity.ManagedEntityRowId AS ManagedEntityRowId,


                        MAX(Perf.vPerfDaily.AverageValue) AS AVG1


                    FROM            


                        Perf.vPerfDaily 


                    INNER JOIN


                        vPerformanceRuleInstance 


                    ON


                        vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId 


                    INNER JOIN                


                        vManagedEntity 


                    ON 


                        Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId 


                    INNER JOIN


                        vManagedEntityType 


                    ON 


                        vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId


                    INNER JOIN Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@VMHostTypeRowId) AS VMHostTypeRowIds


                    ON VMHostTypeRowIds.number = vManagedEntityType.ManagedEntityTypeRowId 


                    INNER JOIN


                        vRule 


                    ON 


                        vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId


                    INNER JOIN 


                        Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@ObjectRowidList) AS ObjectRowidTable


                    ON ObjectRowidTable.number = Perf.vPerfDaily.ManagedEntityRowId 


                    WHERE        


                    (Perf.vPerfDaily.DateTime >= @UTCStartDate1) 


                    AND (Perf.vPerfDaily.DateTime <= @UTCEndDate1)          


                    AND


                    (


                        vRule.RuleSystemName 


                        IN 


                        (


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskReadBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskReadBytesTotalPerSec.V2' ,


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskWriteBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskWriteBytesTotalPerSec.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgReceivedBytesTotalPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.NetworkAvgSentBytesPerSec',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.NetworkAvgBytesTotalPerSec.V2', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.NetworkAvgBytesTotalPerSec.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.MemoryAvailableMBytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.MemoryAvailableMBytes.V2',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.MemoryAvailableMBytes.V2', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskPercentFreeSpace', 


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskPercentFreeSpace',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.DiskFreeMegabytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.DiskFreeMegabytes',


                            N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.DiskFreeMegabytes'


                        )


                     )


                     GROUP BY Perf.vPerfDaily.DateTime,vManagedEntity.ManagedEntityRowId,vRule.RuleSystemName                    


                )  AS Entry


                INNER JOIN 


                (


                        SELECT        


                            ManagedEntityRowId, 


                            MAX(CASE WHEN vMEPS.PropertyGuid = @TotalMemoryPropertyID THEN vMEPS.PropertyValue END) AS Memory


                        FROM            


                            vManagedEntityPropertySet AS vMEPS


                        WHERE        


                            (


                                PropertyGuid = @TotalMemoryPropertyID


                            ) 


                            AND (ToDateTime IS NULL)


                        GROUP BY ManagedEntityRowId


                )AS HostMemTable


                ON Entry.ManagedEntityRowId = HostMemTable.ManagedEntityRowId


                GROUP BY DataPointYear


            ) AS PerformanceDataTable


        


    RETURN;


    END