IF (OBJECTPROPERTY(OBJECT_ID('sdk.[GetServiceLevelMetaData]'), 'IsProcedure') IS NULL)
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.[GetServiceLevelMetaData] AS RETURN 1')
END
GO
ALTER PROCEDURE [sdk].[GetServiceLevelMetaData]
@StartDate datetime
,@EndDate datetime
,@ServiceLevelAgreementXml xml = NULL
,@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
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
)
SELECT
slame.ServiceLevelAgreementRowId
,sla.ServiceLevelAgreementGuid
,ServiceLevelAgreementDisplayName = ISNULL(sla_ds.[Name], sla.ServiceLevelAgreementDefaultName)
,mg.ManagementGroupGuid
,ServiceLevelAgreementManagedEntityRowId = slame.ManagedEntityRowId
,ServiceLevelAgreementManagedEntityTypeGuid = sla_me_met.ManagedEntityTypeGuid
,ServiceLevelAgreementManagedEntityTypeDisplayName = ISNULL(sla_me_met_ds.[Name], sla_me_met.ManagedEntityTypeDefaultName)
,ServiceLevelAgreementManagedEntityGuid = sla_me.ManagedEntityGuid
,ServiceLevelAgreementManagedEntityDisplayName = ISNULL(sla_me_ds.[Name], sla_me.DisplayName)
,ServiceLevelAgreementManagedEntityPath = sla_me.Path
,temp_slo.ServiceLevelObjectiveRowId
,slo.ServiceLevelObjectiveGuid
,ServiceLevelObjectiveDisplayName = ISNULL(slo_ds.[Name], slo.ServiceLevelObjectiveDefaultName)
,ServiceLevelObjectiveTargetManagedEntityTypeGuid = slo_target_met.ManagedEntityTypeGuid
,ServiceLevelObjectiveTargetManagedEntityTypeDisplayName = ISNULL(slo_target_met_ds.[Name], slo_target_met.ManagedEntityTypeDefaultName)
FROM #ServiceLevelObjective temp_slo
JOIN #ServiceLevelAgreementManagedEntity slame ON (temp_slo.ServiceLevelAgreementRowId = slame.ServiceLevelAgreementRowId) AND (temp_slo.ManagementGroupRowId = slame.ManagementGroupRowId)
JOIN vServiceLevelAgreement sla ON (slame.ServiceLevelAgreementRowId = sla.ServiceLevelAgreementRowId)
JOIN vManagementGroup mg ON (mg.ManagementGroupRowId = slame.ManagementGroupRowId)
JOIN vManagedEntity sla_me ON (sla_me.ManagedEntityRowId = slame.ManagedEntityRowId)
JOIN vManagedEntityType sla_me_met ON (sla_me_met.ManagedEntityTypeRowId = sla_me.ManagedEntityTypeRowId)
JOIN vServiceLevelObjective slo ON (temp_slo.ServiceLevelObjectiveRowId = slo.ServiceLevelObjectiveRowId)
JOIN vServiceLevelObjectiveManagementPackVersion slompv ON (slompv.ServiceLevelObjectiveRowId = slo.ServiceLevelObjectiveRowId)
JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = temp_slo.ManagementGroupRowId)
LEFT JOIN vManagedEntityType slo_target_met ON (temp_slo.TargetManagedEntityTypeRowId = slo_target_met.ManagedEntityTypeRowId)
LEFT JOIN vDisplayString sla_ds ON (sla.ServiceLevelAgreementGuid = sla_ds.ElementGuid) AND (sla_ds.LanguageCode = @LanguageCode)
LEFT JOIN vDisplayString sla_me_met_ds ON (sla_me_met.ManagedEntityTypeGuid = sla_me_met_ds.ElementGuid) AND (sla_me_met_ds.LanguageCode = @LanguageCode)
LEFT JOIN vDisplayString slo_ds ON (slo.ServiceLevelObjectiveGuid = slo_ds.ElementGuid) AND (slo_ds.LanguageCode = @LanguageCode)
LEFT JOIN vDisplayString slo_target_met_ds ON (slo_target_met.ManagedEntityTypeGuid = slo_target_met_ds.ElementGuid) AND (slo_target_met_ds.LanguageCode = @LanguageCode)
LEFT JOIN vDisplayString sla_me_ds ON (sla_me.ManagedEntityGuid = sla_me_ds.ElementGuid) AND (sla_me_ds.LanguageCode = @LanguageCode)
WHERE NOT EXISTS (SELECT *
FROM vServiceLevelObjectiveManagementPackVersion slompv_newer
JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv_newer.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)
WHERE (slompv_newer.ServiceLevelObjectiveRowId = slo.ServiceLevelObjectiveRowId)
AND (mgmpv.ManagementGroupRowId = temp_slo.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('#ServiceLevelAgreementManagedEntity') IS NOT NULL)
DROP TABLE #ServiceLevelAgreementManagedEntity
IF (OBJECT_ID('#ServiceLevelObjective') IS NOT NULL)
DROP TABLE #ServiceLevelObjective
-- 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.[GetServiceLevelMetaData] TO OpsMgrReader
GO
Home
