IF NOT EXISTS (SELECT * FROM StandardDataset WHERE DatasetId = '$Config/DatasetId$')
BEGIN
INSERT INTO StandardDataset (
DatasetId
,SchemaName
,DefaultAggregationIntervalCount
,RawInsertTableCount
,StagingProcessorStoredProcedureName
,BlockingMaintenanceDailyStartTime
,BlockingMaintenanceDurationMinutes
)
VALUES
(
'$Config/DatasetId$'
,'Exchange2010'
,$Config/DefaultAggregationIntervalCount$
,$Config/RawInsertTableCount$
,'Exchange2010.AlertImpactProcessStaging'
,'$Config/BlockingMaintenanceDailyStartTime$'
,$Config/BlockingMaintenanceDurationMinutes$
)
END
GO
--------------------------------------------------------------------------------------
-- Create the StandardDatasetAggregation for the AlertImpact dataset.
--------------------------------------------------------------------------------------
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)
IF NOT EXISTS (SELECT * FROM StandardDatasetAggregation WHERE DatasetId = '$Config/DatasetId$')
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 'Hourly' THEN 20
WHEN 'Daily' THEN 30
END
,CASE AggregationType
WHEN 'Raw' THEN 0
WHEN 'Hourly' THEN 60
WHEN 'Daily' THEN 24*60
END
,AggregationStartDelayMinutes
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'Exchange2010.AlertImpactAggregate'
END
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'Exchange2010.AlertImpactAggregationDelete'
END
,'Exchange2010.AlertImpactGroom'
,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 nvarchar(256)
,IndexFileGroupName nvarchar(256)
,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
--------------------------------------------------------------------------------------
-- Define the tables and views for the AlertImpact dataset.
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE [name] = 'Exchange2010')
BEGIN
EXECUTE('CREATE SCHEMA Exchange2010')
END
GO
-- change schema ownership to allow index rebuild and reorganize
ALTER AUTHORIZATION ON SCHEMA::Exchange2010 TO OpsMgrWriter
GO
-- change schema alter to allow domain table index rebuild
GRANT ALTER ON SCHEMA::dbo TO OpsMgrWriter
GO
GRANT ALTER ON SCHEMA::Exchange2010 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
-- Insert definition for Exchange2010.AlertImpactRawStagingV14 table into StandardDatasetStagingArea.
-- The table Exchange2010.AlertImpactRawStagingV14 table is a special table.
-- Unlike others within AlertImpact dataset - it is processed by AlertImpactProcessStaging sproc.
BEGIN TRAN
IF EXISTS (SELECT * FROM StandardDatasetStagingArea WHERE DatasetId = '$Config/DatasetId$')
BEGIN
DELETE StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$';
END
INSERT INTO StandardDatasetStagingArea
(
DatasetId
,StagingTableColumnDefinition
,MaxRowsToProcessPerTransactionCount
,BatchedProcessingSupportedInd
)
VALUES
(
'$Config/DatasetId$'
,
-- The 'LastAccessed' column will be updated on each run of AlertImpactProcessStaging sproc
-- to let the sproc to process different recordset on each run - a FIFO queue mode.
-- Without this, the sproc may each time process the same record set of previously skipped records
-- and thus never get to others until first ones a groomed.
'AlertImpactRawStagingRowId int NOT NULL IDENTITY(1, 1)
,LastAccessedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
,RaisedDateTime datetime NOT NULL
,Context nvarchar(max) NULL'
-- This defines how many records does AlertImpactProcessStaging sproc process per each execution (every 1 minute).
,50 -- Value based on experience in Datacenter Production environment (see bug 297340 for details).
,0
)
COMMIT
-- Create the Exchange2010.AlertImpactRawStagingV14 table.
BEGIN TRAN
-- Drop table if it exists.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactRawStagingV14]') AND type in (N'U'))
BEGIN
DROP TABLE [Exchange2010].[AlertImpactRawStagingV14];
END
GRANT SELECT, INSERT, UPDATE, DELETE ON Exchange2010.AlertImpactRawStagingV14 TO OpsMgrWriter
COMMIT
-- Inserting definition for table that will hold raw (non-aggregated) data.
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
VALUES
(
'$Config/DatasetId$'
,0
,'AlertImpactRawV14'
,0
,'
CREATE TABLE [Exchange2010].[AlertImpactRawV14_$Guid$]
(
AlertImpactRawRowId int NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,AlertGuid uniqueidentifier NOT NULL
,ManagedEntityRowId int NOT NULL
,OnSameEntity tinyint NOT NULL
,CONSTRAINT [PK_AlertImpactRawV14_$Guid$] PRIMARY KEY CLUSTERED (AlertImpactRawRowId) ON $DataFileGroupName$
) ON $DataFileGroupName$
'
,'
SELECT
AlertImpactRawRowId
,[DateTime]
,AlertGuid
,ManagedEntityRowId
,OnSameEntity
'
-- Do not partition table.
,NULL
,NULL
)
-- Inserting definition for table that will hold hourly-aggregated data.
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
VALUES
(
'$Config/DatasetId$'
,20
,'AlertImpactHourlyV14'
,0
,'
CREATE TABLE [Exchange2010].[AlertImpactHourlyV14_$Guid$]
(
AlertImpactHourlyRowId int NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,AlertGuid uniqueidentifier NOT NULL
,ManagedEntityRowId int NOT NULL
,OnSameEntity tinyint NOT NULL
,RawRedImpactInMilliseconds int NOT NULL
,RawYellowImpactInMilliseconds int NOT NULL
,CONSTRAINT [PK_AlertImpactHourlyV14_$Guid$] PRIMARY KEY CLUSTERED (AlertImpactHourlyRowId) ON $DataFileGroupName$
) ON $DataFileGroupName$
'
,'
SELECT
[DateTime]
,AlertImpactHourlyRowId
,AlertGuid
,ManagedEntityRowId
,OnSameEntity
,RawRedImpactInMilliseconds
,RawYellowImpactInMilliseconds
'
-- Do not partition table.
,NULL
,NULL
)
-- Inserting definition for table that will hold daily-aggregated data.
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
VALUES
(
'$Config/DatasetId$'
,30
,'AlertImpactDailyV14'
,0
,'
CREATE TABLE [Exchange2010].[AlertImpactDailyV14_$Guid$]
(
AlertImpactDailyRowId int NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,AlertGuid uniqueidentifier NOT NULL
,ManagedEntityRowId int NOT NULL
,OnSameEntity tinyint NOT NULL
,RawRedImpactInMilliseconds int NOT NULL
,RawYellowImpactInMilliseconds int NOT NULL
,CONSTRAINT [PK_AlertImpactDailyV14_$Guid$] PRIMARY KEY CLUSTERED (AlertImpactDailyRowId) ON $DataFileGroupName$
) ON $DataFileGroupName$
'
,'
SELECT
[DateTime]
,AlertImpactDailyRowId
,AlertGuid
,ManagedEntityRowId
,OnSameEntity
,RawRedImpactInMilliseconds
,RawYellowImpactInMilliseconds
'
-- Do not partition table.
,NULL
,NULL
)
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
--------------------------------------------------------------------------------------
-- Allocate standard storage for the AlertImpact dataset.
--------------------------------------------------------------------------------------
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 '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
--------------------------------------------------------------------------------------
-- This stored procedure is used for grooming the AlertImpact dataset.
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactGroom]') AND type in (N'P'))
BEGIN
EXECUTE ('CREATE PROCEDURE [Exchange2010].[AlertImpactGroom] AS RETURN 1')
END
GO
ALTER PROCEDURE Exchange2010.AlertImpactGroom
@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 AlertImpact 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
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 AlertImpact data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
------------------------------------------------------------------------------------------
-- This stored procedure is called by the WriteAction in the management pack.
-- It parses the correlation context in the alert and save the impact record in the AlertImpactRawV14 table.
------------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactInsert]') AND type in (N'P'))
BEGIN
EXECUTE ('CREATE PROCEDURE [Exchange2010].[AlertImpactInsert] AS RETURN 1')
END
GO
ALTER PROCEDURE Exchange2010.AlertImpactInsert
@ManagementGroupId uniqueidentifier
,@DatasetId uniqueidentifier
,@DataXml nvarchar(max)
AS
BEGIN
SET NOCOUNT ON
DECLARE @CheckExecResult int
,@ManagementGroupRowId int
-- get the management group
SELECT @ManagementGroupRowId = ManagementGroupRowId
FROM vManagementGroup
WHERE (ManagementGroupGuid = @ManagementGroupId) AND (WriterLoginName = SUSER_Name())
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
,@ResultingState int
SET @ErrorInd = 0
SET @ResultingState = 1
DECLARE
@ExecResult int
,@XmlDocHandle int
BEGIN TRY
-- Add it to Exchange2010.AlertImpactRawStagingV14
-- We may not find the alert in the Alert table because the alert is first saved
-- in the AlertImpactRawStagingV14 table and there is a separate process to move it to the
-- Alert table. Therefore, we have to move the parsing of DataXml in to the
-- scheduled aggregation function.
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @DataXml
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'Exchange2010_AlertImpactInsert', @ExecResult)
INSERT INTO Exchange2010.AlertImpactRawStagingV14
SELECT GETUTCDATE(), GETUTCDATE(), CONVERT(nvarchar(max), Context)
FROM OPENXML(@XmlDocHandle, '//CorrelatedContext', 2) WITH (
Context xml '@mp:xmltext'
)
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, @ResultingState
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON Exchange2010.AlertImpactInsert TO OpsMgrWriter
GO
----------------------------------------------------------------------------------------------------------
-- This stored procedure moves data from Exchange2010.AlertImpactRawStagingV14
-- into Exchange2010.AlertImpactRawV14 when it finds matched data in SCOM's Alert dataset.
--
-- By default this stored procedure is called by SCOM every minute. If one instance of execution takes
-- more than 1 minute then SCOM calls the next instance immediately after the first one finishes or times out.
-- The default time out is 5 minutes. Therefore the sproc should limit the number of records to be processed
-- per execution to stay within 5 minute interval of execution.
----------------------------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactProcessStaging]') AND type in (N'P'))
BEGIN
EXECUTE ('CREATE PROCEDURE [Exchange2010].[AlertImpactProcessStaging] AS RETURN 1')
END
GO
ALTER PROCEDURE Exchange2010.AlertImpactProcessStaging
@DatasetId uniqueidentifier
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
DECLARE
-- Configuration variables.
-- Maximum age of data to keep in table AlertImpactRawStagingV14.
@MaxDataAgeDays int
-- Maximum number of rows to process per single run of this stored procedure.
,@MaxRowsToProcess int
-- Diagnostic variables.
,@DebugLevel int
,@DebugMessage nvarchar(4000)
,@OperationStartedDateTime datetime
,@OperationDurationMs bigint
,@RowsDeleted int
,@CountTotalProcessedRecords int
-- Number of records successfully processed and submitted into AlertImpactRawV14 table.
-- They will be deleted from AlertImpactRawStagingV14
,@CountSubmittedRecords int
-- Number of records for which there no match was found in Alert table.
-- They will be kept in AlertImpactRawStagingV14 for a defined period of time.
,@CountSkippedRecords int
-- Number of records with invalid content in 'Context' column.
-- They will be deleted from AlertImpactRawStagingV14.
,@CountInvalidRecords int
-- Processing variables.
,@AlertImpactRawStagingRowId int
,@AlertImpactRawStagingDateTime datetime
,@DataXml nvarchar(max)
,@RCAlertGuid uniqueidentifier
,@RCManagedEntityRowId int
,@RCComponentName nvarchar(max)
,@AlertResolutionState tinyint
,@AlertRaisedDateTime datetime
,@AlertStateSetDateTime datetime
,@ProcessAlert bit
-- Temorary table to hold the context info for each alert from Exchange2010.AlertImpactRawStagingV14.
DECLARE @Context TABLE
(
EntityName nvarchar(256) NOT NULL
,ProblemId nvarchar(256) NULL -- Only need this value for the root cause entity
,RootCauseInd bit NOT NULL -- Whether the entity is the root cause entity
);
-- Variables for processing data into @Context.
DECLARE
@StartIndex bigint
,@Index bigint
,@Length bigint
,@EntityName nvarchar(256)
,@ProblemId nvarchar(256)
BEGIN TRY
-- Get the debug level for AlertImpact dataset.
SELECT @DebugLevel = DebugLevel FROM dbo.StandardDataset WHERE DatasetId = @DatasetId;
--
-- Groom the table AlertImpactRawStagingV14.
--
-- Get the grooming settings for AlertImpactRawV14 table.
SELECT @MaxDataAgeDays = MaxDataAgeDays
FROM dbo.StandardDatasetAggregation WHERE DatasetId = @DatasetId AND AggregationTypeId = 0;
-- Delete the outdated records using the same grooming settings as for AlertImpactRawV14 table.
DELETE FROM Exchange2010.AlertImpactRawStagingV14 WHERE RaisedDateTime < DATEADD(day, - @MaxDataAgeDays, GETUTCDATE());
SET @RowsDeleted = @@ROWCOUNT;
IF((@DebugLevel > 2) AND (@RowsDeleted > 0))
-- Log a debug message.
BEGIN
SET @DebugMessage = 'Groomed table Exchange2010.AlertImpactRawStagingV14 by ' + CAST(@RowsDeleted as varchar(16)) + ' records.';
--
-- Perform stage processing.
--
-- Get the configuration value for stage processing.
SELECT @MaxRowsToProcess = MaxRowsToProcessPerTransactionCount
FROM dbo.StandardDatasetStagingArea
WHERE DatasetId = @DatasetId;
-- Temporary table to hold the records to be processed within single instance of execution of this sproc.
CREATE TABLE #AlertImpactRawStagingBatch
(
AlertImpactRawStagingRowId int NOT NULL
,RaisedDateTime datetime NOT NULL
,Context nvarchar(max) NULL
,DeleteFlag bit NOT NULL DEFAULT(0) -- marks the records to be deleted from original table.
);
-- Select only the earliest @MaxRowsToProcess records from Exchange2010.AlertImpactRawStagingV14 for processing.
-- This way the processing is balanced in order not to let this stored procedure to time out.
INSERT INTO #AlertImpactRawStagingBatch(AlertImpactRawStagingRowId, RaisedDateTime, Context)
SELECT TOP (@MaxRowsToProcess) AlertImpactRawStagingRowId, RaisedDateTime, Context
FROM Exchange2010.AlertImpactRawStagingV14
ORDER BY LastAccessedDateTime; -- FIFO queue mode - get the earliest [of possibly processed] records.
-- Initialize variables.
SET @AlertImpactRawStagingRowId = -1
SET @CountTotalProcessedRecords = 0;
SET @CountSubmittedRecords = 0;
SET @CountSkippedRecords = 0;
SET @CountInvalidRecords = 0;
-- Parse the records in the staging table and update AlertImpactRawV14.
-- The new alerts go to the AlertImpactRawStagingV14 table from Correlation Engine first,
-- and then appear to the Alert table in about 1 minute.
WHILE EXISTS(
SELECT TOP 1 AlertImpactRawStagingRowId
FROM #AlertImpactRawStagingBatch
WHERE AlertImpactRawStagingRowId > @AlertImpactRawStagingRowId
)
BEGIN
SELECT TOP 1 @AlertImpactRawStagingRowId = AlertImpactRawStagingRowId
,@AlertImpactRawStagingDateTime = RaisedDateTime
,@DataXml = Context
FROM #AlertImpactRawStagingBatch AIRS
WHERE AIRS.AlertImpactRawStagingRowId > @AlertImpactRawStagingRowId AND
NOT EXISTS
(SELECT AlertImpactRawStagingRowId
FROM #AlertImpactRawStagingBatch
WHERE AlertImpactRawStagingRowId > @AlertImpactRawStagingRowId AND
AlertImpactRawStagingRowId < AIRS.AlertImpactRawStagingRowId);
-- Process the alert's context and insert the parsed data into @Context.
-- Using string functions to do the parsing instead of SQL XML as it is
-- faster and uses much less memory to avoid the out-of-memory failure.
DELETE FROM @Context;
SET @StartIndex = 0;
-- Bug 229258. Check existence of required tags.
-- If either of them is not present - ignore & delete this record because it is useless.
IF(0 = CHARINDEX('<EntityName>', @DataXml) OR
0 = CHARINDEX('<ProblemId>', @DataXml))
BEGIN
UPDATE #AlertImpactRawStagingBatch
SET DeleteFlag = 1
WHERE AlertImpactRawStagingRowId = @AlertImpactRawStagingRowId;
SET @CountInvalidRecords = @CountInvalidRecords + 1;
SET @CountTotalProcessedRecords = @CountTotalProcessedRecords + 1;
-- Insert the root cause entity into @Context
INSERT INTO @Context VALUES (@EntityName, @ProblemId, 1);
-- Now process each impacted entity and add to @Context
SET @StartIndex = @Index;
WHILE (CHARINDEX('<EntityName>', @DataXml, @StartIndex) > 0)
BEGIN
SELECT @Index = (CHARINDEX('<EntityName>', @DataXml, @StartIndex) + 12);
SELECT @Length = (CHARINDEX('</EntityName>', @DataXml, @Index) - @Index);
SELECT @EntityName = SUBSTRING(@DataXml, @Index, @Length);
-- Insert the impacted entity into @Context
INSERT INTO @Context VALUES (@EntityName, NULL, 0);
SET @StartIndex = @Index;
END
-- Set the default target alert values to NULL
SET @RCAlertGuid = NULL;
SET @RCManagedEntityRowId = NULL;
SET @AlertResolutionState = NULL;
SET @AlertRaisedDateTime = NULL;
SET @AlertStateSetDateTime = NULL;
-- By default, don't process the alert unless the condition is valid.
SET @ProcessAlert = 0;
-- Find the root cause alertGuid and ManagedEntity
SELECT TOP 1
@RCAlertGuid = a.AlertGuid
,@RCManagedEntityRowId = a.ManagedEntityRowId
,@RCComponentName =
CASE
WHEN me.FullName LIKE 'Microsoft.Exchange.2010.Mailbox%' THEN 'Microsoft.Exchange.2010.Mailbox%'
WHEN me.FullName LIKE 'Microsoft.Exchange.2010.ClientAccess%' THEN 'Microsoft.Exchange.2010.ClientAccess%'
WHEN me.FullName LIKE 'Microsoft.Exchange.2010.Hub%' THEN 'Microsoft.Exchange.2010.Hub%'
WHEN me.FullName LIKE 'Microsoft.Exchange.2010.Edge%' THEN 'Microsoft.Exchange.2010.Edge%'
WHEN me.FullName LIKE 'Microsoft.Exchange.2010.CommonTransport%' THEN 'Microsoft.Exchange.2010.CommonTransport%'
WHEN me.FullName LIKE 'Microsoft.Exchange.2010.UnifiedMessaging%' THEN 'Microsoft.Exchange.2010.UnifiedMessaging%'
WHEN me.FullName LIKE 'Microsoft.Exchange.2010.CommonRemotePowerShell%' THEN 'Microsoft.Exchange.2010.CommonRemotePowerShell%'
ELSE me.FullName
END
,@AlertResolutionState = ars.ResolutionState
,@AlertRaisedDateTime = a.RaisedDateTime
,@AlertStateSetDateTime = ars.StateSetDateTime
FROM @Context as rc
INNER JOIN Alert.vAlertDetail AS ad
ON rc.ProblemId = ad.CustomField10
INNER JOIN Alert.vAlert AS a
ON ad.AlertGuid = a.AlertGuid
INNER JOIN Alert.vAlertResolutionState AS ars
ON a.AlertGuid = ars.AlertGuid
INNER JOIN vManagedEntity AS me
ON a.ManagedEntityRowId = me.ManagedEntityRowId
AND me.DisplayName = rc.EntityName
WHERE rc.RootCauseInd = 1
ORDER BY ars.StateSetDateTime DESC;
-- Check if the alert from Exchange2010.AlertImpactRawStagingV14 was matched to an alert stored in SCOM's Alert dataset.
-- And then process the alert according to its resolution state.
IF ((@AlertResolutionState IS NOT NULL) AND (@AlertResolutionState < 255))
BEGIN
-- Found active alert.
-- We will process and delete this alert from Exchange2010.AlertImpactRawStagingV14.
SET @ProcessAlert = 1;
END
ELSE
BEGIN
-- Found no active alert, but check if there is a resolved alert.
--
IF (((@AlertResolutionState IS NOT NULL) AND (@AlertResolutionState = 255)) AND
(@AlertStateSetDateTime IS NOT NULL))
BEGIN
IF (@AlertStateSetDateTime >= @AlertImpactRawStagingDateTime)
BEGIN
-- Found resolved alert with: @AlertStateSetDateTime >= @AlertImpactRawStagingDateTime
-- This implies that the alert has already been resolved.
-- We shall proces and delete this alert from Exchange2010.AlertImpactRawStagingV14.
--
SET @ProcessAlert = 1;
END
ELSE
BEGIN
-- Found an old resolved alert with: @AlertStateSetDateTime < @AlertImpactRawStagingDateTime
-- This implies that the alert has not been moved to the alert table yet.
-- We will process it on the next aggregation schedule.
--
SET @ProcessAlert = 0;
END
END
ELSE
BEGIN
-- Did not find any alert.
-- This implies that the alert has not been moved to the alert table yet.
-- We will process it on the next aggregation schedule.
--
SET @ProcessAlert = 0;
END
END
-- Process the alert by adding it to Exchange2010.vAlertImpactRawV14.
IF (1 = @ProcessAlert)
BEGIN
-- Add it to Exchange2010.vAlertImpactRawV14
-- Note: the WriteAction will be called when the Alert is updated. Make sure the entry is not already there.
IF NOT EXISTS (
SELECT *
FROM Exchange2010.vAlertImpactRawV14 AIR
WHERE AIR.AlertGuid = @RCAlertGuid AND AIR.ManagedEntityRowId = @RCManagedEntityRowId)
BEGIN
INSERT INTO Exchange2010.vAlertImpactRawV14 ([DateTime], AlertGuid, ManagedEntityRowId, OnSameEntity)
VALUES ( @AlertImpactRawStagingDateTime, @RCAlertGuid, @RCManagedEntityRowId, 1);
END
-- Add the other impacted entities to Exchange2010.vAlertImpactRawV14
INSERT Exchange2010.vAlertImpactRawV14
([DateTime], AlertGuid, ManagedEntityRowId, OnSameEntity)
SELECT @AlertImpactRawStagingDateTime, @RCAlertGuid, me.ManagedEntityRowId,
CASE WHEN me.FullName LIKE @RCComponentName THEN 1 ELSE 0 END
FROM @Context AS e
INNER JOIN vManagedEntity AS me
ON me.DisplayName = e.EntityName
WHERE e.RootCauseInd = 0
AND NOT EXISTS (-- Note: the WriteAction will be called when the Alert is updated. Make sure the entry is not already there.
SELECT *
FROM Exchange2010.vAlertImpactRawV14 AIR
WHERE AIR.AlertGuid = @RCAlertGuid AND AIR.ManagedEntityRowId = me.ManagedEntityRowId)
GROUP BY e.EntityName, me.ManagedEntityRowId, me.FullName;
UPDATE #AlertImpactRawStagingBatch
SET DeleteFlag = 1
WHERE AlertImpactRawStagingRowId = @AlertImpactRawStagingRowId;
SET @CountSubmittedRecords = @CountSubmittedRecords + 1;
END
ELSE -- (0 = @ProcessAlert) otherwise keep the record because alert may have not appeared in the MOM's alert dataset yet.
BEGIN
SET @CountSkippedRecords = @CountSkippedRecords + 1;
END
SET @CountTotalProcessedRecords = @CountTotalProcessedRecords + 1;
END -- end of loop.
-- Delete only those records from original table that were makred for deletion in batch table.
-- Deletion from original table is performed here in a single transaction to minimize
-- lock impact to that table.
DELETE FROM Exchange2010.AlertImpactRawStagingV14
FROM Exchange2010.AlertImpactRawStagingV14 AS airs -- Second FROM clause is used for filtering the records to be deleted.
INNER JOIN #AlertImpactRawStagingBatch AS batch ON airs.AlertImpactRawStagingRowId = batch.AlertImpactRawStagingRowId
WHERE batch.DeleteFlag = 1;
-- Update the 'LastAccessedDateTime' column for processed records to allow this sproc
-- to process another record set on next execution (FIFO queue mode).
UPDATE Exchange2010.AlertImpactRawStagingV14
SET LastAccessedDateTime = GETUTCDATE()
FROM Exchange2010.AlertImpactRawStagingV14 AS airs -- Second FROM clause is used for filtering the records to be updated.
INNER JOIN #AlertImpactRawStagingBatch AS batch ON airs.AlertImpactRawStagingRowId = batch.AlertImpactRawStagingRowId
WHERE batch.DeleteFlag = 0;
-- Drop the temporary table.
DROP TABLE #AlertImpactRawStagingBatch;
-- Log a debug message.
IF (@DebugLevel > 2)
BEGIN
SET @OperationDurationMs = DATEDIFF(ms, @OperationStartedDateTime, GETDATE());
SET @DebugMessage = 'Finished stage processing of Exchange2010.AlertImpactRawStagingV14 table. ' +
'Total processed records: ' + CAST(@CountTotalProcessedRecords AS varchar(16)) + '.';
IF (@CountTotalProcessedRecords > 0)
BEGIN
SET @DebugMessage = @DebugMessage +
' (Submitted: ' + CAST(@CountSubmittedRecords AS varchar(16)) +
'; Skipped: ' + CAST(@CountSkippedRecords AS varchar(16)) +
'; Invalid: ' + CAST(@CountInvalidRecords AS varchar(16)) + ').';
END
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to process staging data for AlertImpact 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
----------------------------------------------------------------------------------------------------------
--- This stored procedure aggregates the alert impact data and saves the results in the hourly or daily table
----------------------------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactAggregate]') AND type in (N'P'))
BEGIN
EXECUTE ('CREATE PROCEDURE [Exchange2010].[AlertImpactAggregate] AS RETURN 1')
END
GO
ALTER PROCEDURE Exchange2010.AlertImpactAggregate
@DatasetId uniqueidentifier
,@AggregationTypeId int -- aggregration type: e.g. 20 = hourly
,@InsertTableGuid varchar(256) -- GUID of the aggregation table
,@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
,@IntervalStartDateTimeStr nvarchar(20)
,@IntervalEndDateTimeStr nvarchar(20)
,@AggregationCoverViewName sysname
,@InsertTableName sysname
,@DebugMessage nvarchar(4000)
,@AggregationStartDateTime datetime
,@LowerAggregationTypeId int
,@LowerAggregationIntervalMinutes int
,@NonDirtyLowerAggregationCount int
,@LowerAggregationCoverViewName sysname
,@UseAggregatesToBuildStartOfIntervalStateInd bit
,@SchemaName sysname
,@InsertTableFullName nvarchar (256)
SET @IntervalStartDateTimeStr = CONVERT(varchar(20), @IntervalStartDateTime, 120)
SET @IntervalEndDateTimeStr = CONVERT(varchar(20), @IntervalEndDateTime, 120)
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)
SET @InsertTableFullName = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName)
-- 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 '
+ @IntervalStartDateTimeStr + 'UTC to '
+ @IntervalEndDateTimeStr + '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)
EXECUTE (@Statement)
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 '
+ @IntervalStartDateTimeStr + 'UTC to '
+ @IntervalEndDateTimeStr + 'UTC. Using raw data'
SET @AggregationStartDateTime = GETUTCDATE()
CREATE TABLE #ImpactTemp(
AlertGuid uniqueidentifier
,Severity tinyint
,ManagedEntityRowId int
,OnSameEntity tinyint
,StartDateTime datetime
,EndDateTime datetime
,RawRedImpactInMilliseconds int
,RawYellowImpactInMilliseconds int
)
INSERT INTO #ImpactTemp (
AlertGuid
,Severity
,ManagedEntityRowId
,OnSameEntity
,StartDateTime
,EndDateTime
)
SELECT
AIR.AlertGuid
,A.Severity
,AIR.ManagedEntityRowId
,AIR.OnSameEntity
,CASE WHEN A.RaisedDateTime < @IntervalStartDateTime then @IntervalStartDateTime ELSE A.RaisedDateTime END
,CASE WHEN AR.StateSetDateTime IS NULL then @IntervalEndDateTime
WHEN AR.StateSetDateTime > @IntervalEndDateTime then @IntervalEndDateTime ELSE AR.StateSetDateTime END
FROM Exchange2010.vAlertImpactRawV14 AIR
JOIN Alert.vAlert A
ON A.AlertGuid = AIR.AlertGuid
LEFT JOIN Alert.vAlertResolutionState AR -- LEFT JOIN vAlertResolutionState to get the resolution time if the alert is resolved.
ON A.AlertGuid = AR.AlertGuid
AND AR.ResolutionState = 255
WHERE
A.RaisedDateTime < @IntervalEndDateTime -- filter out the alerts that happened after the end of the period
AND
(AR.StateSetDateTime IS NULL OR
AR.StateSetDateTime > @IntervalStartDateTime) -- filter out the alerts that was closed before the beginning of the period
UPDATE #ImpactTemp
SET RawRedImpactInMilliseconds = CASE WHEN Severity = 2 THEN DATEDIFF(millisecond, StartDateTime, EndDateTime) ELSE 0 END
, RawYellowImpactInMilliseconds = CASE WHEN Severity = 1 THEN DATEDIFF(millisecond, StartDateTime, EndDateTime) ELSE 0 END
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished aggregation of type ' + CAST(@AggregationTypeId AS varchar) + ' for interval from '
+ @IntervalStartDateTimeStr + 'UTC to '
+ @IntervalEndDateTimeStr + 'UTC'
DECLARE @OperationDurationMs bigint
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @AggregationStartDateTime))
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
------------------------------------------------------------------------------
-- This stored procedure is used for deleting the specified aggregation record.
------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactAggregationDelete]') AND type in (N'P'))
BEGIN
EXECUTE ('CREATE PROCEDURE [Exchange2010].[AlertImpactAggregationDelete] AS RETURN 1')
END
GO
ALTER PROCEDURE Exchange2010.AlertImpactAggregationDelete
@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 AlertImpact 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 AlertImpact data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
</Install> <Uninstall>
-- drop SPs
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactGroom]') AND type in (N'P'))
BEGIN
EXECUTE ('DROP PROCEDURE [Exchange2010].[AlertImpactGroom]')
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactInsert]') AND type in (N'P'))
BEGIN
EXECUTE ('DROP PROCEDURE [Exchange2010].[AlertImpactInsert]')
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactProcessStaging]') AND type in (N'P'))
BEGIN
EXECUTE ('DROP PROCEDURE [Exchange2010].[AlertImpactProcessStaging]')
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactAggregate]') AND type in (N'P'))
BEGIN
EXECUTE ('DROP PROCEDURE [Exchange2010].[AlertImpactAggregate]')
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactAggregationDelete]') AND type in (N'P'))
BEGIN
EXECUTE ('DROP PROCEDURE [Exchange2010].[AlertImpactAggregationDelete]')
END
-- delete std dataset related data
EXEC StandardDatasetDelete '$Config/DatasetId$'
GO
</Uninstall> <Upgrade>
-------------------------------------------------------------------------------
--
-- Copyright (c) Microsoft Corporation. All rights reserved.
--
-- AlertImpact.Dataset.Upgrade.sql
--
-- Script upgrades dataset 'Microsoft.Exchange.2010.Dataset.AlertImpact'.
--
-------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
BEGIN TRY
-- Get DatasetId of dataset 'Microsoft.Exchange.2010.Dataset.AlertImpact'.
DECLARE @datasetId uniqueidentifier;
SELECT TOP 1 @datasetId = ds.DatasetId
FROM dbo.DataSetType AS dst
INNER JOIN dbo.DatasetTypeManagementPackVersion AS mpv ON mpv.DatasetTypeRowId = dst.DatasetTypeRowId
INNER JOIN dbo.Dataset AS ds ON mpv.DatasetTypeManagementPackVersionRowId = ds.DatasetTypeManagementPackVersionRowId
WHERE dst.DatasetTypeSystemName = 'Microsoft.Exchange.2010.Dataset.AlertImpact'
ORDER BY ds.InstalledDateTime DESC;
IF (@datasetId IS NOT NULL)
BEGIN
--
-- Update stage processing setting 'MaxRowsToProcessPerTransactionCount' to 50.
--
UPDATE dbo.StandardDatasetStagingArea
SET MaxRowsToProcessPerTransactionCount = 50
WHERE DatasetId = @datasetId;
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 (@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
--------------------------------------------------------------------------------------
-- This stored procedure is used for grooming the AlertImpact dataset.
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactGroom]') AND type in (N'P'))
BEGIN
EXECUTE ('CREATE PROCEDURE [Exchange2010].[AlertImpactGroom] AS RETURN 1')
END
GO
ALTER PROCEDURE Exchange2010.AlertImpactGroom
@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 AlertImpact 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
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 AlertImpact data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
------------------------------------------------------------------------------------------
-- This stored procedure is called by the WriteAction in the management pack.
-- It parses the correlation context in the alert and save the impact record in the AlertImpactRawV14 table.
------------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactInsert]') AND type in (N'P'))
BEGIN
EXECUTE ('CREATE PROCEDURE [Exchange2010].[AlertImpactInsert] AS RETURN 1')
END
GO
ALTER PROCEDURE Exchange2010.AlertImpactInsert
@ManagementGroupId uniqueidentifier
,@DatasetId uniqueidentifier
,@DataXml nvarchar(max)
AS
BEGIN
SET NOCOUNT ON
DECLARE @CheckExecResult int
,@ManagementGroupRowId int
-- get the management group
SELECT @ManagementGroupRowId = ManagementGroupRowId
FROM vManagementGroup
WHERE (ManagementGroupGuid = @ManagementGroupId) AND (WriterLoginName = SUSER_Name())
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
,@ResultingState int
SET @ErrorInd = 0
SET @ResultingState = 1
DECLARE
@ExecResult int
,@XmlDocHandle int
BEGIN TRY
-- Add it to Exchange2010.AlertImpactRawStagingV14
-- We may not find the alert in the Alert table because the alert is first saved
-- in the AlertImpactRawStagingV14 table and there is a separate process to move it to the
-- Alert table. Therefore, we have to move the parsing of DataXml in to the
-- scheduled aggregation function.
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @DataXml
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'Exchange2010_AlertImpactInsert', @ExecResult)
INSERT INTO Exchange2010.AlertImpactRawStagingV14
SELECT GETUTCDATE(), GETUTCDATE(), CONVERT(nvarchar(max), Context)
FROM OPENXML(@XmlDocHandle, '//CorrelatedContext', 2) WITH (
Context xml '@mp:xmltext'
)
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, @ResultingState
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON Exchange2010.AlertImpactInsert TO OpsMgrWriter
GO
----------------------------------------------------------------------------------------------------------
-- This stored procedure moves data from Exchange2010.AlertImpactRawStagingV14
-- into Exchange2010.AlertImpactRawV14 when it finds matched data in SCOM's Alert dataset.
--
-- By default this stored procedure is called by SCOM every minute. If one instance of execution takes
-- more than 1 minute then SCOM calls the next instance immediately after the first one finishes or times out.
-- The default time out is 5 minutes. Therefore the sproc should limit the number of records to be processed
-- per execution to stay within 5 minute interval of execution.
----------------------------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactProcessStaging]') AND type in (N'P'))
BEGIN
EXECUTE ('CREATE PROCEDURE [Exchange2010].[AlertImpactProcessStaging] AS RETURN 1')
END
GO
ALTER PROCEDURE Exchange2010.AlertImpactProcessStaging
@DatasetId uniqueidentifier
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
DECLARE
-- Configuration variables.
-- Maximum age of data to keep in table AlertImpactRawStagingV14.
@MaxDataAgeDays int
-- Maximum number of rows to process per single run of this stored procedure.
,@MaxRowsToProcess int
-- Diagnostic variables.
,@DebugLevel int
,@DebugMessage nvarchar(4000)
,@OperationStartedDateTime datetime
,@OperationDurationMs bigint
,@RowsDeleted int
,@CountTotalProcessedRecords int
-- Number of records successfully processed and submitted into AlertImpactRawV14 table.
-- They will be deleted from AlertImpactRawStagingV14
,@CountSubmittedRecords int
-- Number of records for which there no match was found in Alert table.
-- They will be kept in AlertImpactRawStagingV14 for a defined period of time.
,@CountSkippedRecords int
-- Number of records with invalid content in 'Context' column.
-- They will be deleted from AlertImpactRawStagingV14.
,@CountInvalidRecords int
-- Processing variables.
,@AlertImpactRawStagingRowId int
,@AlertImpactRawStagingDateTime datetime
,@DataXml nvarchar(max)
,@RCAlertGuid uniqueidentifier
,@RCManagedEntityRowId int
,@RCComponentName nvarchar(max)
,@AlertResolutionState tinyint
,@AlertRaisedDateTime datetime
,@AlertStateSetDateTime datetime
,@ProcessAlert bit
-- Temorary table to hold the context info for each alert from Exchange2010.AlertImpactRawStagingV14.
DECLARE @Context TABLE
(
EntityName nvarchar(256) NOT NULL
,ProblemId nvarchar(256) NULL -- Only need this value for the root cause entity
,RootCauseInd bit NOT NULL -- Whether the entity is the root cause entity
);
-- Variables for processing data into @Context.
DECLARE
@StartIndex bigint
,@Index bigint
,@Length bigint
,@EntityName nvarchar(256)
,@ProblemId nvarchar(256)
BEGIN TRY
-- Get the debug level for AlertImpact dataset.
SELECT @DebugLevel = DebugLevel FROM dbo.StandardDataset WHERE DatasetId = @DatasetId;
--
-- Groom the table AlertImpactRawStagingV14.
--
-- Get the grooming settings for AlertImpactRawV14 table.
SELECT @MaxDataAgeDays = MaxDataAgeDays
FROM dbo.StandardDatasetAggregation WHERE DatasetId = @DatasetId AND AggregationTypeId = 0;
-- Delete the outdated records using the same grooming settings as for AlertImpactRawV14 table.
DELETE FROM Exchange2010.AlertImpactRawStagingV14 WHERE RaisedDateTime < DATEADD(day, - @MaxDataAgeDays, GETUTCDATE());
SET @RowsDeleted = @@ROWCOUNT;
IF((@DebugLevel > 2) AND (@RowsDeleted > 0))
-- Log a debug message.
BEGIN
SET @DebugMessage = 'Groomed table Exchange2010.AlertImpactRawStagingV14 by ' + CAST(@RowsDeleted as varchar(16)) + ' records.';
--
-- Perform stage processing.
--
-- Get the configuration value for stage processing.
SELECT @MaxRowsToProcess = MaxRowsToProcessPerTransactionCount
FROM dbo.StandardDatasetStagingArea
WHERE DatasetId = @DatasetId;
-- Temporary table to hold the records to be processed within single instance of execution of this sproc.
CREATE TABLE #AlertImpactRawStagingBatch
(
AlertImpactRawStagingRowId int NOT NULL
,RaisedDateTime datetime NOT NULL
,Context nvarchar(max) NULL
,DeleteFlag bit NOT NULL DEFAULT(0) -- marks the records to be deleted from original table.
);
-- Select only the earliest @MaxRowsToProcess records from Exchange2010.AlertImpactRawStagingV14 for processing.
-- This way the processing is balanced in order not to let this stored procedure to time out.
INSERT INTO #AlertImpactRawStagingBatch(AlertImpactRawStagingRowId, RaisedDateTime, Context)
SELECT TOP (@MaxRowsToProcess) AlertImpactRawStagingRowId, RaisedDateTime, Context
FROM Exchange2010.AlertImpactRawStagingV14
ORDER BY LastAccessedDateTime; -- FIFO queue mode - get the earliest [of possibly processed] records.
-- Initialize variables.
SET @AlertImpactRawStagingRowId = -1
SET @CountTotalProcessedRecords = 0;
SET @CountSubmittedRecords = 0;
SET @CountSkippedRecords = 0;
SET @CountInvalidRecords = 0;
-- Parse the records in the staging table and update AlertImpactRawV14.
-- The new alerts go to the AlertImpactRawStagingV14 table from Correlation Engine first,
-- and then appear to the Alert table in about 1 minute.
WHILE EXISTS(
SELECT TOP 1 AlertImpactRawStagingRowId
FROM #AlertImpactRawStagingBatch
WHERE AlertImpactRawStagingRowId > @AlertImpactRawStagingRowId
)
BEGIN
SELECT TOP 1 @AlertImpactRawStagingRowId = AlertImpactRawStagingRowId
,@AlertImpactRawStagingDateTime = RaisedDateTime
,@DataXml = Context
FROM #AlertImpactRawStagingBatch AIRS
WHERE AIRS.AlertImpactRawStagingRowId > @AlertImpactRawStagingRowId AND
NOT EXISTS
(SELECT AlertImpactRawStagingRowId
FROM #AlertImpactRawStagingBatch
WHERE AlertImpactRawStagingRowId > @AlertImpactRawStagingRowId AND
AlertImpactRawStagingRowId < AIRS.AlertImpactRawStagingRowId);
-- Process the alert's context and insert the parsed data into @Context.
-- Using string functions to do the parsing instead of SQL XML as it is
-- faster and uses much less memory to avoid the out-of-memory failure.
DELETE FROM @Context;
SET @StartIndex = 0;
-- Bug 229258. Check existence of required tags.
-- If either of them is not present - ignore & delete this record because it is useless.
IF(0 = CHARINDEX('<EntityName>', @DataXml) OR
0 = CHARINDEX('<ProblemId>', @DataXml))
BEGIN
UPDATE #AlertImpactRawStagingBatch
SET DeleteFlag = 1
WHERE AlertImpactRawStagingRowId = @AlertImpactRawStagingRowId;
SET @CountInvalidRecords = @CountInvalidRecords + 1;
SET @CountTotalProcessedRecords = @CountTotalProcessedRecords + 1;
-- Insert the root cause entity into @Context
INSERT INTO @Context VALUES (@EntityName, @ProblemId, 1);
-- Now process each impacted entity and add to @Context
SET @StartIndex = @Index;
WHILE (CHARINDEX('<EntityName>', @DataXml, @StartIndex) > 0)
BEGIN
SELECT @Index = (CHARINDEX('<EntityName>', @DataXml, @StartIndex) + 12);
SELECT @Length = (CHARINDEX('</EntityName>', @DataXml, @Index) - @Index);
SELECT @EntityName = SUBSTRING(@DataXml, @Index, @Length);
-- Insert the impacted entity into @Context
INSERT INTO @Context VALUES (@EntityName, NULL, 0);
SET @StartIndex = @Index;
END
-- Set the default target alert values to NULL
SET @RCAlertGuid = NULL;
SET @RCManagedEntityRowId = NULL;
SET @AlertResolutionState = NULL;
SET @AlertRaisedDateTime = NULL;
SET @AlertStateSetDateTime = NULL;
-- By default, don't process the alert unless the condition is valid.
SET @ProcessAlert = 0;
-- Find the root cause alertGuid and ManagedEntity
SELECT TOP 1
@RCAlertGuid = a.AlertGuid
,@RCManagedEntityRowId = a.ManagedEntityRowId
,@RCComponentName =
CASE
WHEN me.FullName LIKE 'Microsoft.Exchange.2010.Mailbox%' THEN 'Microsoft.Exchange.2010.Mailbox%'
WHEN me.FullName LIKE 'Microsoft.Exchange.2010.ClientAccess%' THEN 'Microsoft.Exchange.2010.ClientAccess%'
WHEN me.FullName LIKE 'Microsoft.Exchange.2010.Hub%' THEN 'Microsoft.Exchange.2010.Hub%'
WHEN me.FullName LIKE 'Microsoft.Exchange.2010.Edge%' THEN 'Microsoft.Exchange.2010.Edge%'
WHEN me.FullName LIKE 'Microsoft.Exchange.2010.CommonTransport%' THEN 'Microsoft.Exchange.2010.CommonTransport%'
WHEN me.FullName LIKE 'Microsoft.Exchange.2010.UnifiedMessaging%' THEN 'Microsoft.Exchange.2010.UnifiedMessaging%'
WHEN me.FullName LIKE 'Microsoft.Exchange.2010.CommonRemotePowerShell%' THEN 'Microsoft.Exchange.2010.CommonRemotePowerShell%'
ELSE me.FullName
END
,@AlertResolutionState = ars.ResolutionState
,@AlertRaisedDateTime = a.RaisedDateTime
,@AlertStateSetDateTime = ars.StateSetDateTime
FROM @Context as rc
INNER JOIN Alert.vAlertDetail AS ad
ON rc.ProblemId = ad.CustomField10
INNER JOIN Alert.vAlert AS a
ON ad.AlertGuid = a.AlertGuid
INNER JOIN Alert.vAlertResolutionState AS ars
ON a.AlertGuid = ars.AlertGuid
INNER JOIN vManagedEntity AS me
ON a.ManagedEntityRowId = me.ManagedEntityRowId
AND me.DisplayName = rc.EntityName
WHERE rc.RootCauseInd = 1
ORDER BY ars.StateSetDateTime DESC;
-- Check if the alert from Exchange2010.AlertImpactRawStagingV14 was matched to an alert stored in SCOM's Alert dataset.
-- And then process the alert according to its resolution state.
IF ((@AlertResolutionState IS NOT NULL) AND (@AlertResolutionState < 255))
BEGIN
-- Found active alert.
-- We will process and delete this alert from Exchange2010.AlertImpactRawStagingV14.
SET @ProcessAlert = 1;
END
ELSE
BEGIN
-- Found no active alert, but check if there is a resolved alert.
--
IF (((@AlertResolutionState IS NOT NULL) AND (@AlertResolutionState = 255)) AND
(@AlertStateSetDateTime IS NOT NULL))
BEGIN
IF (@AlertStateSetDateTime >= @AlertImpactRawStagingDateTime)
BEGIN
-- Found resolved alert with: @AlertStateSetDateTime >= @AlertImpactRawStagingDateTime
-- This implies that the alert has already been resolved.
-- We shall proces and delete this alert from Exchange2010.AlertImpactRawStagingV14.
--
SET @ProcessAlert = 1;
END
ELSE
BEGIN
-- Found an old resolved alert with: @AlertStateSetDateTime < @AlertImpactRawStagingDateTime
-- This implies that the alert has not been moved to the alert table yet.
-- We will process it on the next aggregation schedule.
--
SET @ProcessAlert = 0;
END
END
ELSE
BEGIN
-- Did not find any alert.
-- This implies that the alert has not been moved to the alert table yet.
-- We will process it on the next aggregation schedule.
--
SET @ProcessAlert = 0;
END
END
-- Process the alert by adding it to Exchange2010.vAlertImpactRawV14.
IF (1 = @ProcessAlert)
BEGIN
-- Add it to Exchange2010.vAlertImpactRawV14
-- Note: the WriteAction will be called when the Alert is updated. Make sure the entry is not already there.
IF NOT EXISTS (
SELECT *
FROM Exchange2010.vAlertImpactRawV14 AIR
WHERE AIR.AlertGuid = @RCAlertGuid AND AIR.ManagedEntityRowId = @RCManagedEntityRowId)
BEGIN
INSERT INTO Exchange2010.vAlertImpactRawV14 ([DateTime], AlertGuid, ManagedEntityRowId, OnSameEntity)
VALUES ( @AlertImpactRawStagingDateTime, @RCAlertGuid, @RCManagedEntityRowId, 1);
END
-- Add the other impacted entities to Exchange2010.vAlertImpactRawV14
INSERT Exchange2010.vAlertImpactRawV14
([DateTime], AlertGuid, ManagedEntityRowId, OnSameEntity)
SELECT @AlertImpactRawStagingDateTime, @RCAlertGuid, me.ManagedEntityRowId,
CASE WHEN me.FullName LIKE @RCComponentName THEN 1 ELSE 0 END
FROM @Context AS e
INNER JOIN vManagedEntity AS me
ON me.DisplayName = e.EntityName
WHERE e.RootCauseInd = 0
AND NOT EXISTS (-- Note: the WriteAction will be called when the Alert is updated. Make sure the entry is not already there.
SELECT *
FROM Exchange2010.vAlertImpactRawV14 AIR
WHERE AIR.AlertGuid = @RCAlertGuid AND AIR.ManagedEntityRowId = me.ManagedEntityRowId)
GROUP BY e.EntityName, me.ManagedEntityRowId, me.FullName;
UPDATE #AlertImpactRawStagingBatch
SET DeleteFlag = 1
WHERE AlertImpactRawStagingRowId = @AlertImpactRawStagingRowId;
SET @CountSubmittedRecords = @CountSubmittedRecords + 1;
END
ELSE -- (0 = @ProcessAlert) otherwise keep the record because alert may have not appeared in the MOM's alert dataset yet.
BEGIN
SET @CountSkippedRecords = @CountSkippedRecords + 1;
END
SET @CountTotalProcessedRecords = @CountTotalProcessedRecords + 1;
END -- end of loop.
-- Delete only those records from original table that were makred for deletion in batch table.
-- Deletion from original table is performed here in a single transaction to minimize
-- lock impact to that table.
DELETE FROM Exchange2010.AlertImpactRawStagingV14
FROM Exchange2010.AlertImpactRawStagingV14 AS airs -- Second FROM clause is used for filtering the records to be deleted.
INNER JOIN #AlertImpactRawStagingBatch AS batch ON airs.AlertImpactRawStagingRowId = batch.AlertImpactRawStagingRowId
WHERE batch.DeleteFlag = 1;
-- Update the 'LastAccessedDateTime' column for processed records to allow this sproc
-- to process another record set on next execution (FIFO queue mode).
UPDATE Exchange2010.AlertImpactRawStagingV14
SET LastAccessedDateTime = GETUTCDATE()
FROM Exchange2010.AlertImpactRawStagingV14 AS airs -- Second FROM clause is used for filtering the records to be updated.
INNER JOIN #AlertImpactRawStagingBatch AS batch ON airs.AlertImpactRawStagingRowId = batch.AlertImpactRawStagingRowId
WHERE batch.DeleteFlag = 0;
-- Drop the temporary table.
DROP TABLE #AlertImpactRawStagingBatch;
-- Log a debug message.
IF (@DebugLevel > 2)
BEGIN
SET @OperationDurationMs = DATEDIFF(ms, @OperationStartedDateTime, GETDATE());
SET @DebugMessage = 'Finished stage processing of Exchange2010.AlertImpactRawStagingV14 table. ' +
'Total processed records: ' + CAST(@CountTotalProcessedRecords AS varchar(16)) + '.';
IF (@CountTotalProcessedRecords > 0)
BEGIN
SET @DebugMessage = @DebugMessage +
' (Submitted: ' + CAST(@CountSubmittedRecords AS varchar(16)) +
'; Skipped: ' + CAST(@CountSkippedRecords AS varchar(16)) +
'; Invalid: ' + CAST(@CountInvalidRecords AS varchar(16)) + ').';
END
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to process staging data for AlertImpact 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
----------------------------------------------------------------------------------------------------------
--- This stored procedure aggregates the alert impact data and saves the results in the hourly or daily table
----------------------------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactAggregate]') AND type in (N'P'))
BEGIN
EXECUTE ('CREATE PROCEDURE [Exchange2010].[AlertImpactAggregate] AS RETURN 1')
END
GO
ALTER PROCEDURE Exchange2010.AlertImpactAggregate
@DatasetId uniqueidentifier
,@AggregationTypeId int -- aggregration type: e.g. 20 = hourly
,@InsertTableGuid varchar(256) -- GUID of the aggregation table
,@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
,@IntervalStartDateTimeStr nvarchar(20)
,@IntervalEndDateTimeStr nvarchar(20)
,@AggregationCoverViewName sysname
,@InsertTableName sysname
,@DebugMessage nvarchar(4000)
,@AggregationStartDateTime datetime
,@LowerAggregationTypeId int
,@LowerAggregationIntervalMinutes int
,@NonDirtyLowerAggregationCount int
,@LowerAggregationCoverViewName sysname
,@UseAggregatesToBuildStartOfIntervalStateInd bit
,@SchemaName sysname
,@InsertTableFullName nvarchar (256)
SET @IntervalStartDateTimeStr = CONVERT(varchar(20), @IntervalStartDateTime, 120)
SET @IntervalEndDateTimeStr = CONVERT(varchar(20), @IntervalEndDateTime, 120)
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)
SET @InsertTableFullName = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName)
-- 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 '
+ @IntervalStartDateTimeStr + 'UTC to '
+ @IntervalEndDateTimeStr + '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)
EXECUTE (@Statement)
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 '
+ @IntervalStartDateTimeStr + 'UTC to '
+ @IntervalEndDateTimeStr + 'UTC. Using raw data'
SET @AggregationStartDateTime = GETUTCDATE()
CREATE TABLE #ImpactTemp(
AlertGuid uniqueidentifier
,Severity tinyint
,ManagedEntityRowId int
,OnSameEntity tinyint
,StartDateTime datetime
,EndDateTime datetime
,RawRedImpactInMilliseconds int
,RawYellowImpactInMilliseconds int
)
INSERT INTO #ImpactTemp (
AlertGuid
,Severity
,ManagedEntityRowId
,OnSameEntity
,StartDateTime
,EndDateTime
)
SELECT
AIR.AlertGuid
,A.Severity
,AIR.ManagedEntityRowId
,AIR.OnSameEntity
,CASE WHEN A.RaisedDateTime < @IntervalStartDateTime then @IntervalStartDateTime ELSE A.RaisedDateTime END
,CASE WHEN AR.StateSetDateTime IS NULL then @IntervalEndDateTime
WHEN AR.StateSetDateTime > @IntervalEndDateTime then @IntervalEndDateTime ELSE AR.StateSetDateTime END
FROM Exchange2010.vAlertImpactRawV14 AIR
JOIN Alert.vAlert A
ON A.AlertGuid = AIR.AlertGuid
LEFT JOIN Alert.vAlertResolutionState AR -- LEFT JOIN vAlertResolutionState to get the resolution time if the alert is resolved.
ON A.AlertGuid = AR.AlertGuid
AND AR.ResolutionState = 255
WHERE
A.RaisedDateTime < @IntervalEndDateTime -- filter out the alerts that happened after the end of the period
AND
(AR.StateSetDateTime IS NULL OR
AR.StateSetDateTime > @IntervalStartDateTime) -- filter out the alerts that was closed before the beginning of the period
UPDATE #ImpactTemp
SET RawRedImpactInMilliseconds = CASE WHEN Severity = 2 THEN DATEDIFF(millisecond, StartDateTime, EndDateTime) ELSE 0 END
, RawYellowImpactInMilliseconds = CASE WHEN Severity = 1 THEN DATEDIFF(millisecond, StartDateTime, EndDateTime) ELSE 0 END
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished aggregation of type ' + CAST(@AggregationTypeId AS varchar) + ' for interval from '
+ @IntervalStartDateTimeStr + 'UTC to '
+ @IntervalEndDateTimeStr + 'UTC'
DECLARE @OperationDurationMs bigint
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @AggregationStartDateTime))
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
------------------------------------------------------------------------------
-- This stored procedure is used for deleting the specified aggregation record.
------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exchange2010].[AlertImpactAggregationDelete]') AND type in (N'P'))
BEGIN
EXECUTE ('CREATE PROCEDURE [Exchange2010].[AlertImpactAggregationDelete] AS RETURN 1')
END
GO
ALTER PROCEDURE Exchange2010.AlertImpactAggregationDelete
@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 AlertImpact 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 AlertImpact data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
Home