/******************************************************************
Impacted Report: Host Group Forecasting
Functionality: Get historic data time series of all the hosts selected by user during the specified time range
Relative Chart:
(1) CPU Usage Forecast
(2) Disk Read KB/Sec Forecast
(3) Disk Write KB/Sec Forecast
(4) Disk Space Usage Forecast
(5) Memory Usage Forecast
(6) Network Input Forecast
(7) Network Output Forecast
The data time series including:
(1) CPU Usage Time Series
(2) Disk Read Time Series
(3) Disk Write Time Series
(4) Disk Space Time Series
(5) Mem Usage Time Series
(6) Network Input Time Series
(7) Network Output Time Series
******************************************************************/
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_VirtualMachineManager_2016_Report_ForecastMachineGroupDataTimeSeries')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_VirtualMachineManager_2016_Report_ForecastMachineGroupDataTimeSeries] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_VirtualMachineManager_2016_Report_ForecastMachineGroupDataTimeSeries]
@UTCStartDate1 DATETIME,
@StartDate1 DATETIME,
@UTCEndDate1 DATETIME,
@ObjectList XML,
@TotalMemoryPropertyID UNIQUEIDENTIFIER,
@VMHostTypeRowId NVARCHAR(100),
@ForecastGranularity TINYINT = 0
AS
BEGIN
SET NOCOUNT ON
---------------------------------------------------------------------
CREATE TABLE #TempReportObjectList
(
ManagedEntityRowid INT
)
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)
SET @CPUUsageTimeSeries = '';
SET @DiskReadTimeSeries = '';
SET @DiskWriteTimeSeries = '';
SET @DiskSpaceUsageTimeSeries = '';
SET @MemUsageTimeSeries = '';
SET @NetworkInputTimeSeries = '';
SET @NetworkOutputTimeSeries = '';
--Get CPU Usage Time Series
SELECT
@CPUUsageTimeSeries = @CPUUsageTimeSeries + DataPointDateTime + ', '
+ CONVERT(VARCHAR(30), CONVERT(BIGINT,CPUUsage)) + ';'
FROM
(
SELECT
DataPointDateTime,
CPUUsage
FROM Microsoft_SystemCenter_VirtualMachineManager_2016_Function_HostResourcesPerformanceData
(
@UTCStartDate1,
@StartDate1,
@UTCEndDate1,
@ObjectRowidList,
@TotalMemoryPropertyID,
@VMHostTypeRowId,
@ForecastGranularity
)
WHERE CPUUsage IS NOT NULL
) AS PerformanceDataTable
--Get Disk Read Time Series
SELECT
@DiskReadTimeSeries = @DiskReadTimeSeries + DataPointDateTime + ', '
+ CONVERT(VARCHAR(30), CONVERT(BIGINT,DiskReadBytes)) + ';'
FROM
(
SELECT
DataPointDateTime,
DiskReadBytes
FROM Microsoft_SystemCenter_VirtualMachineManager_2016_Function_HostResourcesPerformanceData
(
@UTCStartDate1,
@StartDate1,
@UTCEndDate1,
@ObjectRowidList,
@TotalMemoryPropertyID,
@VMHostTypeRowId,
@ForecastGranularity
)
WHERE DiskReadBytes IS NOT NULL
) AS PerformanceDataTable
--Get Disk Write Time Series
SELECT
@DiskWriteTimeSeries = @DiskWriteTimeSeries + DataPointDateTime + ', '
+ CONVERT(VARCHAR(30), CONVERT(BIGINT,DiskWriteBytes)) + ';'
FROM
(
SELECT
DataPointDateTime,
DiskWriteBytes
FROM Microsoft_SystemCenter_VirtualMachineManager_2016_Function_HostResourcesPerformanceData
(
@UTCStartDate1,
@StartDate1,
@UTCEndDate1,
@ObjectRowidList,
@TotalMemoryPropertyID,
@VMHostTypeRowId,
@ForecastGranularity
)
WHERE DiskWriteBytes IS NOT NULL
) AS PerformanceDataTable
--Get Disk Usage Time Series
SELECT
@DiskSpaceUsageTimeSeries = @DiskSpaceUsageTimeSeries + DataPointDateTime + ', '
+ CONVERT(VARCHAR(30), CONVERT(BIGINT,DiskStorage)) + ';'
FROM
(
SELECT
DataPointDateTime,
DiskStorage
FROM Microsoft_SystemCenter_VirtualMachineManager_2016_Function_HostResourcesPerformanceData
(
@UTCStartDate1,
@StartDate1,
@UTCEndDate1,
@ObjectRowidList,
@TotalMemoryPropertyID,
@VMHostTypeRowId,
@ForecastGranularity
)
WHERE DiskStorage IS NOT NULL
) AS PerformanceDataTable
--Get Mem Usage Time Series
SELECT
@MemUsageTimeSeries = @MemUsageTimeSeries + DataPointDateTime + ', '
+ CONVERT(VARCHAR(30), CONVERT(BIGINT,MemUsagePercent)) + ';'
FROM
(
SELECT
DataPointDateTime,
MemUsagePercent
FROM Microsoft_SystemCenter_VirtualMachineManager_2016_Function_HostResourcesPerformanceData
(
@UTCStartDate1,
@StartDate1,
@UTCEndDate1,
@ObjectRowidList,
@TotalMemoryPropertyID,
@VMHostTypeRowId,
@ForecastGranularity
)
WHERE MemUsagePercent IS NOT NULL
) AS PerformanceDataTable
--Get Network Input Time Series
SELECT
@NetworkInputTimeSeries = @NetworkInputTimeSeries + DataPointDateTime + ', '
+ CONVERT(VARCHAR(30), CONVERT(BIGINT,NetReceived)) + ';'
FROM
(
SELECT
DataPointDateTime,
NetReceived
FROM Microsoft_SystemCenter_VirtualMachineManager_2016_Function_HostResourcesPerformanceData
(
@UTCStartDate1,
@StartDate1,
@UTCEndDate1,
@ObjectRowidList,
@TotalMemoryPropertyID,
@VMHostTypeRowId,
@ForecastGranularity
)
WHERE NetReceived IS NOT NULL
) AS PerformanceDataTable
--Get Network output Time Series
SELECT
@NetworkOutputTimeSeries = @NetworkOutputTimeSeries + DataPointDateTime + ', '
+ CONVERT(VARCHAR(30), CONVERT(BIGINT,NetSent)) + ';'
FROM
(
SELECT
DataPointDateTime,
NetSent
FROM Microsoft_SystemCenter_VirtualMachineManager_2016_Function_HostResourcesPerformanceData
(
@UTCStartDate1,
@StartDate1,
@UTCEndDate1,
@ObjectRowidList,
@TotalMemoryPropertyID,
@VMHostTypeRowId,
@ForecastGranularity
)
WHERE NetSent IS NOT NULL
) AS PerformanceDataTable
SELECT
@CPUUsageTimeSeries AS CPUUsageTimeSeries,
@DiskReadTimeSeries AS DiskReadTimeSeries,
@DiskWriteTimeSeries AS DiskWriteTimeSeries,
@DiskSpaceUsageTimeSeries AS DiskSpaceUsageTimeSeries,
@MemUsageTimeSeries AS MemUsageTimeSeries,
@NetworkInputTimeSeries AS NetworkInputTimeSeries,
@NetworkOutputTimeSeries AS NetworkOutputTimeSeries
DROP TABLE #TempReportObjectList
SET NOCOUNT OFF
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_VirtualMachineManager_2016_Report_ForecastMachineGroupDataTimeSeries] TO OpsMgrReader
GO
Home