-- ##### Veeam_VE_HyperV_ThresholdsTransformedGet_CreateAlter.sql
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [schema_id]=SCHEMA_ID('dbo') AND type = 'P' AND name = 'Veeam_VE_Widget_ThresholdsTransformedGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Veeam_VE_Widget_ThresholdsTransformedGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Veeam_VE_Widget_ThresholdsTransformedGet]
@ReportSystemName NVARCHAR(256),
@Thresholds XML,
@SelectedAspects XML = NULL,
@IsXmlMode BIT = 0,
@OutputXml XML = NULL OUT
AS
BEGIN
BEGIN TRY
DECLARE @Config XML
DECLARE @OptionsXml XML
DECLARE @ThresholdsTransforms XML
SELECT @OptionsXml=vgr.Options
FROM [dbo].[Veeam_GRL_Report] vgr
WHERE vgr.ReportSystemName=@ReportSystemName
SET @Config = @OptionsXml.query('/Options[1]/PerfAnalysisConfig[1]')
SET @ThresholdsTransforms = @OptionsXml.query('/Options[1]/ThresholdsTransforms[1]')
IF OBJECT_ID('tempdb..#thresholds') IS NOT NULL DROP TABLE #thresholds
INSERT INTO #thresholds
(
TargetId,
AspectId,
InputUnit,
OriginalValue,
[Value]
)
SELECT
TargetId = f.t.value('@TargetId','nvarchar(64)'),
AspectId = f.t.value('@AspectId','nvarchar(64)'),
InputUnit = f.t.value('@InputUnit','nvarchar(64)'),
OriginalValue = t.i.value('@Value','float'),
[Value] = t.i.value('@Value','float')*f.t.value('@Multiplier','float')
FROM
@Thresholds.nodes('/Thresholds/Threshold') AS t(i)
INNER JOIN @ThresholdsTransforms.nodes('/ThresholdsTransforms/ThresholdsTransform') AS f(t)
ON t.i.value('@Id','int') = f.t.value('@ThresholdId','int')
LEFT OUTER JOIN @SelectedAspects.nodes('/SelectedAspects/SelectedAspect') AS s(a)
ON f.t.value('@TargetId','nvarchar(64)')=s.a.value('@TargetId','nvarchar(64)') AND f.t.value('@AspectId','nvarchar(64)')=s.a.value('@AspectId','nvarchar(64)')
WHERE
s.a.value('@AspectId','nvarchar(64)') IS NOT NULL OR @SelectedAspects IS NULL
IF @IsXmlMode=1
BEGIN
SET @OutputXml = (
SELECT
t.TargetId, t.AspectId, t.[Value]
FROM
#thresholds t
FOR XML RAW('Threshold'), ROOT('Thresholds')
)
END
ELSE
BEGIN
;WITH x (TargetId, TargetClassName, AspectId, AspectName)
AS
(
SELECT
TargetId = cfg.aspect.value('../@Id','nvarchar(64)'),
TargetClassName = vmet.ManagedEntityTypeDefaultName,
AspectId = cfg.aspect.value('@Id','nvarchar(64)'),
AspectName = cfg.aspect.value('@Name','nvarchar(max)')
FROM
@Config.nodes('/PerfAnalysisConfig/Target/Aspect') AS cfg(aspect)
INNER JOIN [dbo].[vManagedEntityType] vmet ON vmet.ManagedEntityTypeSystemName=cfg.aspect.value('../@ManagedEntityTypeSystemName','nvarchar(512)')
)
SELECT
x.TargetClassName,
x.AspectName,
t.InputUnit,
t.OriginalValue,
t.[Value]
FROM
#thresholds t
INNER JOIN x x ON x.TargetId = t.TargetId AND x.AspectId = t.AspectId
END
IF OBJECT_ID('tempdb..#thresholds') IS NOT NULL DROP TABLE #thresholds
END TRY
BEGIN CATCH
IF OBJECT_ID('tempdb..#thresholds') IS NOT NULL DROP TABLE #thresholds
DECLARE @errMsg VARCHAR(1024)
SET @errMsg = ERROR_MESSAGE()
RAISERROR(@errMsg, 16, 1)
END CATCH
END
GO
GRANT EXECUTE ON dbo.[Veeam_VE_Widget_ThresholdsTransformedGet] TO OpsMgrReader
GO
-- ##### Veeam_VE_HyperV_PerformanceForecastGet_CreateAlter.sql
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [schema_id]=SCHEMA_ID('dbo') AND type = 'P' AND name = 'Veeam_VE_Widget_PerformanceForecastGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Veeam_VE_Widget_PerformanceForecastGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Veeam_VE_Widget_PerformanceForecastGet]
@StartDate DATETIME,
@EndDate DATETIME,
@ReportSystemName NVARCHAR(256),
@ObjectList XML,
@RawThresholds XML,
@SelectedAspects XML = NULL,
@ThresholdOverrides XML = NULL,
@IsXmlMode BIT = 0,
@OutputXml XML = NULL OUT
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
IF GETUTCDATE() BETWEEN @StartDate AND @EndDate
AND GETUTCDATE()<@EndDate
BEGIN
DECLARE @Config XML
DECLARE @OptionsXml XML
SELECT @OptionsXml=vgr.Options
FROM [dbo].[Veeam_GRL_Report] vgr
WHERE vgr.ReportSystemName=@ReportSystemName
SET @Config = @OptionsXml.query('/Options[1]/PerfAnalysisConfig[1]')
IF OBJECT_ID('tempdb..#target') IS NOT NULL DROP TABLE #target
IF OBJECT_ID('tempdb..#aspect') IS NOT NULL DROP TABLE #aspect
IF OBJECT_ID('tempdb..#targetObj') IS NOT NULL DROP TABLE #targetObj
IF OBJECT_ID('tempdb..#objAspect') IS NOT NULL DROP TABLE #objAspect
IF OBJECT_ID('tempdb..#objSummary') IS NOT NULL DROP TABLE #objSummary
IF OBJECT_ID('tempdb..#objAspectPerformance') IS NOT NULL DROP TABLE #objAspectPerformance
CREATE TABLE #target
(
TargetRowId INT IDENTITY PRIMARY KEY,
TargetId NVARCHAR(64) NOT NULL,
ManagedEntityTypeSystemName NVARCHAR(512) NOT NULL,
ManagedEntityTypeRowId INT NOT NULL
)
CREATE TABLE #aspect
(
AspectRowId INT IDENTITY PRIMARY KEY,
TargetRowId INT NOT NULL,
TargetId NVARCHAR(64) NOT NULL,
AspectId NVARCHAR(64) NOT NULL,
AspectName NVARCHAR(MAX) NOT NULL,
Position INT NULL,
IsPropertyBased BIT NOT NULL,
Containment NVARCHAR(16) NOT NULL,
InstanceAggregationFunction NVARCHAR(8) NOT NULL,
RuleSystemName NVARCHAR(512) NOT NULL,
RuleRowId INT NOT NULL,
RuleGUID UNIQUEIDENTIFIER NOT NULL,
-- parse thresholds
UPDATE a
SET a.Threshold = thrs.thr.value('@Value','float')
FROM
@Thresholds.nodes('/Thresholds/Threshold') AS thrs(thr)
INNER JOIN #aspect a ON a.TargetId=thrs.thr.value('@TargetId','nvarchar(64)')
AND a.AspectId=thrs.thr.value('@AspectId','nvarchar(64)')
-- remove:
-- * aspects that do not have thresholds
-- * property-based aspects that do not have property assignment
IF (@ThresholdOverrides IS NULL)
BEGIN
DELETE a
FROM #aspect a
WHERE a.Threshold IS NULL OR (a.IsPropertyBased=1 AND a.PropertyGuid IS NULL)
END
-- get objects within the scope for each analysis target
DECLARE @TargetRowId INT, @ManagedEntityTypeRowId INT, @Objects XML
DECLARE trgtCur CURSOR FOR SELECT t.TargetRowId, t.ManagedEntityTypeRowId FROM #target t
OPEN trgtCur
FETCH trgtCur INTO @TargetRowId, @ManagedEntityTypeRowId
WHILE @@FETCH_STATUS=0
BEGIN
EXEC [dbo].[Veeam_GRL_ObjectListGet]
@StartDate = @StartDate,
@EndDate = @EndDate,
@ObjectList = @ObjectList,
@ManagedEntityTypeRowId = @ManagedEntityTypeRowId,
@XmlOutputOnly = 1,
@OutputXml = @Objects OUT
IF @Objects IS NOT NULL
BEGIN
INSERT INTO #targetObj
(
TargetRowId,
ManagedEntityRowId
)
SELECT
@TargetRowId,
O.o.value('.','int')
FROM
@Objects.nodes('/Objects/Object/ManagedEntityRowId') AS O(o)
END
SET @Objects=NULL
FETCH trgtCur INTO @TargetRowId, @ManagedEntityTypeRowId
END
CLOSE trgtCur
DEALLOCATE trgtCur
-- remove objects that do not have active configuration
DELETE FROM #targetObj
WHERE ManagedEntityRowId NOT IN
(
SELECT DISTINCT
o.ManagedEntityRowId
FROM
#targetObj o
INNER JOIN [dbo].[vManagedEntityProperty] vmep ON vmep.ManagedEntityRowId = o.ManagedEntityRowId
WHERE vmep.ToDateTime IS NULL
)
-- get aspect-object pairs with chart numbers - will be used to generate ObjectList for PerfGet SP
INSERT INTO #objAspect
(
ManagedEntityRowId,
AspectRowId,
PerfChartNo
)
SELECT
o.ManagedEntityRowId,
a.AspectRowId,
PerfChartNo = ROW_NUMBER() OVER (ORDER BY a.TargetRowId, a.AspectRowId)
FROM
#targetObj o
INNER JOIN #aspect a ON a.TargetRowId = o.TargetRowId
-- get actual property values
UPDATE oa
SET
PropertyValue = CAST(vmeps.PropertyValue AS FLOAT)*ISNULL(a.PropertyMultiplier,1)
FROM
#objAspect oa
INNER JOIN #aspect a ON a.AspectRowId = oa.AspectRowId
INNER JOIN [dbo].[vManagedEntityPropertySet] vmeps ON vmeps.ManagedEntityRowId = oa.ManagedEntityRowId AND vmeps.PropertyGuid = a.PropertyGuid
WHERE
a.PropertyGuid IS NOT NULL
AND vmeps.ToDateTime IS NULL
AND ISNUMERIC(vmeps.PropertyValue)=1
-- [bookmark]
-- set thresholds for each object-aspect pair
UPDATE oa
SET
ThresholdValue = CASE a.IsPropertyBased WHEN 1 THEN oa.PropertyValue*a.Threshold ELSE a.Threshold END
FROM
#objAspect oa
INNER JOIN #aspect a ON a.AspectRowId = oa.AspectRowId
-- apply threshold overrides
IF @ThresholdOverrides IS NOT NULL
BEGIN
UPDATE oa
SET
ThresholdValue = t.ov.value('@Value','float')
FROM
#objAspect oa
INNER JOIN #aspect a ON a.AspectRowId = oa.AspectRowId
INNER JOIN @ThresholdOverrides.nodes('/ThresholdOverrides/ThresholdOverride') t(ov) ON
a.TargetId = t.ov.value('@TargetId','nvarchar(64)')
AND a.AspectId = t.ov.value('@AspectId','nvarchar(64)')
AND oa.ManagedEntityRowId = t.ov.value('@ManagedEntityRowId','int')
END
-- get performance data & trends
DECLARE @PerfRequestConfig XML,
@PerfResponse XML
SET @PerfRequestConfig =
(
SELECT
[Value/Object/@Use] = a.Containment,
[Value/Object] = ao.ManagedEntityRowId,
[Value/Rule] = a.RuleGUID
FROM
#aspect a
INNER JOIN #objAspect ao ON ao.AspectRowId = a.AspectRowId
ORDER BY ao.PerfChartNo
FOR XML PATH('Values'), ROOT('Data')
)
-- get trend data for object aspect
UPDATE ao
SET
LastValueNo = perf.trend.value('@LastValueNo','int'),
LastDateTime = perf.trend.value('@LastDateTime','datetime'),
a = CASE a.InstanceAggregationFunction
WHEN 'Avg' THEN perf.trend.value('@a_avg','float')
WHEN 'SumAvg' THEN perf.trend.value('@a_sumavg','float')
WHEN 'Min' THEN perf.trend.value('@a_min','float')
WHEN 'Max' THEN perf.trend.value('@a_max','float')
WHEN 'Sum' THEN perf.trend.value('@a_sum','float')
ELSE NULL
END * a.PerformanceDataMultiplier,
b = CASE a.InstanceAggregationFunction
WHEN 'Avg' THEN perf.trend.value('@b_avg','float')
WHEN 'SumAvg' THEN perf.trend.value('@b_sumavg','float')
WHEN 'Min' THEN perf.trend.value('@b_min','float')
WHEN 'Max' THEN perf.trend.value('@b_max','float')
WHEN 'Sum' THEN perf.trend.value('@b_sum','float')
ELSE NULL
END * a.PerformanceDataMultiplier,
-- We assume that normal distribution works and the number of measurements is >5,
-- so for Pr=90% t~=1.96
ConfidenceInterval = CASE a.InstanceAggregationFunction
WHEN 'Avg' THEN perf.trend.value('@stdev_avg','float')
WHEN 'SumAvg' THEN perf.trend.value('@stdev_sumavg','float')
WHEN 'Min' THEN perf.trend.value('@stdev_min','float')
WHEN 'Max' THEN perf.trend.value('@stdev_max','float')
WHEN 'Sum' THEN perf.trend.value('@stdev_sum','float')
ELSE NULL
END * 1.96 * a.PerformanceDataMultiplier
FROM
#aspect a
INNER JOIN #objAspect ao ON ao.AspectRowId = a.AspectRowId
INNER JOIN @PerfResponse.nodes('/Performance/Trend/TrendCoef') AS perf(trend)
ON ao.PerfChartNo=perf.trend.value('@ChartNo','int')
AND perf.trend.value('@RowType','char(1)')='G'
WHERE
ao.ThresholdValue IS NOT NULL -- we do not need performance data if we don't have a valid threshold
---- get performance data for object aspect
IF OBJECT_ID('tempdb..#perfpoint') IS NOT NULL DROP TABLE #perfpoint
CREATE TABLE #perfpoint
(
ChartNo INT,
RowType CHAR(1),
[DateTime] DATETIME,
ValueNo BIGINT,
SampleCount FLOAT,
AverageValue FLOAT,
SumAvgValue FLOAT,
MinValue FLOAT,
MaxValue FLOAT,
SumValue FLOAT,
TrendValue_Avg FLOAT,
TrendValue_SumAvg FLOAT,
TrendValue_Min FLOAT,
TrendValue_Max FLOAT,
TrendValue_Sum FLOAT,
MeasurementUnitName NVARCHAR(256),
StandalonePointInd TINYINT,
OptionXml NVARCHAR(MAX)
)
-- select only necessary ones
INSERT INTO #objAspectPerformance
(
AspectObjectRowId,
[DateTime],
ValueNo,
[Value],
TrendValue,
MeasurementUnitName,
StandalonePointInd,
OptionXml
)
SELECT
ao.AspectObjectRowId,
pp.[DateTime],
pp.ValueNo,
a.PerformanceDataMultiplier * (
CASE WHEN pp.SampleCount=0 THEN NULL ELSE
CASE a.InstanceAggregationFunction
WHEN 'Avg' THEN pp.AverageValue
WHEN 'SumAvg' THEN pp.SumAvgValue
WHEN 'Min' THEN pp.MinValue
WHEN 'Max' THEN pp.MaxValue
WHEN 'Sum' THEN pp.SumValue
ELSE NULL
END
END
),
a.PerformanceDataMultiplier * (
CASE a.InstanceAggregationFunction
WHEN 'Avg' THEN pp.TrendValue_Avg
WHEN 'SumAvg' THEN pp.TrendValue_SumAvg
WHEN 'Min' THEN pp.TrendValue_Min
WHEN 'Max' THEN pp.TrendValue_Max
WHEN 'Sum' THEN pp.TrendValue_Sum
ELSE NULL
END
),
pp.MeasurementUnitName,
pp.StandalonePointInd,
CAST(pp.OptionXml AS XML)
FROM
#aspect a
INNER JOIN #objAspect ao ON ao.AspectRowId = a.AspectRowId
INNER JOIN #perfpoint pp
ON ao.PerfChartNo=pp.ChartNo
AND pp.RowType='G'
WHERE
ao.ThresholdValue IS NOT NULL -- we do not need performance data if we don't have a valid threshold
IF OBJECT_ID('tempdb..#perfpoint') IS NOT NULL DROP TABLE #perfpoint
---- / get performance data for object aspect
-- calculate number of performance points per object aspect
UPDATE oa
SET
PointsCount=ISNULL(x.cnt,0)
FROM
#objAspect oa
LEFT OUTER JOIN
(
SELECT oap.AspectObjectRowId, cnt = COUNT(1) FROM #objAspectPerformance oap WHERE oap.[Value] IS NOT NULL GROUP BY oap.AspectObjectRowId
) x ON oa.AspectObjectRowId=x.AspectObjectRowId
-- calculate trend confidence interval boundaries.
UPDATE aop
SET
TrendValueLower = aop.TrendValue-ao.ConfidenceInterval,
TrendValueUpper = aop.TrendValue+ao.ConfidenceInterval
FROM
#objAspectPerformance aop
INNER JOIN #objAspect ao ON ao.AspectObjectRowId = aop.AspectObjectRowId
WHERE ao.PointsCount>0
-- calculate current and target points
UPDATE oa
SET
oa.CurrentValueNo = oa.LastValueNo+DATEDIFF(DAY,oa.LastDateTime,GETUTCDATE())-1,
oa.TargetValueNo = oa.LastValueNo+DATEDIFF(DAY,oa.LastDateTime,@EndDate)-1
FROM
#objAspect oa
-- predict when threshold will be hit
;WITH xdays (AspectObjectRowId, XDayMin, XDay, XDayMax, IsPastOkay, DaysToReportEnd, PredictedValueWorst, PredictedValue, PredictedValueBest)
AS
(
SELECT
oa.AspectObjectRowId,
XDayMin = CAST((CASE WHEN ISNULL(oa.b,0)=0 THEN NULL ELSE (oa.ThresholdValue-oa.a-oa.ConfidenceInterval*SIGN(oa.b))/oa.b END)-oa.CurrentValueNo AS NUMERIC(38,4)),
XDay = CAST((CASE WHEN ISNULL(oa.b,0)=0 THEN NULL ELSE (oa.ThresholdValue-oa.a)/oa.b END)-oa.CurrentValueNo AS NUMERIC(38,4)),
XDayMax = CAST((CASE WHEN ISNULL(oa.b,0)=0 THEN NULL ELSE (oa.ThresholdValue-oa.a+oa.ConfidenceInterval*SIGN(oa.b))/oa.b END)-oa.CurrentValueNo AS NUMERIC(38,4)),
-- also used to check for "reverse hit"
IsPastOkay = CASE WHEN (oa.b<0 AND a.IsFloorThreshold=0) OR (oa.b>0 AND a.IsFloorThreshold=1) THEN 1 ELSE 0 END,
DaysToReportEnd = DATEDIFF(DAY,GETUTCDATE(),@EndDate),
PredictedValueWorst = oa.a + oa.b*CAST(oa.TargetValueNo AS FLOAT)+oa.ConfidenceInterval*SIGN(oa.b),
PredictedValue = oa.a + oa.b*CAST(oa.TargetValueNo AS FLOAT),
PredictedValueBest = oa.a + oa.b*CAST(oa.TargetValueNo AS FLOAT)-oa.ConfidenceInterval*SIGN(oa.b)
FROM
#objAspect oa
INNER JOIN #aspect a ON a.AspectRowId = oa.AspectRowId
)
UPDATE oa
SET
oa.DaysTillThresholdMin = CASE WHEN x.XDayMin>=0 AND x.IsPastOkay=0 THEN x.XDayMin
WHEN x.XDayMin<0 AND x.IsPastOkay=0 THEN 0
ELSE NULL END ,
oa.DaysTillThreshold = CASE WHEN x.XDay>=0 AND x.IsPastOkay=0 THEN x.XDay
WHEN x.XDay<0 AND x.IsPastOkay=0 THEN 0
ELSE NULL END ,
oa.DaysTillThresholdMax = CASE WHEN x.XDayMax>=0 AND x.IsPastOkay=0 THEN x.XDayMax
WHEN x.XDayMax<0 AND x.IsPastOkay=0 THEN 0
ELSE NULL END ,
oa.IsAchieved = CASE WHEN (x.XDay<0 AND x.IsPastOkay=0) OR (x.XDay>=0 AND x.IsPastOkay=1) THEN 1
WHEN isnull(oa.b,0)=0 AND ((oa.a<=oa.ThresholdValue AND a.IsFloorThreshold=1) OR (oa.a>=oa.ThresholdValue AND a.IsFloorThreshold=0)) THEN 1
WHEN x.IsPastOkay=1 AND x.XDayMax>x.DaysToReportEnd THEN 1 -- performance will not be back to normal in the worst cases
ELSE 0 END,
oa.WillBeAchievedMin = CASE WHEN x.XDayMin < x.DaysToReportEnd AND x.IsPastOkay=0 THEN 1 ELSE 0 END,
oa.WillBeAchieved = CASE WHEN x.XDay BETWEEN 1 AND x.DaysToReportEnd AND x.IsPastOkay=0 THEN 1 ELSE 0 END,
oa.WillBedAchievedMax = CASE WHEN x.XDayMax BETWEEN 1 AND x.DaysToReportEnd AND x.IsPastOkay=0 THEN 1 ELSE 0 END,
oa.PredictedValueWorst = x.PredictedValueWorst,
oa.PredictedValue = x.PredictedValue,
oa.PredictedValueBest = x.PredictedValueBest,
oa.AvailableResourcesWorst = (CASE WHEN a.IsFloorThreshold=1 THEN -1 ELSE 1 END)*oa.ThresholdValue + (CASE WHEN a.IsFloorThreshold=1 THEN 1 ELSE -1 END)*x.PredictedValueWorst,
oa.AvailableResources = (CASE WHEN a.IsFloorThreshold=1 THEN -1 ELSE 1 END)*oa.ThresholdValue + (CASE WHEN a.IsFloorThreshold=1 THEN 1 ELSE -1 END)*x.PredictedValue,
oa.AvailableResourcesBest = (CASE WHEN a.IsFloorThreshold=1 THEN -1 ELSE 1 END)*oa.ThresholdValue + (CASE WHEN a.IsFloorThreshold=1 THEN 1 ELSE -1 END)*x.PredictedValueBest
FROM
#objAspect oa
INNER JOIN #aspect a ON a.AspectRowId = oa.AspectRowId
INNER JOIN xdays x ON oa.AspectObjectRowId=x.AspectObjectRowId
-- fix some corner cases for available resources
UPDATE oa
SET
AvailableResourcesWorst = CASE WHEN oa.PropertyValue-oa.AvailableResourcesWorst<0 AND oa.AvailableResourcesWorst>0 THEN oa.PropertyValue ELSE oa.AvailableResourcesWorst END,
AvailableResources = CASE WHEN oa.PropertyValue-oa.AvailableResources<0 AND oa.AvailableResources>0 THEN oa.PropertyValue ELSE oa.AvailableResources END,
AvailableResourcesBest = CASE WHEN oa.PropertyValue-oa.AvailableResourcesBest<0 AND oa.AvailableResourcesBest>0 THEN oa.PropertyValue ELSE oa.AvailableResourcesBest END
FROM
#objAspect oa
INNER JOIN #aspect a ON a.AspectRowId = oa.AspectRowId
WHERE
oa.PropertyValue IS NOT NULL
UPDATE oa
SET
AvailableResourcesWorst = CASE WHEN oa.ThresholdValue-oa.AvailableResourcesWorst<0 AND oa.AvailableResourcesWorst>0 THEN oa.ThresholdValue ELSE oa.AvailableResourcesWorst END,
AvailableResources = CASE WHEN oa.ThresholdValue-oa.AvailableResources<0 AND oa.AvailableResources>0 THEN oa.ThresholdValue ELSE oa.AvailableResources END,
AvailableResourcesBest = CASE WHEN oa.ThresholdValue-oa.AvailableResourcesBest<0 AND oa.AvailableResourcesBest>0 THEN oa.ThresholdValue ELSE oa.AvailableResourcesBest END
FROM
#objAspect oa
INNER JOIN #aspect a ON a.AspectRowId = oa.AspectRowId
WHERE
a.IsFloorThreshold=0 AND oa.PropertyValue IS NULL
UPDATE #objAspect
SET
AvailableResourcesWorst = AvailableResourcesBest,
AvailableResourcesBest = AvailableResourcesWorst,
PredictedValueWorst = PredictedValueBest,
PredictedValueBest = PredictedValueWorst
WHERE AvailableResourcesWorst>AvailableResourcesBest
-- Generate recommendations
UPDATE oa
SET
Recommendation = REPLACE(a.RecommendationTemplate,'%1',CONVERT(NVARCHAR(MAX),ROUND(ABS(oa.AvailableResourcesWorst),2)))
FROM
#objAspect oa
INNER JOIN #aspect a ON a.AspectRowId = oa.AspectRowId
WHERE
oa.AvailableResourcesWorst<0
;WITH rec (AspectRowId, RecommendationOverallValue)
AS
(
SELECT
a.AspectRowId,
RecommendationOverallValue = SUM(ROUND(ABS(ao.AvailableResourcesWorst),2))
FROM
#aspect a
INNER JOIN #objAspect ao ON ao.AspectRowId = a.AspectRowId
WHERE ao.AvailableResourcesWorst<0 AND (ao.ThresholdValue>0 OR a.SkipOverallRecommendationsIfThresholdIsZero=0)
GROUP BY a.AspectRowId
)
UPDATE a
SET
a.RecommendationOverall = REPLACE(a.RecommendationTemplateOverall,'%1',CONVERT(NVARCHAR(MAX),r.RecommendationOverallValue))
FROM
#aspect a
INNER JOIN rec r ON r.AspectRowId = a.AspectRowId
-- get per-object infrastructure summary
DECLARE @summary XML
EXEC [dbo].[Veeam_GRL_CurrentSummaryGetByScope] @ReportSystemName=@ReportSystemName, @ObjectList=@ObjectList, @XmlOutputOnly=1, @OutputXml=@summary OUT
INSERT INTO #objSummary
(
GroupId,
ElementId,
ManagedEntityRowId,
GroupName,
ElementName,
[Value]
)
SELECT
s.n.value('@GroupId','int'),
s.n.value('@ElementId','int'),
s.n.value('@ManagedEntityRowId','int'),
s.n.value('@GroupName','nvarchar(max)'),
s.n.value('@ElementName','nvarchar(max)'),
s.n.value('@Value','float')
FROM @summary.nodes('/Summary/Element') AS s(n)
;WITH po (ManagedEntityRowId)
AS
(
SELECT oa.ManagedEntityRowId
FROM #objAspect oa
WHERE
oa.IsAchieved=1
OR oa.WillBeAchievedMin=1
OR oa.WillBeAchieved=1
OR oa.WillBedAchievedMax=1
)
UPDATE os
SET IsObjectProblematic = 1
FROM
#objSummary os
INNER JOIN po ON po.ManagedEntityRowId = os.ManagedEntityRowId
---- final recordset
IF OBJECT_ID('tempdb..#ForecastRes') IS NOT NULL DROP TABLE #ForecastRes
-- totals
;WITH ca (AspectObjectRowId)
AS
(
SELECT
r.AspectObjectRowId
FROM
(
SELECT
oa.AspectObjectRowId,
RowNo = ROW_NUMBER() OVER ( PARTITION BY a.TargetId, oa.ManagedEntityRowId ORDER BY oa.DaysTillThreshold )
FROM
#objAspect oa
INNER JOIN #aspect a ON a.AspectRowId = oa.AspectRowId
WHERE oa.DaysTillThreshold IS NOT NULL OR oa.IsAchieved=1
) r
WHERE
r.RowNo=1
PredictedValueWorst = ao.PredictedValueWorst,
PredictedValue = ao.PredictedValue,
PredictedValueBest = ao.PredictedValueBest,
AvailableResourcesWorst = CASE WHEN a.SuppressResourceOutput=1 THEN NULL ELSE ao.AvailableResourcesWorst END,
AvailableResources = CASE WHEN a.SuppressResourceOutput=1 THEN NULL ELSE ao.AvailableResources END,
AvailableResourcesBest = CASE WHEN a.SuppressResourceOutput=1 THEN NULL ELSE ao.AvailableResourcesBest END,
DaysTillThresholdMin = ao.DaysTillThresholdMin,
DaysTillThreshold = ao.DaysTillThreshold,
DaysTillThresholdMax = ao.DaysTillThresholdMax,
IsAchieved = ao.IsAchieved,
WillBeAchievedMin = ao.WillBeAchievedMin,
WillBeAchieved = ao.WillBeAchieved,
WillBeAchievedMax = ao.WillBedAchievedMax,
PointsCount = ao.PointsCount,
[DateTime] = NULL,
Value = NULL,
TrendValueLower = NULL,
TrendValue = NULL,
TrendValueUpper = NULL,
MeasurementUnitName = a.MeasurementUnitName,
StandalonePointInd = NULL,
OptionXml = NULL,
Recommendation = ao.Recommendation,
IsConstraining = CAST((CASE WHEN ca.AspectObjectRowId IS NOT NULL THEN 1 ELSE 0 END) AS BIT),
ThresholdCalcDesc = a.ThresholdCalcDesc,
OriginalThreshold = a.Threshold
FROM
#objAspect ao
INNER JOIN #aspect a ON a.AspectRowId = ao.AspectRowId
INNER JOIN [dbo].[vManagedEntity] vme ON vme.ManagedEntityRowId = ao.ManagedEntityRowId
INNER JOIN [dbo].[vManagedEntityType] vmet ON vmet.ManagedEntityTypeRowId = vme.ManagedEntityTypeRowId
LEFT OUTER JOIN [dbo].[vManagedEntityTypeImage] vmeti ON vmeti.ManagedEntityTypeRowId = vmet.ManagedEntityTypeRowId AND vmeti.ImageCategory = N'u16x16Icon'
LEFT OUTER JOIN ca ca ON ca.AspectObjectRowId = ao.AspectObjectRowId
UNION ALL
-- Overall Recommendations
SELECT
RowType = 'R',
TargetRowId = a.TargetRowId,
AspectRowId = a.AspectRowId,
a.AspectId,
ManagedEntityRowId = NULL,
[Path] = NULL,
ManagedEntityDisplayName = NULL,
ManagedEntityTypeDefaultName = vmet.ManagedEntityTypeDefaultName,
[Image] = NULL,
AspectObjectRowId = NULL,
AspectName = a.AspectName,
IsPropertyBased = a.IsPropertyBased,
PropertyValue = NULL,
ThresholdValue = NULL,
IF @IsXmlMode=0
BEGIN
SELECT * FROM #ForecastRes
ORDER BY RowType, ManagedEntityRowId, [DateTime]
END
ELSE
BEGIN
SET @OutputXml = (
SELECT
fr.RowType, fr.TargetRowId, fr.AspectRowId, fr.AspectId,
fr.ManagedEntityRowId, fr.AspectObjectRowId,
fr.AspectName, fr.IsPropertyBased, fr.PropertyValue,
fr.ThresholdValue, fr.PredictedValueWorst,
fr.PredictedValue, fr.PredictedValueBest,
fr.AvailableResourcesWorst, fr.AvailableResources,
fr.AvailableResourcesBest, fr.DaysTillThresholdMin,
fr.DaysTillThreshold, fr.DaysTillThresholdMax,
fr.IsAchieved, fr.WillBeAchievedMin, fr.WillBeAchieved,
fr.WillBeAchievedMax, fr.PointsCount, fr.[DateTime],
fr.[Value], fr.TrendValueLower, fr.TrendValue,
fr.TrendValueUpper, fr.MeasurementUnitName,
fr.StandalonePointInd, fr.OptionXml, fr.Recommendation,
fr.IsConstraining, fr.ThresholdCalcDesc, fr.OriginalThreshold
FROM #ForecastRes fr
FOR XML RAW('ForecastRow'),ROOT('Forecast')
)
END
IF OBJECT_ID('tempdb..#target') IS NOT NULL DROP TABLE #target
IF OBJECT_ID('tempdb..#aspect') IS NOT NULL DROP TABLE #aspect
IF OBJECT_ID('tempdb..#targetObj') IS NOT NULL DROP TABLE #targetObj
IF OBJECT_ID('tempdb..#objAspect') IS NOT NULL DROP TABLE #objAspect
IF OBJECT_ID('tempdb..#objSummary') IS NOT NULL DROP TABLE #objSummary
IF OBJECT_ID('tempdb..#objAspectPerformance') IS NOT NULL DROP TABLE #objAspectPerformance
END
END TRY
BEGIN CATCH
IF OBJECT_ID('tempdb..#target') IS NOT NULL DROP TABLE #target
IF OBJECT_ID('tempdb..#aspect') IS NOT NULL DROP TABLE #aspect
IF OBJECT_ID('tempdb..#targetObj') IS NOT NULL DROP TABLE #targetObj
IF OBJECT_ID('tempdb..#objAspect') IS NOT NULL DROP TABLE #objAspect
IF OBJECT_ID('tempdb..#objSummary') IS NOT NULL DROP TABLE #objSummary
IF OBJECT_ID('tempdb..#objAspectPerformance') IS NOT NULL DROP TABLE #objAspectPerformance
DECLARE @errMsg VARCHAR(1024)
SET @errMsg = ERROR_MESSAGE()
RAISERROR(@errMsg, 16, 1)
END CATCH
END
GO
GRANT EXECUTE ON dbo.[Veeam_VE_Widget_PerformanceForecastGet] TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [schema_id]=SCHEMA_ID('sdk') AND type = 'P' AND name = 'Veeam_VE_Widget_PerformanceForecastGet')
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.[Veeam_VE_Widget_PerformanceForecastGet] AS RETURN 1')
END
GO
ALTER PROCEDURE sdk.[Veeam_VE_Widget_PerformanceForecastGet]
@Objects XML,
@SelectedAspect XML,
@DateFrom DATETIME,
@Horisont DATETIME,
@mg UNIQUEIDENTIFIER
AS
BEGIN
BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRY
IF OBJECT_ID('tempdb..#lic') IS NOT NULL DROP TABLE #lic
IF OBJECT_ID('tempdb..#objV') IS NOT NULL DROP TABLE #objV
IF OBJECT_ID('tempdb..#rul') IS NOT NULL DROP TABLE #rul
IF OBJECT_ID('tempdb..#Forecast') IS NOT NULL DROP TABLE #Forecast
CREATE TABLE #lic
(
[Message] NVARCHAR(MAX),
[LinkLabel] NVARCHAR(MAX),
[LinkURL] NVARCHAR(MAX)
)
INSERT INTO #lic
(
[Message], LinkLabel, LinkURL
)
EXEC dbo.[Veeam_CheckServiceAvailability]
@mg,
'WD'
DECLARE @xmlout XML
IF EXISTS(SELECT * FROM #lic)
BEGIN
SELECT
'False' AS '@Succeed',
lic.[Message] +'<a href="'+ lic.[LinkURL]+'">'+lic.[LinkLabel] +'</a>'AS '@Message'
FROM #lic AS lic
FOR XML PATH('LicenseCheck'),ROOT('Forecast')
END
ELSE
BEGIN
CREATE TABLE #rul
(
Id INT IDENTITY(1,1) NOT NULL,
[GUID] UNIQUEIDENTIFIER,
RuleId INT,
RuleSystemName NVARCHAR(MAX),
RuleDefaultName NVARCHAR(MAX),
ObjectName NVARCHAR(MAX),
Threshold NVARCHAR(MAX),
IsFloorThreshold NVARCHAR(MAX),
entityTypeName NVARCHAR(MAX)
)
DECLARE @ObjectList XML,
@Aspects XML,
@Threshold XML
INSERT INTO #objV
(
[Guid],
[Object],
entityTypeId
)
SELECT
EntityId.id.value('.', 'UNIQUEIDENTIFIER'),
vme.ManagedEntityRowId,
vme.ManagedEntityTypeRowId
FROM @Objects.nodes('Objects/Object') AS EntityId(id)
INNER JOIN dbo.vManagedEntity AS vme ON vme.ManagedEntityGuid = EntityId.id.value('.', 'UNIQUEIDENTIFIER')
INSERT INTO #rul
(
[Guid],
RuleId,
RuleSystemName,
RuleDefaultName,
ObjectName,
Threshold,
IsFloorThreshold,
entityTypeName
)
SELECT
DISTINCT(aspect.id.value('@AspectId[1]', 'UNIQUEIDENTIFIER')),
vr.RuleRowId,
vr.RuleSystemName,
vr.RuleDefaultName,
aspect.id.value('@ObjectName[1]', 'NVARCHAR(MAX)'),
aspect.id.value('@Threshold[1]', 'NVARCHAR(MAX)'),
aspect.id.value('@IsFloorThreshold[1]', 'NVARCHAR(MAX)'),
vmet.ManagedEntityTypeSystemName
FROM @SelectedAspect.nodes('SelectedAspects/SelectedAspect') AS aspect(id)
INNER JOIN dbo.vRule AS vr ON vr.RuleGuid = aspect.id.value('@AspectId[1]', 'UNIQUEIDENTIFIER')
INNER JOIN dbo.vPerformanceRuleInstance AS vpri ON vpri.RuleRowId=vr.RuleRowId
INNER JOIN perf.vPerfDaily AS vph ON vph.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
INNER JOIN dbo.vManagedEntity AS vme ON vph.ManagedEntityRowId=vme.ManagedEntityRowId
INNER JOIN dbo.vManagedEntityType AS vmet ON vmet.ManagedEntityTypeRowId=vme.ManagedEntityTypeRowId
INNER JOIN #objV AS o ON o.entityTypeId=vmet.ManagedEntityTypeRowId
WHERE vph.[DateTime] BETWEEN @DateFrom AND @Horisont
SET @ObjectList =
(
SELECT
'Containment' AS 'Object/@Use',
[Object]
FROM #objV
FOR XML PATH('Objects') , ROOT('Data'),ELEMENTS
)
SET @Aspects =
(
SELECT
r.ObjectName AS '@TargetId',
r.RuleSystemName AS '@AspectId'
FROM #rul AS r
FOR XML PATH('SelectedAspect') , ROOT('SelectedAspects'),ELEMENTS
)
SET @Threshold =
(
SELECT
r.Id AS '@Id',
r.Threshold AS '@Value'
FROM #rul AS r
FOR XML PATH('Threshold') , ROOT('Thresholds'),ELEMENTS
)
DECLARE @Options XML
SET @Options =
(
SELECT (
SELECT
r.ObjectName AS '@Id',
r.entityTypeName AS '@ManagedEntityTypeSystemName',
(
SELECT
r1.RuleSystemName AS '@Id',
r1.RuleSystemName AS '@RuleSystemName',
'Containment' AS '@Containment',
'false' AS '@IsPropertyBased',
'NAME' AS '@Name',
'SumAvg' AS '@InstanceAggregationFunction',
r1.IsFloorThreshold AS '@IsFloorThreshold',
r1.RuleSystemName AS '@PropertyName',
'1' AS '@PropertyMultiplier',
'1' AS '@PerformanceDataMultiplier',
'%1' AS '@RecommendationTemplate',
'%1' AS '@RecommendationTemplateOverall'--,
FROM #rul AS r1
FOR XML PATH('Aspect') , TYPE
)
FROM #rul AS r
group by r.ObjectName, r.entityTypeName
FOR XML PATH('Target') , ROOT('PerfAnalysisConfig'), TYPE
),
(
SELECT
r.ObjectName AS 'ThresholdsTransform/@TargetId',
r.RuleSystemName AS 'ThresholdsTransform/@AspectId',
r.Id AS 'ThresholdsTransform/@ThresholdId',
'1' AS 'ThresholdsTransform/@Multiplier'
FROM #rul AS r
FOR XML PATH('') , ROOT('ThresholdsTransforms'), TYPE
)
FOR XML PATH('') , ROOT('Options'), ELEMENTS
)
DELETE FROM dbo.Veeam_GRL_Report WHERE ReportSystemName='Veeam.Virt.Extensions.HyperV.CapacityPlanning.Widget.Forecast'
INSERT INTO [dbo].[Veeam_GRL_Report]
(
ReportSystemName,
ReportDisplayName,
ReportDescription,
LinkedInd,
BaseReportId,
MPSystemName,
Options
)
VALUES
(
'Veeam.Virt.Extensions.HyperV.CapacityPlanning.Widget.Forecast',
'',
'',
0,
NULL,
'',
@Options
)
UPDATE fres
SET ManagedEntityRowGuid=me.ManagedEntityGuid,
ManagedEntityName=me.ManagedEntityName
FROM
(
SELECT
ManagedEntityGuid = vme.ManagedEntityGuid,
ManagedEntityRowId = vme.ManagedEntityRowId,
ManagedEntityName = vme.DisplayName
FROM #Forecast AS fr
INNER JOIN dbo.vManagedEntity AS vme ON vme.ManagedEntityRowId=fr.ManagedEntityRowId
) AS me
INNER JOIN #Forecast AS fres ON fres.ManagedEntityRowId=me.ManagedEntityRowId
UPDATE fres
SET [AspectGuid]=me.[AspectGuid],
AspectId = me.AspectDefoultname
FROM
(
SELECT
[AspectGuid] = r.[GUID],
RuleSystemName = r.RuleSystemName,
AspectDefoultname = r.RuleDefaultName
FROM #Forecast AS fr
INNER JOIN #rul AS r ON r.RuleSystemName=fr.AspectId
) AS me
INNER JOIN #Forecast AS fres ON fres.AspectId=me.RuleSystemName
SET @xmlout =
(
SELECT
(
SELECT
'True' AS '@Succeed'
FOR XML PATH('LicenseCheck') , TYPE
),
(
SELECT
fr.RowType, fr.TargetRowId, fr.AspectRowId, fr.AspectId, fr.AspectGuid, fr.ManagedEntityName,
fr.ManagedEntityRowId, fr.ManagedEntityRowGuid, fr.AspectObjectRowId,
fr.AspectName, fr.IsPropertyBased, fr.PropertyValue,
fr.ThresholdValue, fr.PredictedValueWorst,
fr.PredictedValue, fr.PredictedValueBest,
fr.AvailableResourcesWorst, fr.AvailableResources,
fr.AvailableResourcesBest, fr.DaysTillThresholdMin,
fr.DaysTillThreshold, fr.DaysTillThresholdMax,
fr.IsAchieved, fr.WillBeAchievedMin, fr.WillBeAchieved,
fr.WillBeAchievedMax, fr.PointsCount, fr.[DateTime],
fr.[Value], fr.TrendValueLower, fr.TrendValue,
fr.TrendValueUpper, fr.MeasurementUnitName,
fr.StandalonePointInd, fr.OptionXml, fr.Recommendation,
fr.IsConstraining, fr.ThresholdCalcDesc, fr.OriginalThreshold
FROM #Forecast fr
WHERE fr.RowType='A'
FOR XML RAW('ForecastRow') , TYPE
)
FOR XML PATH('') , ROOT('Forecast'), ELEMENTS
)
DELETE FROM dbo.Veeam_GRL_Report WHERE ReportSystemName='Veeam.Virt.Extensions.HyperV.CapacityPlanning.Widget.Forecast'
SELECT @xmlout
END
IF OBJECT_ID('tempdb..#lic') IS NOT NULL DROP TABLE #lic
IF OBJECT_ID('tempdb..#obj') IS NOT NULL DROP TABLE #objV
IF OBJECT_ID('tempdb..#rul') IS NOT NULL DROP TABLE #rul
IF OBJECT_ID('tempdb..#Forecast') IS NOT NULL DROP TABLE #Forecast
END TRY
BEGIN CATCH
IF OBJECT_ID('tempdb..#lic') IS NOT NULL DROP TABLE #lic
IF OBJECT_ID('tempdb..#obj') IS NOT NULL DROP TABLE #objV
IF OBJECT_ID('tempdb..#rul') IS NOT NULL DROP TABLE #rul
IF OBJECT_ID('tempdb..#Forecast') IS NOT NULL DROP TABLE #Forecast
DECLARE @errMsg VARCHAR(1024)
SET @errMsg = ERROR_MESSAGE()
RAISERROR(@errMsg, 16, 1)
END CATCH
COMMIT TRAN
END
GO
GRANT EXECUTE ON sdk.Veeam_VE_Widget_PerformanceForecastGet TO OpsMgrReader
GO
GRANT DELETE ON dbo.Veeam_GRL_Report TO OpsMgrReader
GO
GRANT INSERT ON dbo.Veeam_GRL_Report TO OpsMgrReader
GO
Home