IF NOT EXISTS (SELECT * FROM sys.objects WHERE [schema_id]=SCHEMA_ID('dbo') AND type = 'P' AND name = 'Savision_Community_SLA_ServiceLevelAgreementMetadataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Savision_Community_SLA_ServiceLevelAgreementMetadataGet AS RETURN 1')
END
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [schema_id]=SCHEMA_ID('dbo') AND type = 'P' AND name = 'Savision_Community_SLA_ServiceLevelAgreementObjectiveAvailabilityMonitorDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Savision_Community_SLA_ServiceLevelAgreementObjectiveAvailabilityMonitorDataGet AS RETURN 1')
END
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [schema_id]=SCHEMA_ID('dbo') AND type = 'P' AND name = 'Savision_Community_SLA_ServiceLevelAgreementObjectiveDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Savision_Community_SLA_ServiceLevelAgreementObjectiveDataGet AS RETURN 1')
END
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [schema_id]=SCHEMA_ID('dbo') AND type = 'P' AND name = 'Savision_Community_SLA_ServiceLevelObjectiveGetByServiceLevelAgreement')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Savision_Community_SLA_ServiceLevelObjectiveGetByServiceLevelAgreement AS RETURN 1')
END
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [schema_id]=SCHEMA_ID('dbo') AND type = 'P' AND name = 'Savision_Community_SLA_ServiceLevelObjectiveManagedEntityDetailDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Savision_Community_SLA_ServiceLevelObjectiveManagedEntityDetailDataGet AS RETURN 1')
END
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [schema_id]=SCHEMA_ID('dbo') AND type = 'P' AND name = 'Savision_Community_SLA_ManagedEntityMonitorDependencyList')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Savision_Community_SLA_ManagedEntityMonitorDependencyList AS RETURN 1')
END
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [schema_id]=SCHEMA_ID('dbo') AND type = 'P' AND name = 'Savision_Community_SLA_ServiceLevelAgreementManagedEntityGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Savision_Community_SLA_ServiceLevelAgreementManagedEntityGet AS RETURN 1')
END
GO
GRANT EXECUTE ON dbo.Savision_Community_SLA_ServiceLevelAgreementMetadataGet TO OpsMgrReader
GRANT EXECUTE ON dbo.Savision_Community_SLA_ServiceLevelAgreementObjectiveAvailabilityMonitorDataGet TO OpsMgrReader
GRANT EXECUTE ON dbo.Savision_Community_SLA_ServiceLevelAgreementObjectiveDataGet TO OpsMgrReader
GRANT EXECUTE ON dbo.Savision_Community_SLA_ServiceLevelObjectiveGetByServiceLevelAgreement TO OpsMgrReader
GRANT EXECUTE ON dbo.Savision_Community_SLA_ServiceLevelObjectiveManagedEntityDetailDataGet TO OpsMgrReader
GRANT EXECUTE ON dbo.Savision_Community_SLA_ManagedEntityMonitorDependencyList TO OpsMgrReader
GRANT EXECUTE ON dbo.Savision_Community_SLA_ServiceLevelAgreementManagedEntityGet TO OpsMgrReader
GO
ALTER PROCEDURE [dbo].[Savision_Community_SLA_ServiceLevelAgreementMetadataGet]
@StartDate datetime
,@EndDate datetime
,@ServiceLevelAgreementXml xml
,@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)
,ServiceLevelAgreementTargetManagedEntityTypeGuid = sla_target_met.ManagedEntityTypeGuid
,ServiceLevelAgreementTargetManagedEntityTypeDisplayName = ISNULL(sla_target_met_ds.[Name], sla_target_met.ManagedEntityTypeDefaultName)
,ServiceLevelAgreementTargetManagedEntityTypeImage = sla_target_met_image.[Image]
,ContextManagedEntityTypeGuid = ISNULL(sla_context_me_met.ManagedEntityTypeGuid, sla_context_met.ManagedEntityTypeGuid)
,ContextManagedEntityTypeDisplayName = COALESCE(sla_context_me_met_ds.[Name], sla_context_met_ds.[Name], sla_context_met.ManagedEntityTypeDefaultName)
,ContextManagedEntityTypeImage = ISNULL(sla_context_me_met_image.[Image], sla_context_met_image.[Image])
,slame.ContextManagedEntityGuid
,ContextManagedEntityDisplayName = sla_context_me.DisplayName
,ContextManagedEntityPath = sla_context_me.Path
,slame.ManagementGroupRowId
,mg.ManagementGroupGuid
,ManagementGroupDisplayName = mg.ManagementGroupDefaultName
,ServiceLevelAgreementManagedEntityRowId = slame.ManagedEntityRowId
,ServiceLevelAgreementManagedEntityTypeGuid = sla_me_met.ManagedEntityTypeGuid
,ServiceLevelAgreementManagedEntityTypeDisplayName = ISNULL(sla_me_met_ds.[Name], sla_me_met.ManagedEntityTypeDefaultName)
,ServiceLevelAgreementManagedEntityTypeImage = sla_me_met_image.[Image]
,ServiceLevelAgreementManagedEntityGuid = sla_me.ManagedEntityGuid
,ServiceLevelAgreementManagedEntityDisplayName = ISNULL(sla_me_ds.[Name], sla_me.DisplayName)
,ServiceLevelAgreementManagedEntityPath = sla_me.Path
,temp_slo.ServiceLevelObjectiveRowId
,slo.ServiceLevelObjectiveGuid
,slo.ServiceLevelObjectiveRowId ServiceLevelObjectiveRowId1
,ServiceLevelObjectiveDisplayName = ISNULL(slo_ds.[Name], slo.ServiceLevelObjectiveDefaultName)
,ServiceLevelObjectiveTargetManagedEntityTypeGuid = slo_target_met.ManagedEntityTypeGuid
,ServiceLevelObjectiveTargetManagedEntityTypeDisplayName = ISNULL(slo_target_met_ds.[Name], slo_target_met.ManagedEntityTypeDefaultName)
,ServiceLevelObjectiveTargetManagedEntityTypeImage = slo_target_met_image.[Image]
,temp_slo.Goal
,temp_slo.DesiredObjective
,temp_slo.AggregationMethod
,slompv.MonitorServiceLevelObjectiveInd
,slompv.PerformanceServiceLevelObjectiveInd
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 vManagedEntityType sla_target_met
ON (slame.TargetManagedEntityTypeRowId = sla_target_met.ManagedEntityTypeRowId)
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 vManagedEntity sla_context_me
ON (slame.ContextManagedEntityGuid = sla_context_me.ManagedEntityGuid)
AND (sla_context_me.ManagementGroupRowId = mg.ManagementGroupRowId)
LEFT JOIN vManagedEntityType sla_context_me_met
ON (sla_context_me.ManagedEntityTypeRowId = sla_context_me_met.ManagedEntityTypeRowId)
LEFT JOIN vManagedEntityType sla_context_met
ON (slame.ContextManagedEntityTypeRowId = sla_context_met.ManagedEntityTypeRowId)
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_target_met_ds
ON (sla_target_met.ManagedEntityTypeGuid = sla_target_met_ds.ElementGuid)
AND (sla_target_met_ds.LanguageCode = @LanguageCode)
LEFT JOIN vManagedEntityTypeImage sla_target_met_image
ON (sla_target_met_image.ManagedEntityTypeRowId = sla_target_met.ManagedEntityTypeRowId)
AND (sla_target_met_image.ImageCategory = N'u16x16Icon')
LEFT JOIN vDisplayString sla_context_met_ds
ON (sla_context_met.ManagedEntityTypeGuid = sla_context_met_ds.ElementGuid)
AND (sla_context_met_ds.LanguageCode = @LanguageCode)
LEFT JOIN vManagedEntityTypeImage sla_context_met_image
ON (sla_context_met_image.ManagedEntityTypeRowId = sla_context_met.ManagedEntityTypeRowId)
AND (sla_context_met_image.ImageCategory = N'u16x16Icon')
LEFT JOIN vDisplayString sla_context_me_met_ds
ON (sla_context_met.ManagedEntityTypeGuid = sla_context_me_met_ds.ElementGuid)
AND (sla_context_me_met_ds.LanguageCode = @LanguageCode)
LEFT JOIN vManagedEntityTypeImage sla_context_me_met_image
ON (sla_context_me_met_image.ManagedEntityTypeRowId = sla_context_met.ManagedEntityTypeRowId)
AND (sla_context_me_met_image.ImageCategory = N'u16x16Icon')
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 vManagedEntityTypeImage sla_me_met_image
ON (sla_me_met_image.ManagedEntityTypeRowId = sla_me_met.ManagedEntityTypeRowId)
AND (sla_me_met_image.ImageCategory = N'u16x16Icon')
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)
LEFT JOIN vManagedEntityTypeImage slo_target_met_image
ON (slo_target_met_image.ManagedEntityTypeRowId = slo_target_met.ManagedEntityTypeRowId)
AND (slo_target_met_image.ImageCategory = N'u16x16Icon')
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
ALTER PROCEDURE [dbo].[Savision_Community_SLA_ServiceLevelAgreementObjectiveAvailabilityMonitorDataGet]
@StartDate datetime,
@EndDate datetime,
@ServiceLevelAgreementRowId int,
@ServiceLevelAgreementManagedEntityRowId int,
@ServiceLevelObjectiveManagedEntityRowId int,
@ServiceLevelObjectiveGuid Uniqueidentifier,
@DataAggregation tinyint = 0,
@LevelCount int = 0,
@LanguageCode varchar(3) = 'ENU',
@ToRun int
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int,
@ManagedEntityMonitorRowId Int
IF @ToRun=0
BEGIN
SELECT
1
END
ELSE
BEGIN
DECLARE @Level INT
,@RowCount INT
,@ManagementGroupRowId INT
,@ContainmentRelationshipTypeRowId INT
-- 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
SET @ServiceLevelObjectiveManagedEntityRowId=(
SELECT
ServiceLevelObjectiveManagedEntityRowId
FROM #ServiceLevelObjectiveManagedEntity)
DROP TABLE #ServiceLevelObjectiveManagedEntity
SELECT
sla.ServiceLevelAgreementRowId
,slo.ServiceLevelObjectiveRowId
,slompv.MonitorServiceLevelObjectiveInd
,slompv.PerformanceServiceLevelObjectiveInd
,m.MonitorGuid
,m.MonitorSystemName
,m.MonitorRowId
,r.RuleGuid
,r.RuleSystemName INTO #tmp
FROM vServiceLevelObjective AS slo
INNER JOIN vServiceLevelObjectiveManagementPackVersion AS slompv
ON slompv.ServiceLevelObjectiveRowId = slo.ServiceLevelObjectiveRowId
INNER JOIN vManagedEntityType AS met
ON slompv.TargetManagedEntityTypeRowId = met.ManagedEntityTypeRowId
INNER JOIN vServiceLevelAgreement AS sla
ON sla.ServiceLevelAgreementRowId = slompv.ServiceLevelAgreementRowId
LEFT OUTER JOIN vMonitorServiceLevelObjectiveManagementPackVersion AS mslompv
ON mslompv.ServiceLevelObjectiveManagementPackVersionRowId = slompv.ServiceLevelObjectiveManagementPackVersionRowId
LEFT OUTER JOIN vPerformanceServiceLevelObjectiveManagementPackVersion AS pslompv
ON pslompv.ServiceLevelObjectiveManagementPackVersionRowId = slompv.ServiceLevelObjectiveManagementPackVersionRowId
LEFT OUTER JOIN vMonitor AS m
ON mslompv.MonitorRowId = m.MonitorRowId
LEFT OUTER JOIN vRule AS r
ON pslompv.RuleRowId = r.RuleRowId
WHERE (slo.ServiceLevelObjectiveGuid = @ServiceLevelObjectiveGuid) --ServiceLevelObjectiveGuid
AND (NOT EXISTS (SELECT
slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId
,slompv_newer.ServiceLevelObjectiveRowId
,slompv_newer.ManagementPackVersionRowId
,slompv_newer.ServiceLevelAgreementRowId
,slompv_newer.TargetManagedEntityTypeRowId
,slompv_newer.MonitorServiceLevelObjectiveInd
,slompv_newer.PerformanceServiceLevelObjectiveInd
,mgmpv.ManagementGroupManagementPackVersionRowId
,mgmpv.ManagementGroupRowId
,mgmpv.ManagementPackVersionRowId AS Expr1
,mgmpv.InstalledDateTime
,mgmpv.DeletedDateTime
,mgmpv.UpgradedInd
,mgmpv.LatestVersionInd
,mgmpv.DWCreatedDateTime
,mgmpv.DWLastModifiedDateTime
FROM vServiceLevelObjectiveManagementPackVersion AS slompv_newer
INNER JOIN vManagementGroupManagementPackVersion AS mgmpv
ON slompv_newer.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId
WHERE (slompv_newer.ServiceLevelObjectiveRowId = slo.ServiceLevelObjectiveRowId)
AND (mgmpv.ManagementGroupRowId = (SELECT
DISTINCT
ManagementGroupRowId
FROM vManagedEntity
WHERE (ManagedEntityRowId = @ServiceLevelAgreementRowId --ServiceLevelAgreementRowId
))
)
AND (slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId > slompv.ServiceLevelObjectiveManagementPackVersionRowId))
)
SET @ManagedEntityMonitorRowId = (SELECT
ManagedEntityMonitorRowId
FROM dbo.vManagedEntityMonitor vmem
JOIN dbo.Monitor M
ON M.MonitorRowId = vmem.MonitorRowId
WHERE m.MonitorSystemName IN (SELECT
MonitorSystemName
FROM #tmp)
AND (vmem.ManagedEntityRowId = @ServiceLevelObjectiveManagedEntityRowId))
DROP TABLE #tmp
CREATE TABLE #ManagedEntityMonitorDependency (
[Level] INT NOT NULL
,ParentManagedEntityMonitorRowId INT NULL
,ChildManagedEntityMonitorRowId INT NOT NULL
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ManagedEntityMonitorDependency
EXECUTE @ExecError = Savision_Community_SLA_ManagedEntityMonitorDependencyList @ManagedEntityMonitorRowId
,@LevelCount
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
SELECT
vmpv.MonitorRowId
,vmpv.UnitMonitorInd
,vmpv.AggregateMonitorInd
,vmpv.DependencyMonitorInd
,vmpv.RelationshipTypeRowId INTO #vMonitorManagementPackVersion
FROM vMonitorManagementPackVersion vmpv
WHERE EXISTS (SELECT
*
FROM vManagementGroupManagementPackVersion mgmpv
JOIN vManagementPackVersion mpv
ON (mpv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)
WHERE (mpv.ManagementPackVersionRowId = vmpv.ManagementPackVersionRowId)
AND (mgmpv.InstalledDateTime <= @EndDate)
AND (ISNULL(mgmpv.DeletedDateTime, '99991231') > @StartDate))
SELECT
M.MonitorDefaultName
,M.MonitorGuid
,VM.UnitMonitorInd
,M.MonitorRowId
,vm.AggregateMonitorInd
,vm.DependencyMonitorInd
,vm.RelationshipTypeRowId INTO #vMonitor
FROM vMonitor M
JOIN #vMonitorManagementPackVersion VM
ON M.MonitorRowId = Vm.MonitorRowId
/* ------------------------------ */
IF @DataAggregation = 1
SELECT
vState.DateTime
,vState.InRedStateMilliseconds
,vState.InYellowStateMilliseconds
,vState.InGreenStateMilliseconds
,vState.InWhiteStateMilliseconds
,vState.InDisabledStateMilliseconds
,vState.InPlannedMaintenanceMilliseconds
,vState.InUnplannedMaintenanceMilliseconds
,vState.HealthServiceUnavailableMilliseconds
,vState.IntervalDurationMilliseconds
,vManagedEntity.ManagedEntityRowId
,vManagedEntityMonitor.ManagedEntityMonitorRowId
,vManagedEntityTypeImage.Image
,vManagedEntity.ManagedEntityDefaultName
,vManagedEntityType.ManagedEntityTypeGuid
,vManagedEntityMonitorDependency.ParentManagedEntityMonitorRowId
,vManagedEntityMonitorDependency.[Level]
,ISNULL(vDisplayStringMonitor.Name, #vMonitor.MonitorDefaultName) AS MonitorDisplayName
,#vMonitor.AggregateMonitorInd
,#vMonitor.DependencyMonitorInd
,vManagedEntity.ManagedEntityGuid
,vManagedEntity.Path
,ISNULL(vDisplayString.Name, vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName
,vManagementGroup.ManagementGroupGuid
,vManagementGroup.ManagementGroupDefaultName
FROM vStateDailyFull AS vState
INNER JOIN vManagedEntityMonitor
ON vState.ManagedEntityMonitorRowId = vManagedEntityMonitor.ManagedEntityMonitorRowId
INNER JOIN vManagedEntity
ON vManagedEntityMonitor.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN vManagedEntityType
ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId
INNER JOIN #vMonitor
ON vState.MonitorRowId = #vMonitor.MonitorRowId
INNER JOIN #ManagedEntityMonitorDependency
AS vManagedEntityMonitorDependency
ON vState.ManagedEntityMonitorRowId = vManagedEntityMonitorDependency.ChildManagedEntityMonitorRowId
INNER JOIN vManagementGroup
ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId
LEFT OUTER JOIN vManagedEntityTypeImage
ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId
AND vManagedEntityTypeImage.ImageCategory = N'u16x16Icon'
LEFT OUTER JOIN vDisplayString
ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString vDisplayStringMonitor
ON #vMonitor.MonitorGuid = vDisplayStringMonitor.ElementGuid
AND vDisplayStringMonitor.LanguageCode = @LanguageCode
WHERE (vState.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate) + 1, CONVERT(VARCHAR(8), @StartDate, 112)))
AND (vState.DateTime < DATEADD(hh, DATEPART(hh, @EndDate) + 1, CONVERT(VARCHAR(8), @EndDate, 112)))
AND (vState.Date BETWEEN DATEADD(DAY, -1, @StartDate) AND DATEADD(DAY, 1, @EndDate))
AND (#vMonitor.AggregateMonitorInd = 1
OR #vMonitor.UnitMonitorInd = 1) --
AND (vState.InRedStateMilliseconds > 0)
ELSE
SELECT
vState.DateTime
,vState.InRedStateMilliseconds
,vState.InYellowStateMilliseconds
,vState.InGreenStateMilliseconds
,vState.InWhiteStateMilliseconds
,vState.InDisabledStateMilliseconds
,vState.InPlannedMaintenanceMilliseconds
,vState.InUnplannedMaintenanceMilliseconds
,vState.HealthServiceUnavailableMilliseconds
,vState.IntervalDurationMilliseconds
,vManagedEntity.ManagedEntityRowId
,vManagedEntityMonitor.ManagedEntityMonitorRowId
,vManagedEntityTypeImage.Image
,vManagedEntity.ManagedEntityDefaultName
,vManagedEntityType.ManagedEntityTypeGuid
,vManagedEntityMonitorDependency.ParentManagedEntityMonitorRowId
,vManagedEntityMonitorDependency.[Level]
,ISNULL(vDisplayStringMonitor.Name, #vMonitor.MonitorDefaultName) AS MonitorDisplayName
,#vMonitor.AggregateMonitorInd
,#vMonitor.DependencyMonitorInd
,vManagedEntity.ManagedEntityGuid
,vManagedEntity.Path
,ISNULL(vDisplayString.Name, vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName
,vManagementGroup.ManagementGroupGuid
,vManagementGroup.ManagementGroupDefaultName
FROM vStateHourlyFull AS vState
INNER JOIN vManagedEntityMonitor
ON vState.ManagedEntityMonitorRowId = vManagedEntityMonitor.ManagedEntityMonitorRowId
INNER JOIN vManagedEntity
ON vManagedEntityMonitor.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN vManagedEntityType
ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId
INNER JOIN #vMonitor
ON vState.MonitorRowId = #vMonitor.MonitorRowId
INNER JOIN #ManagedEntityMonitorDependency
AS vManagedEntityMonitorDependency
ON vState.ManagedEntityMonitorRowId = vManagedEntityMonitorDependency.ChildManagedEntityMonitorRowId
INNER JOIN vManagementGroup
ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId
LEFT OUTER JOIN vManagedEntityTypeImage
ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId
AND vManagedEntityTypeImage.ImageCategory = N'u16x16Icon'
LEFT OUTER JOIN vDisplayString
ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString vDisplayStringMonitor
ON #vMonitor.MonitorGuid = vDisplayStringMonitor.ElementGuid
AND vDisplayStringMonitor.LanguageCode = @LanguageCode
WHERE (vState.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), CONVERT(VARCHAR(8), @StartDate, 112)))
AND (vState.DateTime < DATEADD(hh, DATEPART(hh, @EndDate), CONVERT(VARCHAR(8), @EndDate, 112)))
AND (vState.Date >= DATEADD(DAY, -1, @StartDate))
AND (vState.Date < DATEADD(DAY, 1, @EndDate))
AND (#vMonitor.AggregateMonitorInd = 1
OR #vMonitor.UnitMonitorInd = 1) --
AND (vState.InRedStateMilliseconds > 0)
ORDER BY vState.DateTime, vManagedEntityMonitorDependency.Level
DROP TABLE #vMonitorManagementPackVersion
DROP TABLE #vMonitor
SET @Error = @@ERROR
SET STATISTICS IO OFF
QuitError:
DROP TABLE #ManagedEntityMonitorDependency
RETURN @Error
END
END
GO
ALTER PROCEDURE [dbo].[Savision_Community_SLA_ServiceLevelAgreementObjectiveDataGet]
@StartDate datetime
,@EndDate datetime
,@ServiceLevelAgreementXml xml
,@AggregationTypeId int
--,@ServiceLevelAgreementRowId int
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
)
INSERT #ContainedManagedEntity (
ServiceLevelAgreementRowId
,ManagementGroupRowId
,ServiceLevelAgreementManagedEntityRowId
,ManagedEntityRowId
,[Level]
)
SELECT
sla.ServiceLevelAgreementRowId
,sla.ManagementGroupRowId
,sla.ManagedEntityRowId
,sla.ManagedEntityRowId
,0
FROM #ServiceLevelAgreementManagedEntity sla
--WHERE sla.ServiceLevelAgreementRowId=@ServiceLevelAgreementRowId
SET @Level = 1
SET @RowCount = 1
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
-- 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)
CROSS APPLY dbo.ManagedEntityDerivedTypeHierarchy(slo.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'))
)
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 [Savision_Community_SLA_ServiceLevelObjectiveManagedEntityDetailDataGet]
@StartDate
,@EndDate
,@AggregationTypeId
SELECT
slame.ServiceLevelAgreementRowId
,slame.ManagementGroupRowId
,ServiceLevelAgreementManagedEntityRowId = slame.ManagedEntityRowId
,slomed.ServiceLevelObjectiveRowId
,slomed.ServiceLevelObjectiveManagedEntityRowId
,slomed.[DateTime]
,slomed.ActualValue
,slomed.Weight
,slo.Goal
,slo.DesiredObjective
,slo.AggregationMethod
,slomed.TotalDowntimeMilliseconds
FROM #ServiceLevelObjectiveManagedEntityDetail slomed
JOIN #ServiceLevelObjective slo ON (slo.ServiceLevelObjectiveRowId = slomed.ServiceLevelObjectiveRowId) AND (slo.ManagementGroupRowId = slomed.ManagementGroupRowId)
JOIN #ContainedManagedEntity cme ON (slomed.ServiceLevelObjectiveManagedEntityRowId = cme.ManagedEntityRowId)
JOIN #ServiceLevelAgreementManagedEntity slame ON (slo.ServiceLevelAgreementRowId = slame.ServiceLevelAgreementRowId) AND (slame.ManagementGroupRowId = slo.ManagementGroupRowId) AND (cme.ServiceLevelAgreementManagedEntityRowId = slame.ManagedEntityRowId)
--WHERE slame.ServiceLevelAgreementRowId=@ServiceLevelAgreementRowId
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
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
-- 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
ALTER PROCEDURE [dbo].[Savision_Community_SLA_ServiceLevelObjectiveGetByServiceLevelAgreement]
@StartDate datetime
,@EndDate datetime
,@ServiceLevelAgreementXml xml
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
)
-- 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
-- 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
ALTER PROCEDURE [dbo].[Savision_Community_SLA_ServiceLevelObjectiveManagedEntityDetailDataGet]
@StartDate datetime
,@EndDate datetime
,@AggregationTypeId int
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
@Statement nvarchar(max)
-- adjust date & time inline with availability report
-- exclude minutes, seconds, milliseconds
-- and add 1 hour in case of daily aggregations
SELECT
@StartDate = DATEADD(hh, DATEPART(hh, @StartDate) + CASE @AggregationTypeId WHEN 20 THEN 0 ELSE 1 END, convert(varchar(8), @StartDate, 112))
,@EndDate = DATEADD(hh, DATEPART(hh, @EndDate) + CASE @AggregationTypeId WHEN 20 THEN 0 ELSE 1 END, convert(varchar(8), @EndDate, 112))
+ ' SELECT'
+ ' slome.ServiceLevelObjectiveRowId'
+ ' ,slome.ManagementGroupRowId'
+ ' ,slome.ServiceLevelObjectiveManagedEntityRowId'
+ ' ,p.DateTime'
+ ' ,CASE pslompv.AggregationMethod'
+ ' WHEN ''min'' THEN p.MinValue'
+ ' WHEN ''avg'' THEN p.AverageValue'
+ ' WHEN ''max'' THEN p.MaxValue'
+ ' END '
+ ' ,p.SampleCount'
+ ' ,NULL'
+ ' FROM #ServiceLevelObjectiveManagedEntity slome'
+ ' JOIN vServiceLevelObjectiveManagementPackVersion slompv ON (slompv.ServiceLevelObjectiveRowId = slome.ServiceLevelObjectiveRowId)'
+ ' JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = slome.ManagementGroupRowId)'
+ ' JOIN vPerformanceServiceLevelObjectiveManagementPackVersion pslompv ON (slompv.ServiceLevelObjectiveManagementPackVersionRowId = pslompv.ServiceLevelObjectiveManagementPackVersionRowId)'
+ ' JOIN vPerformanceRuleInstance pri ON (pslompv.RuleRowId = pri.RuleRowId)'
+ ' JOIN %PerfDataTable% p ON (pri.PerformanceRuleInstanceRowId = p.PerformanceRuleInstanceRowId) AND (slome.ServiceLevelObjectiveManagedEntityRowId = p.ManagedEntityRowId)'
+ ' WHERE(p.[DateTime] >= @StartDate)'
+ ' AND (p.[DateTime] < @EndDate)'
+ ' 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 = slome.ManagementGroupRowId)'
+ ' AND (slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId > slompv.ServiceLevelObjectiveManagementPackVersionRowId)'
+ ' )'
IF (@AggregationTypeId = 20)
BEGIN
SET @Statement = REPLACE(@Statement, '%StateDataTable%', '[vStateHourlyFull]')
SET @Statement = REPLACE(@Statement, '%PerfDataTable%', '[Perf].[vPerfHourly]')
END
ELSE IF (@AggregationTypeId = 30)
BEGIN
SET @Statement = REPLACE(@Statement, '%StateDataTable%', '[vStateDailyFull]')
SET @Statement = REPLACE(@Statement, '%PerfDataTable%', '[Perf].[vPerfDaily]')
END
-- 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
ALTER PROCEDURE [dbo].[Savision_Community_SLA_ManagedEntityMonitorDependencyList]
@ParentManagedEntityMonitorRowId int
,@LevelCount int = 0
AS
BEGIN
CREATE TABLE #ResultTable
(
[Level] int NOT NULL
,ParentManagedEntityMonitorRowId int NULL
,ChildManagedEntityMonitorRowId int NOT NULL
)
WHILE (((@LevelCount >= @CurrentLevel) OR (@LevelCount = 0)) AND (@RowCount > 0))
BEGIN
SET @RowCount = 0
INSERT #ResultTable (
[Level]
,ParentManagedEntityMonitorRowId
,ChildManagedEntityMonitorRowId
)
SELECT DISTINCT
@CurrentLevel
,mem_parent.ManagedEntityMonitorRowId
,mem_child.ManagedEntityMonitorRowId
FROM vManagedEntityMonitor mem_parent
JOIN vManagedEntityMonitor mem_child ON (mem_parent.ManagedEntityRowId = mem_child.ManagedEntityRowId)
JOIN vManagedEntity me ON (mem_parent.ManagedEntityRowId = me.ManagedEntityRowId)
JOIN vMonitorManagementPackVersion mmpv_parent ON (mem_parent.MonitorRowId = mmpv_parent.MonitorRowId)
JOIN vMonitorManagementPackVersion mmpv_child ON (mem_child.MonitorRowId = mmpv_child.MonitorRowId)
JOIN #ResultTable r ON (mem_parent.ManagedEntityMonitorRowId = r.ChildManagedEntityMonitorRowId)
WHERE (r.[Level] = @CurrentLevel - 1)
AND (mmpv_parent.AggregateMonitorInd = 1)
AND (mmpv_child.ParentMonitorRowId = mem_parent.MonitorRowId)
SET @RowCount = @RowCount + @@ROWCOUNT
INSERT #ResultTable (
[Level]
,ParentManagedEntityMonitorRowId
,ChildManagedEntityMonitorRowId
)
SELECT DISTINCT
@CurrentLevel
,mem_parent.ManagedEntityMonitorRowId
,mem_child.ManagedEntityMonitorRowId
FROM vManagedEntityMonitor mem_parent
JOIN #ResultTable res ON (mem_parent.ManagedEntityMonitorRowId = res.ChildManagedEntityMonitorRowId)
JOIN vMonitorManagementPackVersion mmpv_parent ON (mem_parent.MonitorRowId = mmpv_parent.MonitorRowId)
JOIN vManagedEntityMonitor mem_child ON (mem_child.MonitorRowId = mmpv_parent.DependencyMonitorRowId)
JOIN vMonitorManagementPackVersion mmpv_child ON (mem_child.MonitorRowId = mmpv_child.MonitorRowId)
JOIN vRelationship r ON ((mem_parent.ManagedEntityRowId = r.SourceManagedEntityRowId) AND (mem_child.ManagedEntityRowId = r.TargetManagedEntityRowId))
WHERE (res.[Level] = @CurrentLevel - 1)
AND (mmpv_parent.DependencyMonitorInd = 1)
AND (mmpv_parent.RelationshipTypeRowId IN (SELECT RelationshipTypeRowId FROM dbo.RelationshipBaseTypeHierarchy(r.RelationshipTypeRowId, 0)))
SET @RowCount = @RowCount + @@ROWCOUNT
SET @CurrentLevel = @CurrentLevel + 1
END
SELECT
[Level]
,ParentManagedEntityMonitorRowId
,ChildManagedEntityMonitorRowId
FROM #ResultTable
DROP TABLE #ResultTable
END
GO
ALTER PROCEDURE [dbo].[Savision_Community_SLA_ServiceLevelAgreementManagedEntityGet]
@StartDate datetime
,@EndDate datetime
,@ServiceLevelAgreementXml xml
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
-- 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
-- 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
Home