PIFHostsResourcesCapacityUtilizationReportDataGet.SP.sql (Resource)

Element properties:

Source Code:

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

File Content: PIFHostsResourcesCapacityUtilizationReportDataGet.SP.sql

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


    Impacted Report: Capacity Utilization 


    Functionality: Getting CPU/Disk/Memory usage performance data of all the Hosts selected by user during the specified time range


                   Peformance data including:


                   (1) CPU usage (in percent)


                   (2) Disk usage (in percent)


                   (3) Memory usage (in percent)


                       


    Relative Chart: 


    (1) Capacity Utilization


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





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


BEGIN


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


END


GO





ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_VirtualMachineManager_Report_PIFHostsResourcesCapacityUtilizationReportDataGet]


    @UTCStartDate1 DATETIME,


    @StartDate1 DATETIME,


    @UTCEndDate1 DATETIME,


    @ObjectList XML,


    @TotalMemoryPropertyID UNIQUEIDENTIFIER,


    @VMHostTypeRowId NVARCHAR(100),


    @DataAggregation TINYINT = 0


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, 


    CPUUsage, 


    DiskStorage,


    MemUsagePercent


FROM Microsoft_SystemCenter_VirtualMachineManager_Function_HostResourcesPerformanceData


    (


        @UTCStartDate1, 


        @StartDate1,


        @UTCEndDate1, 


        @ObjectRowidList,


        @TotalMemoryPropertyID, 


        @VMHostTypeRowId,


        @DataAggregation


    )





WHERE   CPUUsage IS NOT NULL 


        OR DiskStorage IS NOT NULL 


        OR MemUsagePercent IS NOT NULL





DROP TABLE #TempReportObjectList





SET NOCOUNT OFF





END





GO


GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_VirtualMachineManager_Report_PIFHostsResourcesCapacityUtilizationReportDataGet] TO OpsMgrReader


GO