ForecastMachineGroupPerformanceCounters.SP.sql (Resource)

Element properties:

Source Code:

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

File Content: ForecastMachineGroupPerformanceCounters.SP.sql

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


    Impacted Report: Host Group Forecasting


    Functionality: Generate peformance counters rows according to the counters selected by user during the specified time range.


    Table: TablixPerformanceCounterRows


    This table is a container of sub reports


    


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


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


BEGIN


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


END


GO








ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_VirtualMachineManager_Report_ForecastMachineGroupPerformanceCountersReportDataGet]


@CounterList NVARCHAR(20)


AS


BEGIN


  SET NOCOUNT ON





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


CREATE TABLE #TempPerformanceCounters


(


    CounterName NCHAR(100) NULL,


    CounterIndex SMALLINT NULL


)





INSERT INTO #TempPerformanceCounters VALUES ('Disk Space Usage', 0)


INSERT INTO #TempPerformanceCounters VALUES ('Memory Usage', 1)


INSERT INTO #TempPerformanceCounters VALUES ('Disk Read/Write Forecast', 2)


INSERT INTO #TempPerformanceCounters VALUES ('Network Input/Output Forecast', 3)


INSERT INTO #TempPerformanceCounters VALUES ('CPU Usage Forecast', 4)








SELECT 


    #TempPerformanceCounters.CounterName,


    #TempPerformanceCounters.CounterIndex


FROM  #TempPerformanceCounters   


INNER JOIN Microsoft_SystemCenter_VirtualMachineManager_Function_ObjectRowIDList_ToTable(@CounterList) AS CounterListTable


ON CounterListTable.number = #TempPerformanceCounters.CounterIndex





  


DROP TABLE #TempPerformanceCounters


SET NOCOUNT OFF


End   





GO


GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_VirtualMachineManager_Report_ForecastMachineGroupPerformanceCountersReportDataGet] TO OpsMgrReader


GO