/******************************************************************
Impacted Report: Capacity Utilization
Functionality: Getting 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%)
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type IN ('IF', 'TF') AND name = 'Microsoft_SystemCenter_VirtualMachineManager_2016_Function_PIFResouceUtilizationReportDataGet')
BEGIN
EXECUTE ('CREATE FUNCTION dbo.[Microsoft_SystemCenter_VirtualMachineManager_2016_Function_PIFResouceUtilizationReportDataGet]() RETURNS @TempReportDataGetTable TABLE (id int) AS BEGIN RETURN; END')
END
GO
/*PIF is computed per resource by bucketizing the usage measured in the specified time scale
(60 one-minute samples if Hourly time scale is selected, 10minute samples if daily or weekly is selected)
and multiplying the count in each bucket with a weight assigned to each bucket.
These weights have the significance that for each type of resource the weighting could be different
depending on the response-usage curve for that particular resource. One good estimate is to use
PIF = log?(1/(1-utilization)^x )/3
where utilization is a number between 0 and 1 and value of x as 3.
Log base 10 of (1/(1-utilization)^x ) will result in a number between 0 and 3.
Dividing it by 3 normalized it between 0 and 1.
Once PIF numbers are computed per resource (CPU, Disk IO, Disk Usage, Network IO),
the max of those PIFs is taken as the representative for that machine,
as the resource with max PIF will be the bottleneck for the machine.
*/
ALTER FUNCTION [dbo].[Microsoft_SystemCenter_VirtualMachineManager_2016_Function_PIFResouceUtilizationReportDataGet]
(
@UTCStartDate1 DATETIME,
@UTCEndDate1 DATETIME,
@ObjectListcount INT,
@DateDiff INT,
@ObjectRowidList NVARCHAR(MAX),
@VMHostTypeRowId NVARCHAR(100),
@ForecastGranularity tinyint = 0
)
RETURNS @TempReportDataGetTable TABLE
(
DataPointDateTime DATETIME,
StoneCold INT,
Cold INT,
Warm INT,
Hot INT,
Overloaded INT,
NoData INT
)
AS
BEGIN
-- NoData objects for only those time slots where # of objects in that time slot less than current ObjectListCount ,
-- else it will show 0 NoData objects
--Hourly Aggregation
IF @ForecastGranularity = 0
INSERT @TempReportDataGetTable
SELECT
DataPointDateTime,
COUNT(CASE WHEN PIFAVG1 >=0 AND PIFAVG1 <= 10 THEN PIFAVG1 END) AS StoneCold,
COUNT(CASE WHEN PIFAVG1 >10 AND PIFAVG1 <=30 THEN PIFAVG1 END) AS Cold,
COUNT(CASE WHEN PIFAVG1 >30 AND PIFAVG1 <= 70 THEN PIFAVG1 END) AS Warm,
COUNT(CASE WHEN PIFAVG1 >70 AND PIFAVG1 <= 85 THEN PIFAVG1 END) AS Hot,
COUNT(CASE WHEN PIFAVG1 >85 THEN PIFAVG1 END) AS Overloaded,
CASE WHEN (@ObjectListcount - COUNT(CASE WHEN PIFAVG1 IS NOT NULL THEN PIFAVG1 END)) > 0 THEN
@ObjectListcount - COUNT(CASE WHEN PIFAVG1 IS NOT NULL THEN PIFAVG1 END)
ELSE 0
END AS NoData
FROM
(
SELECT * from
(
SELECT DISTINCT
Perf.vPerfHourly.ManagedEntityRowId AS ManagedEntityRowId,
DATEADD(Minute, @DateDiff, Perf.vPerfHourly.DateTime) as DataPointDateTime,
DATEPART(WEEK, DATEADD(Minute, @DateDiff, Perf.vPerfHourly.DateTime)) AS DataPointWeek,
MAX(LOG10(1/(Power(1- (Perf.vPerfHourly.AverageValue/100), 3)))/3) * 100 AS PIFAVG1,
MAX(Perf.vPerfHourly.AverageValue) AS AVG1,
MAX(Perf.vPerfHourly.MinValue) AS MIN1,
MAX(Perf.vPerfHourly.MaxValue) AS MAX1
FROM Perf.vPerfHourly
INNER JOIN vPerformanceRuleInstance
ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfHourly.PerformanceRuleInstanceRowId
INNER JOIN vManagedEntity
ON Perf.vPerfHourly.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN vManagedEntityType
ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId
INNER JOIN Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@VMHostTypeRowId) AS VMHostTypeRowIds
ON VMHostTypeRowIds.number = vManagedEntityType.ManagedEntityTypeRowId
INNER JOIN vRule
ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
LEFT OUTER JOIN vManagedEntityTypeImage
ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId
WHERE (Perf.vPerfHourly.DateTime >= @UTCStartDate1)
AND (Perf.vPerfHourly.DateTime <= @UTCEndDate1)
AND
(
vRule.RuleSystemName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',
N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',
N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2'
)
)
GROUP BY Perf.vPerfHourly.ManagedEntityRowId, Perf.vPerfHourly.DateTime
)AS temp
Inner join Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@ObjectRowidList) AS ObjectRowidTable
ON ObjectRowidTable.number = temp.ManagedEntityRowId
) AS PIF
GROUP BY DataPointDateTime
--Daily Aggregation
ELSE IF @ForecastGranularity = 1
INSERT @TempReportDataGetTable
SELECT
DataPointDateTime,
COUNT(CASE WHEN PIFAVG1 >=0 AND PIFAVG1 <= 10 THEN PIFAVG1 END) AS StoneCold,
COUNT(CASE WHEN PIFAVG1 >10 AND PIFAVG1 <=30 THEN PIFAVG1 END) AS Cold,
COUNT(CASE WHEN PIFAVG1 >30 AND PIFAVG1 <= 70 THEN PIFAVG1 END) AS Warm,
COUNT(CASE WHEN PIFAVG1 >70 AND PIFAVG1 <= 85 THEN PIFAVG1 END) AS Hot,
COUNT(CASE WHEN PIFAVG1 >85 AND PIFAVG1 <= 100 THEN PIFAVG1 END) AS Overloaded,
CASE WHEN (@ObjectListcount - COUNT(CASE WHEN PIFAVG1 IS NOT NULL THEN PIFAVG1 END)) > 0 THEN
@ObjectListcount - COUNT(CASE WHEN PIFAVG1 IS NOT NULL THEN PIFAVG1 END)
ELSE 0
END AS NoData
FROM
(
SELECT DISTINCT
Perf.vPerfDaily.ManagedEntityRowId AS ManagedEntityRowId,
DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime) as DataPointDateTime,
DATEPART(WEEK, DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime)) AS DataPointWeek,
MAX(LOG10(1/(Power(1- (Perf.vPerfDaily.AverageValue/100), 3)))/3) * 100 AS PIFAVG1,
MAX(Perf.vPerfDaily.AverageValue) AS AVG1,
MAX(Perf.vPerfDaily.MinValue) AS MIN1,
MAX(Perf.vPerfDaily.MaxValue) AS MAX1
FROM Perf.vPerfDaily
INNER JOIN vPerformanceRuleInstance
ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId
INNER JOIN vManagedEntity
ON Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN
Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@ObjectRowidList) AS ObjectRowidTable
ON ObjectRowidTable.number = Perf.vPerfDaily.ManagedEntityRowId
INNER JOIN vManagedEntityType
ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId
INNER JOIN Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@VMHostTypeRowId) AS VMHostTypeRowIds
ON VMHostTypeRowIds.number = vManagedEntityType.ManagedEntityTypeRowId
INNER JOIN vRule
ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
LEFT OUTER JOIN vManagedEntityTypeImage
ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId
WHERE (Perf.vPerfDaily.DateTime >= @UTCStartDate1)
AND (Perf.vPerfDaily.DateTime <= @UTCEndDate1)
AND
(
vRule.RuleSystemName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',
N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',
N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2'
)
)
GROUP BY Perf.vPerfDaily.ManagedEntityRowId, Perf.vPerfDaily.DateTime
) AS PIF
GROUP BY DataPointDateTime
--Weekly Aggregation
ELSE IF @ForecastGranularity = 2
INSERT @TempReportDataGetTable
SELECT
DataPointDateTime,
StoneCold,
Cold,
Warm,
Hot,
Overloaded,
NoData
FROM
(
SELECT
MAX(DataPointDateTime) AS DataPointDateTime,
DataPointWeek,
AVG(StoneCold) AS StoneCold,
AVG(Cold) AS Cold,
AVG(Warm) AS Warm,
AVG(Hot) AS Hot,
AVG(Overloaded) AS Overloaded,
AVG(NoData) AS NoData
FROM
(
SELECT
DataPointDateTime,
DataPointWeek,
COUNT(CASE WHEN PIFAVG1 >=0 AND PIFAVG1 <= 10 THEN PIFAVG1 END) AS StoneCold,
COUNT(CASE WHEN PIFAVG1 >10 AND PIFAVG1 <=30 THEN PIFAVG1 END) AS Cold,
COUNT(CASE WHEN PIFAVG1 >30 AND PIFAVG1 <= 70 THEN PIFAVG1 END) AS Warm,
COUNT(CASE WHEN PIFAVG1 >70 AND PIFAVG1 <= 85 THEN PIFAVG1 END) AS Hot,
COUNT(CASE WHEN PIFAVG1 >85 AND PIFAVG1 <= 100 THEN PIFAVG1 END) AS Overloaded,
CASE WHEN (@ObjectListcount - COUNT(CASE WHEN PIFAVG1 IS NOT NULL THEN PIFAVG1 END)) > 0 THEN
@ObjectListcount - COUNT(CASE WHEN PIFAVG1 IS NOT NULL THEN PIFAVG1 END)
ELSE 0
END AS NoData
FROM
(
SELECT DISTINCT
Perf.vPerfDaily.ManagedEntityRowId AS ManagedEntityRowId,
DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime) as DataPointDateTime,
DATEPART(WEEK, DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime)) AS DataPointWeek,
MAX(LOG10(1/(Power(1- (Perf.vPerfDaily.AverageValue/100), 3)))/3) * 100 AS PIFAVG1,
MAX(Perf.vPerfDaily.AverageValue) AS AVG1,
MAX(Perf.vPerfDaily.MinValue) AS MIN1,
MAX(Perf.vPerfDaily.MaxValue) AS MAX1
FROM Perf.vPerfDaily
INNER JOIN vPerformanceRuleInstance
ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId
INNER JOIN vManagedEntity
ON Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN
Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@ObjectRowidList) AS ObjectRowidTable
ON ObjectRowidTable.number = Perf.vPerfDaily.ManagedEntityRowId
INNER JOIN vManagedEntityType
ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId
INNER JOIN Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@VMHostTypeRowId) AS VMHostTypeRowIds
ON VMHostTypeRowIds.number = vManagedEntityType.ManagedEntityTypeRowId
INNER JOIN vRule
ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
LEFT OUTER JOIN vManagedEntityTypeImage
ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId
WHERE (Perf.vPerfDaily.DateTime >= @UTCStartDate1)
AND (Perf.vPerfDaily.DateTime <= @UTCEndDate1)
AND
(
vRule.RuleSystemName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',
N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',
N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2'
)
)
GROUP BY Perf.vPerfDaily.ManagedEntityRowId, Perf.vPerfDaily.DateTime
--order by DataPointDateTime
) AS PIF
GROUP BY DataPointDateTime , DataPointWeek
) AS PIFResouceUtilization
GROUP BY DataPointWeek
)AS FinalPIFResouceUtilization
--Monthly Aggregation
ELSE IF @ForecastGranularity = 3
INSERT @TempReportDataGetTable
SELECT
DataPointDateTime,
StoneCold,
Cold,
Warm,
Hot,
Overloaded,
NoData
FROM
(
SELECT
MAX(DataPointDateTime) AS DataPointDateTime,
DataPointMonth,
AVG(StoneCold) AS StoneCold,
AVG(Cold) AS Cold,
AVG(Warm) AS Warm,
AVG(Hot) AS Hot,
AVG(Overloaded) AS Overloaded,
AVG(NoData) AS NoData
FROM
(
SELECT
DataPointDateTime,
DataPointMonth,
COUNT(CASE WHEN PIFAVG1 >=0 AND PIFAVG1 <= 10 THEN PIFAVG1 END) AS StoneCold,
COUNT(CASE WHEN PIFAVG1 >10 AND PIFAVG1 <=30 THEN PIFAVG1 END) AS Cold,
COUNT(CASE WHEN PIFAVG1 >30 AND PIFAVG1 <= 70 THEN PIFAVG1 END) AS Warm,
COUNT(CASE WHEN PIFAVG1 >70 AND PIFAVG1 <= 85 THEN PIFAVG1 END) AS Hot,
COUNT(CASE WHEN PIFAVG1 >85 AND PIFAVG1 <= 100 THEN PIFAVG1 END) AS Overloaded,
CASE WHEN (@ObjectListcount - COUNT(CASE WHEN PIFAVG1 IS NOT NULL THEN PIFAVG1 END)) > 0 THEN
@ObjectListcount - COUNT(CASE WHEN PIFAVG1 IS NOT NULL THEN PIFAVG1 END)
ELSE 0
END AS NoData
FROM
(
SELECT DISTINCT
Perf.vPerfDaily.ManagedEntityRowId AS ManagedEntityRowId,
DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime) as DataPointDateTime,
DATEPART(MONTH, DATEADD(Minute, @DateDiff, Perf.vPerfDaily.DateTime)) AS DataPointMonth,
MAX(LOG10(1/(Power(1- (Perf.vPerfDaily.AverageValue/100), 3)))/3) * 100 AS PIFAVG1,
MAX(Perf.vPerfDaily.AverageValue) AS AVG1,
MAX(Perf.vPerfDaily.MinValue) AS MIN1,
MAX(Perf.vPerfDaily.MaxValue) AS MAX1
FROM Perf.vPerfDaily
INNER JOIN vPerformanceRuleInstance
ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId
INNER JOIN vManagedEntity
ON Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN
Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@ObjectRowidList) AS ObjectRowidTable
ON ObjectRowidTable.number = Perf.vPerfDaily.ManagedEntityRowId
INNER JOIN vManagedEntityType
ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId
INNER JOIN Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable(@VMHostTypeRowId) AS VMHostTypeRowIds
ON VMHostTypeRowIds.number = vManagedEntityType.ManagedEntityTypeRowId
INNER JOIN vRule
ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
LEFT OUTER JOIN vManagedEntityTypeImage
ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId
WHERE (Perf.vPerfDaily.DateTime >= @UTCStartDate1)
AND (Perf.vPerfDaily.DateTime <= @UTCEndDate1)
AND
(
vRule.RuleSystemName IN
(
N'Microsoft.SystemCenter.VirtualMachineManager.2016.HyperVHost.PercentProcessorTime',
N'Microsoft.SystemCenter.VirtualMachineManager.2016.ESXHost.PercentProcessorTime.V2',
N'Microsoft.SystemCenter.VirtualMachineManager.2016.XenServerHost.PercentProcessorTime.V2'
)
)
GROUP BY Perf.vPerfDaily.ManagedEntityRowId, Perf.vPerfDaily.DateTime
--order by DataPointDateTime
) AS PIF
GROUP BY DataPointDateTime , DataPointMonth
) AS PIFResouceUtilization
GROUP BY DataPointMonth
)AS FinalPIFResouceUtilization
Home