IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetServiceLevelInformationData')
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.[GetServiceLevelInformationData] AS RETURN 1')
END
GO
ALTER PROCEDURE [sdk].[GetServiceLevelInformationData]
@StartDate datetime
,@EndDate datetime
,@ServiceLevelAgreementXml xml = NULL
,@AggregationTypeId int
,@ManagementGroupId uniqueidentifier
,@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
@ContainmentRelationshipTypeRowId int
,@Level int
,@RowCount int
BEGIN TRY
-- build a list of all entities covered by Sla's
CREATE TABLE #ServiceLevelAgreementManagedEntity (
ServiceLevelAgreementRowId int NOT NULL
,ManagementGroupRowId int NOT NULL
,TargetManagedEntityTypeRowId int NULL
,ContextManagedEntityTypeRowId int NULL
,ContextManagedEntityGuid uniqueidentifier NULL
,ManagedEntityRowId int NOT NULL
)
-- build list of Slo's covered by given Sla's
CREATE TABLE #ServiceLevelObjective (
ServiceLevelAgreementRowId int NOT NULL
,ManagementGroupRowId int NOT NULL
,ServiceLevelObjectiveRowId int NOT NULL
,TargetManagedEntityTypeRowId int NOT NULL
,Goal float NOT NULL
,DesiredObjective varchar(5) NOT NULL
,AggregationMethod char(3) NOT NULL
)
-- 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
-- walk down the containment relationship for Sla entities
CREATE TABLE #ContainedManagedEntity (
ServiceLevelAgreementRowId int NOT NULL
,ManagementGroupRowId int NOT NULL
,ServiceLevelAgreementManagedEntityRowId int NOT NULL
,ManagedEntityRowId int NOT NULL
,[Level] int NOT NULL
)
WHILE (@RowCount > 0)
BEGIN
INSERT #ContainedManagedEntity (
ServiceLevelAgreementRowId
,ManagementGroupRowId
,ServiceLevelAgreementManagedEntityRowId
,ManagedEntityRowId
,[Level]
)
SELECT
source_me.ServiceLevelAgreementRowId
,source_me.ManagementGroupRowId
,source_me.ServiceLevelAgreementManagedEntityRowId
,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
-- Determine the derived type hierarchy of each SLO
SELECT
0 as [Level],
slo.ServiceLevelObjectiveRowId,
slo.TargetManagedEntityTypeRowId as ManagedEntityTypeRowId
INTO #DerivedTypeHierarchy
FROM #ServiceLevelObjective slo
SET @Level = 1
SET @RowCount = 1
WHILE (@RowCount > 0)
BEGIN
INSERT #DerivedTypeHierarchy([Level], ServiceLevelObjectiveRowId, ManagedEntityTypeRowId)
SELECT DISTINCT @Level, rt.ServiceLevelObjectiveRowId, metmpv.ManagedEntityTypeRowId
FROM vManagedEntityTypeManagementPackVersion metmpv
JOIN #DerivedTypeHierarchy rt ON (metmpv.BaseManagedEntityTypeRowId = rt.ManagedEntityTypeRowId)
WHERE (rt.[Level] = @Level - 1)
-- if type is derived from a given type on multiple levels according to
-- different version of MPs (which can happen on upgrade) take only the lowest level
AND (NOT EXISTS (SELECT * FROM #DerivedTypeHierarchy r WHERE r.ManagedEntityTypeRowId = metmpv.ManagedEntityTypeRowId
AND r.ServiceLevelObjectiveRowId = rt.ServiceLevelObjectiveRowId))
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
)
INSERT #ServiceLevelObjectiveManagedEntity (
ServiceLevelObjectiveRowId
,ManagementGroupRowId
,ServiceLevelObjectiveManagedEntityRowId
)
SELECT DISTINCT
slo.ServiceLevelObjectiveRowId
,slo.ManagementGroupRowId
,me.ManagedEntityRowId
FROM #ServiceLevelObjective slo
JOIN #ContainedManagedEntity me ON (slo.ManagementGroupRowId = me.ManagementGroupRowId) AND (slo.ServiceLevelAgreementRowId = me.ServiceLevelAgreementRowId)
JOIN #DerivedTypeHierarchy t ON (slo.ServiceLevelObjectiveRowId = t.ServiceLevelObjectiveRowId)
WHERE EXISTS (SELECT TOP 1 *
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'))
)
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
ServiceLevelAgreementRowId int NOT NULL
,ServiceLevelAgreementManagedEntityRowId int NOT NULL
,ServiceLevelObjectiveRowId int NOT NULL
,ServiceLevelObjectiveManagedEntityRowId int NOT NULL
,AggregatedValue float NOT NULL
,ManagementGroupRowId int NOT NULL
)
INSERT #ServiceLevelObjectiveManagedEntityAggregatedValues (
ServiceLevelAgreementRowId
,ServiceLevelAgreementManagedEntityRowId
,ServiceLevelObjectiveRowId
,ServiceLevelObjectiveManagedEntityRowId
,AggregatedValue
,ManagementGroupRowId
)
SELECT
tempAggrValues.ServiceLevelAgreementRowId
, tempAggrValues.ServiceLevelAgreementManagedEntityRowId
, ServiceLevelObjectiveRowId = tempAggrValues.ServiceLevelObjectiveRowId
, ServiceLevelObjectiveManagedEntityRowId = tempAggrValues.ServiceLevelObjectiveManagedEntityRowId
, 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
, ManagementGroupRowId = tempAggrValues.ManagementGroupRowId
FROM
( SELECT ServiceLevelAgreementRowId = slame.ServiceLevelAgreementRowId
,ServiceLevelAgreementManagedEntityRowId = slame.ManagedEntityRowId
,ServiceLevelObjectiveRowId = slome.ServiceLevelObjectiveRowId
,ServiceLevelObjectiveManagedEntityRowId
,AvgValue = sum(slome.ActualValue * slome.Weight)/sum(slome.Weight)
,MinValue = min(slome.ActualValue)
,MaxValue = max(slome.ActualValue)
,ManagementGroupRowId = slome.ManagementGroupRowId
FROM #ServiceLevelAgreementManagedEntity slame
JOIN #ServiceLevelObjective slo ON (slo.ServiceLevelAgreementRowId = slame.ServiceLevelAgreementRowId) AND
(slo.ManagementGroupRowId = slame.ManagementGroupRowId)
JOIN #ContainedManagedEntity cme ON (slame.ManagedEntityRowId = cme.ServiceLevelAgreementManagedEntityRowId) AND
(cme.ServiceLevelAgreementRowId = slame.ServiceLevelAgreementRowId) AND
(cme.ManagementGroupRowId = slame.ManagementGroupRowId)
JOIN #ServiceLevelObjectiveManagedEntityDetail slome ON (slo.ServiceLevelObjectiveRowId = slome.ServiceLevelObjectiveRowId) AND
(slame.ManagementGroupRowId = slo.ManagementGroupRowId) AND
(cme.ManagedEntityRowId = slome.ServiceLevelObjectiveManagedEntityRowId)
GROUP BY slome.ServiceLevelObjectiveManagedEntityRowId, slome.ServiceLevelObjectiveRowId, slame.ManagedEntityRowId, slame.ServiceLevelAgreementRowId, slome.ManagementGroupRowId
) as tempAggrValues
JOIN vServiceLevelObjectiveManagementPackVersion slompv ON tempAggrValues.ServiceLevelObjectiveRowId = slompv.ServiceLevelObjectiveRowId
LEFT JOIN vPerformanceServiceLevelObjectiveManagementPackVersion pslompv ON (pslompv.ServiceLevelObjectiveManagementPackVersionRowId = slompv.ServiceLevelObjectiveManagementPackVersionRowId)
WHERE NOT EXISTS (SELECT *
FROM vServiceLevelObjectiveManagementPackVersion slompv_newer
JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv_newer.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)
WHERE (slompv_newer.ServiceLevelObjectiveRowId = tempAggrValues.ServiceLevelObjectiveRowId)
AND (mgmpv.ManagementGroupRowId = tempAggrValues.ManagementGroupRowId)
AND (slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId > slompv.ServiceLevelObjectiveManagementPackVersionRowId)
) -- this is the latest SLO definition for this MG
-- Calculate the State of Each Service Level Objective corresponding to a Service Level Managed Entity
CREATE TABLE #ServiceLevelObjectiveState (
ServiceLevelAgreementRowId int NOT NULL
,ServiceLevelAgreementManagedEntityRowId int NOT NULL
,ServiceLevelObjectiveRowId int NOT NULL
,ServiceLevelObjectiveState int NOT NULL
,Goal int NOT NULL
,SloManagedEntityCount int NOT NULL
,ManagementGroupRowId int NOT NULL
)
INSERT #ServiceLevelObjectiveState (
ServiceLevelAgreementRowId
,ServiceLevelAgreementManagedEntityRowId
,ServiceLevelObjectiveRowId
,ServiceLevelObjectiveState
,Goal
,SloManagedEntityCount
,ManagementGroupRowId
)
SELECT
tempSloValues.ServiceLevelAgreementRowId
,tempSloValues.ServiceLevelAgreementManagedEntityRowId
,tempSloValues.ServiceLevelObjectiveRowId
,CASE
WHEN slompv.MonitorServiceLevelObjectiveInd = 1
THEN
CASE
WHEN tempSloValues.MinValue >= slo.Goal
THEN 1
ELSE 0
END
WHEN slompv.PerformanceServiceLevelObjectiveInd = 1
THEN
CASE
WHEN slo.DesiredObjective = 'under'
THEN
CASE
WHEN tempSloValues.MaxValue < slo.Goal
THEN 1
ELSE 0
END
WHEN slo.DesiredObjective = 'over'
THEN
CASE
WHEN tempSloValues.MinValue >= slo.Goal
THEN 1
ELSE 0
END
END
END
,Goal
,tempSloValues.SloManagedEntityCount
,tempSloValues.ManagementGroupRowId
FROM
(
SELECT ServiceLevelAgreementRowId = slomeav1.ServiceLevelAgreementRowId
,ServiceLevelAgreementManagedEntityRowId = slomeav1.ServiceLevelAgreementManagedEntityRowId
,slomeav1.ServiceLevelObjectiveRowId
,MaxValue = max(slomeav1.AggregatedValue)
,MinValue = min(slomeav1.AggregatedValue)
,SloManagedEntityCount = COUNT(DISTINCT (slomeav1.ServiceLevelObjectiveManagedEntityRowId))
,ManagementGroupRowId = slomeav1.ManagementGroupRowId
FROM #ServiceLevelObjectiveManagedEntityAggregatedValues slomeav1
GROUP BY slomeav1.ServiceLevelObjectiveRowId, slomeav1.ServiceLevelAgreementManagedEntityRowId, slomeav1.ServiceLevelAgreementRowId, slomeav1.ManagementGroupRowId
) AS tempSloValues
JOIN #ServiceLevelObjective slo ON (tempSloValues.ServiceLevelObjectiveRowId = slo.ServiceLevelObjectiveRowId) AND (slo.ManagementGroupRowId = tempSloValues.ManagementGroupRowId)
JOIN vServiceLevelObjectiveManagementPackVersion slompv ON (slompv.ServiceLevelObjectiveRowId = slo.ServiceLevelObjectiveRowId)
LEFT JOIN vPerformanceServiceLevelObjectiveManagementPackVersion pslompv ON (pslompv.ServiceLevelObjectiveManagementPackVersionRowId = slompv.ServiceLevelObjectiveManagementPackVersionRowId)
WHERE NOT EXISTS (SELECT *
FROM vServiceLevelObjectiveManagementPackVersion slompv_newer
JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv_newer.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)
WHERE (slompv_newer.ServiceLevelObjectiveRowId = tempSloValues.ServiceLevelObjectiveRowId)
AND (mgmpv.ManagementGroupRowId = tempSloValues.ManagementGroupRowId)
AND (slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId > slompv.ServiceLevelObjectiveManagementPackVersionRowId)
) -- this is the latest SLO definition for this MG
-- Calculate the State of Each Service Level Managed Entity
CREATE TABLE #ServiceLevelManagedEntityState (
ServiceLevelAgreementRowId int NOT NULL
,ServiceLevelAgreementManagedEntityRowId int NOT NULL
,ServiceLevelAgreementManagedEntityState int NOT NULL
,ManagementGroupRowId int NOT NULL
)
INSERT #ServiceLevelManagedEntityState (
ServiceLevelAgreementRowId
,ServiceLevelAgreementManagedEntityRowId
,ServiceLevelAgreementManagedEntityState
,ManagementGroupRowId
)
SELECT
slos.ServiceLevelAgreementRowId
,slos.ServiceLevelAgreementManagedEntityRowId
,min(slos.ServiceLevelObjectiveState)
,slos.ManagementGroupRowId
FROM #ServiceLevelObjectiveState slos
GROUP BY slos.ServiceLevelAgreementManagedEntityRowId, slos.ServiceLevelAgreementRowId, slos.ManagementGroupRowId
--Calculate the state of each servcie Level depending on the states of all Service Level Managed Entities
CREATE TABLE #ServiceLevelState (
ServiceLevelAgreementRowId int NOT NULL
,ServiceLevelState int NOT NULL
,ManagementGroupRowId int NOT NULL
)
INSERT #ServiceLevelState (
ServiceLevelAgreementRowId
,ServiceLevelState
,ManagementGroupRowId
)
SELECT
slmes.ServiceLevelAgreementRowId
,min(slmes.ServiceLevelAgreementManagedEntityState)
,slmes.ManagementGroupRowId
FROM #ServiceLevelManagedEntityState slmes
GROUP BY slmes.ServiceLevelAgreementRowId, slmes.ManagementGroupRowId
SELECT DISTINCT
sls.ServiceLevelAgreementRowId
,sls.ServiceLevelState
,slomeav.ManagementGroupRowId
,ServiceLevelAgreementManagedEntityRowId = slmes.ServiceLevelAgreementManagedEntityRowId
,slmes.ServiceLevelAgreementManagedEntityState
,slomeav.ServiceLevelObjectiveRowId
,slos.ServiceLevelObjectiveState
,slos.SloManagedEntityCount
FROM #ServiceLevelObjectiveManagedEntityAggregatedValues slomeav
JOIN #ServiceLevelObjectiveState slos ON (slos.ServiceLevelObjectiveRowId = slomeav.ServiceLevelObjectiveRowId) AND (slos.ManagementGroupRowId = slomeav.ManagementGroupRowId) AND (slomeav.ServiceLevelAgreementManagedEntityRowId = slos.ServiceLevelAgreementManagedEntityRowId) AND (slomeav.ServiceLevelAgreementRowId = slos.ServiceLevelAgreementRowId)
JOIN #ServiceLevelManagedEntityState slmes ON (slmes.ServiceLevelAgreementManagedEntityRowId = slos.ServiceLevelAgreementManagedEntityRowId)AND (slos.ManagementGroupRowId = slmes.ManagementGroupRowId) AND (slmes.ServiceLevelAgreementRowId = slos.ServiceLevelAgreementRowId)
JOIN #ServiceLevelState sls ON (sls.ServiceLevelAgreementRowId = slmes.ServiceLevelAgreementRowId) AND (sls.ManagementGroupRowId = slmes.ManagementGroupRowId)
-- cleanup
IF (OBJECT_ID('tempdb..#ServiceLevelAgreementManagedEntity') IS NOT NULL)
DROP TABLE #ServiceLevelAgreementManagedEntity
IF (OBJECT_ID('tempdb..#ServiceLevelObjective') IS NOT NULL)
DROP TABLE #ServiceLevelObjective
IF (OBJECT_ID('tempdb..#ContainmentRelationshipType') IS NOT NULL)
DROP TABLE #ContainmentRelationshipType
IF (OBJECT_ID('tempdb..#ContainedManagedEntity') IS NOT NULL)
DROP TABLE #ContainedManagedEntity
IF (OBJECT_ID('tempdb..#DerivedTypeHierarchy') IS NOT NULL)
DROP TABLE #DerivedTypeHierarchy
IF (OBJECT_ID('tempdb..#ServiceLevelObjectiveManagedEntity') IS NOT NULL)
DROP TABLE #ServiceLevelObjectiveManagedEntity
IF (OBJECT_ID('tempdb..#ServiceLevelObjectiveManagedEntityDetail') IS NOT NULL)
DROP TABLE #ServiceLevelObjectiveManagedEntityDetail
IF (OBJECT_ID('tempdb..#ServiceLevelObjectiveManagedEntityAggregatedValues') IS NOT NULL)
DROP TABLE #ServiceLevelObjectiveManagedEntityAggregatedValues
IF (OBJECT_ID('tempdb..#ServiceLevelObjectiveState') IS NOT NULL)
DROP TABLE #ServiceLevelObjectiveState
IF (OBJECT_ID('tempdb..#ServiceLevelManagedEntityState') IS NOT NULL)
DROP TABLE #ServiceLevelManagedEntityState
IF (OBJECT_ID('tempdb..#ServiceLevelState') IS NOT NULL)
DROP TABLE #ServiceLevelState
-- 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.[GetServiceLevelInformationData] TO OpsMgrReader
GO
Home
