ForecastMachineGroupHostResourceData.SP.2016.sql (Resource)

Element properties:

Source Code:

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

File Content: ForecastMachineGroupHostResourceData.SP.2016.sql

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


    Impacted Report: Host Group Forecasting


    Functionality: Get host resouces usage/total value of all the hosts selected by user during the specified time range.


    


    Relative Chart: 


    (1) Disk Used/Total MBytes


    (2) Memory Used/Total MBytes'


    


    Host resource usage including:


    (1) Used Disk Space


    (2) Total Disk Space


    (3) Used Memory


    (4) Total Memory


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


IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_VirtualMachineManager_2016_Report_ForecastMachineGroupHostResourceData')


BEGIN


    EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_VirtualMachineManager_2016_Report_ForecastMachineGroupHostResourceData] AS RETURN 1')


END


GO





ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_VirtualMachineManager_2016_Report_ForecastMachineGroupHostResourceData]


    @UTCStartDate1 DATETIME,


    @StartDate1 DATETIME,


    @UTCEndDate1 DATETIME,


    @ObjectList XML,


    @TotalMemoryPropertyID UNIQUEIDENTIFIER,


    @VMHostTypeRowId NVARCHAR(100),


    @ForecastGranularity TINYINT = 1


AS


BEGIN


  SET NOCOUNT ON





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


CREATE TABLE #TempReportObjectList 


(


    ManagedEntityRowid INT


)


INSERT INTO #TempReportObjectList


    EXEC [dbo].[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]


         @ObjectList = @ObjectList,


         @StartDate = @UTCStartDate1,


         @EndDate = @UTCEndDate1


            





DECLARE @ObjectRowidList NVARCHAR(MAX)


SET @ObjectRowidList = ''





-- Combine all the Object rowid in #TempReportObjectList into one string like "122,123,124," and stored it into @ObjectRowidList


SELECT @ObjectRowidList = @ObjectRowidList + CONVERT(NVARCHAR(10), #TempReportObjectList.ManagedEntityRowid) + ','


FROM #TempReportObjectList  


WHERE #TempReportObjectList.ManagedEntityRowid IS NOT NULL





--Remove the last char ',' to make sure the @ObjectRowidList like "122,123,124"


SET @ObjectRowidList = LEFT(@ObjectRowidList, LEN(@ObjectRowidList) -1)





SELECT 


    CONVERT(Datetime, DataPointDateTime, 0) as DataPointDateTime, 


    UsedSpace, 


    TotalSpace,


    UsedMem,


    TotalMem 


FROM Microsoft_SystemCenter_VirtualMachineManager_2016_Function_HostResourcesPerformanceData


    (


        @UTCStartDate1, 


        @StartDate1,


        @UTCEndDate1, 


        @ObjectRowidList,


        @TotalMemoryPropertyID,


        @VMHostTypeRowId, 


        @ForecastGranularity


    )








DROP TABLE #TempReportObjectList


SET NOCOUNT OFF


END








GO


GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_VirtualMachineManager_2016_Report_ForecastMachineGroupHostResourceData] TO OpsMgrReader


GO