PIFResouceUtilizationReportData.SP.sql (Resource)

Element properties:

Source Code:

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

File Content: PIFResouceUtilizationReportData.SP.sql

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


    Impacted Report: Capacity Utilization 


    Functionality: Calling function Microsoft_SystemCenter_VirtualMachineManager_Function_PIFResouceUtilizationReportDataGet


                   to get PIF(Performance Impact Factor) data of all the Storage Pool selected by user during the specified time range


                   PIF is calculated according to one formular (refer to the comments below)


                   In other words, getting  the following data


                   (1) How many hosts' PIF data is Stone Cold(0%-10%)


                   (2) How many hosts' PIF data is Cold(11%-30%)


                   (3) How many hosts' PIF data is Warm(31%-70%)


                   (4) How many hosts' PIF data is Hot(71%-85%)


                   (5) How many hosts' PIF data is Overloaded(86%-100%)


    


    Relative Chart: 


    (1) PIF(Performance Impact Factor)


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


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


BEGIN


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


END





GO





ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_VirtualMachineManager_Report_PIFResouceUtilizationReportDataGet]


@UTCStartDate1 datetime,


@UTCEndDate1 datetime,


@StartDate1 datetime,


@ObjectList xml,


@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 @DateDiff AS INT;


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





--Get the count of all objects included in @ObjectList(Which comes from MOM Object picker control)


DECLARE @ObjectListcount INT


SET @ObjectListcount =


(


    Select COUNT(DISTINCT ManagedEntityDefaultName) from #TempReportObjectList


    INNER JOIN vManagedEntity ON vManagedEntity.ManagedEntityRowId = #TempReportObjectList.ManagedEntityRowid


    INNER JOIN vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId


    WHERE vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.VirtualMachineManager.Discovery.HyperVHost' OR


        vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.VirtualMachineManager.Discovery.ESXHost' OR


        vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.VirtualMachineManager.Discovery.XenServerHost'


)





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 * FROM [dbo].[Microsoft_SystemCenter_VirtualMachineManager_Function_PIFResouceUtilizationReportDataGet]


(


    @UTCStartDate1, 


    @UTCEndDate1, 


    @ObjectListcount, 


    @DateDiff, 


    @ObjectRowidList, 


    @VMHostTypeRowId,


    @DataAggregation


)    


     


DROP TABLE #TempReportObjectList


SET NOCOUNT OFF


END





GO





GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_VirtualMachineManager_Report_PIFResouceUtilizationReportDataGet] TO OpsMgrReader


GO