SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Verify if the stored procedure already exists, if not create the procedure
*/
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'TheBackbone_SMP04_Report_StepSummaryDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TheBackbone_SMP04_Report_StepSummaryDataGet] AS RETURN 1')
END
GO
/*
Always give the OpsMgrReader role execute permissions, this role is used by Operations Manager to
query the DWH for reporting data. Without this permissions reports will stay empty
*/
GRANT EXECUTE ON dbo.[TheBackbone_SMP04_Report_StepSummaryDataGet] TO OpsMgrReader
GO
/*
Alter stored procedure to the correct logic
*/
ALTER PROCEDURE [dbo].[TheBackbone_SMP04_Report_StepSummaryDataGet]
@StartDate datetime,
@EndDate datetime,
@DataAggregation tinyint = 0,
@LanguageCode varchar(3) = 'ENU',
@ObjectList xml
AS
BEGIN
SET NOCOUNT ON
DECLARE @MonitorName nvarchar(255)
DECLARE @Error int
DECLARE @ExecError int
/*
Building temp table for objects and fill with data
*/
CREATE TABLE #ParentObjectList (
ManagedEntityRowId int
)
CREATE TABLE #ObjectList (
ManagedEntityRowId int,
StepOrder nvarchar(max)
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SELECT @StepOrderPropertyGuid = vMETP.PropertyGuid
FROM dbo.vManagedEntityTypeProperty vMETP,
dbo.vManagedEntityType vMET
WHERE vMET.ManagedEntityTypeSystemName = 'TheBackbone.SMP04.Perspective.Step.class' AND
vMET.ManagedEntityTypeRowId = vMETP.ManagedEntityTypeRowId AND
vMETP.PropertyDefaultName = 'Order'
INSERT INTO #ObjectList (ManagedEntityRowId, StepOrder)
SELECT vme.ManagedEntityRowId,
vMEPSO.PropertyValue
FROM dbo.vManagedEntityType as vMET
JOIN dbo.vManagedEntity vME on vME.ManagedEntityTypeRowId = vMET.ManagedEntityTypeRowId
JOIN dbo.Relationship R on vME.ManagedEntityRowId = R.TargetManagedEntityRowId
JOIN dbo.vManagedEntity vMEI on R.SourceManagedEntityRowId = vMEI.ManagedEntityRowId
JOIN dbo.vManagedEntityPropertySet vMEPSO on vMEPSO.ManagedEntityRowId = vME.ManagedEntityRowId
JOIN #ParentObjectList obj ON vMEI.ManagedEntityRowId = obj.ManagedEntityRowId
WHERE vMET.ManagedEntityTypeSystemName like 'TheBackbone.SMP04.Perspective.Step.Class'
AND vMEPSO.ToDateTime IS NULL
AND vMEPSO.PropertyGuid = @StepOrderPropertyGuid
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
/*
Building the data table for the availability data
*/
CREATE TABLE #AvailabilityStateDataTable (
DateTime datetime,
AvailabilityInRedStateMilliseconds int,
AvailabilityInYellowStateMilliseconds int,
AvailabilityInGreenStateMilliseconds int,
AvailabilityInWhiteStateMilliseconds int,
AvailabilityInDisabledStateMilliseconds int,
AvailabilityInPlannedMaintenanceMilliseconds int,
AvailabilityInUnplannedMaintenanceMilliseconds int,
AvailabilityHealthServiceUnavailableMilliseconds int,
IntervalDurationMilliseconds int,
ManagedEntityRowId int,
ManagedEntityGuid uniqueidentifier,
ManagedEntityMonitorRowId int,
Image varbinary(max),
ManagedEntityDefaultName nvarchar(max),
ManagedEntityTypeGuid uniqueidentifier,
Path nvarchar(max),
ManagementGroupGuid uniqueidentifier,
ManagementGroupDefaultName nvarchar(max),
DisplayName nvarchar(max),
StepOrder nvarchar(max)
)
/*
Retrieve availability data and store it in the datatable
*/
SET @MonitorName = N'System.Health.AvailabilityState'
IF @DataAggregation = 1
BEGIN
INSERT INTO #AvailabilityStateDataTable (
DateTime,
AvailabilityInRedStateMilliseconds,
AvailabilityInYellowStateMilliseconds,
AvailabilityInGreenStateMilliseconds,
AvailabilityInWhiteStateMilliseconds,
AvailabilityInDisabledStateMilliseconds,
AvailabilityInPlannedMaintenanceMilliseconds,
AvailabilityInUnplannedMaintenanceMilliseconds,
AvailabilityHealthServiceUnavailableMilliseconds,
IntervalDurationMilliseconds,
ManagedEntityRowId,
ManagedEntityGuid,
ManagedEntityMonitorRowId,
Image,
ManagedEntityDefaultName,
ManagedEntityTypeGuid,
Path,
ManagementGroupGuid,
ManagementGroupDefaultName,
DisplayName,
StepOrder
)
SELECT vState.DateTime, vState.InRedStateMilliseconds, vState.InYellowStateMilliseconds,
vState.InGreenStateMilliseconds, vState.InWhiteStateMilliseconds, vState.InDisabledStateMilliseconds,
vState.InPlannedMaintenanceMilliseconds, vState.InUnplannedMaintenanceMilliseconds, vState.HealthServiceUnavailableMilliseconds,
vState.IntervalDurationMilliseconds, vManagedEntity.ManagedEntityRowId, vManagedEntity.ManagedEntityGuid,
vState.ManagedEntityMonitorRowId, vManagedEntityTypeImage.Image, vManagedEntity.ManagedEntityDefaultName,
vManagedEntityType.ManagedEntityTypeGuid, vManagedEntity.Path, vManagementGroup.ManagementGroupGuid,
vManagementGroup.ManagementGroupDefaultName, ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName,
ObjectList.StepOrder
FROM vStateDailyFull as vState INNER JOIN
#ObjectList As ObjectList ON vState.ManagedEntityRowId = ObjectList.ManagedEntityRowId INNER JOIN
vManagedEntity ON vState.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId INNER JOIN
vMonitor ON vState.MonitorRowId = vMonitor.MonitorRowId INNER JOIN
vManagementGroup ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId LEFT OUTER JOIN
vManagedEntityTypeImage ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId AND
vManagedEntityTypeImage.ImageCategory = N'u16x16Icon' LEFT OUTER JOIN
vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid AND
vDisplayString.LanguageCode = @LanguageCode
WHERE (vMonitor.MonitorSystemName = @MonitorName) AND
(vState.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) AND
(vState.DateTime < DATEADD(hh, DATEPART(hh, @EndDate) + 1, convert(varchar(8), @EndDate, 112))) AND
(vState.Date BETWEEN DATEADD(day, - 1, @StartDate) AND DATEADD(day, 1, @EndDate))
END
ELSE
BEGIN
INSERT INTO #AvailabilityStateDataTable (
DateTime,
AvailabilityInRedStateMilliseconds,
AvailabilityInYellowStateMilliseconds,
AvailabilityInGreenStateMilliseconds,
AvailabilityInWhiteStateMilliseconds,
AvailabilityInDisabledStateMilliseconds,
AvailabilityInPlannedMaintenanceMilliseconds,
AvailabilityInUnplannedMaintenanceMilliseconds,
AvailabilityHealthServiceUnavailableMilliseconds,
IntervalDurationMilliseconds,
ManagedEntityRowId,
ManagedEntityGuid,
ManagedEntityMonitorRowId,
Image,
ManagedEntityDefaultName,
ManagedEntityTypeGuid,
Path,
ManagementGroupGuid,
ManagementGroupDefaultName,
DisplayName,
StepOrder
)
SELECT vState.DateTime, vState.InRedStateMilliseconds, vState.InYellowStateMilliseconds,
vState.InGreenStateMilliseconds, vState.InWhiteStateMilliseconds, vState.InDisabledStateMilliseconds,
vState.InPlannedMaintenanceMilliseconds, vState.InUnplannedMaintenanceMilliseconds, vState.HealthServiceUnavailableMilliseconds,
vState.IntervalDurationMilliseconds, vManagedEntity.ManagedEntityRowId, vManagedEntity.ManagedEntityGuid,
vState.ManagedEntityMonitorRowId, vManagedEntityTypeImage.Image, vManagedEntity.ManagedEntityDefaultName,
vManagedEntityType.ManagedEntityTypeGuid, vManagedEntity.Path, vManagementGroup.ManagementGroupGuid,
vManagementGroup.ManagementGroupDefaultName, ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName,
ObjectList.StepOrder
FROM vStateHourlyFull as vState INNER JOIN
#ObjectList As ObjectList ON vState.ManagedEntityRowId = ObjectList.ManagedEntityRowId INNER JOIN
vManagedEntity ON vState.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId INNER JOIN
vMonitor ON vState.MonitorRowId = vMonitor.MonitorRowId INNER JOIN
vManagementGroup ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId LEFT OUTER JOIN
vManagedEntityTypeImage ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId AND
vManagedEntityTypeImage.ImageCategory = N'u16x16Icon' LEFT OUTER JOIN
vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid AND
vDisplayString.LanguageCode = @LanguageCode
WHERE (vMonitor.MonitorSystemName = @MonitorName) AND
(vState.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) AND
(vState.DateTime < DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112))) AND
(vState.Date BETWEEN DATEADD(day, - 1, @StartDate) AND DATEADD(day, 1, @EndDate))
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/*
Building the data table for the performance state data
*/
CREATE TABLE #PerformanceStateDataTable (
DateTime datetime,
PerformanceInRedStateMilliseconds int,
PerformanceInYellowStateMilliseconds int,
PerformanceInGreenStateMilliseconds int,
PerformanceInWhiteStateMilliseconds int,
PerformanceInDisabledStateMilliseconds int,
PerformanceInPlannedMaintenanceMilliseconds int,
PerformanceInUnplannedMaintenanceMilliseconds int,
PerformanceHealthServiceUnavailableMilliseconds int,
IntervalDurationMilliseconds int,
ManagedEntityRowId int,
ManagedEntityGuid uniqueidentifier,
ManagedEntityMonitorRowId int,
Image varbinary(max),
ManagedEntityDefaultName nvarchar(max),
ManagedEntityTypeGuid uniqueidentifier,
Path nvarchar(max),
ManagementGroupGuid uniqueidentifier,
ManagementGroupDefaultName nvarchar(max),
DisplayName nvarchar(max),
StepOrder nvarchar(max)
)
/*
Fill the performance state data table
*/
SET @MonitorName = N'System.Health.PerformanceState'
IF @DataAggregation = 1
BEGIN
INSERT INTO #PerformanceStateDataTable (
DateTime,
PerformanceInRedStateMilliseconds,
PerformanceInYellowStateMilliseconds,
PerformanceInGreenStateMilliseconds,
PerformanceInWhiteStateMilliseconds,
PerformanceInDisabledStateMilliseconds,
PerformanceInPlannedMaintenanceMilliseconds,
PerformanceInUnplannedMaintenanceMilliseconds,
PerformanceHealthServiceUnavailableMilliseconds,
IntervalDurationMilliseconds,
ManagedEntityRowId,
ManagedEntityGuid,
ManagedEntityMonitorRowId,
Image,
ManagedEntityDefaultName,
ManagedEntityTypeGuid,
Path,
ManagementGroupGuid,
ManagementGroupDefaultName,
DisplayName,
StepOrder
)
SELECT vState.DateTime, vState.InRedStateMilliseconds, vState.InYellowStateMilliseconds,
vState.InGreenStateMilliseconds, vState.InWhiteStateMilliseconds, vState.InDisabledStateMilliseconds,
vState.InPlannedMaintenanceMilliseconds, vState.InUnplannedMaintenanceMilliseconds, vState.HealthServiceUnavailableMilliseconds,
vState.IntervalDurationMilliseconds, vManagedEntity.ManagedEntityRowId, vManagedEntity.ManagedEntityGuid,
vState.ManagedEntityMonitorRowId, vManagedEntityTypeImage.Image, vManagedEntity.ManagedEntityDefaultName,
vManagedEntityType.ManagedEntityTypeGuid, vManagedEntity.Path, vManagementGroup.ManagementGroupGuid,
vManagementGroup.ManagementGroupDefaultName, ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName,
ObjectList.StepOrder
FROM vStateDailyFull as vState INNER JOIN
#ObjectList As ObjectList ON vState.ManagedEntityRowId = ObjectList.ManagedEntityRowId INNER JOIN
vManagedEntity ON vState.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId INNER JOIN
vMonitor ON vState.MonitorRowId = vMonitor.MonitorRowId INNER JOIN
vManagementGroup ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId LEFT OUTER JOIN
vManagedEntityTypeImage ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId AND
vManagedEntityTypeImage.ImageCategory = N'u16x16Icon' LEFT OUTER JOIN
vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid AND
vDisplayString.LanguageCode = @LanguageCode
WHERE (vMonitor.MonitorSystemName = @MonitorName) AND
(vState.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) AND
(vState.DateTime < DATEADD(hh, DATEPART(hh, @EndDate) + 1, convert(varchar(8), @EndDate, 112))) AND
(vState.Date BETWEEN DATEADD(day, - 1, @StartDate) AND DATEADD(day, 1, @EndDate))
END
ELSE
BEGIN
INSERT INTO #PerformanceStateDataTable (
DateTime,
PerformanceInRedStateMilliseconds,
PerformanceInYellowStateMilliseconds,
PerformanceInGreenStateMilliseconds,
PerformanceInWhiteStateMilliseconds,
PerformanceInDisabledStateMilliseconds,
PerformanceInPlannedMaintenanceMilliseconds,
PerformanceInUnplannedMaintenanceMilliseconds,
PerformanceHealthServiceUnavailableMilliseconds,
IntervalDurationMilliseconds,
ManagedEntityRowId,
ManagedEntityGuid,
ManagedEntityMonitorRowId,
Image,
ManagedEntityDefaultName,
ManagedEntityTypeGuid,
Path,
ManagementGroupGuid,
ManagementGroupDefaultName,
DisplayName,
StepOrder
)
SELECT vState.DateTime, vState.InRedStateMilliseconds, vState.InYellowStateMilliseconds,
vState.InGreenStateMilliseconds, vState.InWhiteStateMilliseconds, vState.InDisabledStateMilliseconds,
vState.InPlannedMaintenanceMilliseconds, vState.InUnplannedMaintenanceMilliseconds, vState.HealthServiceUnavailableMilliseconds,
vState.IntervalDurationMilliseconds, vManagedEntity.ManagedEntityRowId, vManagedEntity.ManagedEntityGuid,
vState.ManagedEntityMonitorRowId, vManagedEntityTypeImage.Image, vManagedEntity.ManagedEntityDefaultName,
vManagedEntityType.ManagedEntityTypeGuid, vManagedEntity.Path, vManagementGroup.ManagementGroupGuid,
vManagementGroup.ManagementGroupDefaultName, ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName,
ObjectList.StepOrder
FROM vStateHourlyFull as vState INNER JOIN
#ObjectList As ObjectList ON vState.ManagedEntityRowId = ObjectList.ManagedEntityRowId INNER JOIN
vManagedEntity ON vState.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId INNER JOIN
vMonitor ON vState.MonitorRowId = vMonitor.MonitorRowId INNER JOIN
vManagementGroup ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId LEFT OUTER JOIN
vManagedEntityTypeImage ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId AND
vManagedEntityTypeImage.ImageCategory = N'u16x16Icon' LEFT OUTER JOIN
vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid AND
vDisplayString.LanguageCode = @LanguageCode
WHERE (vMonitor.MonitorSystemName = @MonitorName) AND
(vState.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) AND
(vState.DateTime < DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112))) AND
(vState.Date BETWEEN DATEADD(day, - 1, @StartDate) AND DATEADD(day, 1, @EndDate))
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/*
Create data table for performance data
*/
CREATE TABLE #PerformanceData (
DateTime datetime,
AverageValue float,
MinValue float,
MaxValue float,
SampleCount int,
RuleRowId int,
ManagedEntityRowId int,
RuleSystemName nvarchar(max)
)
IF @DataAggregation = 1
BEGIN
INSERT INTO #PerformanceData
SELECT vPerf.DateTime, vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.SampleCount,
vPerformanceRuleInstance.RuleRowId, vManagedEntity.ManagedEntityRowId, vRule.RuleSystemName
FROM Perf.vPerfDaily as vPerf INNER JOIN
vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
#ObjectList as ObjectList ON vManagedEntity.ManagedEntityRowId = ObjectList.ManagedEntityRowId JOIN
vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowID
WHERE (vPerf.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) AND
(vPerf.DateTime < DATEADD(hh, DATEPART(hh, @EndDate) + 1, convert(varchar(8), @EndDate, 112))) AND
vRule.RuleSystemName LIKE 'TheBackbone.SMP04.Monitoring.Perspective.Step%'
END
ELSE
BEGIN
INSERT INTO #PerformanceData
SELECT vPerf.DateTime, vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.SampleCount,
vPerformanceRuleInstance.RuleRowId, vManagedEntity.ManagedEntityRowId, vRule.RuleSystemName
FROM Perf.vPerfHourly as vPerf INNER JOIN
vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
#ObjectList as ObjectList ON vManagedEntity.ManagedEntityRowId = ObjectList.ManagedEntityRowId JOIN
vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowID
WHERE (vPerf.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) AND
(vPerf.DateTime < DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112))) AND
vRule.RuleSystemName LIKE 'TheBackbone.SMP04.Monitoring.Perspective.Step%'
END
--Pivot lower and upper threshold to add them as columns to resultset
SELECT pvt.DateTime
, pvt.ManagedEntityRowId
, pvt.[TheBackbone.SMP04.Monitoring.Perspective.Step.LowerThreshold.rule] AS LowerThreshold
, pvt.[TheBackbone.SMP04.Monitoring.Perspective.Step.UpperThreshold.rule] AS UpperThreshold
INTO #ThresholdData
FROM (
SELECT PD.DateTime
, PD.ManagedEntityRowId
, PD.AverageValue
, PD.RuleSystemName
FROM #PerformanceData PD
WHERE PD.RuleSystemName IN ('TheBackbone.SMP04.Monitoring.Perspective.Step.UpperThreshold.rule', 'TheBackbone.SMP04.Monitoring.Perspective.Step.LowerThreshold.rule')
) P
PIVOT (AVG(AverageValue)
FOR RuleSystemName IN ([TheBackbone.SMP04.Monitoring.Perspective.Step.UpperThreshold.rule], [TheBackbone.SMP04.Monitoring.Perspective.Step.LowerThreshold.rule])
) AS pvt
--Merge all tables and return data
SELECT
ISNULL(ASDT.DateTime,PSDT.DateTime) AS DateTime,
ISNULL(ASDT.DisplayName, PSDT.DisplayName) AS DisplayName,
ISNULL(ASDT.StepOrder, PSDT.StepOrder) AS StepOrder,
ISNULL(ASDT.ManagedEntityDefaultName, PSDT.ManagedEntityDefaultName) AS ManagedEntityDefaultName,
ISNULL(ASDT.AvailabilityInRedStateMilliseconds, 0) AS AvailabilityInRedStateMilliseconds,
ISNULL(ASDT.AvailabilityInYellowStateMilliseconds, 0) AS AvailabilityInYellowStateMilliseconds,
ISNULL(ASDT.AvailabilityInGreenStateMilliseconds, 0) AS AvailabilityInGreenStateMilliseconds,
ISNULL(ASDT.AvailabilityInWhiteStateMilliseconds, 0) AS AvailabilityInWhiteStateMilliseconds,
ISNULL(ASDT.AvailabilityInDisabledStateMilliseconds, 0) AS AvailabilityInDisabledStateMilliseconds,
ISNULL(ASDT.AvailabilityInPlannedMaintenanceMilliseconds, 0) AS AvailabilityInPlannedMaintenanceMilliseconds,
ISNULL(ASDT.AvailabilityInUnplannedMaintenanceMilliseconds, 0) AS AvailabilityInUnplannedMaintenanceMilliseconds,
ISNULL(ASDT.AvailabilityHealthServiceUnavailableMilliseconds, 0) AS AvailabilityHealthServiceUnavailableMilliseconds,
ISNULL(PSDT.PerformanceInRedStateMilliseconds, 0) AS PerformanceInRedStateMilliseconds,
ISNULL(PSDT.PerformanceInYellowStateMilliseconds, 0) AS PerformanceInYellowStateMilliseconds,
ISNULL(PSDT.PerformanceInGreenStateMilliseconds, 0) AS PerformanceInGreenStateMilliseconds,
ISNULL(PSDT.PerformanceInWhiteStateMilliseconds, 0) AS PerformanceInWhiteStateMilliseconds,
ISNULL(PSDT.PerformanceInDisabledStateMilliseconds, 0) AS PerformanceInDisabledStateMilliseconds,
ISNULL(PSDT.PerformanceInPlannedMaintenanceMilliseconds, 0) AS PerformanceInPlannedMaintenanceMilliseconds,
ISNULL(PSDT.PerformanceInUnplannedMaintenanceMilliseconds, 0) AS PerformanceInUnplannedMaintenanceMilliseconds,
ISNULL(PSDT.PerformanceHealthServiceUnavailableMilliseconds, 0) AS PerformanceHealthServiceUnavailableMilliseconds,
ISNULL(ASDT.IntervalDurationMilliseconds, 0) AS IntervalDurationMilliseconds,
PD.AverageValue,
PD.MinValue,
PD.MaxValue,
PD.SampleCount,
ISNULL(ASDT.ManagedEntityRowId, PSDT.ManagedEntityRowId) AS ManagedEntityRowId,
ISNULL(ASDT.ManagedEntityGuid, PSDT.ManagedEntityGuid) AS ManagedEntityGuid,
ISNULL(ASDT.ManagedEntityMonitorRowId, PSDT.ManagedEntityMonitorRowId) AS ManagedEntityMonitorRowId,
ISNULL(ASDT.ManagedEntityTypeGuid, PSDT.ManagedEntityTypeGuid) AS ManagedEntityTypeGuid,
ISNULL(ASDT.Path, PSDT.Path) AS Path,
TD.LowerThreshold,
TD.UpperThreshold
FROM #AvailabilityStateDataTable AS ASDT
FULL JOIN #PerformanceStateDataTable AS PSDT ON
ASDT.ManagedEntityRowId = PSDT.ManagedEntityRowId
AND ASDT.DateTime = PSDT.DateTime
LEFT JOIN #PerformanceData as PD ON
ASDT.ManagedEntityRowId = PD.ManagedEntityRowId
AND ASDT.DateTime = PD.DateTime
AND PD.RuleSystemName = 'TheBackbone.SMP04.Monitoring.Perspective.Step.TimeMeasured.rule'
LEFT JOIN #ThresholdData TD ON
ASDT.ManagedEntityRowId = TD.ManagedEntityRowId
AND ASDT.DateTime = TD.DateTime
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
QuitError:
DROP TABLE #ParentObjectList
DROP TABLE #ObjectList
DROP TABLE #AvailabilityStateDataTable
DROP TABLE #PerformanceStateDataTable
DROP TABLE #PerformanceData
DROP TABLE #ThresholdData
RETURN @Error
print @Error
END
Home