<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.ServiceLevel.Report.Script" Accessibility="Public"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelObjectiveManagedEntityDetailDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelObjectiveManagedEntityDetailDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_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%', 'GetStateHourly(@StartDate, @EndDate)')
SET @Statement = REPLACE(@Statement, '%PerfDataTable%', '[Perf].[vPerfHourly]')
END
ELSE IF (@AggregationTypeId = 30)
BEGIN
SET @Statement = REPLACE(@Statement, '%StateDataTable%', 'GetStateDaily(@StartDate, @EndDate)')
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
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelObjectiveManagedEntityDetailDataGet] TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet]
@StartDate datetime
,@EndDate datetime
,@ServiceLevelAgreementXml xml
,@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
@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
-- 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 [Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_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)
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
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet] TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportMetaDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportMetaDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportMetaDataGet]
@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
,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
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportMetaDataGet] TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLeveObjectiveDetailReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLeveObjectiveDetailReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLeveObjectiveDetailReportDataGet]
@StartDate datetime
,@EndDate datetime
,@ServiceLevelAgreementManagedEntityRowId int
,@ServiceLevelObjectiveGuid uniqueidentifier
,@AggregationTypeId int
,@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
@Level int
,@RowCount int
,@ManagementGroupRowId int
,@ContainmentRelationshipTypeRowId int
BEGIN TRY
-- 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
-- result table
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 [Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelObjectiveManagedEntityDetailDataGet]
@StartDate
,@EndDate
,@AggregationTypeId
DROP TABLE #ServiceLevelObjectiveManagedEntity
SELECT
ManagedEntityRowId = slome.ServiceLevelObjectiveManagedEntityRowId
,ManagementGroupGuid = (SELECT ManagementGroupGuid FROM ManagementGroup WHERE ManagementGroupRowId = @ManagementGroupRowId)
,ManagementGroupDisplayName = (SELECT ManagementGroupDefaultName FROM ManagementGroup WHERE ManagementGroupRowId = @ManagementGroupRowId)
,met.ManagedEntityTypeGuid
,ManagedEntityTypeDisplayName = ISNULL(met_ds.[Name], met.ManagedEntityTypeDefaultName)
,ManagedEntityTypeImage = met_image.[Image]
,me.ManagedEntityGuid
,ManagedEntityDisplayName = me.DisplayName
,ManagedEntityPath = me.Path
,Goal =
CASE
WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN mslompv.Goal
WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN pslompv.Threshold
END
,DesiredObjective =
CASE
WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN 'over'
WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN pslompv.DesiredObjective
END
,AggregationMethod =
CASE
WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN 'avg'
WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN pslompv.AggregationMethod
END
,slome.[DateTime]
,slome.ActualValue
,slome.Weight
,slome.TotalDowntimeMilliseconds
FROM #ServiceLevelObjectiveManagedEntityDetail slome
JOIN vManagedEntity me ON (slome.ServiceLevelObjectiveManagedEntityRowId = me.ManagedEntityRowId)
JOIN vManagedEntityType met ON (me.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId)
JOIN vServiceLevelObjectiveManagementPackVersion slompv ON (slompv.ServiceLevelObjectiveRowId = slome.ServiceLevelObjectiveRowId)
JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = @ManagementGroupRowId)
LEFT JOIN vMonitorServiceLevelObjectiveManagementPackVersion mslompv ON (mslompv.ServiceLevelObjectiveManagementPackVersionRowId = slompv.ServiceLevelObjectiveManagementPackVersionRowId)
LEFT JOIN vPerformanceServiceLevelObjectiveManagementPackVersion pslompv ON (pslompv.ServiceLevelObjectiveManagementPackVersionRowId = slompv.ServiceLevelObjectiveManagementPackVersionRowId)
LEFT JOIN vDisplayString met_ds ON (met.ManagedEntityTypeGuid = met_ds.ElementGuid) AND (met_ds.LanguageCode = @LanguageCode)
LEFT JOIN vManagedEntityTypeImage met_image ON (met_image.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId) AND (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 = slome.ServiceLevelObjectiveRowId)
AND (mgmpv.ManagementGroupRowId = @ManagementGroupRowId)
AND (slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId > slompv.ServiceLevelObjectiveManagementPackVersionRowId)
) -- this is the latest SLO definition for this MG
ORDER BY slome.[DateTime]
DROP TABLE #ServiceLevelObjectiveManagedEntityDetail
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('#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
IF (OBJECT_ID('#ServiceLevelObjectiveManagedEntityDetail') IS NOT NULL)
DROP TABLE #ServiceLevelObjectiveManagedEntityDetail
-- 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 dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLeveObjectiveDetailReportDataGet] TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ServiceLevelAgreementParameterBlockDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ServiceLevelAgreementParameterBlockDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[ServiceLevelAgreementParameterBlockDataGet]
@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
DECLARE
@XmlDocHandle int
,@ExecResult 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
SELECT DISTINCT
ServiceLevelAgreementRowId = temp_sla.ServiceLevelAgreementRowId
,ServiceLevelAgreementDisplayName = ISNULL(sla_ds.[Name], sla.ServiceLevelAgreementDefaultName)
,ServiceLevelAgreementTargetManagedEntityTypeDisplayName = ISNULL(sla_target_met_ds.[Name], met.ManagedEntityTypeDefaultName)
,ManagementGroupDisplayName = mg.ManagementGroupDefaultName
FROM #ServiceLevelAgreement temp_sla
JOIN ManagementGroupManagementPackVersion mgmpv ON (temp_sla.ManagementGroupRowId = mgmpv.ManagementGroupRowId)
JOIN ServiceLevelAgreementManagementPackVersion slampv ON (slampv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)
JOIN ServiceLevelAgreement sla ON (sla.ServiceLevelAgreementRowId = slampv.ServiceLevelAgreementRowId)
JOIN ManagedEntityType met ON (slampv.TargetManagedEntityTypeRowId = met.ManagedEntityTypeRowId)
JOIN ManagementGroup mg ON (mgmpv.ManagementGroupRowId = mg.ManagementGroupRowId)
AND (slampv.ServiceLevelAgreementRowId = temp_sla.ServiceLevelAgreementRowId)
LEFT JOIN vDisplayString sla_ds ON (sla.ServiceLevelAgreementGuid = sla_ds.ElementGuid) AND (sla_ds.LanguageCode = @LanguageCode)
LEFT JOIN vDisplayString sla_target_met_ds ON (met.ManagedEntityTypeGuid = sla_target_met_ds.ElementGuid) AND (sla_target_met_ds.LanguageCode = @LanguageCode)
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
-- 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
--===============================================================
-- PERMISSIONS
--===============================================================
GRANT EXECUTE ON dbo.[ServiceLevelAgreementParameterBlockDataGet] TO OpsMgrReader
GO </Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelObjectiveManagedEntityDetailDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelObjectiveManagedEntityDetailDataGet]
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet]
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportMetaDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportMetaDataGet]
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLeveObjectiveDetailReportDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLeveObjectiveDetailReportDataGet]
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ServiceLevelAgreementParameterBlockDataGet')
BEGIN
DROP PROCEDURE dbo.[ServiceLevelAgreementParameterBlockDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelObjectiveManagedEntityDetailDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelObjectiveManagedEntityDetailDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_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%', 'GetStateHourly(@StartDate, @EndDate)')
SET @Statement = REPLACE(@Statement, '%PerfDataTable%', '[Perf].[vPerfHourly]')
END
ELSE IF (@AggregationTypeId = 30)
BEGIN
SET @Statement = REPLACE(@Statement, '%StateDataTable%', 'GetStateDaily(@StartDate, @EndDate)')
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
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelObjectiveManagedEntityDetailDataGet] TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet]
@StartDate datetime
,@EndDate datetime
,@ServiceLevelAgreementXml xml
,@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
@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
-- 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 [Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_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)
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
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportDataGet] TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportMetaDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportMetaDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportMetaDataGet]
@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
,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
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelAgreementSummaryReportMetaDataGet] TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLeveObjectiveDetailReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLeveObjectiveDetailReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLeveObjectiveDetailReportDataGet]
@StartDate datetime
,@EndDate datetime
,@ServiceLevelAgreementManagedEntityRowId int
,@ServiceLevelObjectiveGuid uniqueidentifier
,@AggregationTypeId int
,@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
@Level int
,@RowCount int
,@ManagementGroupRowId int
,@ContainmentRelationshipTypeRowId int
BEGIN TRY
-- 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
-- result table
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 [Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLevelObjectiveManagedEntityDetailDataGet]
@StartDate
,@EndDate
,@AggregationTypeId
DROP TABLE #ServiceLevelObjectiveManagedEntity
SELECT
ManagedEntityRowId = slome.ServiceLevelObjectiveManagedEntityRowId
,ManagementGroupGuid = (SELECT ManagementGroupGuid FROM ManagementGroup WHERE ManagementGroupRowId = @ManagementGroupRowId)
,ManagementGroupDisplayName = (SELECT ManagementGroupDefaultName FROM ManagementGroup WHERE ManagementGroupRowId = @ManagementGroupRowId)
,met.ManagedEntityTypeGuid
,ManagedEntityTypeDisplayName = ISNULL(met_ds.[Name], met.ManagedEntityTypeDefaultName)
,ManagedEntityTypeImage = met_image.[Image]
,me.ManagedEntityGuid
,ManagedEntityDisplayName = me.DisplayName
,ManagedEntityPath = me.Path
,Goal =
CASE
WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN mslompv.Goal
WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN pslompv.Threshold
END
,DesiredObjective =
CASE
WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN 'over'
WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN pslompv.DesiredObjective
END
,AggregationMethod =
CASE
WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN 'avg'
WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN pslompv.AggregationMethod
END
,slome.[DateTime]
,slome.ActualValue
,slome.Weight
,slome.TotalDowntimeMilliseconds
FROM #ServiceLevelObjectiveManagedEntityDetail slome
JOIN vManagedEntity me ON (slome.ServiceLevelObjectiveManagedEntityRowId = me.ManagedEntityRowId)
JOIN vManagedEntityType met ON (me.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId)
JOIN vServiceLevelObjectiveManagementPackVersion slompv ON (slompv.ServiceLevelObjectiveRowId = slome.ServiceLevelObjectiveRowId)
JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = @ManagementGroupRowId)
LEFT JOIN vMonitorServiceLevelObjectiveManagementPackVersion mslompv ON (mslompv.ServiceLevelObjectiveManagementPackVersionRowId = slompv.ServiceLevelObjectiveManagementPackVersionRowId)
LEFT JOIN vPerformanceServiceLevelObjectiveManagementPackVersion pslompv ON (pslompv.ServiceLevelObjectiveManagementPackVersionRowId = slompv.ServiceLevelObjectiveManagementPackVersionRowId)
LEFT JOIN vDisplayString met_ds ON (met.ManagedEntityTypeGuid = met_ds.ElementGuid) AND (met_ds.LanguageCode = @LanguageCode)
LEFT JOIN vManagedEntityTypeImage met_image ON (met_image.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId) AND (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 = slome.ServiceLevelObjectiveRowId)
AND (mgmpv.ManagementGroupRowId = @ManagementGroupRowId)
AND (slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId > slompv.ServiceLevelObjectiveManagementPackVersionRowId)
) -- this is the latest SLO definition for this MG
ORDER BY slome.[DateTime]
DROP TABLE #ServiceLevelObjectiveManagedEntityDetail
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('#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
IF (OBJECT_ID('#ServiceLevelObjectiveManagedEntityDetail') IS NOT NULL)
DROP TABLE #ServiceLevelObjectiveManagedEntityDetail
-- 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 dbo.[Microsoft_SystemCenter_DataWarehouse_ServiceLevel_Report_Library_ServiceLeveObjectiveDetailReportDataGet] TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ServiceLevelAgreementParameterBlockDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ServiceLevelAgreementParameterBlockDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[ServiceLevelAgreementParameterBlockDataGet]
@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
DECLARE
@XmlDocHandle int
,@ExecResult 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
SELECT DISTINCT
ServiceLevelAgreementRowId = temp_sla.ServiceLevelAgreementRowId
,ServiceLevelAgreementDisplayName = ISNULL(sla_ds.[Name], sla.ServiceLevelAgreementDefaultName)
,ServiceLevelAgreementTargetManagedEntityTypeDisplayName = ISNULL(sla_target_met_ds.[Name], met.ManagedEntityTypeDefaultName)
,ManagementGroupDisplayName = mg.ManagementGroupDefaultName
FROM #ServiceLevelAgreement temp_sla
JOIN ManagementGroupManagementPackVersion mgmpv ON (temp_sla.ManagementGroupRowId = mgmpv.ManagementGroupRowId)
JOIN ServiceLevelAgreementManagementPackVersion slampv ON (slampv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)
JOIN ServiceLevelAgreement sla ON (sla.ServiceLevelAgreementRowId = slampv.ServiceLevelAgreementRowId)
JOIN ManagedEntityType met ON (slampv.TargetManagedEntityTypeRowId = met.ManagedEntityTypeRowId)
JOIN ManagementGroup mg ON (mgmpv.ManagementGroupRowId = mg.ManagementGroupRowId)
AND (slampv.ServiceLevelAgreementRowId = temp_sla.ServiceLevelAgreementRowId)
LEFT JOIN vDisplayString sla_ds ON (sla.ServiceLevelAgreementGuid = sla_ds.ElementGuid) AND (sla_ds.LanguageCode = @LanguageCode)
LEFT JOIN vDisplayString sla_target_met_ds ON (met.ManagedEntityTypeGuid = sla_target_met_ds.ElementGuid) AND (sla_target_met_ds.LanguageCode = @LanguageCode)
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
-- 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
--===============================================================
-- PERMISSIONS
--===============================================================
GRANT EXECUTE ON dbo.[ServiceLevelAgreementParameterBlockDataGet] TO OpsMgrReader
GO </Upgrade>
</DataWarehouseScript>
Home