PIFDiskNetTransferReportDataGet.SP.2016.sql (Resource)

Element properties:

Source Code:

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

File Content: PIFDiskNetTransferReportDataGet.SP.2016.sql

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


    Impacted Report: Capacity Utilization 


    Functionality: Getting Diks IO and Network IO performance data of all the Hosts selected by user during the specified time range


                   Peformance data including:


                   (1) Disk read bytes per second 


                   (2) Disk write bytes per second


                   (3) Net received bytes per second


                   (4) Net sent bytes per second


    


    Relative Chart: 


    (1) Net Transfer


    (2) Disk IO


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


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


BEGIN


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


END





GO





ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_VirtualMachineManager_2016_Report_PIFDiskNetTransferReportDataGet]


    @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,


    DiskReadBytes, 


    DiskWriteBytes,


    NetReceived,


    NetSent 


FROM Microsoft_SystemCenter_VirtualMachineManager_2016_Function_HostResourcesPerformanceData


    (


        @UTCStartDate1,


        @StartDate1, 


        @UTCEndDate1, 


        @ObjectRowidList,


        @TotalMemoryPropertyID, 


        @VMHostTypeRowId,


        @DataAggregation


    )





WHERE   DiskReadBytes IS NOT NULL 


        OR DiskWriteBytes IS NOT NULL 


        OR NetReceived IS NOT NULL -- NetReceived is NULL because Network Total Bytes are collected for Xen/VMWare by design.


        OR NetSent IS NOT NULL








DROP TABLE #TempReportObjectList





SET NOCOUNT OFF


END   











GO





GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_VirtualMachineManager_2016_Report_PIFDiskNetTransferReportDataGet] TO OpsMgrReader


GO