IF NOT EXISTS (SELECT * FROM sys.schemas WHERE [name] = 'Alert')
BEGIN
EXECUTE('CREATE SCHEMA Alert')
END
GO
-- change schema ownership to allow index rebuild and reorganize
ALTER AUTHORIZATION ON SCHEMA::Alert 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
EXEC StandardDatasetAllocateStorage @DatasetId = '$Config/DatasetId$', @AggregationTypeId = 0
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
,MaxRowsToProcessPerTransactionCount
)
VALUES
(
'$Config/DatasetId$'
,'
DatasetId uniqueidentifier NOT NULL
,ManagementGroupGuid uniqueidentifier NOT NULL
,AlertGuid uniqueidentifier NOT NULL
,AlertProblemGuid uniqueidentifier NOT NULL
,ManagedEntityGuid uniqueidentifier NOT NULL
,AlertName nvarchar(256) NOT NULL
,AlertDescription nvarchar(max) NULL
,Severity tinyint NOT NULL
,Priority tinyint NOT NULL
,Category nvarchar(256) NOT NULL
,MonitorAlertInd bit NOT NULL
,WorkflowGuid uniqueidentifier NOT NULL
,RaisedDateTime datetime NOT NULL
,CreatedDateTime datetime NOT NULL
,ResolutionState tinyint NOT NULL
,Owner nvarchar(256) NULL
,TicketId nvarchar(256) NULL
,CustomField1 nvarchar(256) NULL
,CustomField2 nvarchar(256) NULL
,CustomField3 nvarchar(256) NULL
,CustomField4 nvarchar(256) NULL
,CustomField5 nvarchar(256) NULL
,CustomField6 nvarchar(256) NULL
,CustomField7 nvarchar(256) NULL
,CustomField8 nvarchar(256) NULL
,CustomField9 nvarchar(256) NULL
,CustomField10 nvarchar(256) NULL
,SiteName nvarchar(256) NULL
,AlertParams xml NULL
,ParameterHash uniqueidentifier NULL
,RepeatCount int NOT NULL
,AlertStringGuid uniqueidentifier NULL
,DBLastModifiedDateTime datetime NOT NULL
,DBLastModifiedByUserId nvarchar(256) NULL
,TfsWorkItemId nvarchar(256) NULL
,TfsWorkItemOwner nvarchar(256) NULL
,AlertStageRowId int NOT NULL IDENTITY(1, 1)
,AlertRowId int NULL
,TableGuid uniqueidentifier NULL
,ManagedEntityRowId int NULL
,WorkflowRowId int NULL
,InsertReadyInd AS (ISNULL(ManagedEntityRowId + WorkflowRowId, 0))
,DWCreatedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
'
,1
,10000
)
COMMIT
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AlertStage' AND TABLE_SCHEMA = 'Alert')
BEGIN
DECLARE @Statement nvarchar(max)
SELECT @Statement = 'CREATE TABLE Alert.AlertStage (' + StagingTableColumnDefinition + ')'
FROM StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$'
EXECUTE (@Statement)
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_AlertStage_AlertGuidDBLastModifiedDateTimeResolutionStateAlertStageRowId')
BEGIN
CREATE INDEX IX_AlertStage_AlertGuidDBLastModifiedDateTimeResolutionStateAlertStageRowId ON Alert.AlertStage (AlertGuid, DBLastModifiedDateTime, ResolutionState, AlertStageRowId)
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON Alert.AlertStage TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AlertStage2Process' AND TABLE_SCHEMA = 'Alert')
BEGIN
CREATE TABLE Alert.AlertStage2Process (
DatasetId uniqueidentifier NOT NULL
,ManagementGroupGuid uniqueidentifier NOT NULL
,AlertGuid uniqueidentifier NOT NULL
,AlertProblemGuid uniqueidentifier NOT NULL
,ManagedEntityGuid uniqueidentifier NOT NULL
,AlertName nvarchar(256) NOT NULL
,AlertDescription nvarchar(max) NULL
,Severity tinyint NOT NULL
,Priority tinyint NOT NULL
,Category nvarchar(256) NOT NULL
,MonitorAlertInd bit NOT NULL
,WorkflowGuid uniqueidentifier NOT NULL
,RaisedDateTime datetime NOT NULL
,CreatedDateTime datetime NOT NULL
,ResolutionState tinyint NOT NULL
,Owner nvarchar(256) NULL
,TicketId nvarchar(256) NULL
,CustomField1 nvarchar(256) NULL
,CustomField2 nvarchar(256) NULL
,CustomField3 nvarchar(256) NULL
,CustomField4 nvarchar(256) NULL
,CustomField5 nvarchar(256) NULL
,CustomField6 nvarchar(256) NULL
,CustomField7 nvarchar(256) NULL
,CustomField8 nvarchar(256) NULL
,CustomField9 nvarchar(256) NULL
,CustomField10 nvarchar(256) NULL
,SiteName nvarchar(256) NULL
,AlertParams xml NULL
,ParameterHash uniqueidentifier NULL
,RepeatCount int NOT NULL
,AlertStringGuid uniqueidentifier NULL
,DBLastModifiedDateTime datetime NOT NULL
,DBLastModifiedByUserId nvarchar(256) NULL
,TfsWorkItemId nvarchar(256) NULL
,TfsWorkItemOwner nvarchar(256) NULL
,AlertStageRowId int NOT NULL
,AlertRowId int NULL
,TableGuid uniqueidentifier NULL
,ManagedEntityRowId int NULL
,WorkflowRowId int NULL
,DWCreatedDateTime datetime NOT NULL
)
END
ELSE
BEGIN
-- Table already exists, ensure TFS columns exist
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'AlertStage2Process' AND TABLE_SCHEMA = 'Alert'
AND COLUMN_NAME = N'TfsWorkItemId')
ALTER TABLE [Alert].[AlertStage2Process] ADD [TfsWorkItemId] nvarchar(256) NULL
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'AlertStage2Process' AND TABLE_SCHEMA = 'Alert'
AND COLUMN_NAME = N'TfsWorkItemOwner')
ALTER TABLE [Alert].[AlertStage2Process] ADD [TfsWorkItemOwner] nvarchar(256) NULL
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'AlertGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AlertGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AlertGroom
@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
BEGIN TRAN
CREATE TABLE #AlertGroom (
AlertGuid uniqueidentifier NOT NULL
)
SELECT @MainTableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
SET @Statement = 'INSERT #AlertGroom (AlertGuid)'
+ ' SELECT TOP ' + CAST(@MaxRowsToGroom AS varchar(15)) + ' AlertGuid'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName)
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE (@Statement)
SET @StandardDatasetAggregationStorageRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 1)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetAggregationStorageRowId = StandardDatasetAggregationStorageRowId
,@TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 1)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
Order By StandardDatasetAggregationStorageRowId Asc
SET @Statement = 'DELETE d'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' d'
+ ' JOIN #AlertGroom a ON (d.AlertGuid = a.AlertGuid)'
EXECUTE (@Statement)
END
SET @Statement = 'DELETE d'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName) + ' d'
+ ' JOIN #AlertGroom a ON (d.AlertGuid = a.AlertGuid)'
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT
DROP TABLE #AlertGroom
COMMIT
-- groom staging of data for rejected MGs
DELETE Alert.AlertStage
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 Alert 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 = 'AlertProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AlertProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AlertProcessStaging
@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
@InsertTableName sysname
,@InsertTableGuid uniqueidentifier
,@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@MessageText nvarchar(max)
,@OperationDurationMs bigint
,@ExecResult int
,@DebugLevel int
,@LockResourceName sysname
,@TableGuid uniqueidentifier
,@SchemaName sysname
,@MaxRowsToProcessCount int
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- get max # of rows to process in one shot
SELECT @MaxRowsToProcessCount = MaxRowsToProcessPerTransactionCount
FROM StandardDatasetStagingArea
WHERE (DatasetId = @DatasetId)
IF (@MaxRowsToProcessCount IS NULL)
BEGIN
-- if max row count calue is not set default to 5000
SET @MaxRowsToProcessCount = 5000
END
-- delete old entries
DECLARE @MaxDataAgeDays int
SELECT @MaxDataAgeDays = MaxDataAgeDays
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
DELETE TOP (@MaxRowsToProcessCount)
FROM Alert.AlertStage
WHERE (DatasetId = @DatasetId)
AND (DWCreatedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE()))
-- update managed entity ids
UPDATE s
SET ManagedEntityRowId = me.ManagedEntityRowId
FROM Alert.AlertStage s
JOIN vManagementGroup mg ON (s.ManagementGroupGuid = mg.ManagementGroupGuid)
JOIN vManagedEntity me ON ((s.ManagedEntityGuid = me.ManagedEntityGuid) AND (mg.ManagementGroupRowId = me.ManagementGroupRowId))
WHERE (s.ManagedEntityRowId IS NULL)
-- update workflow ids
UPDATE s
SET WorkflowRowId = m.MonitorRowId
FROM Alert.AlertStage s
JOIN vMonitor m ON (s.WorkflowGuid = m.MonitorGuid)
WHERE (s.MonitorAlertInd = 1)
AND (s.WorkflowRowId IS NULL)
UPDATE s
SET WorkflowRowId = r.RuleRowId
FROM Alert.AlertStage s
JOIN vRule r ON (s.WorkflowGuid = r.RuleGuid)
WHERE (s.MonitorAlertInd = 0)
AND (s.WorkflowRowId IS NULL)
BEGIN TRAN
-- lock table map to ensure we insert into table opened for insertion
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_TableMap'
WHILE EXISTS (SELECT *
FROM Alert.AlertStage s
CROSS JOIN StandardDatasetTableMap m
WHERE (s.AlertRowId IS NULL)
AND (m.DatasetId = @DatasetId)
AND (m.AggregationTypeId = 0)
AND ((s.RaisedDateTime BETWEEN m.StartDateTime and m.EndDateTime) OR (m.InsertInd = 1))
AND (NOT EXISTS (SELECT * FROM @VisitedPartition WHERE (PartitionGuid = m.TableGuid)))
)
BEGIN
SELECT TOP 1
@TableGuid = m.TableGuid
FROM Alert.AlertStage s
CROSS JOIN StandardDatasetTableMap m
WHERE (s.AlertRowId IS NULL)
AND (m.DatasetId = @DatasetId)
AND (m.AggregationTypeId = 0)
AND (s.RaisedDateTime BETWEEN ISNULL(m.StartDateTime, '19000101') AND ISNULL(m.EndDateTime, '99991231'))
AND (NOT EXISTS (SELECT * FROM @VisitedPartition WHERE (PartitionGuid = m.TableGuid)))
ORDER BY m.StandardDatasetTableMapRowId
INSERT @VisitedPartition VALUES (@TableGuid)
SET @Statement =
'UPDATE s'
+ ' SET AlertRowId = a.AlertRowId'
+ ' ,TableGuid = ''' + CAST(@TableGuid AS varchar(50)) + ''''
+ ' FROM Alert.AlertStage s'
+ ' JOIN ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('Alert_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' a ON (s.AlertGuid = a.AlertGuid)'
+ ' WHERE (s.AlertRowId IS NULL)'
EXECUTE (@Statement)
END
-- check to see if AlertStage2Process table is not empty
DECLARE @AlertStage2ProcessRowCount int
DECLARE @SpaceUsedInfo TABLE (
[name] sysname
,[rows] int
,reserved nvarchar(100)
,data nvarchar(100)
,index_size nvarchar(100)
,unused nvarchar(100)
)
,AlertStageRowId
,AlertRowId
,TableGuid
,ManagedEntityRowId
,WorkflowRowId
,DWCreatedDateTime
,TfsWorkItemId
,TfsWorkItemOwner
FROM Alert.AlertStage s
WHERE (s.AlertStageRowId = (SELECT TOP 1 AlertStageRowId
FROM Alert.AlertStage
WHERE (AlertGuid = s.AlertGuid)
ORDER BY DBLastModifiedDateTime, ResolutionState, AlertStageRowId)
)
AND (s.InsertReadyInd > 0)
DELETE s
FROM Alert.AlertStage s
JOIN Alert.AlertStage2Process s2p ON (s2p.AlertStageRowId = s.AlertStageRowId)
COMMIT
END
-- work only with rows in processing table
IF (EXISTS (SELECT * FROM Alert.AlertStage2Process WHERE (AlertRowId IS NULL)))
BEGIN
-- we have new alerts in the staging area
-- process only the first "change" since there
-- may be multiple changes stored in staging at
-- the same time, if that's the case - we'll pick
-- up all other changes next time we process staging
SET @Statement =
'INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' ('
+ ' AlertGuid'
+ ' ,AlertProblemGuid'
+ ' ,ManagedEntityRowId'
+ ' ,AlertName'
+ ' ,AlertDescription'
+ ' ,Severity'
+ ' ,Priority'
+ ' ,Category'
+ ' ,WorkflowRowId'
+ ' ,MonitorAlertInd'
+ ' ,[DateTime]'
+ ' ,SiteName'
+ ' ,RepeatCount'
+ ' ,AlertStringGuid'
+ ' ,ParameterHash'
+ ' ,DBCreatedDateTime'
+ ' )'
+ ' SELECT'
+ ' s.AlertGuid'
+ ' ,s.AlertProblemGuid'
+ ' ,s.ManagedEntityRowId'
+ ' ,s.AlertName'
+ ' ,s.AlertDescription'
+ ' ,s.Severity'
+ ' ,s.Priority'
+ ' ,s.Category'
+ ' ,s.WorkflowRowId'
+ ' ,s.MonitorAlertInd'
+ ' ,s.RaisedDateTime'
+ ' ,s.SiteName'
+ ' ,s.RepeatCount'
+ ' ,NULLIF(s.AlertStringGuid, ''00000000-0000-0000-0000-000000000000'')'
+ ' ,NULLIF(s.ParameterHash, ''00000000-0000-0000-0000-000000000000'')'
+ ' ,s.CreatedDateTime'
+ ' FROM Alert.AlertStage2Process s'
+ ' WHERE (s.AlertRowId IS NULL)'
EXECUTE (@Statement)
INSERT #AlertParameter (
AlertGuid
,ParameterValue
)
SELECT
s.AlertGuid
,xml.ParameterValue.value('.', 'nvarchar(255)')
FROM Alert.AlertStage2Process s
CROSS APPLY s.AlertParams.nodes('AlertParameters/*') as xml(ParameterValue)
WHERE (s.AlertRowId IS NULL)
-- set parameter indexes
UPDATE ap
SET ap.ParameterIndex = ap.AlertParameterRowId - ap1.AlertParameterRowId + 1
FROM #AlertParameter ap
JOIN #AlertParameter ap1 ON (ap.AlertGuid = ap1.AlertGuid)
WHERE NOT EXISTS (SELECT *
FROM #AlertParameter
WHERE (AlertGuid = ap1.AlertGuid)
AND (AlertParameterRowId < ap1.AlertParameterRowId)
)
-- retrieve created alert ids
SET @Statement =
'UPDATE s'
+ ' SET AlertRowId = a.AlertRowId'
+ ' ,TableGuid = ''' + CAST(@InsertTableGuid AS varchar(50)) + ''''
+ ' FROM Alert.AlertStage2Process s'
+ ' JOIN ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' a ON (s.AlertGuid = a.AlertGuid)'
+ ' WHERE (s.AlertRowId IS NULL)'
EXECUTE (@Statement)
END
IF (EXISTS (SELECT * FROM Alert.AlertStage2Process WHERE (AlertRowId IS NOT NULL)))
BEGIN
-- process alert updates
-- build unique list of tables affected
DECLARE @AffectedPartition TABLE (
PartitionRowId int IDENTITY(1, 1)
,TableGuid uniqueidentifier
)
INSERT @AffectedPartition(TableGuid)
SELECT DISTINCT TableGuid
FROM Alert.AlertStage2Process
WHERE (AlertRowId IS NOT NULL)
DECLARE @PartitionRowId int
SET @PartitionRowId = 0
WHILE (EXISTS (SELECT * FROM @AffectedPartition WHERE (PartitionRowId > @PartitionRowId)))
BEGIN
SELECT TOP 1
@PartitionRowId = PartitionRowId
,@TableGuid = TableGuid
FROM @AffectedPartition
WHERE (PartitionRowId > @PartitionRowId)
-- update alert repeat count
SET @Statement =
'UPDATE a'
+ ' SET RepeatCount = s.RepeatCount'
+ ' ,DWLastModifiedDateTime = GETUTCDATE()'
+ ' FROM Alert.AlertStage2Process s'
+ ' JOIN ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('Alert_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' a'
+ ' ON (s.AlertRowId = a.AlertRowId)'
+ ' WHERE (s.RepeatCount > a.RepeatCount)'
EXECUTE (@Statement)
-- check which alerts have resolution state changed
CREATE TABLE #ResolutionState (
AlertStageRowId int NOT NULL
,AlertGuid uniqueidentifier NOT NULL
,LastResolutionState tinyint NULL
)
SET @Statement =
'INSERT #ResolutionState ('
+ ' AlertStageRowId'
+ ' ,AlertGuid'
+ ' ,LastResolutionState'
+ ')'
+ ' SELECT'
+ ' s.AlertStageRowId'
+ ' ,s.AlertGuid'
+ ' ,r.ResolutionState'
+ ' FROM Alert.AlertStage2Process s'
+ ' LEFT JOIN ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertResolutionState_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' r'
+ ' ON (s.AlertGuid = r.AlertGuid) AND (r.AlertResolutionStateRowId = (SELECT TOP 1 AlertResolutionStateRowId FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertResolutionState_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' WHERE (AlertGuid = s.AlertGuid) AND (StateSetDateTime <= s.DBLastModifiedDateTime) ORDER BY StateSetDateTime DESC, ResolutionState DESC))'
+ ' WHERE (s.AlertRowId IS NOT NULL)'
+ ' AND (s.TableGuid = ''' + CAST(@TableGuid AS varchar(50)) + ''')'
EXECUTE (@Statement)
-- insert resolution state changes
SET @Statement =
'INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertResolutionState_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' ('
+ ' AlertGuid'
+ ' ,ResolutionState'
+ ' ,TimeFromRaisedSeconds'
+ ' ,StateSetDateTime'
+ ' ,StateSetByUserId'
+ ' )'
+ ' SELECT'
+ ' s.AlertGuid'
+ ' ,s.ResolutionState'
+ ' ,CASE ' -- check to see if alert was raised way in the past/future
+ ' WHEN ISNULL(ABS(DATEDIFF(year, s.DBLastModifiedDateTime, s.RaisedDateTime)), 0) > 3 THEN 3*365*24*60*60 '
+ ' ELSE ISNULL(ABS(DATEDIFF(second, s.DBLastModifiedDateTime, s.RaisedDateTime)), 0) '
+ ' END '
+ ' ,s.DBLastModifiedDateTime'
+ ' ,s.DBLastModifiedByUserId'
+ ' FROM #ResolutionState r'
+ ' JOIN Alert.AlertStage2Process s ON (r.AlertStageRowId = s.AlertStageRowId)'
+ ' WHERE (s.ResolutionState <> r.LastResolutionState) OR (r.LastResolutionState IS NULL)'
EXECUTE (@Statement)
-- update all "time in state" columns since
-- we've added some state transitions potentially "in the middle"
SET @Statement =
'UPDATE ars'
+ ' SET TimeInStateSeconds = ISNULL(ABS(DATEDIFF(second, ars.StateSetDateTime, ars2.StateSetDateTime)), -1)'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertResolutionState_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' ars'
+ ' JOIN ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertResolutionState_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' ars2'
+ ' ON (ars.AlertGuid = ars2.AlertGuid)'
+ ' JOIN (SELECT DISTINCT AlertGuid = s.AlertGuid'
+ ' FROM #ResolutionState r'
+ ' JOIN Alert.AlertStage2Process s ON (r.AlertStageRowId = s.AlertStageRowId)'
+ ' WHERE (s.ResolutionState <> r.LastResolutionState) OR (r.LastResolutionState IS NULL)'
+ ' ) AS al ON (ars.AlertGuid = al.AlertGuid)'
+ ' WHERE (ars2.AlertResolutionStateRowId = (SELECT TOP 1 AlertResolutionStateRowId'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertResolutionState_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', ''))
+ ' WHERE (AlertGuid = ars.AlertGuid)'
+ ' AND (StateSetDateTime >= ars.StateSetDateTime)'
+ ' AND (AlertResolutionStateRowId <> ars.AlertResolutionStateRowId)'
+ ' ORDER BY StateSetDateTime, ResolutionState DESC'
+ ' ))'
EXECUTE (@Statement)
DROP TABLE #ResolutionState
-- check to see if alert properties were updated
CREATE TABLE #AlertDetail (
AlertStageRowId int NOT NULL
,DetailRecordExistedInd bit NOT NULL
,Owner nvarchar(256) NULL
,TicketId nvarchar(256) NULL
,CustomField1 nvarchar(256) NULL
,CustomField2 nvarchar(256) NULL
,CustomField3 nvarchar(256) NULL
,CustomField4 nvarchar(256) NULL
,CustomField5 nvarchar(256) NULL
,CustomField6 nvarchar(256) NULL
,CustomField7 nvarchar(256) NULL
,CustomField8 nvarchar(256) NULL
,CustomField9 nvarchar(256) NULL
,CustomField10 nvarchar(256) NULL
,TfsWorkItemId nvarchar(256) NULL
,TfsWorkItemOwner nvarchar(256) NULL
)
SET @Statement =
'INSERT #AlertDetail ('
+ ' AlertStageRowId'
+ ' ,DetailRecordExistedInd'
+ ' ,Owner'
+ ' ,TicketId'
+ ' ,CustomField1'
+ ' ,CustomField2'
+ ' ,CustomField3'
+ ' ,CustomField4'
+ ' ,CustomField5'
+ ' ,CustomField6'
+ ' ,CustomField7'
+ ' ,CustomField8'
+ ' ,CustomField9'
+ ' ,CustomField10'
+ ' ,TfsWorkItemId'
+ ' ,TfsWorkItemOwner'
+ ')'
+ ' SELECT'
+ ' s.AlertStageRowId'
+ ' ,CASE WHEN d.AlertGuid IS NULL THEN 0 ELSE 1 END'
+ ' ,d.Owner'
+ ' ,d.TicketId'
+ ' ,d.CustomField1'
+ ' ,d.CustomField2'
+ ' ,d.CustomField3'
+ ' ,d.CustomField4'
+ ' ,d.CustomField5'
+ ' ,d.CustomField6'
+ ' ,d.CustomField7'
+ ' ,d.CustomField8'
+ ' ,d.CustomField9'
+ ' ,d.CustomField10'
+ ' ,d.TfsWorkItemId'
+ ' ,d.TfsWorkItemOwner'
+ ' FROM Alert.AlertStage2Process s'
+ ' LEFT JOIN ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertDetail_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' d ON (s.AlertGuid = d.AlertGuid)'
+ ' WHERE (s.AlertRowId IS NOT NULL)'
+ ' AND (s.TableGuid = ''' + CAST(@TableGuid AS varchar(50)) + ''')'
+ ' AND (NOT EXISTS (SELECT * FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertDetail_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' WHERE (AlertGuid = s.AlertGuid) AND (DBLastModifiedDateTime <= s.DBLastModifiedDateTime) AND (AlertDetailRowId > d.AlertDetailRowId)))'
EXECUTE (@Statement)
-- mark affected table as non-optimized
UPDATE StandardDatasetTableMap
SET OptimizedInd = 0
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (TableGuid = @TableGuid)
AND (InsertInd = 0)
END
END
TRUNCATE TABLE Alert.AlertStage2Process
IF (@DebugLevel > 3)
BEGIN
SET @MessageText = 'Finished inserting alert(s) into ' + @InsertTableName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
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 = 'AlertGroom')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.AlertGroom')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'AlertInsert')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.AlertInsert')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'AlertProcessStaging')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.AlertProcessStaging')
END
-- drop tables
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AlertStage' AND TABLE_SCHEMA = 'Alert')
BEGIN
EXECUTE('DROP TABLE Alert.AlertStage')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AlertStage2Process' AND TABLE_SCHEMA = 'Alert')
BEGIN
EXECUTE('DROP TABLE Alert.AlertStage2Process')
END
-- delete std dataset related data
EXEC StandardDatasetDelete '$Config/DatasetId$'
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AlertStage2Process' AND TABLE_SCHEMA = 'Alert')
BEGIN
CREATE TABLE Alert.AlertStage2Process (
DatasetId uniqueidentifier NOT NULL
,ManagementGroupGuid uniqueidentifier NOT NULL
,AlertGuid uniqueidentifier NOT NULL
,AlertProblemGuid uniqueidentifier NOT NULL
,ManagedEntityGuid uniqueidentifier NOT NULL
,AlertName nvarchar(256) NOT NULL
,AlertDescription nvarchar(max) NULL
,Severity tinyint NOT NULL
,Priority tinyint NOT NULL
,Category nvarchar(256) NOT NULL
,MonitorAlertInd bit NOT NULL
,WorkflowGuid uniqueidentifier NOT NULL
,RaisedDateTime datetime NOT NULL
,CreatedDateTime datetime NOT NULL
,ResolutionState tinyint NOT NULL
,Owner nvarchar(256) NULL
,TicketId nvarchar(256) NULL
,CustomField1 nvarchar(256) NULL
,CustomField2 nvarchar(256) NULL
,CustomField3 nvarchar(256) NULL
,CustomField4 nvarchar(256) NULL
,CustomField5 nvarchar(256) NULL
,CustomField6 nvarchar(256) NULL
,CustomField7 nvarchar(256) NULL
,CustomField8 nvarchar(256) NULL
,CustomField9 nvarchar(256) NULL
,CustomField10 nvarchar(256) NULL
,SiteName nvarchar(256) NULL
,AlertParams xml NULL
,ParameterHash uniqueidentifier NULL
,RepeatCount int NOT NULL
,AlertStringGuid uniqueidentifier NULL
,DBLastModifiedDateTime datetime NOT NULL
,DBLastModifiedByUserId nvarchar(256) NULL
,TfsWorkItemId nvarchar(256) NULL
,TfsWorkItemOwner nvarchar(256) NULL
,AlertStageRowId int NOT NULL
,AlertRowId int NULL
,TableGuid uniqueidentifier NULL
,ManagedEntityRowId int NULL
,WorkflowRowId int NULL
,DWCreatedDateTime datetime NOT NULL
)
END
ELSE
BEGIN
-- Table already exists, ensure TFS columns exist
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'AlertStage2Process' AND TABLE_SCHEMA = 'Alert'
AND COLUMN_NAME = N'TfsWorkItemId')
ALTER TABLE [Alert].[AlertStage2Process] ADD [TfsWorkItemId] nvarchar(256) NULL
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'AlertStage2Process' AND TABLE_SCHEMA = 'Alert'
AND COLUMN_NAME = N'TfsWorkItemOwner')
ALTER TABLE [Alert].[AlertStage2Process] ADD [TfsWorkItemOwner] nvarchar(256) NULL
END
GO
-- Update Alert staging definition in 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
,MaxRowsToProcessPerTransactionCount
)
VALUES
(
'$Config/DatasetId$'
,'
DatasetId uniqueidentifier NOT NULL
,ManagementGroupGuid uniqueidentifier NOT NULL
,AlertGuid uniqueidentifier NOT NULL
,AlertProblemGuid uniqueidentifier NOT NULL
,ManagedEntityGuid uniqueidentifier NOT NULL
,AlertName nvarchar(256) NOT NULL
,AlertDescription nvarchar(max) NULL
,Severity tinyint NOT NULL
,Priority tinyint NOT NULL
,Category nvarchar(256) NOT NULL
,MonitorAlertInd bit NOT NULL
,WorkflowGuid uniqueidentifier NOT NULL
,RaisedDateTime datetime NOT NULL
,CreatedDateTime datetime NOT NULL
,ResolutionState tinyint NOT NULL
,Owner nvarchar(256) NULL
,TicketId nvarchar(256) NULL
,CustomField1 nvarchar(256) NULL
,CustomField2 nvarchar(256) NULL
,CustomField3 nvarchar(256) NULL
,CustomField4 nvarchar(256) NULL
,CustomField5 nvarchar(256) NULL
,CustomField6 nvarchar(256) NULL
,CustomField7 nvarchar(256) NULL
,CustomField8 nvarchar(256) NULL
,CustomField9 nvarchar(256) NULL
,CustomField10 nvarchar(256) NULL
,SiteName nvarchar(256) NULL
,AlertParams xml NULL
,ParameterHash uniqueidentifier NULL
,RepeatCount int NOT NULL
,AlertStringGuid uniqueidentifier NULL
,DBLastModifiedDateTime datetime NOT NULL
,DBLastModifiedByUserId nvarchar(256) NULL
,TfsWorkItemId nvarchar(256) NULL
,TfsWorkItemOwner nvarchar(256) NULL
,AlertStageRowId int NOT NULL IDENTITY(1, 1)
,AlertRowId int NULL
,TableGuid uniqueidentifier NULL
,ManagedEntityRowId int NULL
,WorkflowRowId int NULL
,InsertReadyInd AS (ISNULL(ManagedEntityRowId + WorkflowRowId, 0))
,DWCreatedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
'
,1
,10000
)
COMMIT
BEGIN TRAN
SET NOCOUNT ON
-- Need to place new columns above the standard columns, requires creation of a new table, copying data, then dropping old table
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AlertStage' AND TABLE_SCHEMA = 'Alert')
BEGIN
-- Create the new table
DECLARE @Statement nvarchar(max)
SELECT @Statement = 'CREATE TABLE Alert.Tmp_AlertStage (' + StagingTableColumnDefinition + ')'
FROM StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$'
EXECUTE (@Statement)
-- Copy over the data from the existing AlertStage
SET IDENTITY_INSERT Alert.Tmp_AlertStage ON
INSERT Alert.Tmp_AlertStage
(
DatasetId
,ManagementGroupGuid
,AlertGuid
,AlertProblemGuid
,ManagedEntityGuid
,AlertName
,AlertDescription
,Severity
,Priority
,Category
,MonitorAlertInd
,WorkflowGuid
,RaisedDateTime
,CreatedDateTime
,ResolutionState
,Owner
,TicketId
,CustomField1
,CustomField2
,CustomField3
,CustomField4
,CustomField5
,CustomField6
,CustomField7
,CustomField8
,CustomField9
,CustomField10
,SiteName
,AlertParams
,ParameterHash
,RepeatCount
,AlertStringGuid
,DBLastModifiedDateTime
,DBLastModifiedByUserId
,AlertStageRowId
,AlertRowId
,TableGuid
,ManagedEntityRowId
,WorkflowRowId
,DWCreatedDateTime
FROM Alert.AlertStage
SET IDENTITY_INSERT Alert.Tmp_AlertStage OFF
-- Remove the old table
DROP TABLE Alert.AlertStage
-- Rename the temp table to the real name
EXECUTE sp_rename N'Alert.Tmp_AlertStage', N'AlertStage', 'OBJECT'
-- Apply indexes and permissions
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_AlertStage_AlertGuidDBLastModifiedDateTimeResolutionStateAlertStageRowId')
BEGIN
CREATE INDEX IX_AlertStage_AlertGuidDBLastModifiedDateTimeResolutionStateAlertStageRowId ON Alert.AlertStage (AlertGuid, DBLastModifiedDateTime, ResolutionState, AlertStageRowId)
END
,CONSTRAINT [PK_AlertDetail_$Guid$] PRIMARY KEY CLUSTERED (AlertDetailRowId) ON $DataFileGroupName$
) ON $DataFileGroupName$
'
,CoverViewSelectClause =
'SELECT
AlertGuid
,Owner
,TicketId
,CustomField1
,CustomField2
,CustomField3
,CustomField4
,CustomField5
,CustomField6
,CustomField7
,CustomField8
,CustomField9
,CustomField10
,DBLastModifiedDateTime
,DBLastModifiedByUserId
,DWCreatedDateTime
,TfsWorkItemId
,TfsWorkItemOwner
'
FROM StandardDatasetAggregationStorage sdas
JOIN StandardDataset sd ON (sdas.DatasetId = sd.DatasetId)
WHERE (sd.DatasetId = '$Config/DatasetId$')
AND (sdas.TableTag = 'detail')
AND (sdas.DependentTableInd = 1)
GO
-- update all tables that were already created
DECLARE
@StandardDatasetTableMapRowId int
,@Statement nvarchar(max)
,@SchemaName sysname
,@TableNameSuffix sysname
,@BaseTableName sysname
,@FullTableName sysname
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap tm
WHERE (tm.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (tm.DatasetId = '$Config/DatasetId$')
AND (tm.InsertInd = 1)
)
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.InsertInd = 1)
AND (sdas.TableTag = 'detail')
AND (sdas.DependentTableInd = 1)
ORDER BY tm.StandardDatasetTableMapRowId
SET @FullTableName = @BaseTableName + '_' + @TableNameSuffix
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @FullTableName AND TABLE_SCHEMA = @SchemaName
AND COLUMN_NAME = N'TfsWorkItemId')
BEGIN
SET @Statement = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@FullTableName) + ' ADD TfsWorkItemId nvarchar(256) NULL'
EXECUTE (@Statement)
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @FullTableName AND TABLE_SCHEMA = @SchemaName
AND COLUMN_NAME = N'TfsWorkItemOwner')
BEGIN
SET @Statement = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@FullTableName) + ' ADD TfsWorkItemOwner nvarchar(256) NULL'
EXECUTE (@Statement)
END
END
GO
-- alter cover views
EXEC StandardDatasetBuildCoverView '$Config/DatasetId$', 0
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'AlertProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AlertProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AlertProcessStaging
@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
@InsertTableName sysname
,@InsertTableGuid uniqueidentifier
,@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@MessageText nvarchar(max)
,@OperationDurationMs bigint
,@ExecResult int
,@DebugLevel int
,@LockResourceName sysname
,@TableGuid uniqueidentifier
,@SchemaName sysname
,@MaxRowsToProcessCount int
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- get max # of rows to process in one shot
SELECT @MaxRowsToProcessCount = MaxRowsToProcessPerTransactionCount
FROM StandardDatasetStagingArea
WHERE (DatasetId = @DatasetId)
IF (@MaxRowsToProcessCount IS NULL)
BEGIN
-- if max row count calue is not set default to 5000
SET @MaxRowsToProcessCount = 5000
END
-- delete old entries
DECLARE @MaxDataAgeDays int
SELECT @MaxDataAgeDays = MaxDataAgeDays
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
DELETE TOP (@MaxRowsToProcessCount)
FROM Alert.AlertStage
WHERE (DatasetId = @DatasetId)
AND (DWCreatedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE()))
-- update managed entity ids
UPDATE s
SET ManagedEntityRowId = me.ManagedEntityRowId
FROM Alert.AlertStage s
JOIN vManagementGroup mg ON (s.ManagementGroupGuid = mg.ManagementGroupGuid)
JOIN vManagedEntity me ON ((s.ManagedEntityGuid = me.ManagedEntityGuid) AND (mg.ManagementGroupRowId = me.ManagementGroupRowId))
WHERE (s.ManagedEntityRowId IS NULL)
-- update workflow ids
UPDATE s
SET WorkflowRowId = m.MonitorRowId
FROM Alert.AlertStage s
JOIN vMonitor m ON (s.WorkflowGuid = m.MonitorGuid)
WHERE (s.MonitorAlertInd = 1)
AND (s.WorkflowRowId IS NULL)
UPDATE s
SET WorkflowRowId = r.RuleRowId
FROM Alert.AlertStage s
JOIN vRule r ON (s.WorkflowGuid = r.RuleGuid)
WHERE (s.MonitorAlertInd = 0)
AND (s.WorkflowRowId IS NULL)
BEGIN TRAN
-- lock table map to ensure we insert into table opened for insertion
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_TableMap'
WHILE EXISTS (SELECT *
FROM Alert.AlertStage s
CROSS JOIN StandardDatasetTableMap m
WHERE (s.AlertRowId IS NULL)
AND (m.DatasetId = @DatasetId)
AND (m.AggregationTypeId = 0)
AND ((s.RaisedDateTime BETWEEN m.StartDateTime and m.EndDateTime) OR (m.InsertInd = 1))
AND (NOT EXISTS (SELECT * FROM @VisitedPartition WHERE (PartitionGuid = m.TableGuid)))
)
BEGIN
SELECT TOP 1
@TableGuid = m.TableGuid
FROM Alert.AlertStage s
CROSS JOIN StandardDatasetTableMap m
WHERE (s.AlertRowId IS NULL)
AND (m.DatasetId = @DatasetId)
AND (m.AggregationTypeId = 0)
AND (s.RaisedDateTime BETWEEN ISNULL(m.StartDateTime, '19000101') AND ISNULL(m.EndDateTime, '99991231'))
AND (NOT EXISTS (SELECT * FROM @VisitedPartition WHERE (PartitionGuid = m.TableGuid)))
ORDER BY m.StandardDatasetTableMapRowId
INSERT @VisitedPartition VALUES (@TableGuid)
SET @Statement =
'UPDATE s'
+ ' SET AlertRowId = a.AlertRowId'
+ ' ,TableGuid = ''' + CAST(@TableGuid AS varchar(50)) + ''''
+ ' FROM Alert.AlertStage s'
+ ' JOIN ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('Alert_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' a ON (s.AlertGuid = a.AlertGuid)'
+ ' WHERE (s.AlertRowId IS NULL)'
EXECUTE (@Statement)
END
-- check to see if AlertStage2Process table is not empty
DECLARE @AlertStage2ProcessRowCount int
DECLARE @SpaceUsedInfo TABLE (
[name] sysname
,[rows] int
,reserved nvarchar(100)
,data nvarchar(100)
,index_size nvarchar(100)
,unused nvarchar(100)
)
,AlertStageRowId
,AlertRowId
,TableGuid
,ManagedEntityRowId
,WorkflowRowId
,DWCreatedDateTime
,TfsWorkItemId
,TfsWorkItemOwner
FROM Alert.AlertStage s
WHERE (s.AlertStageRowId = (SELECT TOP 1 AlertStageRowId
FROM Alert.AlertStage
WHERE (AlertGuid = s.AlertGuid)
ORDER BY DBLastModifiedDateTime, ResolutionState, AlertStageRowId)
)
AND (s.InsertReadyInd > 0)
DELETE s
FROM Alert.AlertStage s
JOIN Alert.AlertStage2Process s2p ON (s2p.AlertStageRowId = s.AlertStageRowId)
COMMIT
END
-- work only with rows in processing table
IF (EXISTS (SELECT * FROM Alert.AlertStage2Process WHERE (AlertRowId IS NULL)))
BEGIN
-- we have new alerts in the staging area
-- process only the first "change" since there
-- may be multiple changes stored in staging at
-- the same time, if that's the case - we'll pick
-- up all other changes next time we process staging
SET @Statement =
'INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' ('
+ ' AlertGuid'
+ ' ,AlertProblemGuid'
+ ' ,ManagedEntityRowId'
+ ' ,AlertName'
+ ' ,AlertDescription'
+ ' ,Severity'
+ ' ,Priority'
+ ' ,Category'
+ ' ,WorkflowRowId'
+ ' ,MonitorAlertInd'
+ ' ,[DateTime]'
+ ' ,SiteName'
+ ' ,RepeatCount'
+ ' ,AlertStringGuid'
+ ' ,ParameterHash'
+ ' ,DBCreatedDateTime'
+ ' )'
+ ' SELECT'
+ ' s.AlertGuid'
+ ' ,s.AlertProblemGuid'
+ ' ,s.ManagedEntityRowId'
+ ' ,s.AlertName'
+ ' ,s.AlertDescription'
+ ' ,s.Severity'
+ ' ,s.Priority'
+ ' ,s.Category'
+ ' ,s.WorkflowRowId'
+ ' ,s.MonitorAlertInd'
+ ' ,s.RaisedDateTime'
+ ' ,s.SiteName'
+ ' ,s.RepeatCount'
+ ' ,NULLIF(s.AlertStringGuid, ''00000000-0000-0000-0000-000000000000'')'
+ ' ,NULLIF(s.ParameterHash, ''00000000-0000-0000-0000-000000000000'')'
+ ' ,s.CreatedDateTime'
+ ' FROM Alert.AlertStage2Process s'
+ ' WHERE (s.AlertRowId IS NULL)'
EXECUTE (@Statement)
INSERT #AlertParameter (
AlertGuid
,ParameterValue
)
SELECT
s.AlertGuid
,xml.ParameterValue.value('.', 'nvarchar(255)')
FROM Alert.AlertStage2Process s
CROSS APPLY s.AlertParams.nodes('AlertParameters/*') as xml(ParameterValue)
WHERE (s.AlertRowId IS NULL)
-- set parameter indexes
UPDATE ap
SET ap.ParameterIndex = ap.AlertParameterRowId - ap1.AlertParameterRowId + 1
FROM #AlertParameter ap
JOIN #AlertParameter ap1 ON (ap.AlertGuid = ap1.AlertGuid)
WHERE NOT EXISTS (SELECT *
FROM #AlertParameter
WHERE (AlertGuid = ap1.AlertGuid)
AND (AlertParameterRowId < ap1.AlertParameterRowId)
)
-- retrieve created alert ids
SET @Statement =
'UPDATE s'
+ ' SET AlertRowId = a.AlertRowId'
+ ' ,TableGuid = ''' + CAST(@InsertTableGuid AS varchar(50)) + ''''
+ ' FROM Alert.AlertStage2Process s'
+ ' JOIN ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' a ON (s.AlertGuid = a.AlertGuid)'
+ ' WHERE (s.AlertRowId IS NULL)'
EXECUTE (@Statement)
END
IF (EXISTS (SELECT * FROM Alert.AlertStage2Process WHERE (AlertRowId IS NOT NULL)))
BEGIN
-- process alert updates
-- build unique list of tables affected
DECLARE @AffectedPartition TABLE (
PartitionRowId int IDENTITY(1, 1)
,TableGuid uniqueidentifier
)
INSERT @AffectedPartition(TableGuid)
SELECT DISTINCT TableGuid
FROM Alert.AlertStage2Process
WHERE (AlertRowId IS NOT NULL)
DECLARE @PartitionRowId int
SET @PartitionRowId = 0
WHILE (EXISTS (SELECT * FROM @AffectedPartition WHERE (PartitionRowId > @PartitionRowId)))
BEGIN
SELECT TOP 1
@PartitionRowId = PartitionRowId
,@TableGuid = TableGuid
FROM @AffectedPartition
WHERE (PartitionRowId > @PartitionRowId)
-- update alert repeat count
SET @Statement =
'UPDATE a'
+ ' SET RepeatCount = s.RepeatCount'
+ ' ,DWLastModifiedDateTime = GETUTCDATE()'
+ ' FROM Alert.AlertStage2Process s'
+ ' JOIN ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('Alert_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' a'
+ ' ON (s.AlertRowId = a.AlertRowId)'
+ ' WHERE (s.RepeatCount > a.RepeatCount)'
EXECUTE (@Statement)
-- check which alerts have resolution state changed
CREATE TABLE #ResolutionState (
AlertStageRowId int NOT NULL
,AlertGuid uniqueidentifier NOT NULL
,LastResolutionState tinyint NULL
)
SET @Statement =
'INSERT #ResolutionState ('
+ ' AlertStageRowId'
+ ' ,AlertGuid'
+ ' ,LastResolutionState'
+ ')'
+ ' SELECT'
+ ' s.AlertStageRowId'
+ ' ,s.AlertGuid'
+ ' ,r.ResolutionState'
+ ' FROM Alert.AlertStage2Process s'
+ ' LEFT JOIN ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertResolutionState_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' r'
+ ' ON (s.AlertGuid = r.AlertGuid) AND (r.AlertResolutionStateRowId = (SELECT TOP 1 AlertResolutionStateRowId FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertResolutionState_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' WHERE (AlertGuid = s.AlertGuid) AND (StateSetDateTime <= s.DBLastModifiedDateTime) ORDER BY StateSetDateTime DESC, ResolutionState DESC))'
+ ' WHERE (s.AlertRowId IS NOT NULL)'
+ ' AND (s.TableGuid = ''' + CAST(@TableGuid AS varchar(50)) + ''')'
EXECUTE (@Statement)
-- insert resolution state changes
SET @Statement =
'INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertResolutionState_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' ('
+ ' AlertGuid'
+ ' ,ResolutionState'
+ ' ,TimeFromRaisedSeconds'
+ ' ,StateSetDateTime'
+ ' ,StateSetByUserId'
+ ' )'
+ ' SELECT'
+ ' s.AlertGuid'
+ ' ,s.ResolutionState'
+ ' ,CASE ' -- check to see if alert was raised way in the past/future
+ ' WHEN ISNULL(ABS(DATEDIFF(year, s.DBLastModifiedDateTime, s.RaisedDateTime)), 0) > 3 THEN 3*365*24*60*60 '
+ ' ELSE ISNULL(ABS(DATEDIFF(second, s.DBLastModifiedDateTime, s.RaisedDateTime)), 0) '
+ ' END '
+ ' ,s.DBLastModifiedDateTime'
+ ' ,s.DBLastModifiedByUserId'
+ ' FROM #ResolutionState r'
+ ' JOIN Alert.AlertStage2Process s ON (r.AlertStageRowId = s.AlertStageRowId)'
+ ' WHERE (s.ResolutionState <> r.LastResolutionState) OR (r.LastResolutionState IS NULL)'
EXECUTE (@Statement)
-- update all "time in state" columns since
-- we've added some state transitions potentially "in the middle"
SET @Statement =
'UPDATE ars'
+ ' SET TimeInStateSeconds = ISNULL(ABS(DATEDIFF(second, ars.StateSetDateTime, ars2.StateSetDateTime)), -1)'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertResolutionState_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' ars'
+ ' JOIN ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertResolutionState_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' ars2'
+ ' ON (ars.AlertGuid = ars2.AlertGuid)'
+ ' JOIN (SELECT DISTINCT AlertGuid = s.AlertGuid'
+ ' FROM #ResolutionState r'
+ ' JOIN Alert.AlertStage2Process s ON (r.AlertStageRowId = s.AlertStageRowId)'
+ ' WHERE (s.ResolutionState <> r.LastResolutionState) OR (r.LastResolutionState IS NULL)'
+ ' ) AS al ON (ars.AlertGuid = al.AlertGuid)'
+ ' WHERE (ars2.AlertResolutionStateRowId = (SELECT TOP 1 AlertResolutionStateRowId'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertResolutionState_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', ''))
+ ' WHERE (AlertGuid = ars.AlertGuid)'
+ ' AND (StateSetDateTime >= ars.StateSetDateTime)'
+ ' AND (AlertResolutionStateRowId <> ars.AlertResolutionStateRowId)'
+ ' ORDER BY StateSetDateTime, ResolutionState DESC'
+ ' ))'
EXECUTE (@Statement)
DROP TABLE #ResolutionState
-- check to see if alert properties were updated
CREATE TABLE #AlertDetail (
AlertStageRowId int NOT NULL
,DetailRecordExistedInd bit NOT NULL
,Owner nvarchar(256) NULL
,TicketId nvarchar(256) NULL
,CustomField1 nvarchar(256) NULL
,CustomField2 nvarchar(256) NULL
,CustomField3 nvarchar(256) NULL
,CustomField4 nvarchar(256) NULL
,CustomField5 nvarchar(256) NULL
,CustomField6 nvarchar(256) NULL
,CustomField7 nvarchar(256) NULL
,CustomField8 nvarchar(256) NULL
,CustomField9 nvarchar(256) NULL
,CustomField10 nvarchar(256) NULL
,TfsWorkItemId nvarchar(256) NULL
,TfsWorkItemOwner nvarchar(256) NULL
)
SET @Statement =
'INSERT #AlertDetail ('
+ ' AlertStageRowId'
+ ' ,DetailRecordExistedInd'
+ ' ,Owner'
+ ' ,TicketId'
+ ' ,CustomField1'
+ ' ,CustomField2'
+ ' ,CustomField3'
+ ' ,CustomField4'
+ ' ,CustomField5'
+ ' ,CustomField6'
+ ' ,CustomField7'
+ ' ,CustomField8'
+ ' ,CustomField9'
+ ' ,CustomField10'
+ ' ,TfsWorkItemId'
+ ' ,TfsWorkItemOwner'
+ ')'
+ ' SELECT'
+ ' s.AlertStageRowId'
+ ' ,CASE WHEN d.AlertGuid IS NULL THEN 0 ELSE 1 END'
+ ' ,d.Owner'
+ ' ,d.TicketId'
+ ' ,d.CustomField1'
+ ' ,d.CustomField2'
+ ' ,d.CustomField3'
+ ' ,d.CustomField4'
+ ' ,d.CustomField5'
+ ' ,d.CustomField6'
+ ' ,d.CustomField7'
+ ' ,d.CustomField8'
+ ' ,d.CustomField9'
+ ' ,d.CustomField10'
+ ' ,d.TfsWorkItemId'
+ ' ,d.TfsWorkItemOwner'
+ ' FROM Alert.AlertStage2Process s'
+ ' LEFT JOIN ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertDetail_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' d ON (s.AlertGuid = d.AlertGuid)'
+ ' WHERE (s.AlertRowId IS NOT NULL)'
+ ' AND (s.TableGuid = ''' + CAST(@TableGuid AS varchar(50)) + ''')'
+ ' AND (NOT EXISTS (SELECT * FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('AlertDetail_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')) + ' WHERE (AlertGuid = s.AlertGuid) AND (DBLastModifiedDateTime <= s.DBLastModifiedDateTime) AND (AlertDetailRowId > d.AlertDetailRowId)))'
EXECUTE (@Statement)
-- mark affected table as non-optimized
UPDATE StandardDatasetTableMap
SET OptimizedInd = 0
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (TableGuid = @TableGuid)
AND (InsertInd = 0)
END
END
TRUNCATE TABLE Alert.AlertStage2Process
IF (@DebugLevel > 3)
BEGIN
SET @MessageText = 'Finished inserting alert(s) into ' + @InsertTableName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
Home
ENU