DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
INSERT StandardDatasetAggregation
(
DatasetId
,AggregationTypeId
,AggregationIntervalDurationMinutes
,AggregationStartDelayMinutes
,BuildAggregationStoredProcedureName
,DeleteAggregationStoredProcedureName
,GroomStoredProcedureName
,IndexOptimizationIntervalMinutes
,MaxDataAgeDays
,GroomingIntervalMinutes
,MaxRowsToGroom
,LastGroomingDateTime
,DataFileGroupName
,IndexFileGroupName
)
SELECT
'$Config/DatasetId$'
,CASE AggregationType
WHEN 'Raw' THEN 0
WHEN 'Subhourly' THEN 10
WHEN 'Hourly' THEN 20
WHEN 'Daily' THEN 30
END
,CASE AggregationType
WHEN 'Raw' THEN NULL
WHEN 'Subhourly' THEN AggregationIntervalDurationMinutes
WHEN 'Hourly' THEN 60
WHEN 'Daily' THEN 24*60
END
,AggregationStartDelayMinutes
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'StateAggregate'
END
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'StateAggregationDelete'
END
,'StateGroom'
,IndexOptimizationIntervalMinutes
,MaxDataAgeDays
,GroomingIntervalMinutes
,MaxRowsToGroom
,GETUTCDATE()
,DataFileGroupName
,IndexFileGroupName
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname'
,MaxTableRowCount int
,MaxTableSizeKb int
,AggregationStartDelayMinutes int
,MaxDataAgeDays int
,GroomingIntervalMinutes int
,MaxRowsToGroom int
,IndexOptimizationIntervalMinutes int
,DataFileGroupName sysname
,IndexFileGroupName sysname
,AggregationIntervalDurationMinutes int
)
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
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
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
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
SELECT
'$Config/DatasetId$'
,CASE xml.AggregationType
WHEN 'Raw' THEN 0
WHEN 'Subhourly' THEN 10
WHEN 'Hourly' THEN 20
WHEN 'Daily' THEN 30
END
,xml.BaseTableName
,0
,CASE xml.AggregationType
WHEN 'Raw' THEN '
CREATE TABLE ' + QUOTENAME(d.SchemaName) + '.' + QUOTENAME(xml.BaseTableName + '_$Guid$') + '
(
' + QUOTENAME(xml.BaseTableName + 'RowId') + ' int NOT NULL IDENTITY(1, 1)
,EventOriginId uniqueidentifier NOT NULL
,ManagedEntityMonitorRowId int NOT NULL
,[DateTime] datetime NOT NULL
,OldHealthState tinyint NOT NULL
,NewHealthState tinyint NOT NULL
,CONSTRAINT ' + QUOTENAME('PK_' + xml.BaseTableName + '_$Guid$') + ' PRIMARY KEY CLUSTERED ('
+ QUOTENAME(xml.BaseTableName + 'RowId') + ') ON $DataFileGroupName$
) ON $DataFileGroupName$
'
ELSE '
CREATE TABLE ' + QUOTENAME(d.SchemaName) + '.' + QUOTENAME(xml.BaseTableName + '_$Guid$') + '
(
' + QUOTENAME(xml.BaseTableName + 'RowId') + ' int NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,ManagedEntityMonitorRowId int NOT NULL
,InRedStateMilliseconds int NOT NULL
,InYellowStateMilliseconds int NOT NULL
,InDisabledStateMilliseconds int NOT NULL
,InPlannedMaintenanceMilliseconds int NOT NULL
,InUnplannedMaintenanceMilliseconds int NOT NULL
,HealthServiceUnavailableMilliseconds int NOT NULL
,IntervalEndHealthState tinyint NULL
,CONSTRAINT ' + QUOTENAME('PK_' + xml.BaseTableName + '_$Guid$') + ' PRIMARY KEY CLUSTERED (' +
+ QUOTENAME(xml.BaseTableName + 'RowId') + ') ON $DataFileGroupName$
) ON $DataFileGroupName$
'
END
,CASE xml.AggregationType
WHEN 'Raw' THEN '
SELECT
EventOriginId
,ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState
,NewHealthState
'
ELSE '
SELECT
ManagedEntityMonitorRowId
,[DateTime]
,InRedStateMilliseconds
,InYellowStateMilliseconds
,InDisabledStateMilliseconds
,InPlannedMaintenanceMilliseconds
,InUnplannedMaintenanceMilliseconds
,HealthServiceUnavailableMilliseconds
,IntervalEndHealthState
'
END
,xml.MaxTableRowCount
,xml.MaxTableSizeKb
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname'
,BaseTableName sysname
,MaxTableRowCount int
,MaxTableSizeKb int
,DataFileGroupName sysname
,IndexFileGroupName sysname
) xml
CROSS JOIN StandardDataset d
WHERE (d.DatasetId = '$Config/DatasetId$')
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
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
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
GO
SET NOCOUNT ON
GO
-- create [DateTime] field indexes for all aggregation levels
DECLARE
@AggregationTypeId int
,@Statement nvarchar(max)
,@IndeGuid uniqueidentifier
SET @AggregationTypeId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregation
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
)
BEGIN
SELECT @IndeGuid = NEWID()
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE [name] = 'State')
BEGIN
EXECUTE('CREATE SCHEMA State')
END
GO
-- change schema ownership to allow index rebuild and reorganize
ALTER AUTHORIZATION ON SCHEMA::State TO OpsMgrWriter
GO
-- change schema alter to allow domain table index rebuild
GRANT ALTER ON SCHEMA::dbo TO OpsMgrWriter
GO
-- grant create table permissions
GRANT CREATE TABLE TO OpsMgrWriter
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
WHILE EXISTS (SELECT *
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname')
WHERE AggregationType > @AggregationType
)
BEGIN
SELECT TOP 1 @AggregationType = AggregationType
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname')
WHERE AggregationType > @AggregationType
ORDER BY AggregationType
SET @Statement = 'EXEC StandardDatasetAllocateStorage @DatasetId = ''$Config/DatasetId$'', @AggregationTypeId='
+ CASE @AggregationType
WHEN 'Raw' THEN '0'
WHEN 'Subhourly' THEN '10'
WHEN 'Hourly' THEN '20'
WHEN 'Daily' THEN '30'
END
EXECUTE (@Statement)
END
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
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
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
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ManagedEntityMonitor' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.ManagedEntityMonitor
END
GO
CREATE TABLE dbo.ManagedEntityMonitor
(
ManagedEntityMonitorRowId int NOT NULL IDENTITY(1,1)
,ManagedEntityRowId int NOT NULL
,MonitorRowId int NOT NULL
,DWCreatedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
CREATE INDEX IX_ManagedEntityMonitor_ManagedEntityRowId ON ManagedEntityMonitor(ManagedEntityRowId)
GO
CREATE INDEX IX_ManagedEntityMonitor_MonitorRowId ON ManagedEntityMonitor(MonitorRowId)
GO
EXEC DomainTableRegisterIndexOptimization
@TableName = 'ManagedEntityMonitor'
,@DatasetId = '$Config/DatasetId$'
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StateAggregationProcessing' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('CREATE TABLE dbo.StateAggregationProcessing (
DatasetId uniqueidentifier NOT NULL
,LastProcessedMaintenanceModeModifiedDateTime datetime NULL
,LastProcessedHealthServiceOutageModifiedDateTime datetime NULL
)')
END
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StateHealthServiceOutage' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.StateHealthServiceOutage
(
ReasonCode tinyint NOT NULL
,DWCreatedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
,CONSTRAINT PK_StateHealthServiceOutage PRIMARY KEY CLUSTERED (ReasonCode)
)
END
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StateInvalidatedHealthServiceOutage' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.StateInvalidatedHealthServiceOutage
(
HealthServiceOutageRowId int NOT NULL
,DWCreatedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
,CONSTRAINT PK_StateInvalidatedHealthServiceOutage PRIMARY KEY CLUSTERED (HealthServiceOutageRowId)
)
END
GO
EXEC DomainTableRegisterIndexOptimization
@TableName = 'StateInvalidatedHealthServiceOutage'
,@DatasetId = '$Config/DatasetId$'
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StateProcessedHealthServiceOutage' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.StateProcessedHealthServiceOutage
(
HealthServiceOutageRowId int NOT NULL
,StartDateTime datetime NOT NULL
,EndDateTime datetime NULL
,ReasonCode tinyint NOT NULL
,HealthServiceOutageDWLastModifiedDateTime datetime NULL
,InvalidatedDateTime datetime NULL
,DWCreatedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
,DWLastModifiedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
,CONSTRAINT PK_StateProcessedHealthServiceOutage PRIMARY KEY CLUSTERED (HealthServiceOutageRowId)
)
END
GO
EXEC DomainTableRegisterIndexOptimization
@TableName = 'StateProcessedHealthServiceOutage'
,@DatasetId = '$Config/DatasetId$'
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StateProcessedMaintenanceMode' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.StateProcessedMaintenanceMode
(
MaintenanceModeRowId int NOT NULL
,StartDateTime datetime NOT NULL
,EndDateTime datetime NULL
,PlannedMaintenanceInd bit NOT NULL
,MaintenanceModeDWLastModifiedDateTime datetime NULL
,DWCreatedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
,DWLastModifiedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
,CONSTRAINT PK_StateProcessedMaintenanceMode PRIMARY KEY CLUSTERED (MaintenanceModeRowId)
)
END
GO
EXEC DomainTableRegisterIndexOptimization
@TableName = 'StateProcessedMaintenanceMode'
,@DatasetId = '$Config/DatasetId$'
GO
-- insert staging definition into StandardDatasetStagingArea
BEGIN TRAN
IF EXISTS (SELECT * FROM StandardDatasetStagingArea WHERE DatasetId = '$Config/DatasetId$')
BEGIN
DELETE StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$'
END
INSERT StandardDatasetStagingArea
(
DatasetId
,StagingTableColumnDefinition
,BatchedProcessingSupportedInd
)
VALUES
(
'$Config/DatasetId$'
,'
DatasetId uniqueidentifier NOT NULL
,ManagementGroupGuid uniqueidentifier NOT NULL
,EventOriginId uniqueidentifier NOT NULL
,[DateTime] datetime NOT NULL
,ManagedEntityGuid uniqueidentifier NOT NULL
,MonitorGuid uniqueidentifier NOT NULL
,OldHealthState tinyint NOT NULL
,NewHealthState tinyint NOT NULL
,ManagedEntityMonitorRowId int NULL
,InsertReadyInd AS (ISNULL(ManagedEntityMonitorRowId, 0))
,DWCreatedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
'
,0
)
COMMIT
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StateStage' AND TABLE_SCHEMA = 'State')
BEGIN
DECLARE @Statement nvarchar(max)
SELECT @Statement = 'CREATE TABLE State.StateStage (' + StagingTableColumnDefinition + ')'
FROM StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$'
EXECUTE (@Statement)
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON State.StateStage TO OpsMgrWriter
GO
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM StateHealthServiceOutage WHERE ReasonCode = 0)
BEGIN
INSERT StateHealthServiceOutage(ReasonCode) VALUES (0)
END
IF NOT EXISTS (SELECT * FROM StateHealthServiceOutage WHERE ReasonCode = 1)
BEGIN
INSERT StateHealthServiceOutage(ReasonCode) VALUES (1)
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagedEntityMonitor')
BEGIN
EXECUTE ('CREATE VIEW dbo.vManagedEntityMonitor AS SELECT A = 1')
END
GO
ALTER VIEW dbo.vManagedEntityMonitor
AS
SELECT *
FROM dbo.ManagedEntityMonitor (NOLOCK)
GO
GRANT SELECT ON dbo.vManagedEntityMonitor TO OpsMgrWriter, OpsMgrReader
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
BEGIN TRY
-- roll through all aggregations created
-- and build "full" state views
SET @AggregationTypeId = 0
SET @FullViewStatement = 'ALTER VIEW dbo.[vStateFull] AS '
SET @FullViewStatementConcatenator = ''
WHILE EXISTS (SELECT * FROM StandardDatasetAggregationStorage
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
AND (DependentTableInd = 0)
)
BEGIN
SELECT TOP 1
@AggregationTypeId = AggregationTypeId
,@BaseTableName = BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
AND (DependentTableInd = 0)
-- create dummy view if not yet exists
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'v' + REPLACE(@BaseTableName, '''', '''''') + 'Full')
BEGIN
SET @Statement = 'CREATE VIEW dbo.' + QUOTENAME('v' + @BaseTableName + 'Full') + ' AS SELECT A = 1'
EXECUTE (@Statement)
SET @Statement = 'GRANT SELECT ON dbo.' + QUOTENAME('v' + @BaseTableName + 'Full') + ' TO OpsMgrReader'
EXECUTE (@Statement)
END
SELECT @Statement =
'ALTER VIEW dbo.' + CAST(QUOTENAME('v' + @BaseTableName + 'Full') as nvarchar(max))
+ ' AS'
+ ' SELECT '
-- daily aggregations appear to be in "midnight" or thereabouts
-- to aid UI to present data in different timezones
+ ' [DateTime]'
+ ' ,[Date]'
+ ' ,[Time]'
+ ' ,ManagedEntityMonitorRowId'
+ ' ,ManagedEntityRowId'
+ ' ,MonitorRowId'
+ ' ,IntervalDurationMilliseconds'
+ ' ,InYellowStateMilliseconds'
+ ' ,InRedStateMilliseconds'
+ ' ,InPlannedMaintenanceMilliseconds'
+ ' ,InUnplannedMaintenanceMilliseconds'
+ ' ,InDisabledStateMilliseconds'
+ ' ,HealthServiceUnavailableMilliseconds'
+ ' ,InWhiteStateMilliseconds ='
+ ' CASE'
+ ' WHEN ((ManagedEntityMonitorExistedInd = 0) OR '
+ ' (ah.AggregationCount = 0) OR '
+ ' (DATEADD(ms, IntervalDurationMilliseconds, [DateTime]) >= GETUTCDATE())) '
+ ' THEN IntervalDurationMilliseconds - InYellowStateMilliseconds - InRedStateMilliseconds - InDisabledStateMilliseconds - InPlannedMaintenanceMilliseconds - InUnplannedMaintenanceMilliseconds - HealthServiceUnavailableMilliseconds'
+ ' ELSE 0'
+ ' END'
+ ' ,InGreenStateMilliseconds ='
+ ' CASE'
+ ' WHEN (ManagedEntityMonitorExistedInd = 1) AND '
+ ' ((ah.AggregationCount > 0) OR '
+ ' ((ah.AggregationCount IS NULL) AND '
+ ' (DATEADD(ms, IntervalDurationMilliseconds, [DateTime]) < GETUTCDATE()))) '
+ ' THEN IntervalDurationMilliseconds - InYellowStateMilliseconds - InRedStateMilliseconds - InDisabledStateMilliseconds - InPlannedMaintenanceMilliseconds - InUnplannedMaintenanceMilliseconds - HealthServiceUnavailableMilliseconds'
+ ' ELSE 0'
+ ' END'
+ ' FROM ('
+ ' SELECT'
+ ' dt.[DateTime]'
+ ' ,dt.[Date]'
+ ' ,dt.[Time]'
+ ' ,ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId'
+ ' ,ManagedEntityRowId = mem.ManagedEntityRowId'
+ ' ,MonitorRowId = mem.MonitorRowId'
+ ' ,ManagedEntityMonitorExistedInd = CASE WHEN mem_exists.ManagedEntityMonitorRowId IS NULL THEN 0 ELSE 1 END'
+ ' ,IntervalDurationMilliseconds = 60 * 1000 * ' + CAST(a.AggregationIntervalDurationMinutes AS varchar)
+ ' ,InYellowStateMilliseconds = ISNULL(s.InYellowStateMilliseconds, 0)'
+ ' ,InRedStateMilliseconds = ISNULL(s.InRedStateMilliseconds, 0)'
+ ' ,InDisabledStateMilliseconds = ISNULL(s.InDisabledStateMilliseconds, 0)'
+ ' ,InPlannedMaintenanceMilliseconds = ISNULL(s.InPlannedMaintenanceMilliseconds, 0)'
+ ' ,InUnplannedMaintenanceMilliseconds = ISNULL(s.InUnplannedMaintenanceMilliseconds, 0)'
+ ' ,HealthServiceUnavailableMilliseconds = ISNULL(s.HealthServiceUnavailableMilliseconds, 0)'
+ ' FROM vDateTime dt'
+ ' CROSS JOIN vManagedEntityMonitor mem'
+ ' LEFT JOIN ' + QUOTENAME(d.SchemaName) + '.' + QUOTENAME('v' + @BaseTableName) + ' s ON '
+ CASE @AggregationTypeId
WHEN 30 THEN '((s.[DateTime] >= dt.[DateTime]) AND (s.[DateTime] < DATEADD(day, 1, dt.[DateTime])) AND (s.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId))'
ELSE '((s.[DateTime] = dt.[DateTime]) AND (s.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId))'
END
+ ' LEFT JOIN (SELECT DISTINCT mem2.ManagedEntityMonitorRowId, dt2.[Date], dt2.[Time]'
+ ' FROM vDateTime dt2'
+ ' CROSS JOIN vManagedEntityMonitor mem2'
+ ' JOIN vManagedEntity me ON (me.ManagedEntityRowId = mem2.ManagedEntityRowId)'
+ ' JOIN vMonitorManagementPackVersion mmpv ON (mmpv.MonitorRowId = mem2.MonitorRowId)'
+ ' JOIN vManagementGroupManagementPackVersion mgmpv ON ((mgmpv.ManagementPackVersionRowId = mmpv.ManagementPackVersionRowId) AND'
+ ' (mgmpv.ManagementGroupRowId = me.ManagementGroupRowId) AND'
+ ' (dt2.[DateTime] >= mgmpv.InstalledDateTime) AND '
+ ' (dt2.[DateTime] < ISNULL(mgmpv.DeletedDateTime, ''99991231'')))'
+ ' JOIN vManagedEntityManagementGroup memg ON ((mem2.ManagedEntityRowId = memg.ManagedEntityRowId) AND'
+ ' (dt2.[DateTime] >= memg.FromDateTime) AND '
+ ' (dt2.[DateTime] < ISNULL(memg.ToDateTime, ''99991231'')))'
+ CASE @AggregationTypeId
WHEN 10 /* subhourly */ THEN ' WHERE (dt2.Minute % ' + CAST(a.AggregationIntervalDurationMinutes AS varchar) + ' = 0)'
WHEN 20 /* hourly */ THEN ' WHERE (dt2.Minute = 0)'
-- Perform daily aggregations at midnight UTC times, even in x:30 timezones where dt2.minute != 0
WHEN 30 /* daily */ THEN ' WHERE (dt2.Minute = ABS(DATEDIFF(minute, GETUTCDATE(), GETDATE()) % 60)) AND DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), GETDATE()), dt2.[DateTime]) = CONVERT(datetime, CONVERT(char(8), DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), GETDATE()), dt2.[DateTime]), 112), 112)'
END
+ ' ) AS mem_exists ON (mem_exists.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId) AND (mem_exists.[Date] = dt.[Date]) AND (mem_exists.[Time] = dt.[Time])'
+ CASE @AggregationTypeId
WHEN 10 /* subhourly */ THEN ' WHERE (dt.Minute % ' + CAST(a.AggregationIntervalDurationMinutes AS varchar) + ' = 0)'
WHEN 20 /* hourly */ THEN ' WHERE (dt.Minute = 0)'
-- Perform daily aggregations at midnight UTC times, even in x:30 timezones where dt.Minute != 0
WHEN 30 /* daily */ THEN ' WHERE (dt.Minute = ABS(DATEDIFF(minute, GETUTCDATE(), GETDATE()) % 60)) AND DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), GETDATE()), dt.[DateTime]) = CONVERT(datetime, CONVERT(char(8), DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), GETDATE()), dt.[DateTime]), 112), 112)'
END
+ ' ) AS tbl'
+ ' LEFT JOIN StandardDatasetAggregationHistory (NOLOCK) ah ON'
+ ' ('
+ CASE @AggregationTypeId
WHEN 30 THEN '(ah.AggregationDateTime >= tbl.[DateTime]) AND (ah.AggregationDateTime < DATEADD(day, 1, tbl.[DateTime]))'
ELSE '(ah.AggregationDateTime = tbl.[DateTime])'
END
+ ' AND (ah.DatasetId = ''$Config/DatasetId$'')'
+ ' AND (ah.AggregationTypeId = ' + CAST(AggregationTypeId AS varchar(10)) + '))'
FROM StandardDatasetAggregation a
JOIN StandardDataset d ON (d.DatasetId = a.DatasetId)
WHERE (d.DatasetId = '$Config/DatasetId$')
AND (a.AggregationTypeId = @AggregationTypeId)
SET @FullViewStatementConcatenator = ' UNION ALL '
END
-- create dummy
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vStateFull')
BEGIN
EXECUTE ('CREATE VIEW dbo.[vStateFull] AS SELECT A = 1')
EXECUTE ('GRANT SELECT ON dbo.[vStateFull] TO OpsMgrReader')
END
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
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vStateHealthServiceOutage')
BEGIN
EXECUTE ('CREATE VIEW dbo.vStateHealthServiceOutage AS SELECT A = 1')
END
GO
ALTER VIEW dbo.vStateHealthServiceOutage
AS
SELECT *
FROM dbo.StateHealthServiceOutage (NOLOCK)
GO
GRANT SELECT ON dbo.vStateHealthServiceOutage TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.GetStateHourly') and xtype in (N'FN', N'IF', N'TF'))
EXECUTE('CREATE FUNCTION dbo.GetStateHourly() RETURNS TABLE AS RETURN SELECT NULL as Blank')
GO
ALTER FUNCTION dbo.GetStateHourly
(
@StartDate datetime
,@EndDate datetime
)
RETURNS TABLE
AS
RETURN (
WITH DateTimeCTE
AS (
SELECT
DateTime,
Date,
Time,
Minute
FROM vDateTime dt WHERE dt.Minute = 0
AND dt.DateTime >= @StartDate
AND dt.DateTime < @EndDate
AND dt.[Date] BETWEEN DATEADD(day, -1, @StartDate) AND DATEADD(day, 1, @EndDate)
)
SELECT
[DateTime],
[Date],
[Time],
ManagedEntityMonitorRowId,
ManagedEntityRowId,
MonitorRowId,
IntervalDurationMilliseconds,
InYellowStateMilliseconds,
InRedStateMilliseconds,
InPlannedMaintenanceMilliseconds,
InUnplannedMaintenanceMilliseconds,
InDisabledStateMilliseconds,
HealthServiceUnavailableMilliseconds,
InWhiteStateMilliseconds =
CASE
WHEN ((ManagedEntityMonitorExistedInd = 0) OR (ah.AggregationCount = 0) OR (DATEADD(ms, IntervalDurationMilliseconds, [DateTime]) >= GETUTCDATE())) THEN IntervalDurationMilliseconds - InYellowStateMilliseconds - InRedStateMilliseconds - InDisabledStateMilliseconds - InPlannedMaintenanceMilliseconds - InUnplannedMaintenanceMilliseconds - HealthServiceUnavailableMilliseconds ELSE 0
END,
InGreenStateMilliseconds =
CASE
WHEN (ManagedEntityMonitorExistedInd = 1) AND ((ah.AggregationCount > 0) OR ((ah.AggregationCount IS NULL) AND (DATEADD(ms, IntervalDurationMilliseconds, [DateTime]) < GETUTCDATE()))) THEN IntervalDurationMilliseconds - InYellowStateMilliseconds - InRedStateMilliseconds - InDisabledStateMilliseconds - InPlannedMaintenanceMilliseconds - InUnplannedMaintenanceMilliseconds - HealthServiceUnavailableMilliseconds ELSE 0
END
FROM (SELECT
dt.[DateTime],
dt.[Date],
dt.[Time],
ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId,
ManagedEntityRowId = mem.ManagedEntityRowId,
MonitorRowId = mem.MonitorRowId,
ManagedEntityMonitorExistedInd =
CASE
WHEN mem_exists.ManagedEntityMonitorRowId IS NULL THEN 0 ELSE 1
END,
IntervalDurationMilliseconds = 60 * 1000 * 60,
InYellowStateMilliseconds = ISNULL(s.InYellowStateMilliseconds, 0),
InRedStateMilliseconds = ISNULL(s.InRedStateMilliseconds, 0),
InDisabledStateMilliseconds = ISNULL(s.InDisabledStateMilliseconds, 0),
InPlannedMaintenanceMilliseconds = ISNULL(s.InPlannedMaintenanceMilliseconds, 0),
InUnplannedMaintenanceMilliseconds = ISNULL(s.InUnplannedMaintenanceMilliseconds, 0),
HealthServiceUnavailableMilliseconds = ISNULL(s.HealthServiceUnavailableMilliseconds, 0)
FROM DateTimeCTE dt
CROSS JOIN vManagedEntityMonitor mem
LEFT JOIN [State].[vStateHourly] s
ON ((s.[DateTime] = dt.[DateTime]) AND (s.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId))
LEFT JOIN (SELECT DISTINCT
mem2.ManagedEntityMonitorRowId,
dt2.[Date],
dt2.[Time]
FROM DateTimeCTE dt2
CROSS JOIN vManagedEntityMonitor mem2
JOIN vManagedEntity me
ON (me.ManagedEntityRowId = mem2.ManagedEntityRowId)
JOIN vMonitorManagementPackVersion mmpv
ON (mmpv.MonitorRowId = mem2.MonitorRowId)
JOIN vManagementGroupManagementPackVersion mgmpv
ON ((mgmpv.ManagementPackVersionRowId = mmpv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = me.ManagementGroupRowId) AND (dt2.[DateTime] >= mgmpv.InstalledDateTime) AND (dt2.[DateTime] < ISNULL(mgmpv.DeletedDateTime, '99991231')))
JOIN vManagedEntityManagementGroup memg
ON ((mem2.ManagedEntityRowId = memg.ManagedEntityRowId) AND (dt2.[DateTime] >= memg.FromDateTime) AND (dt2.[DateTime] < ISNULL(memg.ToDateTime, '99991231')))
WHERE (dt2.Minute = 0)) AS mem_exists
ON (mem_exists.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId) AND (mem_exists.[Date] = dt.[Date]) AND (mem_exists.[Time] = dt.[Time])
WHERE (dt.Minute = 0)) AS tbl
LEFT JOIN StandardDataset sd ON sd.SchemaName = 'State'
LEFT JOIN StandardDatasetAggregationHistory(NOLOCK) ah
ON ((ah.AggregationDateTime = tbl.[DateTime]) AND (ah.DatasetId = sd.DatasetId) AND (ah.AggregationTypeId = 20))
)
GO
GRANT SELECT ON GetStateHourly TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.GetStateDaily') and xtype in (N'FN', N'IF', N'TF'))
EXECUTE('CREATE FUNCTION dbo.GetStateDaily() RETURNS TABLE AS RETURN SELECT NULL as Blank')
GO
ALTER FUNCTION dbo.GetStateDaily
(
@StartDate datetime
,@EndDate datetime
)
RETURNS TABLE
AS
RETURN (
WITH DateTimeCTE
AS (
SELECT
DateTime,
Date,
Time,
Minute
FROM vDateTime dt WHERE dt.Minute = 0
AND dt.DateTime >= @StartDate
AND dt.DateTime < @EndDate
AND dt.[Date] BETWEEN DATEADD(day, -1, @StartDate) AND DATEADD(day, 1, @EndDate)
)
SELECT
[DateTime],
[Date],
[Time],
ManagedEntityMonitorRowId,
ManagedEntityRowId,
MonitorRowId,
IntervalDurationMilliseconds,
InYellowStateMilliseconds,
InRedStateMilliseconds,
InPlannedMaintenanceMilliseconds,
InUnplannedMaintenanceMilliseconds,
InDisabledStateMilliseconds,
HealthServiceUnavailableMilliseconds,
InWhiteStateMilliseconds =
CASE
WHEN ((ManagedEntityMonitorExistedInd = 0) OR (ah.AggregationCount = 0) OR (DATEADD(ms, IntervalDurationMilliseconds, [DateTime]) >= GETUTCDATE())) THEN IntervalDurationMilliseconds - InYellowStateMilliseconds - InRedStateMilliseconds - InDisabledStateMilliseconds - InPlannedMaintenanceMilliseconds - InUnplannedMaintenanceMilliseconds - HealthServiceUnavailableMilliseconds ELSE 0
END,
InGreenStateMilliseconds =
CASE
WHEN (ManagedEntityMonitorExistedInd = 1) AND ((ah.AggregationCount > 0) OR ((ah.AggregationCount IS NULL) AND (DATEADD(ms, IntervalDurationMilliseconds, [DateTime]) < GETUTCDATE()))) THEN IntervalDurationMilliseconds - InYellowStateMilliseconds - InRedStateMilliseconds - InDisabledStateMilliseconds - InPlannedMaintenanceMilliseconds - InUnplannedMaintenanceMilliseconds - HealthServiceUnavailableMilliseconds ELSE 0
END
FROM (SELECT
dt.[DateTime],
dt.[Date],
dt.[Time],
ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId,
ManagedEntityRowId = mem.ManagedEntityRowId,
MonitorRowId = mem.MonitorRowId,
ManagedEntityMonitorExistedInd =
CASE
WHEN mem_exists.ManagedEntityMonitorRowId IS NULL THEN 0 ELSE 1
END,
IntervalDurationMilliseconds = 60 * 1000 * 1440,
InYellowStateMilliseconds = ISNULL(s.InYellowStateMilliseconds, 0),
InRedStateMilliseconds = ISNULL(s.InRedStateMilliseconds, 0),
InDisabledStateMilliseconds = ISNULL(s.InDisabledStateMilliseconds, 0),
InPlannedMaintenanceMilliseconds = ISNULL(s.InPlannedMaintenanceMilliseconds, 0),
InUnplannedMaintenanceMilliseconds = ISNULL(s.InUnplannedMaintenanceMilliseconds, 0),
HealthServiceUnavailableMilliseconds = ISNULL(s.HealthServiceUnavailableMilliseconds, 0)
FROM DateTimeCTE dt
CROSS JOIN vManagedEntityMonitor mem
LEFT JOIN [State].[vStateDaily] s
ON ((s.[DateTime] >= dt.[DateTime]) AND (s.[DateTime] < DATEADD(DAY, 1, dt.[DateTime])) AND (s.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId))
LEFT JOIN (SELECT DISTINCT
mem2.ManagedEntityMonitorRowId,
dt2.[Date],
dt2.[Time]
FROM DateTimeCTE dt2
CROSS JOIN vManagedEntityMonitor mem2
JOIN vManagedEntity me
ON (me.ManagedEntityRowId = mem2.ManagedEntityRowId)
JOIN vMonitorManagementPackVersion mmpv
ON (mmpv.MonitorRowId = mem2.MonitorRowId)
JOIN vManagementGroupManagementPackVersion mgmpv
ON ((mgmpv.ManagementPackVersionRowId = mmpv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = me.ManagementGroupRowId) AND (dt2.[DateTime] >= mgmpv.InstalledDateTime) AND (dt2.[DateTime] < ISNULL(mgmpv.DeletedDateTime, '99991231')))
JOIN vManagedEntityManagementGroup memg
ON ((mem2.ManagedEntityRowId = memg.ManagedEntityRowId) AND (dt2.[DateTime] >= memg.FromDateTime) AND (dt2.[DateTime] < ISNULL(memg.ToDateTime, '99991231')))
WHERE (dt2.Minute = ABS(DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()) % 60)) AND DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), dt2.[DateTime]) = CONVERT(datetime, CONVERT(char(8), DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), dt2.[DateTime]), 112), 112)) AS mem_exists
ON (mem_exists.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId) AND (mem_exists.[Date] = dt.[Date]) AND (mem_exists.[Time] = dt.[Time])
WHERE (dt.Minute = ABS(DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()) % 60)) AND DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), dt.[DateTime]) = CONVERT(datetime, CONVERT(char(8), DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), dt.[DateTime]), 112), 112)) AS tbl
LEFT JOIN StandardDataset sd ON sd.SchemaName = 'State'
LEFT JOIN StandardDatasetAggregationHistory(NOLOCK) ah
ON ((ah.AggregationDateTime >= tbl.[DateTime]) AND (ah.AggregationDateTime < DATEADD(DAY, 1, tbl.[DateTime])) AND (ah.DatasetId = sd.DatasetId) AND (ah.AggregationTypeId = 30))
)
GO
GRANT SELECT ON GetStateDaily TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ManagedEntityMonitorDependencyList')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.ManagedEntityMonitorDependencyList AS RETURN 1')
END
GO
ALTER PROCEDURE 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
GRANT EXECUTE ON ManagedEntityMonitorDependencyList TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateAggregate')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StateAggregate AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StateAggregate
@DatasetId uniqueidentifier
,@AggregationTypeId int
,@InsertTableGuid varchar(256)
,@IntervalStartDateTime datetime
,@IntervalEndDateTime datetime
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
BEGIN TRY
DECLARE
@Statement nvarchar(max)
,@DebugLevel tinyint
,@AggregationCoverViewName sysname
,@InsertTableName sysname
,@PreviousIntervalStartDateTime datetime
,@DebugMessage nvarchar(4000)
,@AggregationStartDateTime datetime
,@LowerAggregationTypeId int
,@LowerAggregationIntervalMinutes int
,@NonDirtyLowerAggregationCount int
,@LowerAggregationCoverViewName sysname
,@UseAggregatesToBuildStartOfIntervalStateInd bit
,@SchemaName sysname
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
-- check to see if non-dirty lower rank
-- aggregations are available to build this
-- layer of aggregations
SELECT TOP 1
@LowerAggregationTypeId = AggregationTypeId
,@LowerAggregationIntervalMinutes = AggregationIntervalDurationMinutes
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId > 0)
AND (AggregationTypeId < @AggregationTypeId)
IF (@LowerAggregationTypeId IS NOT NULL)
BEGIN
-- check if once-calculated aggregations of lower rank
-- cover the entire interval of this aggregation
SELECT @NonDirtyLowerAggregationCount = COUNT(*)
FROM StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @LowerAggregationTypeId)
AND (AggregationCount > 0)
AND (AggregationDateTime >= @IntervalStartDateTime)
AND (AggregationDateTime < @IntervalEndDateTime)
END
IF (@NonDirtyLowerAggregationCount = (ABS(DATEDIFF(minute, @IntervalStartDateTime, @IntervalEndDateTime))) / @LowerAggregationIntervalMinutes)
BEGIN
IF (@DebugLevel > 2)
BEGIN
-- lower aggregation is available
-- use is to build aggregates
SET @DebugMessage = 'Starting aggregation of type ' + CAST(@AggregationTypeId AS varchar) + ' for interval from '
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + 'UTC to '
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + 'UTC. Using existing non-dirty aggregations of type ' + CAST(@LowerAggregationTypeId AS varchar)
SET @AggregationStartDateTime = GETUTCDATE()
SELECT @LowerAggregationCoverViewName = 'v' + BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @LowerAggregationTypeId)
AND (DependentTableInd = 0)
SET @Statement =
'INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' (
ManagedEntityMonitorRowId
,[DateTime]
,InRedStateMilliseconds
,InYellowStateMilliseconds
,InDisabledStateMilliseconds
,InPlannedMaintenanceMilliseconds
,InUnplannedMaintenanceMilliseconds
,HealthServiceUnavailableMilliseconds
)
SELECT
ManagedEntityMonitorRowId
,@IntervalStartDateTime
,SUM(InRedStateMilliseconds)
,SUM(InYellowStateMilliseconds)
,SUM(InDisabledStateMilliseconds)
,SUM(InPlannedMaintenanceMilliseconds)
,SUM(InUnplannedMaintenanceMilliseconds)
,SUM(HealthServiceUnavailableMilliseconds)
FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@LowerAggregationCoverViewName) + '
WHERE ([DateTime] >= @IntervalStartDateTime)
AND ([DateTime] < @IntervalEndDateTime)
GROUP BY ManagedEntityMonitorRowId'
EXEC sp_executesql
@stmt = @Statement
,@params = N'@IntervalStartDateTime datetime, @IntervalEndDateTime datetime'
,@IntervalStartDateTime = @IntervalStartDateTime
,@IntervalEndDateTime = @IntervalEndDateTime
END
ELSE
BEGIN
-- lower aggregation is dirty or not available
-- use raw data to build aggregates
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting aggregation of type ' + CAST(@AggregationTypeId AS varchar) + ' for interval from '
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + 'UTC to '
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + 'UTC. Using raw data'
SET @AggregationStartDateTime = GETUTCDATE()
SET @PreviousIntervalStartDateTime = DATEADD(minute, -ABS(DATEDIFF(minute, @IntervalStartDateTime, @IntervalEndDateTime)), @IntervalStartDateTime)
-- load states for monitors that were
-- not green at the beginning of this period
IF EXISTS (SELECT * FROM StandardDatasetAggregationHistory
WHERE (AggregationCount > 0)
AND (AggregationTypeId = @AggregationTypeId)
AND (AggregationDateTime = @PreviousIntervalStartDateTime)
)
BEGIN
-- we have non-dirty previous aggregation
-- use its info to get all non-green monitors
SET @UseAggregatesToBuildStartOfIntervalStateInd = 1
SELECT @AggregationCoverViewName = 'v' + BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
END
ELSE
BEGIN
-- pick up the latest state change
-- for all me/monitor pairs when
-- there was a change during the period
-- or the last state wasn't green
SET @UseAggregatesToBuildStartOfIntervalStateInd = 0
END
IF (OBJECT_ID('tempdb..#StateEvent') IS NOT NULL)
DROP TABLE #StateEvent
IF (OBJECT_ID('tempdb..#StateEventRanked') IS NOT NULL)
DROP TABLE #StateEventRanked
CREATE TABLE #StateEventRanked (
ManagedEntityMonitorRowId int
,[DateTime] datetime
,IntervalNumber int
,OldHealthState tinyint
,NewHealthState tinyint
)
INSERT #StateEventRanked (ManagedEntityMonitorRowId, [DateTime], IntervalNumber, OldHealthState, NewHealthState)
SELECT
ManagedEntityMonitorRowId
,[DateTime]
,IntervalNumber = RANK() OVER (PARTITION BY ManagedEntityMonitorRowId ORDER BY [DateTime])
,OldHealthState
,NewHealthState
FROM #StateEvent
IF (OBJECT_ID('tempdb..#StateInterval') IS NOT NULL)
DROP TABLE #StateInterval
INSERT #StateInterval (
ManagedEntityMonitorRowId
,IntervalStartDateTime
,IntervalEndDateTime
,HealthState
)
SELECT
l.ManagedEntityMonitorRowId
,CASE WHEN l.[DateTime] < @IntervalStartDateTime
THEN @IntervalStartDateTime
ELSE l.[DateTime]
END
,CASE WHEN ISNULL(r.[DateTime], '99991231') > @IntervalEndDateTime
THEN @IntervalEndDateTime
ELSE r.[DateTime]
END
,CASE WHEN l.NewHealthState > ISNULL(r.OldHealthState, l.NewHealthState)
THEN l.NewHealthState
ELSE ISNULL(r.OldHealthState, l.NewHealthState)
END
FROM #StateEventRanked AS l
LEFT JOIN #StateEventRanked AS r ON (l.ManagedEntityMonitorRowId = r.ManagedEntityMonitorRowId) AND (l.IntervalNumber + 1 = r.IntervalNumber)
WHERE (l.[DateTime] < @IntervalEndDateTime)
SET @Statement =
' INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' (
[DateTime]
,ManagedEntityMonitorRowId
,InRedStateMilliseconds
,InYellowStateMilliseconds
,InDisabledStateMilliseconds
,InPlannedMaintenanceMilliseconds
,InUnplannedMaintenanceMilliseconds
,HealthServiceUnavailableMilliseconds
,IntervalEndHealthState
)
SELECT
@IntervalStartDateTime
,si.ManagedEntityMonitorRowId
,SUM(ABS(DATEDIFF(ms, si.IntervalEndDateTime, si.IntervalStartDateTime)) * (CASE si.HealthState WHEN 3 THEN 1 ELSE 0 END) )
,SUM(ABS(DATEDIFF(ms, si.IntervalEndDateTime, si.IntervalStartDateTime)) * (CASE si.HealthState WHEN 2 THEN 1 ELSE 0 END) )
,SUM(ABS(DATEDIFF(ms, si.IntervalEndDateTime, si.IntervalStartDateTime)) * (CASE si.HealthState WHEN 0 THEN 1 ELSE 0 END) )
,SUM(ABS(DATEDIFF(ms, si.IntervalEndDateTime, si.IntervalStartDateTime)) * (CASE si.HealthState WHEN 101 THEN 1 ELSE 0 END) )
,SUM(ABS(DATEDIFF(ms, si.IntervalEndDateTime, si.IntervalStartDateTime)) * (CASE si.HealthState WHEN 100 THEN 1 ELSE 0 END) )
,SUM(ABS(DATEDIFF(ms, si.IntervalEndDateTime, si.IntervalStartDateTime)) * (CASE si.HealthState WHEN 50 THEN 1 ELSE 0 END) )
,MAX(ISNULL(se.NewHealthState, 1)) *
CASE WHEN MAX(ISNULL(se.NewHealthState, 1)) = 1 THEN 1
WHEN EXISTS (SELECT *
FROM vManagedEntityMonitor mem
JOIN vManagedEntity me ON (me.ManagedEntityRowId = mem.ManagedEntityRowId)
JOIN vMonitorManagementPackVersion mmpv ON (mmpv.MonitorRowId = mem.MonitorRowId)
JOIN vManagementGroupManagementPackVersion mgmpv ON ((mgmpv.ManagementPackVersionRowId = mmpv.ManagementPackVersionRowId) AND
(mgmpv.ManagementGroupRowId = me.ManagementGroupRowId) AND
(@IntervalEndDateTime >= mgmpv.InstalledDateTime) AND
(@IntervalEndDateTime < ISNULL(mgmpv.DeletedDateTime, ''99991231'')))
JOIN vManagedEntityManagementGroup memg ON ((mem.ManagedEntityRowId = memg.ManagedEntityRowId) AND
(@IntervalEndDateTime >= memg.FromDateTime) AND
(@IntervalEndDateTime < ISNULL(memg.ToDateTime, ''99991231'') ))
WHERE (mem.ManagedEntityMonitorRowId = si.ManagedEntityMonitorRowId)
)
THEN 1
ELSE NULL
END
FROM #StateInterval si
LEFT JOIN (
SELECT
ser.ManagedEntityMonitorRowId
,IntervalNumber = MAX(ser.IntervalNumber)
FROM #StateEventRanked ser
WHERE (ser.OldHealthState < 50)
AND (ser.NewHealthState < 50)
GROUP BY ser.ManagedEntityMonitorRowId
) AS last_transition ON (si.ManagedEntityMonitorRowId = last_transition.ManagedEntityMonitorRowId)
LEFT JOIN #StateEventRanked se ON (si.ManagedEntityMonitorRowId = se.ManagedEntityMonitorRowId) AND (se.IntervalNumber = last_transition.IntervalNumber)
WHERE (si.HealthState <> 1)
GROUP BY si.ManagedEntityMonitorRowId'
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished aggregation of type ' + CAST(@AggregationTypeId AS varchar) + ' for interval from '
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + 'UTC to '
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + 'UTC'
DECLARE @OperationDurationMs bigint
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @AggregationStartDateTime))
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to build aggregates for state data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateAggregationDelete')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StateAggregationDelete AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StateAggregationDelete
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@AggregationDateTime datetime
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- there are no dependent tables in state data set
SELECT @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
-- Index hint in the query below ensures that SQL won't
-- consider index on DateTime field to be a good source
-- to find rows to delete - it may happen periodically
-- if SQL thinks there's no rows to delete due to
-- lack of [recently updated] statistics
SET @Statement = 'DELETE t' +
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' t WITH (INDEX(1))'
+ ' WHERE (t.[DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
IF (@DebugLevel > 2)
BEGIN
SELECT @DebugMessage = 'Starting delete of existing aggregated data. Table: ' + @TableName + ', aggregation date: ' + CONVERT(varchar(20), @AggregationDateTime, 120)
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to delete aggregated data for state data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateChangeEventList')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StateChangeEventList AS RETURN 1')
END
GO
Builds list of all state change events that a relevant
for a given interval. If MEMRowId is provided only
that monitor instance is considered otherwise all
monitors are considered.
If @UseAggregatesToBuildStartOfIntervalStateInd is 0 the
proc will return last state change that happened
just before @IntervalStartDateTime for each monitor
that had a state change within the interval OR for the one
provided in parameters
If @UseAggregatesToBuildStartOfIntervalStateInd is 1, the
proc will use existing aggregated data specified
by parameters @AggregationCoverViewName, @AggregationDateTime
to build fake state change event that happened at
@AggregationDateTime (this method is used by aggregations
to save time when calculating aggregates and previous
aggregation is available)
*******************************************************/
ALTER PROCEDURE StateChangeEventList
@ManagedEntityMonitorRowId int = NULL
,@IntervalStartDateTime datetime
,@IntervalEndDateTime datetime
,@UseAggregatesToBuildStartOfIntervalStateInd bit = 0
,@AggregationCoverViewName sysname = NULL
,@AggregationDateTime datetime = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
BEGIN TRY
DECLARE
@Statement nvarchar(max)
,@RawCoverViewName sysname
,@ExtraCondition nvarchar(max)
SET @ExtraCondition =
CASE
WHEN @ManagedEntityMonitorRowId IS NULL THEN
CASE
WHEN (OBJECT_ID('tempdb..#ManagedEntityMonitor') IS NOT NULL) THEN ' AND (ManagedEntityMonitorRowId IN (SELECT ManagedEntityMonitorRowId FROM #ManagedEntityMonitor)) '
ELSE ''
END
ELSE ' AND (ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId)'
END
IF (OBJECT_ID('tempdb..#StateEventChange') IS NOT NULL)
DROP TABLE #StateEventChange
-- get all state changes
-- that happened within the interval
SET @Statement =
' INSERT #StateEventChange(
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState
,NewHealthState
FROM [State].[vStateRaw] s
WHERE ([DateTime] BETWEEN @IntervalStartDateTime AND @IntervalEndDateTime)'
+ @ExtraCondition
IF (@UseAggregatesToBuildStartOfIntervalStateInd = 1)
BEGIN
-- NOTE: We do not return intervals where end state is 1 (healthy)
SET @Statement =
' INSERT #StateEventChange(
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
s.ManagedEntityMonitorRowId
,@IntervalStartDateTime
,0
,ISNULL(s.IntervalEndHealthState, 0)
FROM [State].' + QUOTENAME(@AggregationCoverViewName) + ' s
WHERE (s.[DateTime] = @AggregationDateTime)
AND (s.IntervalEndHealthState <> 1)'
+ @ExtraCondition
EXEC sp_executesql
@stmt = @Statement
,@params = N'@IntervalStartDateTime datetime, @AggregationDateTime datetime, @ManagedEntityMonitorRowId int'
,@IntervalStartDateTime = @IntervalStartDateTime
,@AggregationDateTime = @AggregationDateTime
,@ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId
END
ELSE
BEGIN
-- pick up the latest state change
-- for all me/monitor pairs when
-- there was a change during the period
-- or the last state wasn't green
SET @Statement =
' INSERT #StateEventChange(
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
s.ManagedEntityMonitorRowId
,@IntervalStartDateTime
,0
,MAX(s.NewHealthState)
FROM [State].[vStateRaw] s
JOIN
(SELECT ManagedEntityMonitorRowId, [DateTime] = MAX(DateTime)
FROM [State].[vStateRaw]
WHERE ([DateTime] < @IntervalStartDateTime)'
+ @ExtraCondition + '
GROUP BY ManagedEntityMonitorRowId
) AS sd
ON (s.ManagedEntityMonitorRowId = sd.ManagedEntityMonitorRowId) AND (s.[DateTime] = sd.[DateTime])
GROUP BY s.ManagedEntityMonitorRowId'
EXEC sp_executesql
@stmt = @Statement
,@params = N'@IntervalStartDateTime datetime, @ManagedEntityMonitorRowId int'
,@IntervalStartDateTime = @IntervalStartDateTime
,@ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId
END
-- factor in HS availability/outage
IF EXISTS (SELECT * FROM StateHealthServiceOutage)
BEGIN
-- store all relationship types that represent 'manages'
-- concept in the table
IF (OBJECT_ID('tempdb..#ManagesRelationshipType') IS NOT NULL)
DROP TABLE #ManagesRelationshipType
CREATE TABLE #ManagesRelationshipType (
RelationshipTypeRowId int NOT NULL
)
-- R2 style 'manages' relationship between HS and an object
INSERT #ManagesRelationshipType (RelationshipTypeRowId)
SELECT RelationshipTypeRowId
FROM RelationshipType rt
WHERE (rt.RelationshipTypeSystemName = 'Microsoft.SystemCenter.HealthServiceManagesEntity')
-- OM12 style 'manages' relationship between HS2(Management Service) or pool and an object
INSERT #ManagesRelationshipType (RelationshipTypeRowId)
SELECT RelationshipTypeRowId
FROM RelationshipType rt
WHERE (rt.RelationshipTypeSystemName = 'Microsoft.SystemCenter.ManagementActionPointManagesEntity')
-- get a list of all relevant HSOs occurring in the given period of time
-- NOTE: we store two rows for every HSO - one for start of interval and one for the end
IF (OBJECT_ID('tempdb..#HealthServiceOutageEvent') IS NOT NULL)
DROP TABLE #HealthServiceOutageEvent
CREATE TABLE #HealthServiceOutageEvent (
ManagedEntityRowId int NOT NULL
,[DateTime] datetime NOT NULL
,OldHealthState tinyint NOT NULL
,NewHealthState tinyint NOT NULL
)
INSERT #HealthServiceOutageEvent (
ManagedEntityRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
hso.ManagedEntityRowId
,CASE
WHEN interval.StartIntervalInd = 1 THEN
CASE
WHEN hso.StartDateTime < @IntervalStartDateTime THEN @IntervalStartDateTime
ELSE hso.StartDateTime
END
ELSE
CASE
WHEN ISNULL(hso.EndDateTime, '99991231') > @IntervalEndDateTime THEN @IntervalEndDateTime
ELSE hso.EndDateTime
END
END
,CASE WHEN interval.StartIntervalInd = 1 THEN 0 ELSE 50 END
,CASE WHEN interval.StartIntervalInd = 1 THEN 50 ELSE 1 END
FROM vHealthServiceOutage hso
JOIN StateHealthServiceOutage shso ON (hso.ReasonCode = shso.ReasonCode)
CROSS JOIN (SELECT StartIntervalInd = 0
UNION ALL
SELECT 1
) AS interval
-- Only go back a maximum of 60 days to prevent infinite growth
WHERE (hso.StartDateTime BETWEEN DateAdd(DAY, -60, GETUTCDATE()) AND @IntervalEndDateTime)
AND (ISNULL(hso.EndDateTime, '99991231') >= @IntervalStartDateTime)
AND (NOT EXISTS (SELECT *
FROM StateInvalidatedHealthServiceOutage
WHERE (HealthServiceOutageRowId = hso.HealthServiceOutageRowId)
)
)
IF EXISTS (SELECT * FROM #HealthServiceOutageEvent)
BEGIN
-- take care of HSO outages that indicate HS or a pool outage
SET @Statement =
' INSERT #StateEventChange (
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
mem.ManagedEntityMonitorRowId
,hso.[DateTime]
,hso.OldHealthState
,hso.NewHealthState
FROM #HealthServiceOutageEvent hso
JOIN vRelationship r ON (r.SourceManagedEntityRowId = hso.ManagedEntityRowId)
JOIN #ManagesRelationshipType mrt ON (mrt.RelationshipTypeRowId = r.RelationshipTypeRowId)
JOIN vRelationshipManagementGroup rmg ON (r.RelationshipRowId = rmg.RelationshipRowId)
JOIN vManagedEntity me ON (r.TargetManagedEntityRowId = me.TopLevelHostManagedEntityRowId)
JOIN vManagedEntityMonitor mem ON (me.ManagedEntityRowId = mem.ManagedEntityRowId)
WHERE (@IntervalStartDateTime BETWEEN rmg.FromDateTime AND ISNULL(rmg.ToDateTime, ''99991231''))'
+ @ExtraCondition
-- take care of HSO outages that indicate entity (which is TLH) outage
SET @Statement =
' INSERT #StateEventChange (
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
mem.ManagedEntityMonitorRowId
,hso.[DateTime]
,hso.OldHealthState
,hso.NewHealthState
FROM #HealthServiceOutageEvent hso
JOIN vManagedEntity me ON (hso.ManagedEntityRowId = me.TopLevelHostManagedEntityRowId)
JOIN vManagedEntityMonitor mem ON (me.ManagedEntityRowId = mem.ManagedEntityRowId)'
+ @ExtraCondition
-- set new state of the periods where hso ended to 'last reported state transition'
CREATE INDEX temp1 ON #StateEventChange (ManagedEntityMonitorRowId)
SET @Statement = '
UPDATE se
SET se.NewHealthState = ISNULL(previous_real_transition.NewHealthState, 1)
FROM #StateEventChange se
JOIN (
SELECT
MEMRowId = previous_se.ManagedEntityMonitorRowId
,CutOffDateTime = cutoff_se.[DateTime]
,previous_se.NewHealthState
,StateEventNumber = RANK() OVER (PARTITION BY previous_se.ManagedEntityMonitorRowId, cutoff_se.[DateTime] ORDER BY previous_se.[DateTime] DESC, previous_se.NewHealthState DESC)
FROM #StateEventChange cutoff_se
JOIN #StateEventChange previous_se ON (cutoff_se.ManagedEntityMonitorRowId = previous_se.ManagedEntityMonitorRowId) AND (cutoff_se.[DateTime] > previous_se.[DateTime])
WHERE (cutoff_se.OldHealthState = 50)
AND (previous_se.OldHealthState < 50)
AND (previous_se.NewHealthState < 50) -- real state transition, not hso or mmode
) AS previous_real_transition ON (se.ManagedEntityMonitorRowId = previous_real_transition.MEMRowId) AND (se.[DateTime] = previous_real_transition.CutOffDateTime)
WHERE (se.OldHealthState = 50)
AND (previous_real_transition.StateEventNumber = 1)'
+ @ExtraCondition
EXEC sp_executesql
@stmt = @Statement
,@params = N'@ManagedEntityMonitorRowId int'
,@ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId
END
END
-- factor in maintenance mode
-- using two itertions - first for unlanned maintenance
-- then second for planned maintenance
DECLARE @i int
SET @i = 0
WHILE (@i < 2)
BEGIN
-- find all relevant mmode intervals
IF (OBJECT_ID('tempdb..#MaintenanceModeEvent') IS NOT NULL)
DROP TABLE #MaintenanceModeEvent
CREATE TABLE #MaintenanceModeEvent (
ManagedEntityRowId int NOT NULL
,[DateTime] datetime NOT NULL
,OldHealthState tinyint NOT NULL
,NewHealthState tinyint NOT NULL
)
INSERT #MaintenanceModeEvent (
ManagedEntityRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
mm.ManagedEntityRowId
,CASE
WHEN interval.StartIntervalInd = 1 THEN
CASE
WHEN mm.StartDateTime < @IntervalStartDateTime THEN @IntervalStartDateTime
ELSE mm.StartDateTime
END
ELSE
CASE
WHEN ISNULL(mm.EndDateTime, '99991231') > @IntervalEndDateTime THEN @IntervalEndDateTime
ELSE mm.EndDateTime
END
END
,CASE WHEN interval.StartIntervalInd = 1 THEN 0 ELSE 100 + @i END
,CASE WHEN interval.StartIntervalInd = 1 THEN 100 + @i ELSE 1 END
FROM vMaintenanceMode mm
CROSS JOIN (SELECT StartIntervalInd = 0
UNION ALL
SELECT 1
) AS interval
-- Only go back a maximum of 60 days to prevent infinite growth
WHERE (mm.StartDateTime BETWEEN DateAdd(DAY, -60, GETUTCDATE()) AND @IntervalEndDateTime)
AND (ISNULL(mm.EndDateTime, '99991231') >= @IntervalStartDateTime)
AND (mm.PlannedMaintenanceInd = @i)
IF EXISTS (SELECT * FROM #MaintenanceModeEvent)
BEGIN
DELETE se
FROM #StateEventChange se
JOIN vManagedEntityMonitor mem ON (se.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId)
JOIN vMaintenanceMode mm ON (mem.ManagedEntityRowId = mm.ManagedEntityRowId)
WHERE (se.[DateTime] BETWEEN mm.StartDateTime AND ISNULL(mm.EndDateTime, '99990101'))
AND (mm.PlannedMaintenanceInd = @i)
SET @Statement =
' INSERT #StateEventChange (
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
mem.ManagedEntityMonitorRowId
,mm.[DateTime]
,mm.OldHealthState
,mm.NewHealthState
FROM #MaintenanceModeEvent mm
JOIN vManagedEntityMonitor mem ON (mm.ManagedEntityRowId = mem.ManagedEntityRowId)
WHERE (1 = 1)'
+ @ExtraCondition
EXEC sp_executesql
@stmt = @Statement
,@params = N'@ManagedEntityMonitorRowId int'
,@ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId
END
SET @i = @i + 1
END
-- clean out dupes in state transition
SELECT
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState = MAX(OldHealthState)
,NewHealthState = MAX(NewHealthState)
FROM #StateEventChange
GROUP BY ManagedEntityMonitorRowId, [DateTime]
DROP TABLE #StateEventChange
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
-- 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
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateDowntimeList')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StateDowntimeList AS RETURN 1')
END
GO
ALTER PROCEDURE StateDowntimeList
@ManagedEntityMonitorRowId int
,@FromDateTime datetime
,@ToDateTime datetime
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
BEGIN TRY
IF (OBJECT_ID('tempdb..#StateEvent') IS NOT NULL)
DROP TABLE #StateEvent
INSERT #StateEventRanked (ManagedEntityMonitorRowId, [DateTime], IntervalNumber, OldHealthState, NewHealthState)
SELECT
ManagedEntityMonitorRowId
,[DateTime]
,IntervalNumber = RANK() OVER (PARTITION BY ManagedEntityMonitorRowId ORDER BY [DateTime])
,OldHealthState
,NewHealthState
FROM #StateEvent
SELECT
ManagedEntityMonitorRowId
,FromDateTime =
CASE
WHEN FromDateTime < @FromDateTime THEN @FromDateTime
ELSE FromDateTime
END
,ToDateTime = ISNULL(ToDateTime, @ToDateTime)
,HealthState
FROM (SELECT
l.ManagedEntityMonitorRowId
,FromDateTime = l.[DateTime]
,ToDateTime = r.[DateTime]
,HealthState =
CASE WHEN l.NewHealthState > ISNULL(r.OldHealthState, l.NewHealthState)
THEN l.NewHealthState
ELSE ISNULL(r.OldHealthState, l.NewHealthState)
END
FROM #StateEventRanked AS l
LEFT JOIN #StateEventRanked AS r ON (l.ManagedEntityMonitorRowId = r.ManagedEntityMonitorRowId) AND (l.IntervalNumber + 1 = r.IntervalNumber)
) AS tbl
WHERE (HealthState <> 1)
AND (FromDateTime < ISNULL(ToDateTime, @ToDateTime)) -- eliminate zero-length intervals
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- 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 StateDowntimeList TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StateGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StateGroom
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@CutoffDateTime datetime
,@MaxRowsToGroom int
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- there are no dependent tables in state data set
SELECT @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT
-- groom dynamic dimentions
DELETE mem
FROM ManagedEntityMonitor mem
WHERE NOT EXISTS (SELECT * FROM vMonitor m WHERE (m.MonitorRowId = mem.MonitorRowId))
DELETE mem
FROM ManagedEntityMonitor mem
WHERE NOT EXISTS (SELECT * FROM vManagedEntity me WHERE (me.ManagedEntityRowId = mem.ManagedEntityRowId))
-- groom staging of data for rejected MGs
DELETE State.StateStage
WHERE ManagementGroupGuid NOT IN (SELECT ManagementGroupGuid
FROM ManagementGroup mg
WHERE (ConnectConfirmedDateTime IS NOT NULL)
AND (ConnectRejectedInd = 0)
)
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to groom data for state data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StateProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StateProcessStaging
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
DECLARE
@DebugLevel int
,@SchemaName sysname
,@ExecResult int
,@LockResourceName sysname
,@InsertTableName sysname
,@InsertTableGuid uniqueidentifier
,@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@MessageText varchar(max)
,@OperationDurationMs bigint
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- groom records that are > month old since
-- aggregations only kept one month long
DELETE State.StateStage WITH (TABLOCK)
WHERE ([DateTime] < DATEADD(month, -1, GETUTCDATE()))
-- process staging to create ME Monitor records for newly discovered monitors
INSERT ManagedEntityMonitor (
ManagedEntityRowId
,MonitorRowId
)
SELECT DISTINCT
me.ManagedEntityRowId
,m.MonitorRowId
FROM State.StateStage s
JOIN vManagementGroup mg ON (s.ManagementGroupGuid = mg.ManagementGroupGuid)
JOIN vManagedEntity me ON ((me.ManagedEntityGuid = s.ManagedEntityGuid) AND (mg.ManagementGroupRowId = me.ManagementGroupRowId))
JOIN vMonitor m ON (m.MonitorGuid = s.MonitorGuid)
WHERE (s.DatasetId = @DatasetId)
AND NOT EXISTS (SELECT *
FROM vManagedEntityMonitor
WHERE (ManagedEntityRowId = me.ManagedEntityRowId)
AND (MonitorRowId = m.MonitorRowId)
)
-- process events in staging setting MEM id if it is not yet determined
-- NOTE: The force order hint below is meant to avoid situation when
-- dw has more records in ME then in MEM and SQL thinks that querying
-- MEM first and then ME is beneficial (which is not true really)
UPDATE s
SET ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId
FROM State.StateStage s
JOIN vManagementGroup mg ON (s.ManagementGroupGuid = mg.ManagementGroupGuid)
JOIN vManagedEntity me ON ((me.ManagedEntityGuid = s.ManagedEntityGuid) AND (mg.ManagementGroupRowId = me.ManagementGroupRowId))
JOIN vMonitor m ON (m.MonitorGuid = s.MonitorGuid)
JOIN vManagedEntityMonitor mem ON ((mem.ManagedEntityRowId = me.ManagedEntityRowId) AND (mem.MonitorRowId = m.MonitorRowId))
WHERE (s.DatasetId = @DatasetId)
AND (s.InsertReadyInd = 0)
OPTION (FORCE ORDER)
-- delete sate transitions older then 48 hours to
-- not to go back 'too much' - such state transitions
-- are cut of by the agents, but they can linger in staging
-- if object existed but then was deleted
DELETE State.StateStage
WHERE (DatasetId = @DatasetId)
AND (InsertReadyInd > 0)
AND ([DateTime] < DATEADD(hour, -48, GETUTCDATE()))
-- invalidate HSO intervals when the HS in question
-- did send some state changes during the interval
-- NOTE: Starting OM2012 SP1 we do not invalidate
-- HS outage intervals to close map to operational
-- side of the house and not to throw state aggregations
-- back too much when HS coming out of long outage period
/*
IF EXISTS (SELECT * FROM StateHealthServiceOutage)
BEGIN
INSERT StateInvalidatedHealthServiceOutage (HealthServiceOutageRowId)
SELECT DISTINCT hso.HealthServiceOutageRowId
FROM State.StateStage s
JOIN vManagedEntity me ON (me.ManagedEntityGuid = s.ManagedEntityGuid)
JOIN vRelationship r ON (me.TopLevelHostManagedEntityRowId = r.TargetManagedEntityRowId)
JOIN vRelationshipManagementGroup rmg ON (r.RelationshipRowId = rmg.RelationshipRowId)
JOIN vRelationshipType rt ON (r.RelationshipTypeRowId = rt.RelationshipTypeRowId)
JOIN vHealthServiceOutage hso ON (r.SourceManagedEntityRowId = hso.ManagedEntityRowId)
JOIN StateHealthServiceOutage shso ON (hso.ReasonCode = shso.ReasonCode)
WHERE (s.InsertReadyInd > 0)
AND (s.[DateTime] BETWEEN rmg.FromDateTime AND ISNULL(rmg.ToDateTime, '99991231'))
AND (rt.RelationshipTypeSystemName = 'Microsoft.SystemCenter.HealthServiceManagesEntity')
AND (s.[DateTime] BETWEEN hso.StartDateTime AND ISNULL(hso.EndDateTime, '99991231'))
AND (NOT EXISTS (SELECT *
FROM StateInvalidatedHealthServiceOutage
WHERE (HealthServiceOutageRowId = hso.HealthServiceOutageRowId)
)
)
END
*/
-- mark existing aggregations as dirty when
-- new information about mmode is received
BEGIN TRAN
SELECT @LastProcessedMModeModifiedDateTime = LastProcessedMaintenanceModeModifiedDateTime
FROM StateAggregationProcessing
WHERE (DatasetId = @DatasetId)
IF (@LastProcessedMModeModifiedDateTime IS NULL)
BEGIN
SET @LastProcessedMModeModifiedDateTime = '20050101'
END
-- build a list of all mmode intervals
-- touched since last processing up until [almost] now
CREATE TABLE #UpdatedMaintenanceMode (
MaintenanceModeRowId int NOT NULL PRIMARY KEY
,StartDateTime datetime NOT NULL
,EndDateTime datetime NULL
,PlannedMaintenanceInd bit NOT NULL
,DWLastModifiedDateTime datetime NOT NULL
)
INSERT #UpdatedMaintenanceMode (
MaintenanceModeRowId
,StartDateTime
,EndDateTime
,PlannedMaintenanceInd
,DWLastModifiedDateTime
)
SELECT
MaintenanceModeRowId
,StartDateTime
,EndDateTime
,PlannedMaintenanceInd
,DWLastModifiedDateTime
FROM vMaintenanceMode mmode
WHERE (mmode.DWLastModifiedDateTime >= @LastProcessedMModeModifiedDateTime)
AND (mmode.DWLastModifiedDateTime < @NewLastProcessedMModeModifiedDateTime)
-- calculate minimum date for re-aggregations
SET @MinDate = NULL
SELECT @MinDate = MIN(
CASE
WHEN (processed_mmode.MaintenanceModeRowId IS NULL) -- not yet processed
THEN mmode.StartDateTime
ELSE
CASE
WHEN (processed_mmode.PlannedMaintenanceInd != mmode.PlannedMaintenanceInd) -- changed planned / unplanned
THEN mmode.StartDateTime
WHEN (mmode.EndDateTime IS NOT NULL) -- end time update - pick up minimum of end times
AND (ISNULL(processed_mmode.EndDateTime, '99991231') != mmode.EndDateTime)
THEN CASE
WHEN mmode.EndDateTime < ISNULL(processed_mmode.EndDateTime, '99991231') THEN mmode.EndDateTime
ELSE processed_mmode.EndDateTime
END
ELSE NULL
END
END
)
FROM #UpdatedMaintenanceMode mmode
LEFT JOIN StateProcessedMaintenanceMode processed_mmode ON (mmode.MaintenanceModeRowId = processed_mmode.MaintenanceModeRowId)
-- update processed mmode for the intervals that were updated
UPDATE processed_mmode
SET StartDateTime = mmode.StartDateTime
,EndDateTime = mmode.EndDateTime
,PlannedMaintenanceInd = mmode.PlannedMaintenanceInd
,MaintenanceModeDWLastModifiedDateTime = mmode.DWLastModifiedDateTime
,DWLastModifiedDateTime = GETUTCDATE()
FROM #UpdatedMaintenanceMode mmode
JOIN StateProcessedmaintenanceMode processed_mmode ON (mmode.MaintenanceModeRowId = processed_mmode.MaintenanceModeRowId)
-- add records to processed HSO for the outages that are new
INSERT StateProcessedMaintenanceMode (
MaintenanceModeRowId
,StartDateTime
,EndDateTime
,PlannedMaintenanceInd
,MaintenanceModeDWLastModifiedDateTime
)
SELECT
MaintenanceModeRowId
,StartDateTime
,EndDateTime
,PlannedMaintenanceInd
,DWLastModifiedDateTime
FROM #UpdatedMaintenanceMode mmode
WHERE (NOT EXISTS (SELECT * FROM StateProcessedMaintenanceMode WHERE MaintenanceModeRowId = mmode.MaintenanceModeRowId))
IF (@MinDate IS NOT NULL)
BEGIN
UPDATE ah
SET DirtyInd = 1
,DataLastReceivedDateTime = GETUTCDATE()
FROM StandardDatasetAggregationHistory ah
JOIN StandardDatasetAggregation a ON (ah.DatasetId = a.DatasetId AND ah.AggregationTypeId = a.AggregationTypeId)
WHERE (ah.DatasetId = @DatasetId)
AND (ah.AggregationDateTime > DATEADD(minute, -a.AggregationIntervalDurationMinutes, @MinDate))
END
UPDATE StateAggregationProcessing
SET LastProcessedMaintenanceModeModifiedDateTime = @NewLastProcessedMModeModifiedDateTime
WHERE (DatasetId = @DatasetId)
IF (@@ROWCOUNT = 0)
BEGIN
INSERT StateAggregationProcessing(DatasetId, LastProcessedMaintenanceModeModifiedDateTime)
VALUES (@DatasetId, @NewLastProcessedMModeModifiedDateTime)
END
COMMIT
-- mark existing aggregations as dirty when
-- new information about health service outage is received
BEGIN TRAN
SELECT @LastProcessedHsoModifiedDateTime = LastProcessedHealthServiceOutageModifiedDateTime
FROM StateAggregationProcessing
WHERE (DatasetId = @DatasetId)
IF (@LastProcessedHsoModifiedDateTime IS NULL)
BEGIN
SET @LastProcessedHsoModifiedDateTime = '20050101'
END
-- calculate the minimum date to
-- declare aggregations as dirty
-- build a list of all HSO records and invalidation
-- records touched since last processing up until [almost] now
CREATE TABLE #UpdatedHealthServiceOutage (
HealthServiceOutageRowId int NOT NULL PRIMARY KEY
,StartDateTime datetime NOT NULL
,EndDateTime datetime NULL
,ReasonCode tinyint NOT NULL
,DWLastModifiedDateTime datetime NOT NULL
,InvalidatedDateTime datetime NULL
)
INSERT #UpdatedHealthServiceOutage (
HealthServiceOutageRowId
,StartDateTime
,EndDateTime
,ReasonCode
,DWLastModifiedDateTime
,InvalidatedDateTime
)
SELECT
hso.HealthServiceOutageRowId
,hso.StartDateTime
,hso.EndDateTime
,hso.ReasonCode
,hso.DWLastModifiedDateTime
,sihso.DWCreatedDateTime
FROM vHealthServiceOutage hso
JOIN StateHealthServiceOutage shso ON (hso.ReasonCode = shso.ReasonCode)
LEFT JOIN StateInvalidatedHealthServiceOutage sihso ON (hso.HealthServiceOutageRowId = sihso.HealthServiceOutageRowId)
WHERE (hso.DWLastModifiedDateTime >= @LastProcessedHsoModifiedDateTime)
AND (hso.DWLastModifiedDateTime < @NewLastProcessedHsoModifiedDateTime)
UNION
SELECT
hso.HealthServiceOutageRowId
,hso.StartDateTime
,hso.EndDateTime
,hso.ReasonCode
,hso.DWLastModifiedDateTime
,sihso.DWCreatedDateTime
FROM vHealthServiceOutage hso
JOIN StateHealthServiceOutage shso ON (hso.ReasonCode = shso.ReasonCode)
JOIN StateInvalidatedHealthServiceOutage sihso ON (hso.HealthServiceOutageRowId = sihso.HealthServiceOutageRowId)
WHERE (sihso.DWCreatedDateTime >= @LastProcessedHsoModifiedDateTime)
AND (sihso.DWCreatedDateTime < @NewLastProcessedHsoModifiedDateTime)
SET @MinDate = NULL
SELECT @MinDate = MIN(
CASE
WHEN (processed_hso.HealthServiceOutageRowId IS NULL) -- this hso was never processed before
THEN
CASE
WHEN (uhso.InvalidatedDateTime IS NULL) -- not invalidated
THEN uhso.StartDateTime
ELSE -- this hso is already invalidated - do not process
NULL
END
ELSE -- this hso was seen before
CASE
WHEN (processed_hso.InvalidatedDateTime IS NOT NULL) -- already invalidated, does not count
THEN NULL
WHEN (uhso.InvalidatedDateTime IS NOT NULL) -- hso invalidated
THEN uhso.InvalidatedDateTime -- look at invalidated date time not Start date time for perf optimizations. Otherwise we throw aggregation process back all the time
WHEN (uhso.EndDateTime IS NOT NULL) -- end time update - pick up minimum of end times
AND (ISNULL(processed_hso.EndDateTime, '99991231') != uhso.EndDateTime)
THEN CASE
WHEN uhso.EndDateTime < ISNULL(processed_hso.EndDateTime, '99991231') THEN uhso.EndDateTime
ELSE processed_hso.EndDateTime
END
ELSE NULL
END
END
)
FROM #UpdatedHealthServiceOutage AS uhso
LEFT JOIN StateProcessedHealthServiceOutage processed_hso ON (uhso.HealthServiceOutageRowId = processed_hso.HealthServiceOutageRowId)
-- update processed HSO for the outages that were updated
UPDATE processed_hso
SET StartDateTime = hso.StartDateTime
,EndDateTime = hso.EndDateTime
,ReasonCode = hso.ReasonCode
,HealthServiceOutageDWLastModifiedDateTime = hso.DWLastModifiedDateTime
,InvalidatedDateTime = hso.InvalidatedDateTime
,DWLastModifiedDateTime = GETUTCDATE()
FROM #UpdatedHealthServiceOutage hso
JOIN StateProcessedHealthServiceOutage processed_hso ON (hso.HealthServiceOutageRowId = processed_hso.HealthServiceOutageRowId)
-- add records to processed HSO for the outages that are new
INSERT StateProcessedHealthServiceOutage (
HealthServiceOutageRowId
,StartDateTime
,EndDateTime
,ReasonCode
,HealthServiceOutageDWLastModifiedDateTime
,InvalidatedDateTime
)
SELECT
HealthServiceOutageRowId
,StartDateTime
,EndDateTime
,ReasonCode
,DWLastModifiedDateTime
,InvalidatedDateTime
FROM #UpdatedHealthServiceOutage hso
WHERE (NOT EXISTS (SELECT * FROM StateProcessedHealthServiceOutage WHERE HealthServiceOutageRowId = hso.HealthServiceOutageRowId))
-- groom invalidated Health Service Outage table
DELETE sihso
FROM StateInvalidatedHealthServiceOutage sihso
WHERE NOT EXISTS (SELECT *
FROM HealthServiceOutage hso
WHERE (sihso.HealthServiceOutageRowId = hso.HealthServiceOutageRowId)
)
-- groom processed Health Service Outage table
DELETE StateProcessedHealthServiceOutage
WHERE EndDateTime < DATEADD(month, -1, GETUTCDATE())
DROP TABLE #UpdatedHealthServiceOutage
IF (@MinDate IS NOT NULL)
BEGIN
UPDATE ah
SET DirtyInd = 1
,DataLastReceivedDateTime = GETUTCDATE()
FROM StandardDatasetAggregationHistory ah
JOIN StandardDatasetAggregation a ON (ah.DatasetId = a.DatasetId AND ah.AggregationTypeId = a.AggregationTypeId)
WHERE (ah.DatasetId = @DatasetId)
AND (ah.AggregationDateTime > DATEADD(minute, -a.AggregationIntervalDurationMinutes, @MinDate))
END
UPDATE StateAggregationProcessing
SET LastProcessedHealthServiceOutageModifiedDateTime = @NewLastProcessedHsoModifiedDateTime
WHERE (DatasetId = @DatasetId)
IF (@@ROWCOUNT = 0)
BEGIN
INSERT StateAggregationProcessing(DatasetId, LastProcessedHealthServiceOutageModifiedDateTime)
VALUES (@DatasetId, @NewLastProcessedHsoModifiedDateTime)
END
COMMIT
BEGIN TRAN
-- lock table map to ensure we insert into table opened for insertion
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_TableMap'
-- mark existing affected aggregations as dirty
SET @MinDate = NULL
SELECT @MinDate = MIN([DateTime])
FROM State.StateStage
WHERE (DatasetId = @DatasetId)
AND (InsertReadyInd > 0)
IF (@MinDate IS NOT NULL)
BEGIN
UPDATE ah
SET DirtyInd = 1
,DataLastReceivedDateTime = GETUTCDATE()
FROM StandardDatasetAggregationHistory ah
JOIN StandardDatasetAggregation a ON (ah.DatasetId = a.DatasetId AND ah.AggregationTypeId = a.AggregationTypeId)
WHERE (ah.DatasetId = @DatasetId)
AND (ah.AggregationDateTime > DATEADD(minute, -a.AggregationIntervalDurationMinutes, @MinDate))
END
-- make sure at least one interval of the lowest
-- aggregation type is not invalidated - if they all are
-- aggregation process may have problems on high volume
-- find lowest type aggregations we need to perform
DECLARE @LowestAggregationTypeId int
SELECT @LowestAggregationTypeId = MIN(AggregationTypeId)
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId > 0)
UPDATE StandardDatasetAggregationHistory
SET DirtyInd = 0
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @LowestAggregationTypeId)
AND (AggregationDateTime = (SELECT MIN(AggregationDateTime)
FROM StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @LowestAggregationTypeId)
)
)
-- remove rows from staging
DELETE State.StateStage WITH (TABLOCK)
WHERE (DatasetId = @DatasetId)
AND (InsertReadyInd > 0)
IF (@DebugLevel > 3)
BEGIN
SET @MessageText = 'Finished inserting state change events into ' + @InsertTableName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to process data in the state data set staging area. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
</Install> <Uninstall>
-- drop SPs
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateAggregate')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StateAggregate')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateAggregationDelete')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StateAggregationDelete')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateChangeEventList')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StateChangeEventList')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateDowntimeList')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StateDowntimeList')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateGroom')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StateGroom')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateInsert')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StateInsert')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateProcessStaging')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.StateProcessStaging')
END
-- drop functions
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'ManagedEntityMonitorDependency') and xtype in (N'FN', N'IF', N'TF'))
EXECUTE('DROP FUNCTION ManagedEntityMonitorDependency')
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'GetStateHourly') and xtype in (N'FN', N'IF', N'TF'))
EXECUTE('DROP FUNCTION GetStateHourly')
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'GetStateDaily') and xtype in (N'FN', N'IF', N'TF'))
EXECUTE('DROP FUNCTION GetStateDaily')
GO
-- drop views
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagedEntityMonitor')
BEGIN
EXECUTE ('DROP VIEW vManagedEntityMonitor')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vHealthServiceOutage')
BEGIN
EXECUTE ('DROP VIEW vHealthServiceOutage')
END
-- drop tables
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StateHealthServiceOutage' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('DROP TABLE dbo.StateHealthServiceOutage')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StateInvalidatedHealthServiceOutage' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('DROP TABLE dbo.StateInvalidatedHealthServiceOutage')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StateAggregationProcessing' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('DROP TABLE dbo.StateAggregationProcessing')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StateStage' AND TABLE_SCHEMA = 'State')
BEGIN
EXECUTE('DROP TABLE State.StateStage')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ManagedEntityMonitor' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('DROP TABLE dbo.ManagedEntityMonitor')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'HealthSerfviceOutage' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('DROP TABLE dbo.HealthSerfviceOutage')
END
-- drop "full" views
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vStateFull')
BEGIN
EXECUTE ('DROP VIEW [vStateFull]')
END
DECLARE
@AggregationTypeId int
,@Statement nvarchar(max)
,@BaseTableName nvarchar(256)
-- roll through all aggregations created
-- and drop "full" state views
SET @AggregationTypeId = 0
WHILE EXISTS (SELECT * FROM StandardDatasetAggregationStorage
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
AND (DependentTableInd = 0)
)
BEGIN
SELECT TOP 1
@AggregationTypeId = AggregationTypeId
,@BaseTableName = BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
AND (DependentTableInd = 0)
-- create dummy view if not yet exists
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'v' + REPLACE(@BaseTableName, '''', '''''') + 'Full')
BEGIN
SET @Statement = 'DROP VIEW ' + QUOTENAME('v' + @BaseTableName + 'Full')
EXECUTE (@Statement)
END
END
-- delete std dataset related data
EXEC StandardDatasetDelete '$Config/DatasetId$'
GO
</Uninstall> <Upgrade>
SET NOCOUNT ON
-- update table definition templates
UPDATE sdas
SET TableTemplate = '
CREATE TABLE ' + QUOTENAME(sd.SchemaName) + '.' + QUOTENAME(sdas.BaseTableName + '_$Guid$') + '
(
' + QUOTENAME(sdas.BaseTableName + 'RowId') + ' int NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,ManagedEntityMonitorRowId int NOT NULL
,InRedStateMilliseconds int NOT NULL
,InYellowStateMilliseconds int NOT NULL
,InDisabledStateMilliseconds int NOT NULL
,InPlannedMaintenanceMilliseconds int NOT NULL
,InUnplannedMaintenanceMilliseconds int NOT NULL
,HealthServiceUnavailableMilliseconds int NOT NULL
,IntervalEndHealthState tinyint NULL
,CONSTRAINT ' + QUOTENAME('PK_' + sdas.BaseTableName + '_$Guid$') + ' PRIMARY KEY CLUSTERED (' +
+ QUOTENAME(sdas.BaseTableName + 'RowId') + ') ON $DataFileGroupName$
) ON $DataFileGroupName$
'
, CoverViewSelectClause = '
SELECT
ManagedEntityMonitorRowId
,[DateTime]
,InRedStateMilliseconds
,InYellowStateMilliseconds
,InDisabledStateMilliseconds
,InPlannedMaintenanceMilliseconds
,InUnplannedMaintenanceMilliseconds
,HealthServiceUnavailableMilliseconds
,IntervalEndHealthState
'
FROM StandardDatasetAggregationStorage sdas
JOIN StandardDataset sd ON (sdas.DatasetId = sd.DatasetId)
WHERE (sd.DatasetId = '$Config/DatasetId$')
AND (sdas.AggregationTypeId > 0)
GO
-- update all tables that were already created
DECLARE
@StandardDatasetTableMapRowId int
,@Statement nvarchar(max)
,@SchemaName sysname
,@TableNameSuffix sysname
,@BaseTableName sysname
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap tm
WHERE (tm.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (tm.DatasetId = '$Config/DatasetId$')
AND (tm.AggregationTypeId > 0)
)
BEGIN
SELECT TOP 1
@StandardDatasetTableMapRowId = tm.StandardDatasetTableMapRowId
,@SchemaName = sd.SchemaName
,@TableNameSuffix = tm.TableNameSuffix
,@BaseTableName = sdas.BaseTableName
FROM StandardDatasetTableMap tm
JOIN StandardDataset sd ON (tm.DatasetId = sd.DatasetId)
JOIN StandardDatasetAggregationStorage sdas ON (sdas.DatasetId = tm.DatasetId) AND (sdas.AggregationTypeId = tm.AggregationTypeId)
WHERE (tm.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (tm.DatasetId = '$Config/DatasetId$')
AND (tm.AggregationTypeId > 0)
ORDER BY tm.StandardDatasetTableMapRowId
SET @Statement = 'IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''' + @BaseTableName + '_' + @TableNameSuffix + ''' AND COLUMN_NAME = ''HealthServiceUnavailableMilliseconds'' AND TABLE_SCHEMA = ''' + @SchemaName + ''')'
+ ' ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@BaseTableName + '_' + @TableNameSuffix)
+ ' ADD HealthServiceUnavailableMilliseconds int NOT NULL DEFAULT 0'
EXECUTE (@Statement)
END
GO
-- alter cover views
DECLARE
@AggregationTypeId int
SET @AggregationTypeId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregation
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
)
BEGIN
SELECT TOP 1
@AggregationTypeId = AggregationTypeId
FROM StandardDatasetAggregation
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
ORDER BY AggregationTypeId
EXEC StandardDatasetBuildCoverView '$Config/DatasetId$', @AggregationTypeId
END
GO
SET NOCOUNT ON
GO
-- create [DateTime] field indexes for all aggregation levels
DECLARE
@AggregationTypeId int
,@IndexGuid uniqueidentifier
SET @AggregationTypeId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregation
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
)
BEGIN
SELECT TOP 1
@AggregationTypeId = AggregationTypeId
FROM StandardDatasetAggregation
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
ORDER BY AggregationTypeId
-- check to see if covering index exists
SET @IndexGuid = NULL
SELECT
@IndexGuid = si.IndexGuid
FROM StandardDatasetAggregationStorage s
JOIN StandardDatasetAggregationStorageindex si ON (s.StandardDatasetAggregationStorageRowId = si.StandardDatasetAggregationStorageRowId)
WHERE (s.DatasetId = '$Config/DatasetId$')
AND (s.AggregationTypeId = @AggregationTypeId)
AND (si.IndexDefinition LIKE '(DateTime, ManagedEntityMonitorRowId) INCLUDE%')
IF (@IndexGuid IS NOT NULL)
BEGIN
EXEC StandardDatasetIndexDelete '$Config/DatasetId$', @IndexGuid, 1
END
ELSE
BEGIN
SET @IndexGuid = NEWID()
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StateAggregationProcessing' AND COLUMN_NAME = 'LastProcessedHealthServiceOutageModifiedDateTime')
BEGIN
EXECUTE ('ALTER TABLE StateAggregationProcessing ADD LastProcessedHealthServiceOutageModifiedDateTime datetime NULL')
END
GO
SET NOCOUNT ON
-- create [DateTime] index for raw aggregations
DECLARE @IndexGuid uniqueidentifier
-- check to see if index exists
SET @IndexGuid = NULL
SELECT
@IndexGuid = si.IndexGuid
FROM StandardDatasetAggregationStorage s
JOIN StandardDatasetAggregationStorageIndex si ON (s.StandardDatasetAggregationStorageRowId = si.StandardDatasetAggregationStorageRowId)
WHERE (s.DatasetId = '$Config/DatasetId$')
AND (s.AggregationTypeId = 0)
AND (si.IndexDefinition LIKE '(DateTime, ManagedEntityMonitorRowId) INCLUDE%')
IF (@IndexGuid IS NULL)
BEGIN
SET @IndexGuid = NEWID()
-- delete index w/o included fields if one still exists
SET @IndexGuid = NULL
SELECT
@IndexGuid = si.IndexGuid
FROM StandardDatasetAggregationStorage s
JOIN StandardDatasetAggregationStorageIndex si ON (s.StandardDatasetAggregationStorageRowId = si.StandardDatasetAggregationStorageRowId)
WHERE (s.DatasetId = '$Config/DatasetId$')
AND (s.AggregationTypeId = 0)
AND (si.IndexDefinition LIKE '(DateTime, ManagedEntityMonitorRowId)')
IF (@IndexGuid IS NOT NULL)
BEGIN
EXEC StandardDatasetIndexDelete '$Config/DatasetId$', @IndexGuid, 1
END
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StateInvalidatedHealthServiceOutage' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.StateInvalidatedHealthServiceOutage
(
HealthServiceOutageRowId int NOT NULL
,DWCreatedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
,CONSTRAINT PK_StateInvalidatedHealthServiceOutage PRIMARY KEY CLUSTERED (HealthServiceOutageRowId)
)
END
GO
EXEC DomainTableRegisterIndexOptimization
@TableName = 'StateInvalidatedHealthServiceOutage'
,@DatasetId = '$Config/DatasetId$'
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StateProcessedHealthServiceOutage' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.StateProcessedHealthServiceOutage
(
HealthServiceOutageRowId int NOT NULL
,StartDateTime datetime NOT NULL
,EndDateTime datetime NULL
,ReasonCode tinyint NOT NULL
,HealthServiceOutageDWLastModifiedDateTime datetime NULL
,InvalidatedDateTime datetime NULL
,DWCreatedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
,DWLastModifiedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
,CONSTRAINT PK_StateProcessedHealthServiceOutage PRIMARY KEY CLUSTERED (HealthServiceOutageRowId)
)
END
GO
EXEC DomainTableRegisterIndexOptimization
@TableName = 'StateProcessedHealthServiceOutage'
,@DatasetId = '$Config/DatasetId$'
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StateProcessedMaintenanceMode' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.StateProcessedMaintenanceMode
(
MaintenanceModeRowId int NOT NULL
,StartDateTime datetime NOT NULL
,EndDateTime datetime NULL
,PlannedMaintenanceInd bit NOT NULL
,MaintenanceModeDWLastModifiedDateTime datetime NULL
,DWCreatedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
,DWLastModifiedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
,CONSTRAINT PK_StateProcessedMaintenanceMode PRIMARY KEY CLUSTERED (MaintenanceModeRowId)
)
END
GO
EXEC DomainTableRegisterIndexOptimization
@TableName = 'StateProcessedMaintenanceMode'
,@DatasetId = '$Config/DatasetId$'
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StateHealthServiceOutage' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.StateHealthServiceOutage
(
ReasonCode tinyint NOT NULL
,DWCreatedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
,CONSTRAINT PK_StateHealthServiceOutage PRIMARY KEY CLUSTERED (ReasonCode)
)
END
GO
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM StateHealthServiceOutage WHERE ReasonCode = 0)
BEGIN
INSERT StateHealthServiceOutage(ReasonCode) VALUES (0)
END
IF NOT EXISTS (SELECT * FROM StateHealthServiceOutage WHERE ReasonCode = 1)
BEGIN
INSERT StateHealthServiceOutage(ReasonCode) VALUES (1)
END
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
BEGIN TRY
-- roll through all aggregations created
-- and build "full" state views
SET @AggregationTypeId = 0
SET @FullViewStatement = 'ALTER VIEW dbo.[vStateFull] AS '
SET @FullViewStatementConcatenator = ''
WHILE EXISTS (SELECT * FROM StandardDatasetAggregationStorage
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
AND (DependentTableInd = 0)
)
BEGIN
SELECT TOP 1
@AggregationTypeId = AggregationTypeId
,@BaseTableName = BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
AND (DependentTableInd = 0)
-- create dummy view if not yet exists
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'v' + REPLACE(@BaseTableName, '''', '''''') + 'Full')
BEGIN
SET @Statement = 'CREATE VIEW dbo.' + QUOTENAME('v' + @BaseTableName + 'Full') + ' AS SELECT A = 1'
EXECUTE (@Statement)
SET @Statement = 'GRANT SELECT ON dbo.' + QUOTENAME('v' + @BaseTableName + 'Full') + ' TO OpsMgrReader'
EXECUTE (@Statement)
END
SELECT @Statement =
'ALTER VIEW dbo.' + CAST(QUOTENAME('v' + @BaseTableName + 'Full') as nvarchar(max))
+ ' AS'
+ ' SELECT '
-- daily aggregations appear to be in "midnight" or thereabouts
-- to aid UI to present data in different timezones
+ ' [DateTime]'
+ ' ,[Date]'
+ ' ,[Time]'
+ ' ,ManagedEntityMonitorRowId'
+ ' ,ManagedEntityRowId'
+ ' ,MonitorRowId'
+ ' ,IntervalDurationMilliseconds'
+ ' ,InYellowStateMilliseconds'
+ ' ,InRedStateMilliseconds'
+ ' ,InPlannedMaintenanceMilliseconds'
+ ' ,InUnplannedMaintenanceMilliseconds'
+ ' ,InDisabledStateMilliseconds'
+ ' ,HealthServiceUnavailableMilliseconds'
+ ' ,InWhiteStateMilliseconds ='
+ ' CASE'
+ ' WHEN ((ManagedEntityMonitorExistedInd = 0) OR '
+ ' (ah.AggregationCount = 0) OR '
+ ' (DATEADD(ms, IntervalDurationMilliseconds, [DateTime]) >= GETUTCDATE())) '
+ ' THEN IntervalDurationMilliseconds - InYellowStateMilliseconds - InRedStateMilliseconds - InDisabledStateMilliseconds - InPlannedMaintenanceMilliseconds - InUnplannedMaintenanceMilliseconds - HealthServiceUnavailableMilliseconds'
+ ' ELSE 0'
+ ' END'
+ ' ,InGreenStateMilliseconds ='
+ ' CASE'
+ ' WHEN (ManagedEntityMonitorExistedInd = 1) AND '
+ ' ((ah.AggregationCount > 0) OR '
+ ' ((ah.AggregationCount IS NULL) AND '
+ ' (DATEADD(ms, IntervalDurationMilliseconds, [DateTime]) < GETUTCDATE()))) '
+ ' THEN IntervalDurationMilliseconds - InYellowStateMilliseconds - InRedStateMilliseconds - InDisabledStateMilliseconds - InPlannedMaintenanceMilliseconds - InUnplannedMaintenanceMilliseconds - HealthServiceUnavailableMilliseconds'
+ ' ELSE 0'
+ ' END'
+ ' FROM ('
+ ' SELECT'
+ ' dt.[DateTime]'
+ ' ,dt.[Date]'
+ ' ,dt.[Time]'
+ ' ,ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId'
+ ' ,ManagedEntityRowId = mem.ManagedEntityRowId'
+ ' ,MonitorRowId = mem.MonitorRowId'
+ ' ,ManagedEntityMonitorExistedInd = CASE WHEN mem_exists.ManagedEntityMonitorRowId IS NULL THEN 0 ELSE 1 END'
+ ' ,IntervalDurationMilliseconds = 60 * 1000 * ' + CAST(a.AggregationIntervalDurationMinutes AS varchar)
+ ' ,InYellowStateMilliseconds = ISNULL(s.InYellowStateMilliseconds, 0)'
+ ' ,InRedStateMilliseconds = ISNULL(s.InRedStateMilliseconds, 0)'
+ ' ,InDisabledStateMilliseconds = ISNULL(s.InDisabledStateMilliseconds, 0)'
+ ' ,InPlannedMaintenanceMilliseconds = ISNULL(s.InPlannedMaintenanceMilliseconds, 0)'
+ ' ,InUnplannedMaintenanceMilliseconds = ISNULL(s.InUnplannedMaintenanceMilliseconds, 0)'
+ ' ,HealthServiceUnavailableMilliseconds = ISNULL(s.HealthServiceUnavailableMilliseconds, 0)'
+ ' FROM vDateTime dt'
+ ' CROSS JOIN vManagedEntityMonitor mem'
+ ' LEFT JOIN ' + QUOTENAME(d.SchemaName) + '.' + QUOTENAME('v' + @BaseTableName) + ' s ON '
+ CASE @AggregationTypeId
WHEN 30 THEN '((s.[DateTime] >= dt.[DateTime]) AND (s.[DateTime] < DATEADD(day, 1, dt.[DateTime])) AND (s.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId))'
ELSE '((s.[DateTime] = dt.[DateTime]) AND (s.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId))'
END
+ ' LEFT JOIN (SELECT DISTINCT mem2.ManagedEntityMonitorRowId, dt2.[Date], dt2.[Time]'
+ ' FROM vDateTime dt2'
+ ' CROSS JOIN vManagedEntityMonitor mem2'
+ ' JOIN vManagedEntity me ON (me.ManagedEntityRowId = mem2.ManagedEntityRowId)'
+ ' JOIN vMonitorManagementPackVersion mmpv ON (mmpv.MonitorRowId = mem2.MonitorRowId)'
+ ' JOIN vManagementGroupManagementPackVersion mgmpv ON ((mgmpv.ManagementPackVersionRowId = mmpv.ManagementPackVersionRowId) AND'
+ ' (mgmpv.ManagementGroupRowId = me.ManagementGroupRowId) AND'
+ ' (dt2.[DateTime] >= mgmpv.InstalledDateTime) AND '
+ ' (dt2.[DateTime] < ISNULL(mgmpv.DeletedDateTime, ''99991231'')))'
+ ' JOIN vManagedEntityManagementGroup memg ON ((mem2.ManagedEntityRowId = memg.ManagedEntityRowId) AND'
+ ' (dt2.[DateTime] >= memg.FromDateTime) AND '
+ ' (dt2.[DateTime] < ISNULL(memg.ToDateTime, ''99991231'')))'
+ CASE @AggregationTypeId
WHEN 10 /* subhourly */ THEN ' WHERE (dt2.Minute % ' + CAST(a.AggregationIntervalDurationMinutes AS varchar) + ' = 0)'
WHEN 20 /* hourly */ THEN ' WHERE (dt2.Minute = 0)'
-- Perform daily aggregations at midnight UTC times, even in x:30 timezones where dt2.minute != 0
WHEN 30 /* daily */ THEN ' WHERE (dt2.Minute = ABS(DATEDIFF(minute, GETUTCDATE(), GETDATE()) % 60)) AND DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), GETDATE()), dt2.[DateTime]) = CONVERT(datetime, CONVERT(char(8), DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), GETDATE()), dt2.[DateTime]), 112), 112)'
END
+ ' ) AS mem_exists ON (mem_exists.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId) AND (mem_exists.[Date] = dt.[Date]) AND (mem_exists.[Time] = dt.[Time])'
+ CASE @AggregationTypeId
WHEN 10 /* subhourly */ THEN ' WHERE (dt.Minute % ' + CAST(a.AggregationIntervalDurationMinutes AS varchar) + ' = 0)'
WHEN 20 /* hourly */ THEN ' WHERE (dt.Minute = 0)'
-- Perform daily aggregations at midnight UTC times, even in x:30 timezones where dt.Minute != 0
WHEN 30 /* daily */ THEN ' WHERE (dt.Minute = ABS(DATEDIFF(minute, GETUTCDATE(), GETDATE()) % 60)) AND DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), GETDATE()), dt.[DateTime]) = CONVERT(datetime, CONVERT(char(8), DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), GETDATE()), dt.[DateTime]), 112), 112)'
END
+ ' ) AS tbl'
+ ' LEFT JOIN StandardDatasetAggregationHistory (NOLOCK) ah ON'
+ ' ('
+ CASE @AggregationTypeId
WHEN 30 THEN '(ah.AggregationDateTime >= tbl.[DateTime]) AND (ah.AggregationDateTime < DATEADD(day, 1, tbl.[DateTime]))'
ELSE '(ah.AggregationDateTime = tbl.[DateTime])'
END
+ ' AND (ah.DatasetId = ''$Config/DatasetId$'')'
+ ' AND (ah.AggregationTypeId = ' + CAST(AggregationTypeId AS varchar(10)) + '))'
FROM StandardDatasetAggregation a
JOIN StandardDataset d ON (d.DatasetId = a.DatasetId)
WHERE (d.DatasetId = '$Config/DatasetId$')
AND (a.AggregationTypeId = @AggregationTypeId)
SET @FullViewStatementConcatenator = ' UNION ALL '
END
-- create dummy
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vStateFull')
BEGIN
EXECUTE ('CREATE VIEW dbo.[vStateFull] AS SELECT A = 1')
EXECUTE ('GRANT SELECT ON dbo.[vStateFull] TO OpsMgrReader')
END
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
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vStateHealthServiceOutage')
BEGIN
EXECUTE ('CREATE VIEW dbo.vStateHealthServiceOutage AS SELECT A = 1')
END
GO
ALTER VIEW dbo.vStateHealthServiceOutage
AS
SELECT *
FROM dbo.StateHealthServiceOutage (NOLOCK)
GO
GRANT SELECT ON dbo.vStateHealthServiceOutage TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.GetStateHourly') and xtype in (N'FN', N'IF', N'TF'))
EXECUTE('CREATE FUNCTION dbo.GetStateHourly() RETURNS TABLE AS RETURN SELECT NULL as Blank')
GO
ALTER FUNCTION dbo.GetStateHourly
(
@StartDate datetime
,@EndDate datetime
)
RETURNS TABLE
AS
RETURN (
WITH DateTimeCTE
AS (
SELECT
DateTime,
Date,
Time,
Minute
FROM vDateTime dt WHERE dt.Minute = 0
AND dt.DateTime >= @StartDate
AND dt.DateTime < @EndDate
AND dt.[Date] BETWEEN DATEADD(day, -1, @StartDate) AND DATEADD(day, 1, @EndDate)
)
SELECT
[DateTime],
[Date],
[Time],
ManagedEntityMonitorRowId,
ManagedEntityRowId,
MonitorRowId,
IntervalDurationMilliseconds,
InYellowStateMilliseconds,
InRedStateMilliseconds,
InPlannedMaintenanceMilliseconds,
InUnplannedMaintenanceMilliseconds,
InDisabledStateMilliseconds,
HealthServiceUnavailableMilliseconds,
InWhiteStateMilliseconds =
CASE
WHEN ((ManagedEntityMonitorExistedInd = 0) OR (ah.AggregationCount = 0) OR (DATEADD(ms, IntervalDurationMilliseconds, [DateTime]) >= GETUTCDATE())) THEN IntervalDurationMilliseconds - InYellowStateMilliseconds - InRedStateMilliseconds - InDisabledStateMilliseconds - InPlannedMaintenanceMilliseconds - InUnplannedMaintenanceMilliseconds - HealthServiceUnavailableMilliseconds ELSE 0
END,
InGreenStateMilliseconds =
CASE
WHEN (ManagedEntityMonitorExistedInd = 1) AND ((ah.AggregationCount > 0) OR ((ah.AggregationCount IS NULL) AND (DATEADD(ms, IntervalDurationMilliseconds, [DateTime]) < GETUTCDATE()))) THEN IntervalDurationMilliseconds - InYellowStateMilliseconds - InRedStateMilliseconds - InDisabledStateMilliseconds - InPlannedMaintenanceMilliseconds - InUnplannedMaintenanceMilliseconds - HealthServiceUnavailableMilliseconds ELSE 0
END
FROM (SELECT
dt.[DateTime],
dt.[Date],
dt.[Time],
ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId,
ManagedEntityRowId = mem.ManagedEntityRowId,
MonitorRowId = mem.MonitorRowId,
ManagedEntityMonitorExistedInd =
CASE
WHEN mem_exists.ManagedEntityMonitorRowId IS NULL THEN 0 ELSE 1
END,
IntervalDurationMilliseconds = 60 * 1000 * 60,
InYellowStateMilliseconds = ISNULL(s.InYellowStateMilliseconds, 0),
InRedStateMilliseconds = ISNULL(s.InRedStateMilliseconds, 0),
InDisabledStateMilliseconds = ISNULL(s.InDisabledStateMilliseconds, 0),
InPlannedMaintenanceMilliseconds = ISNULL(s.InPlannedMaintenanceMilliseconds, 0),
InUnplannedMaintenanceMilliseconds = ISNULL(s.InUnplannedMaintenanceMilliseconds, 0),
HealthServiceUnavailableMilliseconds = ISNULL(s.HealthServiceUnavailableMilliseconds, 0)
FROM DateTimeCTE dt
CROSS JOIN vManagedEntityMonitor mem
LEFT JOIN [State].[vStateHourly] s
ON ((s.[DateTime] = dt.[DateTime]) AND (s.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId))
LEFT JOIN (SELECT DISTINCT
mem2.ManagedEntityMonitorRowId,
dt2.[Date],
dt2.[Time]
FROM DateTimeCTE dt2
CROSS JOIN vManagedEntityMonitor mem2
JOIN vManagedEntity me
ON (me.ManagedEntityRowId = mem2.ManagedEntityRowId)
JOIN vMonitorManagementPackVersion mmpv
ON (mmpv.MonitorRowId = mem2.MonitorRowId)
JOIN vManagementGroupManagementPackVersion mgmpv
ON ((mgmpv.ManagementPackVersionRowId = mmpv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = me.ManagementGroupRowId) AND (dt2.[DateTime] >= mgmpv.InstalledDateTime) AND (dt2.[DateTime] < ISNULL(mgmpv.DeletedDateTime, '99991231')))
JOIN vManagedEntityManagementGroup memg
ON ((mem2.ManagedEntityRowId = memg.ManagedEntityRowId) AND (dt2.[DateTime] >= memg.FromDateTime) AND (dt2.[DateTime] < ISNULL(memg.ToDateTime, '99991231')))
WHERE (dt2.Minute = 0)) AS mem_exists
ON (mem_exists.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId) AND (mem_exists.[Date] = dt.[Date]) AND (mem_exists.[Time] = dt.[Time])
WHERE (dt.Minute = 0)) AS tbl
LEFT JOIN StandardDataset sd ON sd.SchemaName = 'State'
LEFT JOIN StandardDatasetAggregationHistory(NOLOCK) ah
ON ((ah.AggregationDateTime = tbl.[DateTime]) AND (ah.DatasetId = sd.DatasetId) AND (ah.AggregationTypeId = 20))
)
GO
GRANT SELECT ON GetStateHourly TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.GetStateDaily') and xtype in (N'FN', N'IF', N'TF'))
EXECUTE('CREATE FUNCTION dbo.GetStateDaily() RETURNS TABLE AS RETURN SELECT NULL as Blank')
GO
ALTER FUNCTION dbo.GetStateDaily
(
@StartDate datetime
,@EndDate datetime
)
RETURNS TABLE
AS
RETURN (
WITH DateTimeCTE
AS (
SELECT
DateTime,
Date,
Time,
Minute
FROM vDateTime dt WHERE dt.Minute = 0
AND dt.DateTime >= @StartDate
AND dt.DateTime < @EndDate
AND dt.[Date] BETWEEN DATEADD(day, -1, @StartDate) AND DATEADD(day, 1, @EndDate)
)
SELECT
[DateTime],
[Date],
[Time],
ManagedEntityMonitorRowId,
ManagedEntityRowId,
MonitorRowId,
IntervalDurationMilliseconds,
InYellowStateMilliseconds,
InRedStateMilliseconds,
InPlannedMaintenanceMilliseconds,
InUnplannedMaintenanceMilliseconds,
InDisabledStateMilliseconds,
HealthServiceUnavailableMilliseconds,
InWhiteStateMilliseconds =
CASE
WHEN ((ManagedEntityMonitorExistedInd = 0) OR (ah.AggregationCount = 0) OR (DATEADD(ms, IntervalDurationMilliseconds, [DateTime]) >= GETUTCDATE())) THEN IntervalDurationMilliseconds - InYellowStateMilliseconds - InRedStateMilliseconds - InDisabledStateMilliseconds - InPlannedMaintenanceMilliseconds - InUnplannedMaintenanceMilliseconds - HealthServiceUnavailableMilliseconds ELSE 0
END,
InGreenStateMilliseconds =
CASE
WHEN (ManagedEntityMonitorExistedInd = 1) AND ((ah.AggregationCount > 0) OR ((ah.AggregationCount IS NULL) AND (DATEADD(ms, IntervalDurationMilliseconds, [DateTime]) < GETUTCDATE()))) THEN IntervalDurationMilliseconds - InYellowStateMilliseconds - InRedStateMilliseconds - InDisabledStateMilliseconds - InPlannedMaintenanceMilliseconds - InUnplannedMaintenanceMilliseconds - HealthServiceUnavailableMilliseconds ELSE 0
END
FROM (SELECT
dt.[DateTime],
dt.[Date],
dt.[Time],
ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId,
ManagedEntityRowId = mem.ManagedEntityRowId,
MonitorRowId = mem.MonitorRowId,
ManagedEntityMonitorExistedInd =
CASE
WHEN mem_exists.ManagedEntityMonitorRowId IS NULL THEN 0 ELSE 1
END,
IntervalDurationMilliseconds = 60 * 1000 * 1440,
InYellowStateMilliseconds = ISNULL(s.InYellowStateMilliseconds, 0),
InRedStateMilliseconds = ISNULL(s.InRedStateMilliseconds, 0),
InDisabledStateMilliseconds = ISNULL(s.InDisabledStateMilliseconds, 0),
InPlannedMaintenanceMilliseconds = ISNULL(s.InPlannedMaintenanceMilliseconds, 0),
InUnplannedMaintenanceMilliseconds = ISNULL(s.InUnplannedMaintenanceMilliseconds, 0),
HealthServiceUnavailableMilliseconds = ISNULL(s.HealthServiceUnavailableMilliseconds, 0)
FROM DateTimeCTE dt
CROSS JOIN vManagedEntityMonitor mem
LEFT JOIN [State].[vStateDaily] s
ON ((s.[DateTime] >= dt.[DateTime]) AND (s.[DateTime] < DATEADD(DAY, 1, dt.[DateTime])) AND (s.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId))
LEFT JOIN (SELECT DISTINCT
mem2.ManagedEntityMonitorRowId,
dt2.[Date],
dt2.[Time]
FROM DateTimeCTE dt2
CROSS JOIN vManagedEntityMonitor mem2
JOIN vManagedEntity me
ON (me.ManagedEntityRowId = mem2.ManagedEntityRowId)
JOIN vMonitorManagementPackVersion mmpv
ON (mmpv.MonitorRowId = mem2.MonitorRowId)
JOIN vManagementGroupManagementPackVersion mgmpv
ON ((mgmpv.ManagementPackVersionRowId = mmpv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = me.ManagementGroupRowId) AND (dt2.[DateTime] >= mgmpv.InstalledDateTime) AND (dt2.[DateTime] < ISNULL(mgmpv.DeletedDateTime, '99991231')))
JOIN vManagedEntityManagementGroup memg
ON ((mem2.ManagedEntityRowId = memg.ManagedEntityRowId) AND (dt2.[DateTime] >= memg.FromDateTime) AND (dt2.[DateTime] < ISNULL(memg.ToDateTime, '99991231')))
WHERE (dt2.Minute = ABS(DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()) % 60)) AND DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), dt2.[DateTime]) = CONVERT(datetime, CONVERT(char(8), DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), dt2.[DateTime]), 112), 112)) AS mem_exists
ON (mem_exists.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId) AND (mem_exists.[Date] = dt.[Date]) AND (mem_exists.[Time] = dt.[Time])
WHERE (dt.Minute = ABS(DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()) % 60)) AND DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), dt.[DateTime]) = CONVERT(datetime, CONVERT(char(8), DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), dt.[DateTime]), 112), 112)) AS tbl
LEFT JOIN StandardDataset sd ON sd.SchemaName = 'State'
LEFT JOIN StandardDatasetAggregationHistory(NOLOCK) ah
ON ((ah.AggregationDateTime >= tbl.[DateTime]) AND (ah.AggregationDateTime < DATEADD(DAY, 1, tbl.[DateTime])) AND (ah.DatasetId = sd.DatasetId) AND (ah.AggregationTypeId = 30))
)
GO
GRANT SELECT ON GetStateDaily TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ManagedEntityMonitorDependencyList')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.ManagedEntityMonitorDependencyList AS RETURN 1')
END
GO
ALTER PROCEDURE 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
GRANT EXECUTE ON ManagedEntityMonitorDependencyList TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateAggregate')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StateAggregate AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StateAggregate
@DatasetId uniqueidentifier
,@AggregationTypeId int
,@InsertTableGuid varchar(256)
,@IntervalStartDateTime datetime
,@IntervalEndDateTime datetime
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
BEGIN TRY
DECLARE
@Statement nvarchar(max)
,@DebugLevel tinyint
,@AggregationCoverViewName sysname
,@InsertTableName sysname
,@PreviousIntervalStartDateTime datetime
,@DebugMessage nvarchar(4000)
,@AggregationStartDateTime datetime
,@LowerAggregationTypeId int
,@LowerAggregationIntervalMinutes int
,@NonDirtyLowerAggregationCount int
,@LowerAggregationCoverViewName sysname
,@UseAggregatesToBuildStartOfIntervalStateInd bit
,@SchemaName sysname
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
-- check to see if non-dirty lower rank
-- aggregations are available to build this
-- layer of aggregations
SELECT TOP 1
@LowerAggregationTypeId = AggregationTypeId
,@LowerAggregationIntervalMinutes = AggregationIntervalDurationMinutes
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId > 0)
AND (AggregationTypeId < @AggregationTypeId)
IF (@LowerAggregationTypeId IS NOT NULL)
BEGIN
-- check if once-calculated aggregations of lower rank
-- cover the entire interval of this aggregation
SELECT @NonDirtyLowerAggregationCount = COUNT(*)
FROM StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @LowerAggregationTypeId)
AND (AggregationCount > 0)
AND (AggregationDateTime >= @IntervalStartDateTime)
AND (AggregationDateTime < @IntervalEndDateTime)
END
IF (@NonDirtyLowerAggregationCount = (ABS(DATEDIFF(minute, @IntervalStartDateTime, @IntervalEndDateTime))) / @LowerAggregationIntervalMinutes)
BEGIN
IF (@DebugLevel > 2)
BEGIN
-- lower aggregation is available
-- use is to build aggregates
SET @DebugMessage = 'Starting aggregation of type ' + CAST(@AggregationTypeId AS varchar) + ' for interval from '
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + 'UTC to '
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + 'UTC. Using existing non-dirty aggregations of type ' + CAST(@LowerAggregationTypeId AS varchar)
SET @AggregationStartDateTime = GETUTCDATE()
SELECT @LowerAggregationCoverViewName = 'v' + BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @LowerAggregationTypeId)
AND (DependentTableInd = 0)
SET @Statement =
'INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' (
ManagedEntityMonitorRowId
,[DateTime]
,InRedStateMilliseconds
,InYellowStateMilliseconds
,InDisabledStateMilliseconds
,InPlannedMaintenanceMilliseconds
,InUnplannedMaintenanceMilliseconds
,HealthServiceUnavailableMilliseconds
)
SELECT
ManagedEntityMonitorRowId
,@IntervalStartDateTime
,SUM(InRedStateMilliseconds)
,SUM(InYellowStateMilliseconds)
,SUM(InDisabledStateMilliseconds)
,SUM(InPlannedMaintenanceMilliseconds)
,SUM(InUnplannedMaintenanceMilliseconds)
,SUM(HealthServiceUnavailableMilliseconds)
FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@LowerAggregationCoverViewName) + '
WHERE ([DateTime] >= @IntervalStartDateTime)
AND ([DateTime] < @IntervalEndDateTime)
GROUP BY ManagedEntityMonitorRowId'
EXEC sp_executesql
@stmt = @Statement
,@params = N'@IntervalStartDateTime datetime, @IntervalEndDateTime datetime'
,@IntervalStartDateTime = @IntervalStartDateTime
,@IntervalEndDateTime = @IntervalEndDateTime
END
ELSE
BEGIN
-- lower aggregation is dirty or not available
-- use raw data to build aggregates
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting aggregation of type ' + CAST(@AggregationTypeId AS varchar) + ' for interval from '
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + 'UTC to '
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + 'UTC. Using raw data'
SET @AggregationStartDateTime = GETUTCDATE()
SET @PreviousIntervalStartDateTime = DATEADD(minute, -ABS(DATEDIFF(minute, @IntervalStartDateTime, @IntervalEndDateTime)), @IntervalStartDateTime)
-- load states for monitors that were
-- not green at the beginning of this period
IF EXISTS (SELECT * FROM StandardDatasetAggregationHistory
WHERE (AggregationCount > 0)
AND (AggregationTypeId = @AggregationTypeId)
AND (AggregationDateTime = @PreviousIntervalStartDateTime)
)
BEGIN
-- we have non-dirty previous aggregation
-- use its info to get all non-green monitors
SET @UseAggregatesToBuildStartOfIntervalStateInd = 1
SELECT @AggregationCoverViewName = 'v' + BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
END
ELSE
BEGIN
-- pick up the latest state change
-- for all me/monitor pairs when
-- there was a change during the period
-- or the last state wasn't green
SET @UseAggregatesToBuildStartOfIntervalStateInd = 0
END
IF (OBJECT_ID('tempdb..#StateEvent') IS NOT NULL)
DROP TABLE #StateEvent
IF (OBJECT_ID('tempdb..#StateEventRanked') IS NOT NULL)
DROP TABLE #StateEventRanked
CREATE TABLE #StateEventRanked (
ManagedEntityMonitorRowId int
,[DateTime] datetime
,IntervalNumber int
,OldHealthState tinyint
,NewHealthState tinyint
)
INSERT #StateEventRanked (ManagedEntityMonitorRowId, [DateTime], IntervalNumber, OldHealthState, NewHealthState)
SELECT
ManagedEntityMonitorRowId
,[DateTime]
,IntervalNumber = RANK() OVER (PARTITION BY ManagedEntityMonitorRowId ORDER BY [DateTime])
,OldHealthState
,NewHealthState
FROM #StateEvent
IF (OBJECT_ID('tempdb..#StateInterval') IS NOT NULL)
DROP TABLE #StateInterval
INSERT #StateInterval (
ManagedEntityMonitorRowId
,IntervalStartDateTime
,IntervalEndDateTime
,HealthState
)
SELECT
l.ManagedEntityMonitorRowId
,CASE WHEN l.[DateTime] < @IntervalStartDateTime
THEN @IntervalStartDateTime
ELSE l.[DateTime]
END
,CASE WHEN ISNULL(r.[DateTime], '99991231') > @IntervalEndDateTime
THEN @IntervalEndDateTime
ELSE r.[DateTime]
END
,CASE WHEN l.NewHealthState > ISNULL(r.OldHealthState, l.NewHealthState)
THEN l.NewHealthState
ELSE ISNULL(r.OldHealthState, l.NewHealthState)
END
FROM #StateEventRanked AS l
LEFT JOIN #StateEventRanked AS r ON (l.ManagedEntityMonitorRowId = r.ManagedEntityMonitorRowId) AND (l.IntervalNumber + 1 = r.IntervalNumber)
WHERE (l.[DateTime] < @IntervalEndDateTime)
SET @Statement =
' INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' (
[DateTime]
,ManagedEntityMonitorRowId
,InRedStateMilliseconds
,InYellowStateMilliseconds
,InDisabledStateMilliseconds
,InPlannedMaintenanceMilliseconds
,InUnplannedMaintenanceMilliseconds
,HealthServiceUnavailableMilliseconds
,IntervalEndHealthState
)
SELECT
@IntervalStartDateTime
,si.ManagedEntityMonitorRowId
,SUM(ABS(DATEDIFF(ms, si.IntervalEndDateTime, si.IntervalStartDateTime)) * (CASE si.HealthState WHEN 3 THEN 1 ELSE 0 END) )
,SUM(ABS(DATEDIFF(ms, si.IntervalEndDateTime, si.IntervalStartDateTime)) * (CASE si.HealthState WHEN 2 THEN 1 ELSE 0 END) )
,SUM(ABS(DATEDIFF(ms, si.IntervalEndDateTime, si.IntervalStartDateTime)) * (CASE si.HealthState WHEN 0 THEN 1 ELSE 0 END) )
,SUM(ABS(DATEDIFF(ms, si.IntervalEndDateTime, si.IntervalStartDateTime)) * (CASE si.HealthState WHEN 101 THEN 1 ELSE 0 END) )
,SUM(ABS(DATEDIFF(ms, si.IntervalEndDateTime, si.IntervalStartDateTime)) * (CASE si.HealthState WHEN 100 THEN 1 ELSE 0 END) )
,SUM(ABS(DATEDIFF(ms, si.IntervalEndDateTime, si.IntervalStartDateTime)) * (CASE si.HealthState WHEN 50 THEN 1 ELSE 0 END) )
,MAX(ISNULL(se.NewHealthState, 1)) *
CASE WHEN MAX(ISNULL(se.NewHealthState, 1)) = 1 THEN 1
WHEN EXISTS (SELECT *
FROM vManagedEntityMonitor mem
JOIN vManagedEntity me ON (me.ManagedEntityRowId = mem.ManagedEntityRowId)
JOIN vMonitorManagementPackVersion mmpv ON (mmpv.MonitorRowId = mem.MonitorRowId)
JOIN vManagementGroupManagementPackVersion mgmpv ON ((mgmpv.ManagementPackVersionRowId = mmpv.ManagementPackVersionRowId) AND
(mgmpv.ManagementGroupRowId = me.ManagementGroupRowId) AND
(@IntervalEndDateTime >= mgmpv.InstalledDateTime) AND
(@IntervalEndDateTime < ISNULL(mgmpv.DeletedDateTime, ''99991231'')))
JOIN vManagedEntityManagementGroup memg ON ((mem.ManagedEntityRowId = memg.ManagedEntityRowId) AND
(@IntervalEndDateTime >= memg.FromDateTime) AND
(@IntervalEndDateTime < ISNULL(memg.ToDateTime, ''99991231'') ))
WHERE (mem.ManagedEntityMonitorRowId = si.ManagedEntityMonitorRowId)
)
THEN 1
ELSE NULL
END
FROM #StateInterval si
LEFT JOIN (
SELECT
ser.ManagedEntityMonitorRowId
,IntervalNumber = MAX(ser.IntervalNumber)
FROM #StateEventRanked ser
WHERE (ser.OldHealthState < 50)
AND (ser.NewHealthState < 50)
GROUP BY ser.ManagedEntityMonitorRowId
) AS last_transition ON (si.ManagedEntityMonitorRowId = last_transition.ManagedEntityMonitorRowId)
LEFT JOIN #StateEventRanked se ON (si.ManagedEntityMonitorRowId = se.ManagedEntityMonitorRowId) AND (se.IntervalNumber = last_transition.IntervalNumber)
WHERE (si.HealthState <> 1)
GROUP BY si.ManagedEntityMonitorRowId'
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished aggregation of type ' + CAST(@AggregationTypeId AS varchar) + ' for interval from '
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + 'UTC to '
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + 'UTC'
DECLARE @OperationDurationMs bigint
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @AggregationStartDateTime))
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to build aggregates for state data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateAggregationDelete')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StateAggregationDelete AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StateAggregationDelete
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@AggregationDateTime datetime
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- there are no dependent tables in state data set
SELECT @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
-- Index hint in the query below ensures that SQL won't
-- consider index on DateTime field to be a good source
-- to find rows to delete - it may happen periodically
-- if SQL thinks there's no rows to delete due to
-- lack of [recently updated] statistics
SET @Statement = 'DELETE t' +
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' t WITH (INDEX(1))'
+ ' WHERE (t.[DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
IF (@DebugLevel > 2)
BEGIN
SELECT @DebugMessage = 'Starting delete of existing aggregated data. Table: ' + @TableName + ', aggregation date: ' + CONVERT(varchar(20), @AggregationDateTime, 120)
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to delete aggregated data for state data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateChangeEventList')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StateChangeEventList AS RETURN 1')
END
GO
Builds list of all state change events that a relevant
for a given interval. If MEMRowId is provided only
that monitor instance is considered otherwise all
monitors are considered.
If @UseAggregatesToBuildStartOfIntervalStateInd is 0 the
proc will return last state change that happened
just before @IntervalStartDateTime for each monitor
that had a state change within the interval OR for the one
provided in parameters
If @UseAggregatesToBuildStartOfIntervalStateInd is 1, the
proc will use existing aggregated data specified
by parameters @AggregationCoverViewName, @AggregationDateTime
to build fake state change event that happened at
@AggregationDateTime (this method is used by aggregations
to save time when calculating aggregates and previous
aggregation is available)
*******************************************************/
ALTER PROCEDURE StateChangeEventList
@ManagedEntityMonitorRowId int = NULL
,@IntervalStartDateTime datetime
,@IntervalEndDateTime datetime
,@UseAggregatesToBuildStartOfIntervalStateInd bit = 0
,@AggregationCoverViewName sysname = NULL
,@AggregationDateTime datetime = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
BEGIN TRY
DECLARE
@Statement nvarchar(max)
,@RawCoverViewName sysname
,@ExtraCondition nvarchar(max)
SET @ExtraCondition =
CASE
WHEN @ManagedEntityMonitorRowId IS NULL THEN
CASE
WHEN (OBJECT_ID('tempdb..#ManagedEntityMonitor') IS NOT NULL) THEN ' AND (ManagedEntityMonitorRowId IN (SELECT ManagedEntityMonitorRowId FROM #ManagedEntityMonitor)) '
ELSE ''
END
ELSE ' AND (ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId)'
END
IF (OBJECT_ID('tempdb..#StateEventChange') IS NOT NULL)
DROP TABLE #StateEventChange
-- get all state changes
-- that happened within the interval
SET @Statement =
' INSERT #StateEventChange(
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState
,NewHealthState
FROM [State].[vStateRaw] s
WHERE ([DateTime] BETWEEN @IntervalStartDateTime AND @IntervalEndDateTime)'
+ @ExtraCondition
IF (@UseAggregatesToBuildStartOfIntervalStateInd = 1)
BEGIN
-- NOTE: We do not return intervals where end state is 1 (healthy)
SET @Statement =
' INSERT #StateEventChange(
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
s.ManagedEntityMonitorRowId
,@IntervalStartDateTime
,0
,ISNULL(s.IntervalEndHealthState, 0)
FROM [State].' + QUOTENAME(@AggregationCoverViewName) + ' s
WHERE (s.[DateTime] = @AggregationDateTime)
AND (s.IntervalEndHealthState <> 1)'
+ @ExtraCondition
EXEC sp_executesql
@stmt = @Statement
,@params = N'@IntervalStartDateTime datetime, @AggregationDateTime datetime, @ManagedEntityMonitorRowId int'
,@IntervalStartDateTime = @IntervalStartDateTime
,@AggregationDateTime = @AggregationDateTime
,@ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId
END
ELSE
BEGIN
-- pick up the latest state change
-- for all me/monitor pairs when
-- there was a change during the period
-- or the last state wasn't green
SET @Statement =
' INSERT #StateEventChange(
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
s.ManagedEntityMonitorRowId
,@IntervalStartDateTime
,0
,MAX(s.NewHealthState)
FROM [State].[vStateRaw] s
JOIN
(SELECT ManagedEntityMonitorRowId, [DateTime] = MAX(DateTime)
FROM [State].[vStateRaw]
WHERE ([DateTime] < @IntervalStartDateTime)'
+ @ExtraCondition + '
GROUP BY ManagedEntityMonitorRowId
) AS sd
ON (s.ManagedEntityMonitorRowId = sd.ManagedEntityMonitorRowId) AND (s.[DateTime] = sd.[DateTime])
GROUP BY s.ManagedEntityMonitorRowId'
EXEC sp_executesql
@stmt = @Statement
,@params = N'@IntervalStartDateTime datetime, @ManagedEntityMonitorRowId int'
,@IntervalStartDateTime = @IntervalStartDateTime
,@ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId
END
-- factor in HS availability/outage
IF EXISTS (SELECT * FROM StateHealthServiceOutage)
BEGIN
-- store all relationship types that represent 'manages'
-- concept in the table
IF (OBJECT_ID('tempdb..#ManagesRelationshipType') IS NOT NULL)
DROP TABLE #ManagesRelationshipType
CREATE TABLE #ManagesRelationshipType (
RelationshipTypeRowId int NOT NULL
)
-- R2 style 'manages' relationship between HS and an object
INSERT #ManagesRelationshipType (RelationshipTypeRowId)
SELECT RelationshipTypeRowId
FROM RelationshipType rt
WHERE (rt.RelationshipTypeSystemName = 'Microsoft.SystemCenter.HealthServiceManagesEntity')
-- OM12 style 'manages' relationship between HS2(Management Service) or pool and an object
INSERT #ManagesRelationshipType (RelationshipTypeRowId)
SELECT RelationshipTypeRowId
FROM RelationshipType rt
WHERE (rt.RelationshipTypeSystemName = 'Microsoft.SystemCenter.ManagementActionPointManagesEntity')
-- get a list of all relevant HSOs occurring in the given period of time
-- NOTE: we store two rows for every HSO - one for start of interval and one for the end
IF (OBJECT_ID('tempdb..#HealthServiceOutageEvent') IS NOT NULL)
DROP TABLE #HealthServiceOutageEvent
CREATE TABLE #HealthServiceOutageEvent (
ManagedEntityRowId int NOT NULL
,[DateTime] datetime NOT NULL
,OldHealthState tinyint NOT NULL
,NewHealthState tinyint NOT NULL
)
INSERT #HealthServiceOutageEvent (
ManagedEntityRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
hso.ManagedEntityRowId
,CASE
WHEN interval.StartIntervalInd = 1 THEN
CASE
WHEN hso.StartDateTime < @IntervalStartDateTime THEN @IntervalStartDateTime
ELSE hso.StartDateTime
END
ELSE
CASE
WHEN ISNULL(hso.EndDateTime, '99991231') > @IntervalEndDateTime THEN @IntervalEndDateTime
ELSE hso.EndDateTime
END
END
,CASE WHEN interval.StartIntervalInd = 1 THEN 0 ELSE 50 END
,CASE WHEN interval.StartIntervalInd = 1 THEN 50 ELSE 1 END
FROM vHealthServiceOutage hso
JOIN StateHealthServiceOutage shso ON (hso.ReasonCode = shso.ReasonCode)
CROSS JOIN (SELECT StartIntervalInd = 0
UNION ALL
SELECT 1
) AS interval
-- Only go back a maximum of 60 days to prevent infinite growth
WHERE (hso.StartDateTime BETWEEN DateAdd(DAY, -60, GETUTCDATE()) AND @IntervalEndDateTime)
AND (ISNULL(hso.EndDateTime, '99991231') >= @IntervalStartDateTime)
AND (NOT EXISTS (SELECT *
FROM StateInvalidatedHealthServiceOutage
WHERE (HealthServiceOutageRowId = hso.HealthServiceOutageRowId)
)
)
IF EXISTS (SELECT * FROM #HealthServiceOutageEvent)
BEGIN
-- take care of HSO outages that indicate HS or a pool outage
SET @Statement =
' INSERT #StateEventChange (
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
mem.ManagedEntityMonitorRowId
,hso.[DateTime]
,hso.OldHealthState
,hso.NewHealthState
FROM #HealthServiceOutageEvent hso
JOIN vRelationship r ON (r.SourceManagedEntityRowId = hso.ManagedEntityRowId)
JOIN #ManagesRelationshipType mrt ON (mrt.RelationshipTypeRowId = r.RelationshipTypeRowId)
JOIN vRelationshipManagementGroup rmg ON (r.RelationshipRowId = rmg.RelationshipRowId)
JOIN vManagedEntity me ON (r.TargetManagedEntityRowId = me.TopLevelHostManagedEntityRowId)
JOIN vManagedEntityMonitor mem ON (me.ManagedEntityRowId = mem.ManagedEntityRowId)
WHERE (@IntervalStartDateTime BETWEEN rmg.FromDateTime AND ISNULL(rmg.ToDateTime, ''99991231''))'
+ @ExtraCondition
-- take care of HSO outages that indicate entity (which is TLH) outage
SET @Statement =
' INSERT #StateEventChange (
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
mem.ManagedEntityMonitorRowId
,hso.[DateTime]
,hso.OldHealthState
,hso.NewHealthState
FROM #HealthServiceOutageEvent hso
JOIN vManagedEntity me ON (hso.ManagedEntityRowId = me.TopLevelHostManagedEntityRowId)
JOIN vManagedEntityMonitor mem ON (me.ManagedEntityRowId = mem.ManagedEntityRowId)'
+ @ExtraCondition
-- set new state of the periods where hso ended to 'last reported state transition'
CREATE INDEX temp1 ON #StateEventChange (ManagedEntityMonitorRowId)
SET @Statement = '
UPDATE se
SET se.NewHealthState = ISNULL(previous_real_transition.NewHealthState, 1)
FROM #StateEventChange se
JOIN (
SELECT
MEMRowId = previous_se.ManagedEntityMonitorRowId
,CutOffDateTime = cutoff_se.[DateTime]
,previous_se.NewHealthState
,StateEventNumber = RANK() OVER (PARTITION BY previous_se.ManagedEntityMonitorRowId, cutoff_se.[DateTime] ORDER BY previous_se.[DateTime] DESC, previous_se.NewHealthState DESC)
FROM #StateEventChange cutoff_se
JOIN #StateEventChange previous_se ON (cutoff_se.ManagedEntityMonitorRowId = previous_se.ManagedEntityMonitorRowId) AND (cutoff_se.[DateTime] > previous_se.[DateTime])
WHERE (cutoff_se.OldHealthState = 50)
AND (previous_se.OldHealthState < 50)
AND (previous_se.NewHealthState < 50) -- real state transition, not hso or mmode
) AS previous_real_transition ON (se.ManagedEntityMonitorRowId = previous_real_transition.MEMRowId) AND (se.[DateTime] = previous_real_transition.CutOffDateTime)
WHERE (se.OldHealthState = 50)
AND (previous_real_transition.StateEventNumber = 1)'
+ @ExtraCondition
EXEC sp_executesql
@stmt = @Statement
,@params = N'@ManagedEntityMonitorRowId int'
,@ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId
END
END
-- factor in maintenance mode
-- using two itertions - first for unlanned maintenance
-- then second for planned maintenance
DECLARE @i int
SET @i = 0
WHILE (@i < 2)
BEGIN
-- find all relevant mmode intervals
IF (OBJECT_ID('tempdb..#MaintenanceModeEvent') IS NOT NULL)
DROP TABLE #MaintenanceModeEvent
CREATE TABLE #MaintenanceModeEvent (
ManagedEntityRowId int NOT NULL
,[DateTime] datetime NOT NULL
,OldHealthState tinyint NOT NULL
,NewHealthState tinyint NOT NULL
)
INSERT #MaintenanceModeEvent (
ManagedEntityRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
mm.ManagedEntityRowId
,CASE
WHEN interval.StartIntervalInd = 1 THEN
CASE
WHEN mm.StartDateTime < @IntervalStartDateTime THEN @IntervalStartDateTime
ELSE mm.StartDateTime
END
ELSE
CASE
WHEN ISNULL(mm.EndDateTime, '99991231') > @IntervalEndDateTime THEN @IntervalEndDateTime
ELSE mm.EndDateTime
END
END
,CASE WHEN interval.StartIntervalInd = 1 THEN 0 ELSE 100 + @i END
,CASE WHEN interval.StartIntervalInd = 1 THEN 100 + @i ELSE 1 END
FROM vMaintenanceMode mm
CROSS JOIN (SELECT StartIntervalInd = 0
UNION ALL
SELECT 1
) AS interval
-- Only go back a maximum of 60 days to prevent infinite growth
WHERE (mm.StartDateTime BETWEEN DateAdd(DAY, -60, GETUTCDATE()) AND @IntervalEndDateTime)
AND (ISNULL(mm.EndDateTime, '99991231') >= @IntervalStartDateTime)
AND (mm.PlannedMaintenanceInd = @i)
IF EXISTS (SELECT * FROM #MaintenanceModeEvent)
BEGIN
DELETE se
FROM #StateEventChange se
JOIN vManagedEntityMonitor mem ON (se.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId)
JOIN vMaintenanceMode mm ON (mem.ManagedEntityRowId = mm.ManagedEntityRowId)
WHERE (se.[DateTime] BETWEEN mm.StartDateTime AND ISNULL(mm.EndDateTime, '99990101'))
AND (mm.PlannedMaintenanceInd = @i)
SET @Statement =
' INSERT #StateEventChange (
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
mem.ManagedEntityMonitorRowId
,mm.[DateTime]
,mm.OldHealthState
,mm.NewHealthState
FROM #MaintenanceModeEvent mm
JOIN vManagedEntityMonitor mem ON (mm.ManagedEntityRowId = mem.ManagedEntityRowId)
WHERE (1 = 1)'
+ @ExtraCondition
EXEC sp_executesql
@stmt = @Statement
,@params = N'@ManagedEntityMonitorRowId int'
,@ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId
END
SET @i = @i + 1
END
-- clean out dupes in state transition
SELECT
ManagedEntityMonitorRowId
,[DateTime]
,OldHealthState = MAX(OldHealthState)
,NewHealthState = MAX(NewHealthState)
FROM #StateEventChange
GROUP BY ManagedEntityMonitorRowId, [DateTime]
DROP TABLE #StateEventChange
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
-- 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
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateDowntimeList')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StateDowntimeList AS RETURN 1')
END
GO
ALTER PROCEDURE StateDowntimeList
@ManagedEntityMonitorRowId int
,@FromDateTime datetime
,@ToDateTime datetime
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
BEGIN TRY
IF (OBJECT_ID('tempdb..#StateEvent') IS NOT NULL)
DROP TABLE #StateEvent
INSERT #StateEventRanked (ManagedEntityMonitorRowId, [DateTime], IntervalNumber, OldHealthState, NewHealthState)
SELECT
ManagedEntityMonitorRowId
,[DateTime]
,IntervalNumber = RANK() OVER (PARTITION BY ManagedEntityMonitorRowId ORDER BY [DateTime])
,OldHealthState
,NewHealthState
FROM #StateEvent
SELECT
ManagedEntityMonitorRowId
,FromDateTime =
CASE
WHEN FromDateTime < @FromDateTime THEN @FromDateTime
ELSE FromDateTime
END
,ToDateTime = ISNULL(ToDateTime, @ToDateTime)
,HealthState
FROM (SELECT
l.ManagedEntityMonitorRowId
,FromDateTime = l.[DateTime]
,ToDateTime = r.[DateTime]
,HealthState =
CASE WHEN l.NewHealthState > ISNULL(r.OldHealthState, l.NewHealthState)
THEN l.NewHealthState
ELSE ISNULL(r.OldHealthState, l.NewHealthState)
END
FROM #StateEventRanked AS l
LEFT JOIN #StateEventRanked AS r ON (l.ManagedEntityMonitorRowId = r.ManagedEntityMonitorRowId) AND (l.IntervalNumber + 1 = r.IntervalNumber)
) AS tbl
WHERE (HealthState <> 1)
AND (FromDateTime < ISNULL(ToDateTime, @ToDateTime)) -- eliminate zero-length intervals
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- 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 StateDowntimeList TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StateGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StateGroom
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@CutoffDateTime datetime
,@MaxRowsToGroom int
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- there are no dependent tables in state data set
SELECT @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT
-- groom dynamic dimentions
DELETE mem
FROM ManagedEntityMonitor mem
WHERE NOT EXISTS (SELECT * FROM vMonitor m WHERE (m.MonitorRowId = mem.MonitorRowId))
DELETE mem
FROM ManagedEntityMonitor mem
WHERE NOT EXISTS (SELECT * FROM vManagedEntity me WHERE (me.ManagedEntityRowId = mem.ManagedEntityRowId))
-- groom staging of data for rejected MGs
DELETE State.StateStage
WHERE ManagementGroupGuid NOT IN (SELECT ManagementGroupGuid
FROM ManagementGroup mg
WHERE (ConnectConfirmedDateTime IS NOT NULL)
AND (ConnectRejectedInd = 0)
)
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to groom data for state data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'StateProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.StateProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.StateProcessStaging
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
DECLARE
@DebugLevel int
,@SchemaName sysname
,@ExecResult int
,@LockResourceName sysname
,@InsertTableName sysname
,@InsertTableGuid uniqueidentifier
,@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@MessageText varchar(max)
,@OperationDurationMs bigint
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- groom records that are > month old since
-- aggregations only kept one month long
DELETE State.StateStage WITH (TABLOCK)
WHERE ([DateTime] < DATEADD(month, -1, GETUTCDATE()))
-- process staging to create ME Monitor records for newly discovered monitors
INSERT ManagedEntityMonitor (
ManagedEntityRowId
,MonitorRowId
)
SELECT DISTINCT
me.ManagedEntityRowId
,m.MonitorRowId
FROM State.StateStage s
JOIN vManagementGroup mg ON (s.ManagementGroupGuid = mg.ManagementGroupGuid)
JOIN vManagedEntity me ON ((me.ManagedEntityGuid = s.ManagedEntityGuid) AND (mg.ManagementGroupRowId = me.ManagementGroupRowId))
JOIN vMonitor m ON (m.MonitorGuid = s.MonitorGuid)
WHERE (s.DatasetId = @DatasetId)
AND NOT EXISTS (SELECT *
FROM vManagedEntityMonitor
WHERE (ManagedEntityRowId = me.ManagedEntityRowId)
AND (MonitorRowId = m.MonitorRowId)
)
-- process events in staging setting MEM id if it is not yet determined
-- NOTE: The force order hint below is meant to avoid situation when
-- dw has more records in ME then in MEM and SQL thinks that querying
-- MEM first and then ME is beneficial (which is not true really)
UPDATE s
SET ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId
FROM State.StateStage s
JOIN vManagementGroup mg ON (s.ManagementGroupGuid = mg.ManagementGroupGuid)
JOIN vManagedEntity me ON ((me.ManagedEntityGuid = s.ManagedEntityGuid) AND (mg.ManagementGroupRowId = me.ManagementGroupRowId))
JOIN vMonitor m ON (m.MonitorGuid = s.MonitorGuid)
JOIN vManagedEntityMonitor mem ON ((mem.ManagedEntityRowId = me.ManagedEntityRowId) AND (mem.MonitorRowId = m.MonitorRowId))
WHERE (s.DatasetId = @DatasetId)
AND (s.InsertReadyInd = 0)
OPTION (FORCE ORDER)
-- delete sate transitions older then 48 hours to
-- not to go back 'too much' - such state transitions
-- are cut of by the agents, but they can linger in staging
-- if object existed but then was deleted
DELETE State.StateStage
WHERE (DatasetId = @DatasetId)
AND (InsertReadyInd > 0)
AND ([DateTime] < DATEADD(hour, -48, GETUTCDATE()))
-- invalidate HSO intervals when the HS in question
-- did send some state changes during the interval
-- NOTE: Starting OM2012 SP1 we do not invalidate
-- HS outage intervals to close map to operational
-- side of the house and not to throw state aggregations
-- back too much when HS coming out of long outage period
/*
IF EXISTS (SELECT * FROM StateHealthServiceOutage)
BEGIN
INSERT StateInvalidatedHealthServiceOutage (HealthServiceOutageRowId)
SELECT DISTINCT hso.HealthServiceOutageRowId
FROM State.StateStage s
JOIN vManagedEntity me ON (me.ManagedEntityGuid = s.ManagedEntityGuid)
JOIN vRelationship r ON (me.TopLevelHostManagedEntityRowId = r.TargetManagedEntityRowId)
JOIN vRelationshipManagementGroup rmg ON (r.RelationshipRowId = rmg.RelationshipRowId)
JOIN vRelationshipType rt ON (r.RelationshipTypeRowId = rt.RelationshipTypeRowId)
JOIN vHealthServiceOutage hso ON (r.SourceManagedEntityRowId = hso.ManagedEntityRowId)
JOIN StateHealthServiceOutage shso ON (hso.ReasonCode = shso.ReasonCode)
WHERE (s.InsertReadyInd > 0)
AND (s.[DateTime] BETWEEN rmg.FromDateTime AND ISNULL(rmg.ToDateTime, '99991231'))
AND (rt.RelationshipTypeSystemName = 'Microsoft.SystemCenter.HealthServiceManagesEntity')
AND (s.[DateTime] BETWEEN hso.StartDateTime AND ISNULL(hso.EndDateTime, '99991231'))
AND (NOT EXISTS (SELECT *
FROM StateInvalidatedHealthServiceOutage
WHERE (HealthServiceOutageRowId = hso.HealthServiceOutageRowId)
)
)
END
*/
-- mark existing aggregations as dirty when
-- new information about mmode is received
BEGIN TRAN
SELECT @LastProcessedMModeModifiedDateTime = LastProcessedMaintenanceModeModifiedDateTime
FROM StateAggregationProcessing
WHERE (DatasetId = @DatasetId)
IF (@LastProcessedMModeModifiedDateTime IS NULL)
BEGIN
SET @LastProcessedMModeModifiedDateTime = '20050101'
END
-- build a list of all mmode intervals
-- touched since last processing up until [almost] now
CREATE TABLE #UpdatedMaintenanceMode (
MaintenanceModeRowId int NOT NULL PRIMARY KEY
,StartDateTime datetime NOT NULL
,EndDateTime datetime NULL
,PlannedMaintenanceInd bit NOT NULL
,DWLastModifiedDateTime datetime NOT NULL
)
INSERT #UpdatedMaintenanceMode (
MaintenanceModeRowId
,StartDateTime
,EndDateTime
,PlannedMaintenanceInd
,DWLastModifiedDateTime
)
SELECT
MaintenanceModeRowId
,StartDateTime
,EndDateTime
,PlannedMaintenanceInd
,DWLastModifiedDateTime
FROM vMaintenanceMode mmode
WHERE (mmode.DWLastModifiedDateTime >= @LastProcessedMModeModifiedDateTime)
AND (mmode.DWLastModifiedDateTime < @NewLastProcessedMModeModifiedDateTime)
-- calculate minimum date for re-aggregations
SET @MinDate = NULL
SELECT @MinDate = MIN(
CASE
WHEN (processed_mmode.MaintenanceModeRowId IS NULL) -- not yet processed
THEN mmode.StartDateTime
ELSE
CASE
WHEN (processed_mmode.PlannedMaintenanceInd != mmode.PlannedMaintenanceInd) -- changed planned / unplanned
THEN mmode.StartDateTime
WHEN (mmode.EndDateTime IS NOT NULL) -- end time update - pick up minimum of end times
AND (ISNULL(processed_mmode.EndDateTime, '99991231') != mmode.EndDateTime)
THEN CASE
WHEN mmode.EndDateTime < ISNULL(processed_mmode.EndDateTime, '99991231') THEN mmode.EndDateTime
ELSE processed_mmode.EndDateTime
END
ELSE NULL
END
END
)
FROM #UpdatedMaintenanceMode mmode
LEFT JOIN StateProcessedMaintenanceMode processed_mmode ON (mmode.MaintenanceModeRowId = processed_mmode.MaintenanceModeRowId)
-- update processed mmode for the intervals that were updated
UPDATE processed_mmode
SET StartDateTime = mmode.StartDateTime
,EndDateTime = mmode.EndDateTime
,PlannedMaintenanceInd = mmode.PlannedMaintenanceInd
,MaintenanceModeDWLastModifiedDateTime = mmode.DWLastModifiedDateTime
,DWLastModifiedDateTime = GETUTCDATE()
FROM #UpdatedMaintenanceMode mmode
JOIN StateProcessedmaintenanceMode processed_mmode ON (mmode.MaintenanceModeRowId = processed_mmode.MaintenanceModeRowId)
-- add records to processed HSO for the outages that are new
INSERT StateProcessedMaintenanceMode (
MaintenanceModeRowId
,StartDateTime
,EndDateTime
,PlannedMaintenanceInd
,MaintenanceModeDWLastModifiedDateTime
)
SELECT
MaintenanceModeRowId
,StartDateTime
,EndDateTime
,PlannedMaintenanceInd
,DWLastModifiedDateTime
FROM #UpdatedMaintenanceMode mmode
WHERE (NOT EXISTS (SELECT * FROM StateProcessedMaintenanceMode WHERE MaintenanceModeRowId = mmode.MaintenanceModeRowId))
IF (@MinDate IS NOT NULL)
BEGIN
UPDATE ah
SET DirtyInd = 1
,DataLastReceivedDateTime = GETUTCDATE()
FROM StandardDatasetAggregationHistory ah
JOIN StandardDatasetAggregation a ON (ah.DatasetId = a.DatasetId AND ah.AggregationTypeId = a.AggregationTypeId)
WHERE (ah.DatasetId = @DatasetId)
AND (ah.AggregationDateTime > DATEADD(minute, -a.AggregationIntervalDurationMinutes, @MinDate))
END
UPDATE StateAggregationProcessing
SET LastProcessedMaintenanceModeModifiedDateTime = @NewLastProcessedMModeModifiedDateTime
WHERE (DatasetId = @DatasetId)
IF (@@ROWCOUNT = 0)
BEGIN
INSERT StateAggregationProcessing(DatasetId, LastProcessedMaintenanceModeModifiedDateTime)
VALUES (@DatasetId, @NewLastProcessedMModeModifiedDateTime)
END
COMMIT
-- mark existing aggregations as dirty when
-- new information about health service outage is received
BEGIN TRAN
SELECT @LastProcessedHsoModifiedDateTime = LastProcessedHealthServiceOutageModifiedDateTime
FROM StateAggregationProcessing
WHERE (DatasetId = @DatasetId)
IF (@LastProcessedHsoModifiedDateTime IS NULL)
BEGIN
SET @LastProcessedHsoModifiedDateTime = '20050101'
END
-- calculate the minimum date to
-- declare aggregations as dirty
-- build a list of all HSO records and invalidation
-- records touched since last processing up until [almost] now
CREATE TABLE #UpdatedHealthServiceOutage (
HealthServiceOutageRowId int NOT NULL PRIMARY KEY
,StartDateTime datetime NOT NULL
,EndDateTime datetime NULL
,ReasonCode tinyint NOT NULL
,DWLastModifiedDateTime datetime NOT NULL
,InvalidatedDateTime datetime NULL
)
INSERT #UpdatedHealthServiceOutage (
HealthServiceOutageRowId
,StartDateTime
,EndDateTime
,ReasonCode
,DWLastModifiedDateTime
,InvalidatedDateTime
)
SELECT
hso.HealthServiceOutageRowId
,hso.StartDateTime
,hso.EndDateTime
,hso.ReasonCode
,hso.DWLastModifiedDateTime
,sihso.DWCreatedDateTime
FROM vHealthServiceOutage hso
JOIN StateHealthServiceOutage shso ON (hso.ReasonCode = shso.ReasonCode)
LEFT JOIN StateInvalidatedHealthServiceOutage sihso ON (hso.HealthServiceOutageRowId = sihso.HealthServiceOutageRowId)
WHERE (hso.DWLastModifiedDateTime >= @LastProcessedHsoModifiedDateTime)
AND (hso.DWLastModifiedDateTime < @NewLastProcessedHsoModifiedDateTime)
UNION
SELECT
hso.HealthServiceOutageRowId
,hso.StartDateTime
,hso.EndDateTime
,hso.ReasonCode
,hso.DWLastModifiedDateTime
,sihso.DWCreatedDateTime
FROM vHealthServiceOutage hso
JOIN StateHealthServiceOutage shso ON (hso.ReasonCode = shso.ReasonCode)
JOIN StateInvalidatedHealthServiceOutage sihso ON (hso.HealthServiceOutageRowId = sihso.HealthServiceOutageRowId)
WHERE (sihso.DWCreatedDateTime >= @LastProcessedHsoModifiedDateTime)
AND (sihso.DWCreatedDateTime < @NewLastProcessedHsoModifiedDateTime)
SET @MinDate = NULL
SELECT @MinDate = MIN(
CASE
WHEN (processed_hso.HealthServiceOutageRowId IS NULL) -- this hso was never processed before
THEN
CASE
WHEN (uhso.InvalidatedDateTime IS NULL) -- not invalidated
THEN uhso.StartDateTime
ELSE -- this hso is already invalidated - do not process
NULL
END
ELSE -- this hso was seen before
CASE
WHEN (processed_hso.InvalidatedDateTime IS NOT NULL) -- already invalidated, does not count
THEN NULL
WHEN (uhso.InvalidatedDateTime IS NOT NULL) -- hso invalidated
THEN uhso.InvalidatedDateTime -- look at invalidated date time not Start date time for perf optimizations. Otherwise we throw aggregation process back all the time
WHEN (uhso.EndDateTime IS NOT NULL) -- end time update - pick up minimum of end times
AND (ISNULL(processed_hso.EndDateTime, '99991231') != uhso.EndDateTime)
THEN CASE
WHEN uhso.EndDateTime < ISNULL(processed_hso.EndDateTime, '99991231') THEN uhso.EndDateTime
ELSE processed_hso.EndDateTime
END
ELSE NULL
END
END
)
FROM #UpdatedHealthServiceOutage AS uhso
LEFT JOIN StateProcessedHealthServiceOutage processed_hso ON (uhso.HealthServiceOutageRowId = processed_hso.HealthServiceOutageRowId)
-- update processed HSO for the outages that were updated
UPDATE processed_hso
SET StartDateTime = hso.StartDateTime
,EndDateTime = hso.EndDateTime
,ReasonCode = hso.ReasonCode
,HealthServiceOutageDWLastModifiedDateTime = hso.DWLastModifiedDateTime
,InvalidatedDateTime = hso.InvalidatedDateTime
,DWLastModifiedDateTime = GETUTCDATE()
FROM #UpdatedHealthServiceOutage hso
JOIN StateProcessedHealthServiceOutage processed_hso ON (hso.HealthServiceOutageRowId = processed_hso.HealthServiceOutageRowId)
-- add records to processed HSO for the outages that are new
INSERT StateProcessedHealthServiceOutage (
HealthServiceOutageRowId
,StartDateTime
,EndDateTime
,ReasonCode
,HealthServiceOutageDWLastModifiedDateTime
,InvalidatedDateTime
)
SELECT
HealthServiceOutageRowId
,StartDateTime
,EndDateTime
,ReasonCode
,DWLastModifiedDateTime
,InvalidatedDateTime
FROM #UpdatedHealthServiceOutage hso
WHERE (NOT EXISTS (SELECT * FROM StateProcessedHealthServiceOutage WHERE HealthServiceOutageRowId = hso.HealthServiceOutageRowId))
-- groom invalidated Health Service Outage table
DELETE sihso
FROM StateInvalidatedHealthServiceOutage sihso
WHERE NOT EXISTS (SELECT *
FROM HealthServiceOutage hso
WHERE (sihso.HealthServiceOutageRowId = hso.HealthServiceOutageRowId)
)
-- groom processed Health Service Outage table
DELETE StateProcessedHealthServiceOutage
WHERE EndDateTime < DATEADD(month, -1, GETUTCDATE())
DROP TABLE #UpdatedHealthServiceOutage
IF (@MinDate IS NOT NULL)
BEGIN
UPDATE ah
SET DirtyInd = 1
,DataLastReceivedDateTime = GETUTCDATE()
FROM StandardDatasetAggregationHistory ah
JOIN StandardDatasetAggregation a ON (ah.DatasetId = a.DatasetId AND ah.AggregationTypeId = a.AggregationTypeId)
WHERE (ah.DatasetId = @DatasetId)
AND (ah.AggregationDateTime > DATEADD(minute, -a.AggregationIntervalDurationMinutes, @MinDate))
END
UPDATE StateAggregationProcessing
SET LastProcessedHealthServiceOutageModifiedDateTime = @NewLastProcessedHsoModifiedDateTime
WHERE (DatasetId = @DatasetId)
IF (@@ROWCOUNT = 0)
BEGIN
INSERT StateAggregationProcessing(DatasetId, LastProcessedHealthServiceOutageModifiedDateTime)
VALUES (@DatasetId, @NewLastProcessedHsoModifiedDateTime)
END
COMMIT
BEGIN TRAN
-- lock table map to ensure we insert into table opened for insertion
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_TableMap'
-- mark existing affected aggregations as dirty
SET @MinDate = NULL
SELECT @MinDate = MIN([DateTime])
FROM State.StateStage
WHERE (DatasetId = @DatasetId)
AND (InsertReadyInd > 0)
IF (@MinDate IS NOT NULL)
BEGIN
UPDATE ah
SET DirtyInd = 1
,DataLastReceivedDateTime = GETUTCDATE()
FROM StandardDatasetAggregationHistory ah
JOIN StandardDatasetAggregation a ON (ah.DatasetId = a.DatasetId AND ah.AggregationTypeId = a.AggregationTypeId)
WHERE (ah.DatasetId = @DatasetId)
AND (ah.AggregationDateTime > DATEADD(minute, -a.AggregationIntervalDurationMinutes, @MinDate))
END
-- make sure at least one interval of the lowest
-- aggregation type is not invalidated - if they all are
-- aggregation process may have problems on high volume
-- find lowest type aggregations we need to perform
DECLARE @LowestAggregationTypeId int
SELECT @LowestAggregationTypeId = MIN(AggregationTypeId)
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId > 0)
UPDATE StandardDatasetAggregationHistory
SET DirtyInd = 0
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @LowestAggregationTypeId)
AND (AggregationDateTime = (SELECT MIN(AggregationDateTime)
FROM StandardDatasetAggregationHistory
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @LowestAggregationTypeId)
)
)
-- remove rows from staging
DELETE State.StateStage WITH (TABLOCK)
WHERE (DatasetId = @DatasetId)
AND (InsertReadyInd > 0)
IF (@DebugLevel > 3)
BEGIN
SET @MessageText = 'Finished inserting state change events into ' + @InsertTableName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to process data in the state data set staging area. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
-- defrag State.StateStage table (its a heap) by creating and dropping clustered index
CREATE CLUSTERED INDEX IDX_StateStage_Temporary ON State.StateStage (DWCreatedDateTime)
GO
DROP INDEX IDX_StateStage_Temporary ON State.StateStage
GO
Home
CHS