IF (OBJECTPROPERTY(OBJECT_ID('sdk.[ServiceLevelAgreementManagedEntityGet]'), 'IsProcedure') IS NULL)
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.[ServiceLevelAgreementManagedEntityGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [sdk].[ServiceLevelAgreementManagedEntityGet]
@StartDate datetime
,@EndDate datetime
,@ServiceLevelAgreementXml xml = NULL
,@ManagementGroupId uniqueidentifier
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
@XmlDocHandle int
,@ExecResult int
DECLARE
@ContainmentRelationshipTypeRowId int
,@Level int
,@RowCount int
BEGIN TRY
CREATE TABLE #ServiceLevelAgreement (
ServiceLevelAgreementRowId int NOT NULL
,ManagementGroupRowId int NOT NULL
,TargetManagedEntityTypeRowId int NULL
,ContextManagedEntityTypeRowId int NULL
,ContextManagedEntityGuid uniqueidentifier NULL
IF (@ServiceLevelAgreementXml IS NULL)
BEGIN
INSERT #ServiceLevelAgreement (
ServiceLevelAgreementRowId
,ManagementGroupRowId
)
SELECT
ServiceLevelAgreementRowId
,mg.ManagementGroupRowId
FROM vServiceLevelAgreement
JOIN vManagementGroup mg ON (mg.ManagementGroupGuid = @ManagementGroupId)
END
ELSE
BEGIN
-- process sla xml
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @ServiceLevelAgreementXml
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'ServiceLevelAgreement', @ExecResult)
INSERT #ServiceLevelAgreement (
ServiceLevelAgreementRowId
,ManagementGroupRowId
)
SELECT DISTINCT
sla.ServiceLevelAgreementRowId
,mg.ManagementGroupRowId
FROM OPENXML(@XmlDocHandle, 'Data/ServiceLevelAgreements/ServiceLevelAgreement', 2) WITH (
ServiceLevelAgreementGuid uniqueidentifier '@ServiceLevelAgreementGuid'
,ManagementGroupGuid uniqueidentifier '@ManagementGroupGuid'
) AS xml
JOIN vServiceLevelAgreement sla ON (sla.ServiceLevelAgreementGuid = xml.ServiceLevelAgreementGuid)
JOIN vManagementGroup mg ON (mg.ManagementGroupGuid = xml.ManagementGroupGuid)
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
END
-- ensure all Sla's were defined in the respective MGs
-- during the reporting period
DELETE temp_sla
FROM #ServiceLevelAgreement temp_sla
WHERE NOT EXISTS (SELECT *
FROM ServiceLevelAgreementManagementPackVersion slampv
JOIN ManagementGroupManagementPackVersion mgmpv ON (slampv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId) AND (temp_sla.ManagementGroupRowId = mgmpv.ManagementGroupRowId)
WHERE (slampv.ServiceLevelAgreementRowId = temp_sla.ServiceLevelAgreementRowId)
AND (mgmpv.ManagementGroupRowId = temp_sla.ManagementGroupRowId)
AND (mgmpv.InstalledDateTime < ISNULL(@EndDate, '99991231'))
AND (ISNULL(mgmpv.DeletedDateTime, '99991231') > ISNULL(@StartDate, '19000101'))
)
-- obtain the latest definition of the Sla's in respective MGs
UPDATE temp_sla
SET TargetManagedEntityTypeRowId = slampv.TargetManagedEntityTypeRowId
,ContextManagedEntityTypeRowId = slampv.ContextManagedEntityTypeRowId
,ContextManagedEntityGuid = slampv.ContextManagedEntityGuid
FROM #ServiceLevelAgreement temp_sla
JOIN vServiceLevelAgreementManagementPackVersion slampv ON (temp_sla.ServiceLevelAgreementRowId = slampv.ServiceLevelAgreementRowId)
JOIN vManagementGroupManagementPackVersion mgmpv ON (slampv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId) AND (temp_sla.ManagementGroupRowId = mgmpv.ManagementGroupRowId)
WHERE NOT EXISTS (SELECT *
FROM vServiceLevelAgreementManagementPackVersion slampv_newer
JOIN vManagementGroupManagementPackVersion mgmpv ON (slampv_newer.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)
WHERE (slampv_newer.ServiceLevelAgreementRowId = temp_sla.ServiceLevelAgreementRowId)
AND (mgmpv.ManagementGroupRowId = temp_sla.ManagementGroupRowId)
AND (slampv_newer.ServiceLevelAgreementManagementPackVersionRowId > slampv.ServiceLevelAgreementManagementPackVersionRowId)
) -- this is the latest SLA definition for this MG
-- 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 all entities covered by Sla
CREATE TABLE #ServiceLevelAgreementManagedEntity (
ServiceLevelAgreementRowId int NOT NULL
,ManagementGroupRowId int NOT NULL
,ManagedEntityRowId int NOT NULL
)
-- first consider all Sla's w/o context
INSERT #ServiceLevelAgreementManagedEntity (
ServiceLevelAgreementRowId
,ManagementGroupRowId
,ManagedEntityRowId
)
SELECT DISTINCT
sla.ServiceLevelAgreementRowId
,sla.ManagementGroupRowId
,tme.ManagedEntityRowId
FROM #ServiceLevelAgreement sla
CROSS APPLY dbo.ManagedEntityDerivedTypeHierarchy(sla.TargetManagedEntityTypeRowId, 0) as t
JOIN vTypedManagedEntity tme ON (t.ManagedEntityTypeRowId = tme.ManagedEntityTypeRowId)
JOIN vManagedEntity me ON (me.ManagedEntityRowId = tme.ManagedEntityRowId) AND (me.ManagementGroupRowId = sla.ManagementGroupRowId)
WHERE (sla.ContextManagedEntityTypeRowId IS NULL)
AND (sla.ContextManagedEntityGuid IS NULL)
AND (tme.FromDateTime < ISNULL(@EndDate, '99991231'))
AND (ISNULL(tme.ToDateTime, '99991231') > ISNULL(@StartDate, '19000101'))
-- Sla's with context specified will
-- need containment relationship walk-down
CREATE TABLE #ContainedManagedEntity (
ServiceLevelAgreementRowId int NOT NULL
,ManagementGroupRowId int NOT NULL
,ManagedEntityRowId int NOT NULL
,[Level] int NOT NULL
)
-- Sla's where only type context is specified
-- Containers of interest are all instances of given type
INSERT #ContainedManagedEntity (
ServiceLevelAgreementRowId
,ManagementGroupRowId
,ManagedEntityRowId
,[Level]
)
SELECT DISTINCT
sla.ServiceLevelAgreementRowId
,sla.ManagementGroupRowId
,tme.ManagedEntityRowId
,0
FROM #ServiceLevelAgreement sla
CROSS APPLY dbo.ManagedEntityDerivedTypeHierarchy(sla.ContextManagedEntityTypeRowId, 0) as t
JOIN vTypedManagedEntity tme ON (t.ManagedEntityTypeRowId = tme.ManagedEntityTypeRowId)
JOIN vManagedEntity me ON (tme.ManagedEntityRowId = me.ManagedEntityRowId) AND (sla.ManagementGroupRowId = me.ManagementGroupRowId)
WHERE (sla.ContextManagedEntityTypeRowId IS NOT NULL)
AND (sla.ContextManagedEntityGuid IS NULL)
AND (tme.FromDateTime < ISNULL(@EndDate, '99991231'))
AND (ISNULL(tme.ToDateTime, '99991231') > ISNULL(@StartDate, '19000101'))
-- Sla's where isntance context is specified
-- Containers of interest are those instances
INSERT #ContainedManagedEntity (
ServiceLevelAgreementRowId
,ManagementGroupRowId
,ManagedEntityRowId
,[Level]
)
SELECT DISTINCT
sla.ServiceLevelAgreementRowId
,sla.ManagementGroupRowId
,me.ManagedEntityRowId
,0
FROM #ServiceLevelAgreement sla
JOIN vManagedEntity me ON (sla.ContextManagedEntityGuid = me.ManagedEntityGuid) AND (sla.ManagementGroupRowId = me.ManagementGroupRowId)
WHERE (sla.ContextManagedEntityTypeRowId IS NOT NULL)
AND (sla.ContextManagedEntityGuid IS NOT NULL)
AND EXISTS (SELECT *
FROM vManagedEntityManagementGroup memg
WHERE (me.ManagedEntityRowId = memg.ManagedEntityRowId)
AND (memg.FromDateTime < ISNULL(@EndDate, '99991231'))
AND (ISNULL(memg.ToDateTime, '99991231') > ISNULL(@StartDate, '19000101'))
)
-- recursively walk down containment relationship chain
SET @Level = 1
SET @RowCount = 1
WHILE (@RowCount > 0)
BEGIN
INSERT #ContainedManagedEntity (
ServiceLevelAgreementRowId
,ManagementGroupRowId
,ManagedEntityRowId
,[Level]
)
SELECT DISTINCT
source_me.ServiceLevelAgreementRowId
,source_me.ManagementGroupRowId
,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
-- Sla's where context is specified (any type of context)
INSERT #ServiceLevelAgreementManagedEntity (
ServiceLevelAgreementRowId
,ManagementGroupRowId
,ManagedEntityRowId
)
SELECT DISTINCT
sla.ServiceLevelAgreementRowId
,sla.ManagementGroupRowId
,me.ManagedEntityRowId
FROM #ServiceLevelAgreement sla
JOIN #ContainedManagedEntity me ON (sla.ServiceLevelAgreementRowId = me.ServiceLevelAgreementRowId) AND (sla.ManagementGroupRowId = me.ManagementGroupRowId)
CROSS APPLY dbo.ManagedEntityDerivedTypeHierarchy(sla.TargetManagedEntityTypeRowId, 0) as t
WHERE (sla.ContextManagedEntityTypeRowId IS NOT NULL)
AND 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'))
)
SELECT
sla.ServiceLevelAgreementRowId
,sla.ManagementGroupRowId
,sla.TargetManagedEntityTypeRowId
,sla.ContextManagedEntityTypeRowId
,sla.ContextManagedEntityGuid
,slame.ManagedEntityRowId
FROM #ServiceLevelAgreement sla
JOIN #ServiceLevelAgreementManagedEntity slame ON (sla.ServiceLevelAgreementRowId = slame.ServiceLevelAgreementRowId) AND (sla.ManagementGroupRowId = slame.ManagementGroupRowId)
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('tempdb..#ServiceLevelAgreement') IS NOT NULL)
DROP TABLE #ServiceLevelAgreement
IF (OBJECT_ID('tempdb..#ContainmentRelationshipType') IS NOT NULL)
DROP TABLE #ContainmentRelationshipType
IF (OBJECT_ID('tempdb..#ServiceLevelAgreementManagedEntity') IS NOT NULL)
DROP TABLE #ServiceLevelAgreementManagedEntity
IF (OBJECT_ID('tempdb..#ContainedManagedEntity') IS NOT NULL)
DROP TABLE #ContainedManagedEntity
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
-- 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.ServiceLevelAgreementManagedEntityGet TO OpsMgrReader
GO
Home
