IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ServiceLevelObjectiveDetailReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.[ServiceLevelObjectiveDetailReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE sdk.[ServiceLevelObjectiveDetailReportDataGet]
@StartDate datetime
,@EndDate datetime
,@ServiceLevelAgreementManagedEntityRowId int
,@ServiceLevelObjectiveGuid uniqueidentifier
,@AggregationTypeId int
,@LanguageCode varchar(3) ='ENU'
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@Level int
,@RowCount int
,@ManagementGroupRowId int
,@ContainmentRelationshipTypeRowId int
BEGIN TRY
-- build a list of all containment relationships
CREATE TABLE #ContainmentRelationshipType (
RelationshipTypeRowId int PRIMARY KEY
)
SELECT @ContainmentRelationshipTypeRowId = RelationshipTypeRowId
FROM RelationshipType
WHERE RelationshipTypeSystemName = 'System.Containment'
IF (@ContainmentRelationshipTypeRowId IS NOT NULL)
BEGIN
INSERT #ContainmentRelationshipType (RelationshipTypeRowId)
SELECT RelationshipTypeRowId
FROM dbo.RelationshipDerivedTypeHierarchy(@ContainmentRelationshipTypeRowId, 0)
END
-- build a list of entities contained in a given Sla entity
CREATE TABLE #ContainedManagedEntity (
ManagedEntityRowId int NOT NULL
,[Level] int NOT NULL
)
-- recursively walk down containment relationship chain
SET @Level = 1
SET @RowCount = 1
WHILE (@RowCount > 0)
BEGIN
INSERT #ContainedManagedEntity (
ManagedEntityRowId
,[Level]
)
SELECT
r.TargetManagedEntityRowId
,@Level
FROM #ContainedManagedEntity source_me
JOIN vRelationship r ON (source_me.ManagedEntityRowId = r.SourceManagedEntityRowId)
JOIN #ContainmentRelationshipType rt ON (r.RelationshipTypeRowId = rt.RelationshipTypeRowId)
WHERE (source_me.Level = @Level - 1)
AND EXISTS (SELECT *
FROM vRelationshipManagementGroup rmg
WHERE (rmg.RelationshipRowId = r.RelationshipRowId)
AND (rmg.FromDateTime < ISNULL(@EndDate, '99991231'))
AND (ISNULL(rmg.ToDateTime, '99991231') > ISNULL(@StartDate, '19000101'))
)
SET @RowCount = @@ROWCOUNT
SET @Level = @Level + 1
END
-- build a list of Slo entities
CREATE TABLE #ServiceLevelObjectiveManagedEntity (
ServiceLevelObjectiveRowId int NOT NULL
,ManagementGroupRowId int NOT NULL
,ServiceLevelObjectiveManagedEntityRowId int NOT NULL
)
SELECT @ManagementGroupRowId = (SELECT ManagementGroupRowId FROM vManagedEntity WHERE ManagedEntityRowId = @ServiceLevelAgreementManagedEntityRowId)
INSERT #ServiceLevelObjectiveManagedEntity (
ServiceLevelObjectiveRowId
,ManagementGroupRowId
,ServiceLevelObjectiveManagedEntityRowId
)
SELECT DISTINCT
slo.ServiceLevelObjectiveRowId
,@ManagementGroupRowId
,me.ManagedEntityRowId
FROM #ContainedManagedEntity me
CROSS JOIN vServiceLevelObjective slo
JOIN vServiceLevelObjectiveManagementPackVersion slompv ON (slo.ServiceLevelObjectiveRowId = slompv.ServiceLevelObjectiveRowId)
CROSS APPLY dbo.ManagedEntityDerivedTypeHierarchy(slompv.TargetManagedEntityTypeRowId, 0) as t
WHERE EXISTS (SELECT *
FROM vTypedManagedEntity tme
WHERE (t.ManagedEntityTypeRowId = tme.ManagedEntityTypeRowId)
AND (me.ManagedEntityRowId = tme.ManagedEntityRowId)
AND (tme.FromDateTime < ISNULL(@EndDate, '99991231'))
AND (ISNULL(tme.ToDateTime, '99991231') > ISNULL(@StartDate, '19000101'))
)
AND (slo.ServiceLevelObjectiveGuid = @ServiceLevelObjectiveGuid)
AND NOT EXISTS (SELECT *
FROM vServiceLevelObjectiveManagementPackVersion slompv_newer
JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv_newer.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)
WHERE (slompv_newer.ServiceLevelObjectiveRowId = slompv.ServiceLevelObjectiveRowId)
AND (mgmpv.ManagementGroupRowId = @ManagementGroupRowId)
AND (slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId > slompv.ServiceLevelObjectiveManagementPackVersionRowId)
) -- this is the latest SLO definition for this MG
DROP TABLE #ContainmentRelationshipType
DROP TABLE #ContainedManagedEntity
-- result table
CREATE TABLE #ServiceLevelObjectiveManagedEntityDetail (
ServiceLevelObjectiveRowId int NOT NULL
,ManagementGroupRowId int NOT NULL
,ServiceLevelObjectiveManagedEntityRowId int NOT NULL
,[DateTime] datetime NOT NULL
,ActualValue float NOT NULL
,Weight int NOT NULL
,TotalDowntimeMilliseconds int NULL
)
-- details data get SP looks for #ServiceLevelObjectiveManagedEntity
-- table to pull up MEs it needs to work with
INSERT #ServiceLevelObjectiveManagedEntityDetail (
ServiceLevelObjectiveRowId
,ManagementGroupRowId
,ServiceLevelObjectiveManagedEntityRowId
,[DateTime]
,ActualValue
,Weight
,TotalDowntimeMilliseconds
)
EXEC sdk.[ServiceLevelObjectiveManagedEntityDetailDataGet]
@StartDate
,@EndDate
,@AggregationTypeId
DROP TABLE #ServiceLevelObjectiveManagedEntity
SELECT
ManagedEntityRowId = slome.ServiceLevelObjectiveManagedEntityRowId
,ManagementGroupGuid = (SELECT ManagementGroupGuid FROM ManagementGroup WHERE ManagementGroupRowId = @ManagementGroupRowId)
,ManagementGroupDisplayName = (SELECT ManagementGroupDefaultName FROM ManagementGroup WHERE ManagementGroupRowId = @ManagementGroupRowId)
,met.ManagedEntityTypeGuid
,ManagedEntityTypeDisplayName = ISNULL(met_ds.[Name], met.ManagedEntityTypeDefaultName)
,ManagedEntityDisplayName = me.DisplayName
,ManagedEntityPath = me.Path
,Goal =
CASE
WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN mslompv.Goal
WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN pslompv.Threshold
END
,DesiredObjective =
CASE
WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN 'over'
WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN pslompv.DesiredObjective
END
,AggregationMethod =
CASE
WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN 'avg'
WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN pslompv.AggregationMethod
END
,slome.[DateTime]
,slome.ActualValue
,slome.Weight
,slome.TotalDowntimeMilliseconds
,AggregatedValue =
CASE
WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN tempAggrValues.AvgValue
WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN
CASE pslompv.AggregationMethod
WHEN 'avg' THEN tempAggrValues.AvgValue
WHEN 'min' THEN tempAggrValues.MinValue
WHEN 'max' THEN tempAggrValues.MaxValue
END
END
,Rule_MonitorDisplayName =
CASE
WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN ISNULL(vDisplayStringMonitor.Name, m.MonitorDefaultName)
WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName)
END
,slompv.MonitorServiceLevelObjectiveInd
FROM
#ServiceLevelObjectiveManagedEntityDetail slome
JOIN vManagedEntity me ON (slome.ServiceLevelObjectiveManagedEntityRowId = me.ManagedEntityRowId)
JOIN vManagedEntityType met ON (me.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId)
JOIN vServiceLevelObjectiveManagementPackVersion slompv ON (slompv.ServiceLevelObjectiveRowId = slome.ServiceLevelObjectiveRowId)
JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = @ManagementGroupRowId)
LEFT JOIN vMonitorServiceLevelObjectiveManagementPackVersion mslompv ON (mslompv.ServiceLevelObjectiveManagementPackVersionRowId = slompv.ServiceLevelObjectiveManagementPackVersionRowId)
LEFT JOIN vPerformanceServiceLevelObjectiveManagementPackVersion pslompv ON (pslompv.ServiceLevelObjectiveManagementPackVersionRowId = slompv.ServiceLevelObjectiveManagementPackVersionRowId)
LEFT JOIN vDisplayString met_ds ON (met.ManagedEntityTypeGuid = met_ds.ElementGuid) AND (met_ds.LanguageCode = @LanguageCode)
JOIN (
SELECT
ServiceLevelObjectiveRowId = slome1.ServiceLevelObjectiveRowId
,ServiceLevelObjectiveManagedEntityRowId
,AvgValue = sum(slome1.ActualValue * slome1.Weight)/sum(slome1.Weight)
,MinValue = min(slome1.ActualValue)
,MaxValue = max(slome1.ActualValue)
FROM #ServiceLevelObjectiveManagedEntityDetail slome1
GROUP BY slome1.ServiceLevelObjectiveManagedEntityRowId, slome1.ServiceLevelObjectiveRowId
) as tempAggrValues ON (slome.ServiceLevelObjectiveManagedEntityRowId = tempAggrValues.ServiceLevelObjectiveManagedEntityRowId)
LEFT JOIN vRule ON (pslompv.RuleRowId = vRule.RuleRowId)
LEFT JOIN vMonitor AS m ON mslompv.MonitorRowId = m.MonitorRowId
LEFT JOIN vDisplayString vDisplayStringRule ON vRule.RuleGuid = vDisplayStringRule.ElementGuid AND
vDisplayStringRule.LanguageCode = @LanguageCode
LEFT JOIN vDisplayString AS vDisplayStringMonitor ON m.MonitorGuid = vDisplayStringMonitor.ElementGuid AND
vDisplayStringMonitor.LanguageCode = @LanguageCode
WHERE NOT EXISTS (SELECT *
FROM vServiceLevelObjectiveManagementPackVersion slompv_newer
JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv_newer.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)
WHERE (slompv_newer.ServiceLevelObjectiveRowId = slome.ServiceLevelObjectiveRowId)
AND (mgmpv.ManagementGroupRowId = @ManagementGroupRowId)
AND (slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId > slompv.ServiceLevelObjectiveManagementPackVersionRowId)
) -- this is the latest SLO definition for this MG
DROP TABLE #ServiceLevelObjectiveManagedEntityDetail
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
Cleanup:
-- cleanup
IF (OBJECT_ID('#ContainmentRelationshipType') IS NOT NULL)
DROP TABLE #ContainmentRelationshipType
IF (OBJECT_ID('#ContainedManagedEntity') IS NOT NULL)
DROP TABLE #ContainedManagedEntity
IF (OBJECT_ID('#ServiceLevelObjectiveManagedEntity') IS NOT NULL)
DROP TABLE #ServiceLevelObjectiveManagedEntity
IF (OBJECT_ID('#ServiceLevelObjectiveManagedEntityDetail') IS NOT NULL)
DROP TABLE #ServiceLevelObjectiveManagedEntityDetail
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON sdk.[ServiceLevelObjectiveDetailReportDataGet] TO OpsMgrReader
GO
Home
