IF (OBJECTPROPERTY(OBJECT_ID('sdk.[ServiceLevelObjectiveGetByServiceLevelAgreement]'), 'IsProcedure') IS NULL)
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.[ServiceLevelObjectiveGetByServiceLevelAgreement] AS RETURN 1')
END
GO
ALTER PROCEDURE sdk.ServiceLevelObjectiveGetByServiceLevelAgreement
@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
)
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 (mgmpv.ManagementGroupRowId = temp_sla.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'))
)
SELECT
sla.ServiceLevelAgreementRowId
,sla.ManagementGroupRowId
,slompv.ServiceLevelObjectiveRowId
,slompv.TargetManagedEntityTypeRowId
,Goal =
CASE
WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN mslompv.Goal
WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN pslompv.Threshold
END
,DesiredObjective =
CASE
WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN CAST('over' AS varchar(5))
WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN pslompv.DesiredObjective
END
,AggregationMethod =
CASE
WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN CAST('avg' AS char(3))
WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN pslompv.AggregationMethod
END
FROM #ServiceLevelAgreement sla
JOIN vServiceLevelObjectiveManagementPackVersion slompv ON (slompv.ServiceLevelAgreementRowId = sla.ServiceLevelAgreementRowId)
JOIN vManagementGroupManagementPackVersion mgmpv ON (mgmpv.ManagementPackVersionRowId = slompv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = sla.ManagementGroupRowId)
LEFT JOIN vMonitorServiceLevelObjectiveManagementPackVersion mslompv ON (mslompv.ServiceLevelObjectiveManagementPackVersionRowId = slompv.ServiceLevelObjectiveManagementPackVersionRowId)
LEFT JOIN vPerformanceServiceLevelObjectiveManagementPackVersion pslompv ON (pslompv.ServiceLevelObjectiveManagementPackVersionRowId = slompv.ServiceLevelObjectiveManagementPackVersionRowId)
WHERE EXISTS (SELECT *
FROM vServiceLevelObjectiveManagementPackVersion slompv_defined
JOIN vManagementGroupManagementPackVersion mgmpv ON (mgmpv.ManagementPackVersionRowId = slompv_defined.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = sla.ManagementGroupRowId)
WHERE (slompv.ServiceLevelObjectiveRowId = slompv_defined.ServiceLevelObjectiveRowId)
AND (mgmpv.InstalledDateTime < ISNULL(@EndDate, '99991231'))
AND (ISNULL(mgmpv.DeletedDateTime, '99991231') > ISNULL(@StartDate, '19000101'))
) -- slo was defined at some point between start and end dates
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 = sla.ManagementGroupRowId)
AND (slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId > slompv.ServiceLevelObjectiveManagementPackVersionRowId)
) -- this is the latest SLO definition for this MG
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 (@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.ServiceLevelObjectiveGetByServiceLevelAgreement TO OpsMgrReader
GO
Home
