/******************************************************************
Impacted Report: SAN Usage Forecasting
Functionality: Get the total capacity and used capacity of all the Storage Pool selected by user during the specified time range
Relative Chart:
(1) Storage Total/Used Capacity
******************************************************************/
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_VirtualMachineManager_Report_ForecastSANTotalAndUsedCapacityReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_VirtualMachineManager_Report_ForecastSANTotalAndUsedCapacityReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_VirtualMachineManager_Report_ForecastSANTotalAndUsedCapacityReportDataGet]
@UTCStartDate1 DATETIME,
@StartDate1 DATETIME,
@UTCEndDate1 DATETIME,
@ObjectList XML,
@ForecastGranularity INT
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #TempReportObjectList
(
ManagedEntityRowId INT
)
DECLARE @TimeSeries VARCHAR(MAX)
SET @TimeSeries = '';
---------------------------------------------------------------
-- calculate the time difference
---------------------------------------------------------------
DECLARE @DateDiff AS INT;
SET @DateDiff = DATEDIFF(MINUTE , @UTCStartDate1, @StartDate1);
-- Daily Aggregation
if @ForecastGranularity = 0
BEGIN
SELECT
ConsolidatedPerfData.DataPointDate,
CASE WHEN (ConsolidatedPerfData.TotalCapacity - ConsolidatedPerfData.RemainingCapacity) IS NULL or (ConsolidatedPerfData.TotalCapacity - ConsolidatedPerfData.RemainingCapacity) < 0
THEN 0
ELSE (ConsolidatedPerfData.TotalCapacity - ConsolidatedPerfData.RemainingCapacity) END AS UsedCapacity,
ConsolidatedPerfData.TotalCapacity
FROM
(
SELECT
DataPointDate,
SUM(CAST(StoragePoolUsedAndRemainingCapacity.RemainingCapacity AS BIGINT)*1.0/(1024*1024*1024)) AS RemainingCapacity,
SUM(CAST(StoragePoolUsedAndRemainingCapacity.TotalCapacity AS BIGINT)*1.0/(1024*1024*1024)) AS TotalCapacity
FROM
(
SELECT
Entry.ManagedEntityRowId,
DataPointDateTime AS DataPointDate,
CASE WHEN RName = N'Microsoft.SystemCenter.VirtualMachineManager.Storage.StoragePool.RemainingManagedSpace.PerfData.Rule' THEN Entry.AVG1 END AS RemainingCapacity,
CASE WHEN RName = N'Microsoft.SystemCenter.VirtualMachineManager.Storage.StoragePool.TotalManagedSpace.PerfData.Rule' THEN Entry.AVG1 END AS TotalCapacity
FROM
(
SELECT
DISTINCT
vRule.RuleSystemName AS RName,
DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime) AS DataPointDateTime,
vManagedEntity.ManagedEntityRowId AS ManagedEntityRowId,
MAX(Perf.vPerfDaily.AverageValue) AS AVG1
FROM
Perf.vPerfDaily
INNER JOIN
vPerformanceRuleInstance
ON
vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId
INNER JOIN
vManagedEntity
ON
Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN
vManagedEntityType
ON
vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId
INNER JOIN
vRule
ON
vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
INNER JOIN
#TempReportObjectList
ON
#TempReportObjectList.ManagedEntityRowId = Perf.vPerfDaily.ManagedEntityRowId
WHERE
(
Perf.vPerfDaily.DateTime >= @UTCStartDate1
)
AND (Perf.vPerfDaily.DateTime <= @UTCEndDate1)
AND
(
vRule.RuleSystemName
IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.Storage.StoragePool.RemainingManagedSpace.PerfData.Rule',
N'Microsoft.SystemCenter.VirtualMachineManager.Storage.StoragePool.TotalManagedSpace.PerfData.Rule'
)
)
GROUP BY RuleSystemName, Perf.vPerfDaily.DateTime, vManagedEntity.ManagedEntityRowId
)AS Entry
) AS StoragePoolUsedAndRemainingCapacity
GROUP BY DataPointDate
) ConsolidatedPerfData
END
-- Monthly/Quarterly/Yearly Aggregation
ELSE IF @ForecastGranularity = 1 OR @ForecastGranularity = 2
BEGIN
SELECT
ConsolidatedPerfData.DataPointDate,
CASE WHEN (ConsolidatedPerfData.TotalCapacity - ConsolidatedPerfData.RemainingCapacity) IS NULL or (ConsolidatedPerfData.TotalCapacity - ConsolidatedPerfData.RemainingCapacity) < 0
THEN 0
ELSE (ConsolidatedPerfData.TotalCapacity - ConsolidatedPerfData.RemainingCapacity) END AS UsedCapacity,
ConsolidatedPerfData.TotalCapacity
FROM
(
SELECT
--Format DatapointData like '2011-01'
CONVERT(NVARCHAR(2), DataPointMonth)+ '/' +CONVERT(NVARCHAR(4), DataPointYear) AS DataPointDate,
SUM(CAST(StoragePoolUsedAndRemainingCapacity.RemainingCapacity AS BIGINT)*1.0/(1024*1024*1024)) AS RemainingCapacity ,
SUM(CAST(StoragePoolUsedAndRemainingCapacity.TotalCapacity AS BIGINT)*1.0/(1024*1024*1024)) AS TotalCapacity
FROM
(
SELECT
DataPointYear,
DataPointMonth,
Entry.ManagedEntityRowId,
MAX(RemainingCapacity) AS RemainingCapacity,
MAX(TotalCapacity) AS TotalCapacity
FROM
(
SELECT
DISTINCT
vRule.RuleSystemName AS RName,
DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime) AS DataPointDateTime,
DATEPART(YEAR, DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime)) AS DataPointYear,
(
CASE @ForecastGranularity
WHEN 1 THEN DATEPART(MONTH, DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime))
WHEN 2 THEN CAST(DATEPART(QUARTER, DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime)) AS Decimal(2,0)) * 3
END
) AS DataPointMonth,
vManagedEntity.ManagedEntityRowId AS ManagedEntityRowId,
Perf.vPerfDaily.AverageValue AS AVG1,
CASE WHEN vRule.RuleSystemName = N'Microsoft.SystemCenter.VirtualMachineManager.Storage.StoragePool.RemainingManagedSpace.PerfData.Rule' THEN Perf.vPerfDaily.AverageValue END AS RemainingCapacity,
CASE WHEN vRule.RuleSystemName = N'Microsoft.SystemCenter.VirtualMachineManager.Storage.StoragePool.TotalManagedSpace.PerfData.Rule' THEN Perf.vPerfDaily.AverageValue END AS TotalCapacity
FROM
Perf.vPerfDaily
INNER JOIN
vPerformanceRuleInstance
ON
vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId
INNER JOIN
vManagedEntity
ON
Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN
vManagedEntityType
ON
vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId
INNER JOIN
vRule
ON
vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
INNER JOIN
#TempReportObjectList
ON
#TempReportObjectList.ManagedEntityRowId = Perf.vPerfDaily.ManagedEntityRowId
WHERE
(
Perf.vPerfDaily.DateTime >= @UTCStartDate1
)
AND (Perf.vPerfDaily.DateTime <= @UTCEndDate1)
AND
(
vRule.RuleSystemName
IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.Storage.StoragePool.RemainingManagedSpace.PerfData.Rule',
N'Microsoft.SystemCenter.VirtualMachineManager.Storage.StoragePool.TotalManagedSpace.PerfData.Rule'
)
)
)AS Entry
GROUP BY DataPointYear, DataPointMonth, Entry.ManagedEntityRowId
) AS StoragePoolUsedAndRemainingCapacity
GROUP BY DataPointYear, DataPointMonth
)ConsolidatedPerfData
END
ELSE
BEGIN
SELECT
ConsolidatedPerfData.DataPointDate,
CASE WHEN (ConsolidatedPerfData.TotalCapacity - ConsolidatedPerfData.RemainingCapacity) IS NULL or (ConsolidatedPerfData.TotalCapacity - ConsolidatedPerfData.RemainingCapacity) < 0
THEN 0
ELSE (ConsolidatedPerfData.TotalCapacity - ConsolidatedPerfData.RemainingCapacity) END AS UsedCapacity,
ConsolidatedPerfData.TotalCapacity
FROM
(
SELECT
--Format DatapointData like '2011-01'
CONVERT(NVARCHAR(2), MAX(DataPointMonth))+ '/' + CONVERT(NVARCHAR(4), DataPointYear) AS DataPointDate,
SUM(CAST(StoragePoolUsedAndRemainingCapacity.RemainingCapacity AS BIGINT)*1.0/(1024*1024*1024)) AS RemainingCapacity,
SUM(CAST(StoragePoolUsedAndRemainingCapacity.TotalCapacity AS BIGINT)*1.0/(1024*1024*1024)) AS TotalCapacity
FROM
(
SELECT
DataPointYear,
MAX(DataPointMonth) AS DataPointMonth,
Entry.ManagedEntityRowId,
MAX(RemainingCapacity) AS RemainingCapacity,
MAX(TotalCapacity) AS TotalCapacity
FROM
(
SELECT
DISTINCT
vRule.RuleSystemName AS RName,
DATEPART(YEAR, DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime)) AS DataPointYear,
DATEPART(MONTH, DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime)) AS DataPointMonth,
vManagedEntity.ManagedEntityRowId AS ManagedEntityRowId,
Perf.vPerfDaily.AverageValue AS AVG1,
CASE WHEN vRule.RuleSystemName = N'Microsoft.SystemCenter.VirtualMachineManager.Storage.StoragePool.RemainingManagedSpace.PerfData.Rule' THEN Perf.vPerfDaily.AverageValue END AS RemainingCapacity,
CASE WHEN vRule.RuleSystemName = N'Microsoft.SystemCenter.VirtualMachineManager.Storage.StoragePool.TotalManagedSpace.PerfData.Rule' THEN Perf.vPerfDaily.AverageValue END AS TotalCapacity
FROM
Perf.vPerfDaily
INNER JOIN
vPerformanceRuleInstance
ON
vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId
INNER JOIN
vManagedEntity
ON
Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN
vManagedEntityType
ON
vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId
INNER JOIN
vRule
ON
vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
INNER JOIN
#TempReportObjectList
ON
#TempReportObjectList.ManagedEntityRowId = Perf.vPerfDaily.ManagedEntityRowId
WHERE
(
Perf.vPerfDaily.DateTime >= @UTCStartDate1
)
AND (Perf.vPerfDaily.DateTime <= @UTCEndDate1)
AND
(
vRule.RuleSystemName
IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.Storage.StoragePool.RemainingManagedSpace.PerfData.Rule',
N'Microsoft.SystemCenter.VirtualMachineManager.Storage.StoragePool.TotalManagedSpace.PerfData.Rule'
)
)
)AS Entry
GROUP BY DataPointYear, Entry.ManagedEntityRowId
) AS StoragePoolUsedAndRemainingCapacity
GROUP BY DataPointYear
) ConsolidatedPerfData
END
drop table #TempReportObjectList
End
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_VirtualMachineManager_Report_ForecastSANTotalAndUsedCapacityReportDataGet] TO OpsMgrReader
GO
Home