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 varchar(max)
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
INSERT StandardDatasetAggregation
(
DatasetId
,AggregationTypeId
,AggregationIntervalDurationMinutes
,AggregationStartDelayMinutes
,BuildAggregationStoredProcedureName
,DeleteAggregationStoredProcedureName
,GroomStoredProcedureName
,IndexOptimizationIntervalMinutes
,MaxDataAgeDays
,GroomingIntervalMinutes
,MaxRowsToGroom
,LastGroomingDateTime
,DataFileGroupName
,IndexFileGroupName
)
SELECT
'$Config/DatasetId$'
,CASE AggregationType
WHEN 'Raw' THEN 0
WHEN 'Subhourly' THEN 10
WHEN 'Hourly' THEN 20
WHEN 'Daily' THEN 30
END
,CASE AggregationType
WHEN 'Raw' THEN NULL
WHEN 'Subhourly' THEN AggregationIntervalDurationMinutes
WHEN 'Hourly' THEN 60
WHEN 'Daily' THEN 24*60
END
,AggregationStartDelayMinutes
,'AemAggregate'
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'AemAggregationDelete'
END
,'AemGroom'
,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
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@MaxTableRowCount int
,@MaxTableSizeKb int
,@ExecResult int
SET @ErrorInd = 0
DECLARE
@XmlDocHandle int
,@XmlDocText nvarchar(max)
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
-- Inserting info about raw and non-dependent daily aggregations
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
SELECT
'$Config/DatasetId$'
,CASE AggregationType
WHEN 'Raw' THEN 0
WHEN 'Daily' THEN 30
END
,BaseTableName
,0
,CASE AggregationType
WHEN 'Raw' THEN '
CREATE TABLE [CM].[CMAemRaw_$Guid$]
(
AemRawRowId int NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,ErrorGroupRowId int NOT NULL
,CrashId uniqueidentifier NOT NULL
,CrashTime as [DateTime]
,AemUserRowId int NULL
,AemComputerRowId int NULL
,CONSTRAINT [PK_CMAemRaw_$Guid$] PRIMARY KEY CLUSTERED ([DateTime], AemRawRowId) ON $DataFileGroupName$
) ON $DataFileGroupName$ '
WHEN 'Daily' THEN '
CREATE TABLE [CM].[CMAemDaily_$Guid$]
(
AemDailyRowId int NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,CONSTRAINT [PK_CMAemDaily_$Guid$] PRIMARY KEY CLUSTERED ([DateTime], AemDailyRowId) ON $DataFileGroupName$
) ON $DataFileGroupName$ '
END
,CASE AggregationType
WHEN 'Raw' THEN '
SELECT
[DateTime]
,ErrorGroupRowId
,CrashId
,CrashTime
,AemUserRowId
,AemComputerRowId '
WHEN 'Daily' THEN ' SELECT [DateTime] '
END
,MaxTableRowCount
,MaxTableSizeKb
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname'
,BaseTableName varchar(256)
,MaxTableRowCount int
,MaxTableSizeKb int
,DataFileGroupName varchar(256)
,IndexFileGroupName varchar(256)
)
CROSS JOIN StandardDataset d
WHERE (d.DatasetId = '$Config/DatasetId$')
SELECT
@MaxTableSizeKb = MaxTableSizeKb
, @MaxTableRowCount = MaxTableRowCount
FROM StandardDatasetAggregationStorage
WHERE DatasetId = '$Config/DatasetId$' AND AggregationTypeId = 30
-- inserting dependent error group aggregation info
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,TableTag
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
VALUES
(
'$Config/DatasetId$'
,30
,'CMAemErrorGroupDaily'
,'ErrorGroup'
,1
,'
CREATE TABLE [CM].[CMAemErrorGroupDaily_$Guid$]
(
AemErrorGroupDailyRowId int NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,ApplicationRowId int NOT NULL
,ErrorGroupRowId int NOT NULL
,CrashCount int NOT NULL
,UniqueUsersAffectedCount int NOT NULL
,UniqueComputersAffectedCount int NOT NULL
-- inserting dependent Aem application level aggregation info
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,TableTag
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
VALUES
(
'$Config/DatasetId$'
,30
,'CMAemApplicationDaily'
,'Application'
,1
,'
CREATE TABLE [CM].[CMAemApplicationDaily_$Guid$]
(
AemApplicationDailyRowId int NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,ApplicationRowId int NOT NULL
,ErrorGroupCount int NOT NULL
,CrashCount int NOT NULL
,UniqueUsersAffectedCount int NOT NULL
,UniqueComputersAffectedCount int NOT NULL
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AemErrorGroup' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'ErrorGroupId')
BEGIN
ALTER TABLE dbo.AemErrorGroup
DROP COLUMN ErrorGroupId
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AemErrorGroup' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'BucketId')
BEGIN
ALTER TABLE dbo.AemErrorGroup
ADD BucketId bigint
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AemErrorGroup' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'BucketTableId')
BEGIN
ALTER TABLE dbo.AemErrorGroup
ADD BucketTableId bigint
END
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].AemErrorGroup TO OpsMgrWriter
GO
GRANT SELECT ON [dbo].AemErrorGroup TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AemEventStage' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('CREATE TABLE dbo.AemEventStage (
DatasetId uniqueidentifier NOT NULL
,[DateTime] datetime NOT NULL
,CrashId uniqueidentifier NOT NULL
,ErrorGroupManagedEntityId uniqueidentifier NOT NULL
,AemComputerName nvarchar(256)
,AemUserName nvarchar(256)
,CrashTime as [DateTime]
,DWCreatedDataTime datetime NOT NULL DEFAULT (GETUTCDATE())
)')
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].AemEventStage TO OpsMgrWriter
GO
GRANT SELECT ON [dbo].AemEventStage TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AemManagedEntityStage' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.AemManagedEntityStage (
DatasetId uniqueidentifier NOT NULL
,ApplicationManagedEntityId uniqueidentifier NOT NULL
,ApplicationName nvarchar(256) NOT NULL
,ApplicationVersion nvarchar(256)
,CompanyName nvarchar(256)
,FriendlyApplicationName nvarchar(256)
,DWCreatedDataTime datetime NOT NULL DEFAULT (GETUTCDATE())
)
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AemManagedEntityStage' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'ErrorGroupId')
BEGIN
ALTER TABLE dbo.AemManagedEntityStage
DROP COLUMN ErrorGroupId
END
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AemManagedEntityStage' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'BucketId')
BEGIN
ALTER TABLE dbo.AemManagedEntityStage
ADD BucketId bigint
END
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AemManagedEntityStage' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'BucketTableId')
BEGIN
ALTER TABLE dbo.AemManagedEntityStage
ADD BucketTableId bigint
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].AemManagedEntityStage TO OpsMgrWriter
GO
GRANT SELECT ON [dbo].AemManagedEntityStage TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AemSystemErrorGroup' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.AemSystemErrorGroup
(
ErrorGroupRowId int NOT NULL IDENTITY(1, 1)
,ErrorGroupManagedEntityId uniqueidentifier NOT NULL
,BucketType nvarchar(4) NOT NULL DEFAULT('Blue')
,CrashTime datetime
,AemUserRowId int
,AemComputerRowId int
-- ,CabFileName nvarchar(1024)
,MicrosoftResponseUrl nvarchar(256)
/*
,MicrosoftSolutionId int
,MicrosoftGenericSolutionId int
,MicrosoftSolution nvarchar(256)
,MicrosoftGenericSolution nvarchar(256)
,MicrosoftResponseTypeId int
*/
,LastReceivedDateTime smalldatetime NOT NULL DEFAULT (GETUTCDATE())
)
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].AemUser TO OpsMgrWriter
GO
GRANT SELECT ON [dbo].AemUser TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemAggregate')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemAggregate AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[AemAggregate]
@DatasetId uniqueidentifier
,@AggregationTypeId int
,@InsertTableGuid varchar(256)
,@IntervalStartDateTime datetime
,@IntervalEndDateTime datetime
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
BEGIN TRY
IF EXISTS (SELECT * FROM StandardDataset WHERE SchemaName = 'Aem' AND DatasetId = @DatasetId)
BEGIN
RETURN
END
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
BEGIN TRAN
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting aggregation at main level of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ '''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''' UTC to '
+ '''' + CONVERT(varchar(50), @IntervalEndDateTime, 120) + ''' UTC'
SET @AggregationStartDateTime = GETUTCDATE()
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished aggregation at main level of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ '''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''' UTC to '
+ '''' + CONVERT(varchar(50), @IntervalEndDateTime, 120) + ''' UTC'
DECLARE @OperationDurationMs bigint
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @AggregationStartDateTime))
SELECT @CoverRawViewName = QUOTENAME(@SchemaName) + '.v' + BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
-- We'll first finish aggregations for error group, and then we'll start aggregations for application
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting error group aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ '''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''' UTC to '
+ '''' + CONVERT(varchar(50), @IntervalEndDateTime, 120) + ''' UTC'
SET @AggregationStartDateTime = GETUTCDATE()
CREATE TABLE #DailyErrorGroupAggregatedData
(
StartDateTime datetime
,EndDateTime datetime
,ErrorGroupRowId int
,CrashCount int
,UniqueUsersAffectedCount int
,UniqueComputersAffectedCount int
)
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished error group aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ '''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''' UTC to '
+ '''' + CONVERT(varchar(50), @IntervalEndDateTime, 120) + ''' UTC'
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @AggregationStartDateTime))
-- start aggregation for application type, now that aggregation for error group is over
SELECT @InsertTableName = QUOTENAME(@SchemaName) + '.CMAemApplicationDaily_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting application aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ '''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''' UTC to '
+ '''' + CONVERT(varchar(50), @IntervalEndDateTime, 120) + ''' UTC'
CREATE TABLE #DailyApplicationAggregatedData
(
StartDateTime datetime
,EndDateTime datetime
,ApplicationRowId int
,ErrorGroupCount int
,CrashCount int
,UniqueUsersAffectedCount int
,UniqueComputersAffectedCount int
)
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished application aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ '''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''' UTC to '
+ '''' + CONVERT(varchar(50), @IntervalEndDateTime, 120) + ''' UTC'
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
GRANT EXECUTE ON AemAggregate TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemAggregationDelete')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemAggregationDelete AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AemAggregationDelete
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@AggregationDateTime datetime
,@RowsDeleted int OUTPUT
AS
BEGIN
IF EXISTS (SELECT * FROM StandardDataset WHERE SchemaName = 'Aem' AND DatasetId = @DatasetId)
BEGIN
RETURN
END
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
IF (@AggregationTypeId <> 30) RETURN
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
BEGIN TRAN
-- there are dependent tables in aem data set; we'll 1st delete error group aggregation
SELECT @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 1)
AND (BaseTableName = 'CMAemErrorGroupDaily')
-- we'll now delete main aggregation table
SELECT @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
-- 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 AEM data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
GRANT EXECUTE ON AemAggregationDelete TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemEventInsert')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemEventInsert AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AemEventInsert
@ManagementGroupId uniqueidentifier
,@DatasetId uniqueidentifier
,@DataXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group permissions
SELECT @ManagementGroupRowId = ManagementGroupRowId
FROM vManagementGroup
WHERE (ManagementGroupGuid = @ManagementGroupId)
AND (WriterLoginName = SUSER_NAME())
IF (@ManagementGroupRowId IS NULL)
BEGIN
RAISERROR(777970003, 16, 1)
RETURN -1
END
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@ResultingState int
SET @ResultingState = 1
BEGIN TRY
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @DataXml
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'CM', @ExecResult)
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 AemEventInsert TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemEventProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemEventProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AemEventProcessStaging
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure sysname
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
DECLARE
@InsertTableName sysname
,@InsertTableGuid uniqueidentifier
,@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@ExecResult int
,@TempTableCreated bit
,@DebugLevel int
,@LockResourceName sysname
,@MessageText nvarchar(max)
,@OperationDurationMs bigint
,@SchemaName sysname
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
BEGIN TRAN
-- remove duplicate data
SELECT @Statement = 'DELETE e'
+ ' FROM AemEventStage e, ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' i'
+ ' WHERE (i.CrashId = e.CrashId)'
EXECUTE (@Statement)
-- creating temp table
CREATE TABLE #Event
(
ErrorGroupManagedEntityId uniqueidentifier NOT NULL
,[DateTime] datetime NOT NULL
,ErrorGroupRowId int
,CrashId uniqueidentifier NOT NULL
,CrashTime as [DateTime]
,AemUserName nvarchar(256)
,AemUserRowId int
,AemComputerName nvarchar(256)
,AemComputerRowId int
,WatsonBucket bit
)
-- getting data ready to be moved to CMAemRaw in temp table
INSERT #Event (
ErrorGroupManagedEntityId
,[DateTime]
,ErrorGroupRowId
,CrashId
,AemUserName
,AemUserRowId
,AemComputerName
,AemComputerRowId
,WatsonBucket
)
SELECT
aes.ErrorGroupManagedEntityId
,aes.[DateTime]
,aeg.ErrorGroupRowId
,aes.CrashId
,aes.AemUserName
,au.AemUserRowId
,aes.AemComputerName
,ac.AemComputerRowId
,1
FROM AemEventStage aes
JOIN AemErrorGroup aeg ON (aeg.ErrorGroupManagedEntityId = aes.ErrorGroupManagedEntityId)
LEFT JOIN AemUser au ON (au.AemUserName = aes.AemUserName)
LEFT JOIN AemComputer ac ON (ac.AemComputerName = aes.AemComputerName)
-- getting data ready to be moved to CMAemRaw in temp table
INSERT #Event (
ErrorGroupManagedEntityId
,[DateTime]
,ErrorGroupRowId
,CrashId
,AemUserName
,AemUserRowId
,AemComputerName
,AemComputerRowId
,WatsonBucket
)
SELECT
aes.ErrorGroupManagedEntityId
,aes.[DateTime]
,aseg.ErrorGroupRowId
,aes.CrashId
,aes.AemUserName
,au.AemUserRowId
,aes.AemComputerName
,ac.AemComputerRowId
,0
FROM AemEventStage aes
JOIN AemSystemErrorGroup aseg ON (aseg.ErrorGroupManagedEntityId = aes.ErrorGroupManagedEntityId)
LEFT JOIN AemUser au ON (au.AemUserName = aes.AemUserName)
LEFT JOIN AemComputer ac ON (ac.AemComputerName = aes.AemComputerName)
-- we will not insert data whose Error Group's are not known yet
DELETE #Event
WHERE ErrorGroupRowId IS NULL
UPDATE au
SET LastReceivedDateTime = GETUTCDATE()
FROM AemUser au
JOIN #Event e ON (au.AemUserRowId = e.AemUserRowId)
WHERE (DATEADD(day, 1, au.LastReceivedDateTime) < GETUTCDATE())
INSERT AemUser (AemUserName)
SELECT DISTINCT e.AemUserName COLLATE database_default
FROM #Event e
WHERE 0 = (SELECT COUNT(*) FROM AemUser au WHERE (au.AemUserName = e.AemUserName COLLATE database_default))
AND e.AemUserName IS NOT NULL
UPDATE e
SET e.AemUserRowId = au.AemUserRowId
FROM #Event e JOIN AemUser au ON (e.AemUserName = au.AemUserName COLLATE database_default)
WHERE (e.AemUserRowId IS NULL)
UPDATE ac
SET LastReceivedDateTime = GETUTCDATE()
FROM AemComputer ac
JOIN #Event e ON (ac.AemComputerRowId = e.AemComputerRowId)
WHERE (DATEADD(day, 1, ac.LastReceivedDateTime) < GETUTCDATE())
INSERT AemComputer (AemComputerName)
SELECT DISTINCT e.AemComputerName COLLATE database_default
FROM #Event e
WHERE 0 = (SELECT COUNT(*) FROM AemComputer ac WHERE (ac.AemComputerName = e.AemComputerName COLLATE database_default))
AND e.AemComputerName IS NOT NULL
UPDATE e
SET e.AemComputerRowId = ac.AemComputerRowId
FROM #Event e JOIN AemComputer ac ON (e.AemComputerName = ac.AemComputerName COLLATE database_default)
WHERE (e.AemComputerRowId IS NULL)
-- remove duplicate data that we recevied via Check-For-Solution feature
-- that has CrashId probably different
-- we start with watson bucket 1st
SELECT @Statement = 'DELETE aes'
+ ' FROM AemEventStage aes, '
+ QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' i, '
+ ' #Event e '
+ ' WHERE (aes.CrashId = e.CrashId) '
+ ' AND (e.WatsonBucket = 1) '
+ ' AND (e.AemComputerRowId = i.AemComputerRowId '
+ ' AND e.AemUserRowId = i.AemUserRowId '
+ ' AND e.ErrorGroupRowId = i.ErrorGroupRowId '
+ ' AND e.CrashTime = i.CrashTime)'
EXECUTE (@Statement)
-- now, system bucket
SELECT @Statement = 'DELETE aes'
+ ' FROM AemEventStage aes, '
+ ' AemSystemErrorGroup aseg, '
+ ' #Event e '
+ ' WHERE (aes.CrashId = e.CrashId) '
+ ' AND (e.WatsonBucket = 0) '
+ ' AND (e.AemComputerRowId = aseg.AemComputerRowId '
+ ' AND e.AemUserRowId = aseg.AemUserRowId '
+ ' AND e.ErrorGroupRowId = aseg.ErrorGroupRowId '
+ ' AND e.CrashTime = aseg.CrashTime)'
EXECUTE (@Statement)
-- we deleted above data from AemEventStage, we need to delete data from #Event too
SELECT @Statement = 'DELETE e'
+ ' FROM #Event e '
+ ' WHERE e.CrashId NOT IN (SELECT CrashId FROM AemEventStage) '
EXECUTE (@Statement)
-- ***************************************************************
-- lock table map to ensure we insert into table opened for insertion
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_TableMap'
IF (@DebugLevel > 3)
BEGIN
SELECT @MessageText = 'Starting to insert ' + CAST(COUNT(*) AS varchar(30)) + ' AEM event data into table set with GUID ' + CAST(@InsertTableGuid AS varchar(100))
FROM #Event
-- mark existing affected aggregations as dirty
UPDATE sdah
SET DirtyInd = 1
,DataLastReceivedDateTime = GETUTCDATE()
FROM StandardDatasetAggregationHistory sdah
JOIN StandardDatasetAggregation sda ON (sdah.DatasetId = sda.DatasetId AND sdah.AggregationTypeId = sda.AggregationTypeId)
CROSS JOIN #Event e
WHERE (sdah.DatasetId = @DatasetId)
AND (sdah.DirtyInd = 0)
AND (e.[DateTime] >= sdah.AggregationDateTime)
AND (e.[DateTime] < DATEADD(minute, sda.AggregationIntervalDurationMinutes, sdah.AggregationDateTime))
AND (e.WatsonBucket = 1)
-- let's deal with system bucket data now
-- for system bucket: the event details map to system error group
-- the event data did not make into AemRaw table
UPDATE aseg
SET aseg.CrashTime = e.[DateTime]
,aseg.AemUserRowId = e.AemUserRowId
,aseg.AemComputerRowId = e.AemComputerRowId
--,aseg.CabFileName =
FROM AemSystemErrorGroup aseg
JOIN #Event e ON (aseg.ErrorGroupManagedEntityId = e.ErrorGroupManagedEntityId)
-- remove inserted systemBucket related data
SELECT @Statement = 'DELETE aes'
+ ' FROM AemEventStage aes'
+ ' JOIN #Event e ON (e.ErrorGroupManagedEntityId = aes.ErrorGroupManagedEntityId)'
EXECUTE (@Statement)
-- remove inserted data
SELECT @Statement =
'DELETE aes '
+ 'FROM AemEventStage aes '
+ ' JOIN #Event e ON (aes.CrashId = e.CrashId)'
EXECUTE (@Statement)
IF (@DebugLevel > 3)
BEGIN
SET @MessageText = 'Finished inserting AEM event data into table set with GUID ' + CAST(@InsertTableGuid AS varchar(100))
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to insert AEM event data into AEM 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
GRANT EXECUTE ON AemEventProcessStaging TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemGetDailyAggregationDate')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemGetDailyAggregationDate AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[AemGetDailyAggregationDate]
@InDate datetime
,@OutDate datetime out
AS
BEGIN
SET NOCOUNT ON
SET @OutDate = CAST(CONVERT(char(8), @InDate, 112) AS DateTime)
RETURN 1
END
GO
GRANT EXECUTE ON AemGetDailyAggregationDate TO OpsMgrWriter
GO
GRANT EXECUTE ON AemGetDailyAggregationDate TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AemGroom
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@CutoffDateTime datetime
,@MaxRowsToGroom int
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT * FROM StandardDataset WHERE SchemaName = 'Aem' AND DatasetId = @DatasetId)
BEGIN
RETURN
END
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 Aem 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
IF (@AggregationTypeId = 30)
BEGIN
SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('CMAemApplicationDaily' + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', ''))
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT + @RowsDeleted
SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('CMAemErrorGroupDaily' + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', ''))
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT + @RowsDeleted
END
-- groom dynamic dimensions
DECLARE @MaxDataAgeDays int
SELECT @MaxDataAgeDays = MAX(MaxDataAgeDays)
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
DELETE aseg
FROM AemSystemErrorGroup aseg
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE au
FROM AemUser au
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE ac
FROM AemComputer ac
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE aeg
FROM AemErrorGroup aeg
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE aa
FROM AemApplication aa
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
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 Aem 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] = 'AemManagedEntityInsert')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemManagedEntityInsert AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AemManagedEntityInsert
@ManagementGroupId uniqueidentifier
,@DatasetId uniqueidentifier
,@DataXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group permissions
SELECT @ManagementGroupRowId = ManagementGroupRowId
FROM vManagementGroup
WHERE (ManagementGroupGuid = @ManagementGroupId)
AND (WriterLoginName = SUSER_NAME())
IF (@ManagementGroupRowId IS NULL)
BEGIN
RAISERROR(777970003, 16, 1)
RETURN -1
END
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@ResultingState int
SET @ResultingState = 1
BEGIN TRY
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @DataXml
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'CM', @ExecResult)
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 AemManagedEntityInsert TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemManagedEntityProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemManagedEntityProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AemManagedEntityProcessStaging
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure sysname
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
DECLARE
@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@ExecResult int
,@DebugLevel int
,@LockResourceName sysname
,@MessageText nvarchar(max)
,@OperationDurationMs bigint
,@SchemaName sysname
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
BEGIN TRAN
IF (@DebugLevel > 3)
BEGIN
SELECT @MessageText = 'Starting to insert ' + CAST(COUNT(*) AS varchar(30)) + ' managed entity data'
FROM AemManagedEntityStage
-- *****************************************************
-- update non-key properties of ME(s) we already know
-- start with AemApplication
UPDATE AemApplication
SET LastReceivedDateTime = GETUTCDATE()
FROM AemApplication aa, AemManagedEntityStage ames
WHERE aa.ApplicationManagedEntityId = ames.ApplicationManagedEntityId
UPDATE AemApplication
SET CompanyName = ames.CompanyName
FROM AemApplication aa, AemManagedEntityStage ames
WHERE aa.ApplicationManagedEntityId = ames.ApplicationManagedEntityId AND
ames.CompanyName IS NOT NULL AND
len(ames.CompanyName) > 0
UPDATE AemApplication
SET FriendlyApplicationName = ames.FriendlyApplicationName
FROM AemApplication aa, AemManagedEntityStage ames
WHERE aa.ApplicationManagedEntityId = ames.ApplicationManagedEntityId AND
ames.FriendlyApplicationName IS NOT NULL AND
len(ames.FriendlyApplicationName) > 0
-- update AemErrorGroup table with entries that already exist
UPDATE AemErrorGroup
SET LastReceivedDateTime = GETUTCDATE()
FROM AemErrorGroup aeg, AemManagedEntityStage ames
WHERE aeg.ErrorGroupManagedEntityId = ames.ErrorGroupManagedEntityId
UPDATE AemErrorGroup
SET SelectedSolutionResponseType = ames.SelectedSolutionResponseType
FROM AemErrorGroup aeg, AemManagedEntityStage ames
WHERE aeg.ErrorGroupManagedEntityId = ames.ErrorGroupManagedEntityId AND
ames.SelectedSolutionResponseType IS NOT NULL AND
len(ames.SelectedSolutionResponseType) > 0
UPDATE AemErrorGroup
SET SelectedCollectionResponseType = ames.SelectedCollectionResponseType
FROM AemErrorGroup aeg, AemManagedEntityStage ames
WHERE aeg.ErrorGroupManagedEntityId = ames.ErrorGroupManagedEntityId AND
ames.SelectedCollectionResponseType IS NOT NULL AND
len(ames.SelectedCollectionResponseType) > 0
UPDATE AemErrorGroup
SET BucketId = ames.BucketId
FROM AemErrorGroup aeg, AemManagedEntityStage ames
WHERE aeg.ErrorGroupManagedEntityId = ames.ErrorGroupManagedEntityId AND
ames.BucketId IS NOT NULL AND
ames.BucketId > 0
UPDATE AemErrorGroup
SET BucketTableId = ames.BucketTableId
FROM AemErrorGroup aeg, AemManagedEntityStage ames
WHERE aeg.ErrorGroupManagedEntityId = ames.ErrorGroupManagedEntityId AND
ames.BucketTableId IS NOT NULL AND
ames.BucketTableId > 0
UPDATE AemErrorGroup
SET MicrosoftResponseUrl = ames.MicrosoftResponseUrl
FROM AemErrorGroup aeg, AemManagedEntityStage ames
WHERE aeg.ErrorGroupManagedEntityId = ames.ErrorGroupManagedEntityId AND
ames.MicrosoftResponseUrl IS NOT NULL AND
len(ames.MicrosoftResponseUrl) > 0
-- *****************************************************
-- now, we remove duplicate entries of Application from staging table and insert into AemApplication
-- then, we remove duplicate entries of Error Groups from staging table and insert into AemErrorGroup
-- finally, we insert blue/system error groups
-- *****************************************************
-- let's remove duplicate entries for AemApplication
CREATE TABLE #AemTempApplication (
RowId int NOT NULL IDENTITY(1, 1)
,DatasetId uniqueidentifier NOT NULL
,ApplicationManagedEntityId uniqueidentifier NOT NULL
,ApplicationName nvarchar(256) NOT NULL
,ApplicationVersion nvarchar(256)
,CompanyName nvarchar(256)
,FriendlyApplicationName nvarchar(256)
)
-- remove application entries we already know about
DELETE #AemTempApplication
WHERE ApplicationManagedEntityId IN (SELECT ApplicationManagedEntityId FROM AemApplication)
INSERT #TempAppRowIds(RowId1, RowId2)
SELECT t1.RowId, t2.RowId FROM #AemTempApplication t1, #AemTempApplication t2
WHERE t1.RowId < t2.RowId
AND t1.ApplicationManagedEntityId = t2.ApplicationManagedEntityId
DELETE #TempAppRowIds
WHERE RowId1 IN (SELECT RowId2 FROM #TempAppRowIds);
WITH NewData(RowId, CompanyName)
AS (
SELECT atmes1.RowId, atmes2.CompanyName
FROM #AemTempApplication atmes1, #AemTempApplication atmes2
WHERE atmes1.RowId in (SELECT RowId1 from #TempAppRowIds)
AND atmes2.RowId in (SELECT RowId2 from #TempAppRowIds WHERE RowId1 = atmes1.RowId)
AND atmes2.CompanyName IS NOT NULL
AND len(atmes2.CompanyName) > 0
)
UPDATE #AemTempApplication
SET CompanyName = nd.CompanyName
FROM #AemTempApplication AS ata
JOIN NewData AS nd ON ata.RowId = nd.RowId;
WITH NewData(RowId, FriendlyApplicationName)
AS (
SELECT atmes1.RowId, atmes2.FriendlyApplicationName
FROM #AemTempApplication atmes1, #AemTempApplication atmes2
WHERE atmes1.RowId in (SELECT RowId1 from #TempAppRowIds)
AND atmes2.RowId in (SELECT RowId2 from #TempAppRowIds WHERE RowId1 = atmes1.RowId)
AND atmes2.FriendlyApplicationName IS NOT NULL
AND len(atmes2.FriendlyApplicationName) > 0
)
UPDATE #AemTempApplication
SET FriendlyApplicationName = nd.FriendlyApplicationName
FROM #AemTempApplication AS ata
JOIN NewData AS nd ON ata.RowId = nd.RowId;
DELETE #AemTempApplication
WHERE RowId in (SELECT RowId2 from #TempAppRowIds);
-- #AemTempApplication contains non-duplicated entries
-- now, we do the insertion into AemApplication
INSERT AemApplication (
ApplicationManagedEntityId
,ApplicationName
,ApplicationVersion
,CompanyName
,FriendlyApplicationName
)
SELECT
ata.ApplicationManagedEntityId
,ata.ApplicationName
,ata.ApplicationVersion
,ata.CompanyName
,ata.FriendlyApplicationName
FROM #AemTempApplication ata
-- *****************************************************
-- Now, we insert values into AemError Group, starting removal of duplicate entries from staging table 1st
-- remove duplicate error group entries:
CREATE TABLE #AemTempErrorGroup (
RowId int NOT NULL IDENTITY(1, 1)
,DatasetId uniqueidentifier NOT NULL
-- delete error groups we already know about
DELETE #AemTempErrorGroup
WHERE ErrorGroupManagedEntityId IN (SELECT ErrorGroupManagedEntityId FROM AemErrorGroup)
INSERT #TempErrorGroupRowIds(RowId1, RowId2)
SELECT t1.RowId, t2.RowId FROM #AemTempErrorGroup t1, #AemTempErrorGroup t2
WHERE t1.RowId < t2.RowId
AND t1.ErrorGroupManagedEntityId = t2.ErrorGroupManagedEntityId
DELETE #TempErrorGroupRowIds
WHERE RowId1 IN (SELECT RowId2 FROM #TempErrorGroupRowIds);
WITH NewData(RowId, SelectedSolutionResponseType)
AS (
SELECT atmes1.RowId, atmes2.SelectedSolutionResponseType
FROM #AemTempErrorGroup atmes1, #AemTempErrorGroup atmes2
WHERE atmes1.RowId in (SELECT RowId1 from #TempErrorGroupRowIds)
AND atmes2.RowId in (SELECT RowId2 from #TempErrorGroupRowIds WHERE RowId1 = atmes1.RowId)
AND atmes2.SelectedSolutionResponseType IS NOT NULL
AND len(atmes2.SelectedSolutionResponseType) > 0
)
UPDATE #AemTempErrorGroup
SET SelectedSolutionResponseType = nd.SelectedSolutionResponseType
FROM #AemTempErrorGroup AS ateg
JOIN NewData AS nd ON ateg.RowId = nd.RowId;
WITH NewData(RowId, SelectedCollectionResponseType)
AS (
SELECT atmes1.RowId, atmes2.SelectedCollectionResponseType
FROM #AemTempErrorGroup atmes1, #AemTempErrorGroup atmes2
WHERE atmes1.RowId in (SELECT RowId1 from #TempErrorGroupRowIds)
AND atmes2.RowId in (SELECT RowId2 from #TempErrorGroupRowIds WHERE RowId1 = atmes1.RowId)
AND atmes2.SelectedCollectionResponseType IS NOT NULL
AND len(atmes2.SelectedCollectionResponseType) > 0
)
UPDATE #AemTempErrorGroup
SET SelectedCollectionResponseType = nd.SelectedCollectionResponseType
FROM #AemTempErrorGroup AS ateg
JOIN NewData AS nd ON ateg.RowId = nd.RowId;
WITH NewData(RowId, BucketId)
AS (
SELECT atmes1.RowId, atmes2.BucketId
FROM #AemTempErrorGroup atmes1, #AemTempErrorGroup atmes2
WHERE atmes1.RowId in (SELECT RowId1 from #TempErrorGroupRowIds)
AND atmes2.RowId in (SELECT RowId2 from #TempErrorGroupRowIds WHERE RowId1 = atmes1.RowId)
AND atmes2.BucketId IS NOT NULL
AND atmes2.BucketId > 0
)
UPDATE #AemTempErrorGroup
SET BucketId = nd.BucketId
FROM #AemTempErrorGroup AS ateg
JOIN NewData AS nd ON ateg.RowId = nd.RowId;
WITH NewData(RowId, BucketTableId)
AS (
SELECT atmes1.RowId, atmes2.BucketTableId
FROM #AemTempErrorGroup atmes1, #AemTempErrorGroup atmes2
WHERE atmes1.RowId in (SELECT RowId1 from #TempErrorGroupRowIds)
AND atmes2.RowId in (SELECT RowId2 from #TempErrorGroupRowIds WHERE RowId1 = atmes1.RowId)
AND atmes2.BucketTableId IS NOT NULL
AND atmes2.BucketTableId > 0
)
UPDATE #AemTempErrorGroup
SET BucketTableId = nd.BucketTableId
FROM #AemTempErrorGroup AS ateg
JOIN NewData AS nd ON ateg.RowId = nd.RowId;
WITH NewData(RowId, MicrosoftResponseUrl)
AS (
SELECT atmes1.RowId, atmes2.MicrosoftResponseUrl
FROM #AemTempErrorGroup atmes1, #AemTempErrorGroup atmes2
WHERE atmes1.RowId in (SELECT RowId1 from #TempErrorGroupRowIds)
AND atmes2.RowId in (SELECT RowId2 from #TempErrorGroupRowIds WHERE RowId1 = atmes1.RowId)
AND atmes2.MicrosoftResponseUrl IS NOT NULL
AND len(atmes2.MicrosoftResponseUrl) > 0
)
UPDATE #AemTempErrorGroup
SET MicrosoftResponseUrl = nd.MicrosoftResponseUrl
FROM #AemTempErrorGroup AS ateg
JOIN NewData AS nd ON ateg.RowId = nd.RowId;
DELETE #AemTempErrorGroup
WHERE RowId in (SELECT RowId2 from #TempErrorGroupRowIds)
-- #AemTempErrorGroup contains non-duplicated entries
-- now, we do the insertion into AemErrorGroup
INSERT AemErrorGroup (
ApplicationRowId
,ErrorGroupManagedEntityId
,BucketType
,Parameter0
,Parameter1
,Parameter2
,Parameter3
,Parameter4
,Parameter5
,Parameter6
,Parameter7
,Parameter8
,Parameter9
,Parameter10
,SelectedSolutionResponseType
,SelectedCollectionResponseType
,BucketId
,BucketTableId
,MicrosoftResponseUrl
)
SELECT
aa.ApplicationRowId
,ames.ErrorGroupManagedEntityId
,ames.BucketType
,ames.Parameter0
,ames.Parameter1
,ames.Parameter2
,ames.Parameter3
,ames.Parameter4
,ames.Parameter5
,ames.Parameter6
,ames.Parameter7
,ames.Parameter8
,ames.Parameter9
,ames.Parameter10
,ames.SelectedSolutionResponseType
,ames.SelectedCollectionResponseType
,ames.BucketId
,ames.BucketTableId
,ames.MicrosoftResponseUrl
FROM #AemTempErrorGroup ames
JOIN AemApplication aa ON (aa.ApplicationManagedEntityId = ames.ApplicationManagedEntityId)
WHERE ames.BucketType <> 'Blue'
-- *****************************************************
-- inserting blue error groups
INSERT AemSystemErrorGroup (
ErrorGroupManagedEntityId
,MicrosoftResponseUrl
)
SELECT
ErrorGroupManagedEntityId
,MicrosoftResponseUrl
FROM AemManagedEntityStage ames
WHERE ames.BucketType = 'Blue'
-- *****************************************************
-- delete from staging table managed entities we inserted
DELETE AemManagedEntityStage
WHERE
BucketType <> 'Blue'
AND ErrorGroupManagedEntityId IN (SELECT ErrorGroupManagedEntityId
FROM AemErrorGroup)
AND ApplicationManagedEntityId IN (SELECT ApplicationManagedEntityId
FROM AemApplication)
DELETE AemManagedEntityStage
WHERE
BucketType = 'Blue'
AND ErrorGroupManagedEntityId IN (SELECT ErrorGroupManagedEntityId
FROM AemSystemErrorGroup)
IF (@DebugLevel > 3)
BEGIN
SET @MessageText = 'Finished inserting AEM managed entity data'
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
GRANT EXECUTE ON AemManagedEntityProcessStaging TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AemProcessStaging
@DatasetId uniqueidentifier
AS
BEGIN
IF EXISTS (SELECT * FROM StandardDataset WHERE SchemaName = 'Aem' AND DatasetId = @DatasetId)
BEGIN
RETURN
END
SET NOCOUNT ON
-- move MEs (error groups and applications from staging table(s)) to domain tables
EXEC dbo.AemManagedEntityProcessStaging @DatasetId
-- move event data from staging to datawarehouse tables
EXEC dbo.AemEventProcessStaging @DatasetId
END
GO
GRANT EXECUTE ON AemProcessStaging TO OpsMgrWriter
GO
SET NOCOUNT ON
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='Aem')
BEGIN
SELECT @SourceTableName = '[AEM].AemRaw_' + sdtm.TableNameSuffix
FROM StandardDataset sd JOIN StandardDatasetTableMap sdtm ON sd.DatasetId = sdtm.DatasetId
WHERE sdtm.AggregationTypeId = 0
AND sd.SchemaName = 'AEM'
SELECT @DestinationTableName = '[CM].CMAemRaw_' + sdtm.TableNameSuffix
FROM StandardDataset sd JOIN StandardDatasetTableMap sdtm ON sd.DatasetId = sdtm.DatasetId
WHERE sdtm.AggregationTypeId = 0
AND sd.SchemaName = 'CM'
EXECUTE ('INSERT INTO ' + @SourceTableName + ' ([DateTime], ErrorGroupRowId, CrashId, AemUserRowId, AemComputerRowId) ' +
' SELECT [DateTime], ErrorGroupRowId, CrashId, AemUserRowId, AemComputerRowId FROM ' + @DestinationTableName +
' WHERE CrashId NOT IN (SELECT CrashId FROM ' + @SourceTableName + ')')
-- setting dirty bit to get aggregations redone as raw data has been transferred
DECLARE @DatasetId uniqueidentifier
SELECT @DatasetId = sd.DatasetId FROM StandardDataset sd WHERE sd.SchemaName = 'CM'
DECLARE @Statement nvarchar(4000)
UPDATE sdah
SET DirtyInd = 1
,DataLastReceivedDateTime = GETUTCDATE()
FROM StandardDatasetAggregationHistory sdah
JOIN StandardDatasetAggregation sda ON (sdah.DatasetId = sda.DatasetId AND sdah.AggregationTypeId = sda.AggregationTypeId)
CROSS JOIN [Aem].vAemRaw e
WHERE (sdah.DatasetId = @DatasetId)
AND (sdah.DirtyInd = 0)
AND (e.[DateTime] >= sdah.AggregationDateTime)
AND (e.[DateTime] < DATEADD(minute, sda.AggregationIntervalDurationMinutes, sdah.AggregationDateTime))
-- deleting old data set aggregations
SELECT @SourceTableName = '[AEM].AemErrorGroupDaily_' + sdtm.TableNameSuffix
FROM StandardDataset sd JOIN StandardDatasetTableMap sdtm ON sd.DatasetId = sdtm.DatasetId
WHERE sdtm.AggregationTypeId = 30
AND sd.SchemaName = 'AEM'
EXECUTE ('DELETE FROM ' + @SourceTableName)
SELECT @SourceTableName = '[AEM].AemApplicationDaily_' + sdtm.TableNameSuffix
FROM StandardDataset sd JOIN StandardDatasetTableMap sdtm ON sd.DatasetId = sdtm.DatasetId
WHERE sdtm.AggregationTypeId = 30
AND sd.SchemaName = 'AEM'
EXECUTE ('DELETE FROM ' + @SourceTableName)
SELECT @SourceTableName = '[AEM].AemDaily_' + sdtm.TableNameSuffix
FROM StandardDataset sd JOIN StandardDatasetTableMap sdtm ON sd.DatasetId = sdtm.DatasetId
WHERE sdtm.AggregationTypeId = 30
AND sd.SchemaName = 'AEM'
EXECUTE ('DELETE FROM ' + @SourceTableName)
SELECT @SourceTableName = '[AEM].AemRaw_' + sdtm.TableNameSuffix
FROM StandardDataset sd JOIN StandardDatasetTableMap sdtm ON sd.DatasetId = sdtm.DatasetId
WHERE sdtm.AggregationTypeId = 0
AND sd.SchemaName = 'AEM'
EXECUTE ('DELETE FROM ' + @SourceTableName)
UPDATE sdas
SET MaxTableRowCount = 10000000
FROM StandardDatasetAggregationStorage sdas
WHERE DatasetId = @DatasetId
COMMIT
END
GO
</Install>
<Uninstall/> <Upgrade>
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AemApplication' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE [dbo].AemApplication
(
ApplicationRowId int NOT NULL IDENTITY(1, 1)
,ApplicationManagedEntityId uniqueidentifier NOT NULL
,ApplicationName nvarchar(256) NOT NULL
,ApplicationVersion nvarchar(256) NOT NULL
,CompanyName nvarchar(256)
,FriendlyApplicationName nvarchar(256)
,LastReceivedDateTime smalldatetime NOT NULL DEFAULT (GETUTCDATE())
)
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AemErrorGroup' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'ErrorGroupId')
BEGIN
ALTER TABLE dbo.AemErrorGroup
DROP COLUMN ErrorGroupId
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AemErrorGroup' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'BucketId')
BEGIN
ALTER TABLE dbo.AemErrorGroup
ADD BucketId bigint
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AemErrorGroup' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'BucketTableId')
BEGIN
ALTER TABLE dbo.AemErrorGroup
ADD BucketTableId bigint
END
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].AemErrorGroup TO OpsMgrWriter
GO
GRANT SELECT ON [dbo].AemErrorGroup TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AemEventStage' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('CREATE TABLE dbo.AemEventStage (
DatasetId uniqueidentifier NOT NULL
,[DateTime] datetime NOT NULL
,CrashId uniqueidentifier NOT NULL
,ErrorGroupManagedEntityId uniqueidentifier NOT NULL
,AemComputerName nvarchar(256)
,AemUserName nvarchar(256)
,CrashTime as [DateTime]
,DWCreatedDataTime datetime NOT NULL DEFAULT (GETUTCDATE())
)')
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].AemEventStage TO OpsMgrWriter
GO
GRANT SELECT ON [dbo].AemEventStage TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AemManagedEntityStage' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.AemManagedEntityStage (
DatasetId uniqueidentifier NOT NULL
,ApplicationManagedEntityId uniqueidentifier NOT NULL
,ApplicationName nvarchar(256) NOT NULL
,ApplicationVersion nvarchar(256)
,CompanyName nvarchar(256)
,FriendlyApplicationName nvarchar(256)
,DWCreatedDataTime datetime NOT NULL DEFAULT (GETUTCDATE())
)
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AemManagedEntityStage' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'ErrorGroupId')
BEGIN
ALTER TABLE dbo.AemManagedEntityStage
DROP COLUMN ErrorGroupId
END
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AemManagedEntityStage' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'BucketId')
BEGIN
ALTER TABLE dbo.AemManagedEntityStage
ADD BucketId bigint
END
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AemManagedEntityStage' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'BucketTableId')
BEGIN
ALTER TABLE dbo.AemManagedEntityStage
ADD BucketTableId bigint
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].AemManagedEntityStage TO OpsMgrWriter
GO
GRANT SELECT ON [dbo].AemManagedEntityStage TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AemSystemErrorGroup' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.AemSystemErrorGroup
(
ErrorGroupRowId int NOT NULL IDENTITY(1, 1)
,ErrorGroupManagedEntityId uniqueidentifier NOT NULL
,BucketType nvarchar(4) NOT NULL DEFAULT('Blue')
,CrashTime datetime
,AemUserRowId int
,AemComputerRowId int
-- ,CabFileName nvarchar(1024)
,MicrosoftResponseUrl nvarchar(256)
/*
,MicrosoftSolutionId int
,MicrosoftGenericSolutionId int
,MicrosoftSolution nvarchar(256)
,MicrosoftGenericSolution nvarchar(256)
,MicrosoftResponseTypeId int
*/
,LastReceivedDateTime smalldatetime NOT NULL DEFAULT (GETUTCDATE())
)
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].AemUser TO OpsMgrWriter
GO
GRANT SELECT ON [dbo].AemUser TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemAggregate')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemAggregate AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[AemAggregate]
@DatasetId uniqueidentifier
,@AggregationTypeId int
,@InsertTableGuid varchar(256)
,@IntervalStartDateTime datetime
,@IntervalEndDateTime datetime
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
BEGIN TRY
IF EXISTS (SELECT * FROM StandardDataset WHERE SchemaName = 'Aem' AND DatasetId = @DatasetId)
BEGIN
RETURN
END
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
BEGIN TRAN
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting aggregation at main level of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ '''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''' UTC to '
+ '''' + CONVERT(varchar(50), @IntervalEndDateTime, 120) + ''' UTC'
SET @AggregationStartDateTime = GETUTCDATE()
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished aggregation at main level of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ '''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''' UTC to '
+ '''' + CONVERT(varchar(50), @IntervalEndDateTime, 120) + ''' UTC'
DECLARE @OperationDurationMs bigint
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @AggregationStartDateTime))
SELECT @CoverRawViewName = QUOTENAME(@SchemaName) + '.v' + BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
-- We'll first finish aggregations for error group, and then we'll start aggregations for application
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting error group aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ '''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''' UTC to '
+ '''' + CONVERT(varchar(50), @IntervalEndDateTime, 120) + ''' UTC'
SET @AggregationStartDateTime = GETUTCDATE()
CREATE TABLE #DailyErrorGroupAggregatedData
(
StartDateTime datetime
,EndDateTime datetime
,ErrorGroupRowId int
,CrashCount int
,UniqueUsersAffectedCount int
,UniqueComputersAffectedCount int
)
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished error group aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ '''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''' UTC to '
+ '''' + CONVERT(varchar(50), @IntervalEndDateTime, 120) + ''' UTC'
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @AggregationStartDateTime))
-- start aggregation for application type, now that aggregation for error group is over
SELECT @InsertTableName = QUOTENAME(@SchemaName) + '.CMAemApplicationDaily_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting application aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ '''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''' UTC to '
+ '''' + CONVERT(varchar(50), @IntervalEndDateTime, 120) + ''' UTC'
CREATE TABLE #DailyApplicationAggregatedData
(
StartDateTime datetime
,EndDateTime datetime
,ApplicationRowId int
,ErrorGroupCount int
,CrashCount int
,UniqueUsersAffectedCount int
,UniqueComputersAffectedCount int
)
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished application aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ '''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''' UTC to '
+ '''' + CONVERT(varchar(50), @IntervalEndDateTime, 120) + ''' UTC'
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
GRANT EXECUTE ON AemAggregate TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemAggregationDelete')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemAggregationDelete AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AemAggregationDelete
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@AggregationDateTime datetime
,@RowsDeleted int OUTPUT
AS
BEGIN
IF EXISTS (SELECT * FROM StandardDataset WHERE SchemaName = 'Aem' AND DatasetId = @DatasetId)
BEGIN
RETURN
END
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
IF (@AggregationTypeId <> 30) RETURN
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
BEGIN TRAN
-- there are dependent tables in aem data set; we'll 1st delete error group aggregation
SELECT @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 1)
AND (BaseTableName = 'CMAemErrorGroupDaily')
-- we'll now delete main aggregation table
SELECT @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
-- 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 AEM data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
GRANT EXECUTE ON AemAggregationDelete TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemEventInsert')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemEventInsert AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AemEventInsert
@ManagementGroupId uniqueidentifier
,@DatasetId uniqueidentifier
,@DataXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group permissions
SELECT @ManagementGroupRowId = ManagementGroupRowId
FROM vManagementGroup
WHERE (ManagementGroupGuid = @ManagementGroupId)
AND (WriterLoginName = SUSER_NAME())
IF (@ManagementGroupRowId IS NULL)
BEGIN
RAISERROR(777970003, 16, 1)
RETURN -1
END
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@ResultingState int
SET @ResultingState = 1
BEGIN TRY
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @DataXml
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'CM', @ExecResult)
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 AemEventInsert TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemEventProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemEventProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AemEventProcessStaging
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure sysname
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
DECLARE
@InsertTableName sysname
,@InsertTableGuid uniqueidentifier
,@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@ExecResult int
,@TempTableCreated bit
,@DebugLevel int
,@LockResourceName sysname
,@MessageText nvarchar(max)
,@OperationDurationMs bigint
,@SchemaName sysname
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
BEGIN TRAN
-- remove duplicate data
SELECT @Statement = 'DELETE e'
+ ' FROM AemEventStage e, ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' i'
+ ' WHERE (i.CrashId = e.CrashId)'
EXECUTE (@Statement)
-- creating temp table
CREATE TABLE #Event
(
ErrorGroupManagedEntityId uniqueidentifier NOT NULL
,[DateTime] datetime NOT NULL
,ErrorGroupRowId int
,CrashId uniqueidentifier NOT NULL
,CrashTime as [DateTime]
,AemUserName nvarchar(256)
,AemUserRowId int
,AemComputerName nvarchar(256)
,AemComputerRowId int
,WatsonBucket bit
)
-- getting data ready to be moved to CMAemRaw in temp table
INSERT #Event (
ErrorGroupManagedEntityId
,[DateTime]
,ErrorGroupRowId
,CrashId
,AemUserName
,AemUserRowId
,AemComputerName
,AemComputerRowId
,WatsonBucket
)
SELECT
aes.ErrorGroupManagedEntityId
,aes.[DateTime]
,aeg.ErrorGroupRowId
,aes.CrashId
,aes.AemUserName
,au.AemUserRowId
,aes.AemComputerName
,ac.AemComputerRowId
,1
FROM AemEventStage aes
JOIN AemErrorGroup aeg ON (aeg.ErrorGroupManagedEntityId = aes.ErrorGroupManagedEntityId)
LEFT JOIN AemUser au ON (au.AemUserName = aes.AemUserName)
LEFT JOIN AemComputer ac ON (ac.AemComputerName = aes.AemComputerName)
-- getting data ready to be moved to CMAemRaw in temp table
INSERT #Event (
ErrorGroupManagedEntityId
,[DateTime]
,ErrorGroupRowId
,CrashId
,AemUserName
,AemUserRowId
,AemComputerName
,AemComputerRowId
,WatsonBucket
)
SELECT
aes.ErrorGroupManagedEntityId
,aes.[DateTime]
,aseg.ErrorGroupRowId
,aes.CrashId
,aes.AemUserName
,au.AemUserRowId
,aes.AemComputerName
,ac.AemComputerRowId
,0
FROM AemEventStage aes
JOIN AemSystemErrorGroup aseg ON (aseg.ErrorGroupManagedEntityId = aes.ErrorGroupManagedEntityId)
LEFT JOIN AemUser au ON (au.AemUserName = aes.AemUserName)
LEFT JOIN AemComputer ac ON (ac.AemComputerName = aes.AemComputerName)
-- we will not insert data whose Error Group's are not known yet
DELETE #Event
WHERE ErrorGroupRowId IS NULL
UPDATE au
SET LastReceivedDateTime = GETUTCDATE()
FROM AemUser au
JOIN #Event e ON (au.AemUserRowId = e.AemUserRowId)
WHERE (DATEADD(day, 1, au.LastReceivedDateTime) < GETUTCDATE())
INSERT AemUser (AemUserName)
SELECT DISTINCT e.AemUserName COLLATE database_default
FROM #Event e
WHERE 0 = (SELECT COUNT(*) FROM AemUser au WHERE (au.AemUserName = e.AemUserName COLLATE database_default))
AND e.AemUserName IS NOT NULL
UPDATE e
SET e.AemUserRowId = au.AemUserRowId
FROM #Event e JOIN AemUser au ON (e.AemUserName = au.AemUserName COLLATE database_default)
WHERE (e.AemUserRowId IS NULL)
UPDATE ac
SET LastReceivedDateTime = GETUTCDATE()
FROM AemComputer ac
JOIN #Event e ON (ac.AemComputerRowId = e.AemComputerRowId)
WHERE (DATEADD(day, 1, ac.LastReceivedDateTime) < GETUTCDATE())
INSERT AemComputer (AemComputerName)
SELECT DISTINCT e.AemComputerName COLLATE database_default
FROM #Event e
WHERE 0 = (SELECT COUNT(*) FROM AemComputer ac WHERE (ac.AemComputerName = e.AemComputerName COLLATE database_default))
AND e.AemComputerName IS NOT NULL
UPDATE e
SET e.AemComputerRowId = ac.AemComputerRowId
FROM #Event e JOIN AemComputer ac ON (e.AemComputerName = ac.AemComputerName COLLATE database_default)
WHERE (e.AemComputerRowId IS NULL)
-- remove duplicate data that we recevied via Check-For-Solution feature
-- that has CrashId probably different
-- we start with watson bucket 1st
SELECT @Statement = 'DELETE aes'
+ ' FROM AemEventStage aes, '
+ QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' i, '
+ ' #Event e '
+ ' WHERE (aes.CrashId = e.CrashId) '
+ ' AND (e.WatsonBucket = 1) '
+ ' AND (e.AemComputerRowId = i.AemComputerRowId '
+ ' AND e.AemUserRowId = i.AemUserRowId '
+ ' AND e.ErrorGroupRowId = i.ErrorGroupRowId '
+ ' AND e.CrashTime = i.CrashTime)'
EXECUTE (@Statement)
-- now, system bucket
SELECT @Statement = 'DELETE aes'
+ ' FROM AemEventStage aes, '
+ ' AemSystemErrorGroup aseg, '
+ ' #Event e '
+ ' WHERE (aes.CrashId = e.CrashId) '
+ ' AND (e.WatsonBucket = 0) '
+ ' AND (e.AemComputerRowId = aseg.AemComputerRowId '
+ ' AND e.AemUserRowId = aseg.AemUserRowId '
+ ' AND e.ErrorGroupRowId = aseg.ErrorGroupRowId '
+ ' AND e.CrashTime = aseg.CrashTime)'
EXECUTE (@Statement)
-- we deleted above data from AemEventStage, we need to delete data from #Event too
SELECT @Statement = 'DELETE e'
+ ' FROM #Event e '
+ ' WHERE e.CrashId NOT IN (SELECT CrashId FROM AemEventStage) '
EXECUTE (@Statement)
-- ***************************************************************
-- lock table map to ensure we insert into table opened for insertion
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_TableMap'
IF (@DebugLevel > 3)
BEGIN
SELECT @MessageText = 'Starting to insert ' + CAST(COUNT(*) AS varchar(30)) + ' AEM event data into table set with GUID ' + CAST(@InsertTableGuid AS varchar(100))
FROM #Event
-- mark existing affected aggregations as dirty
UPDATE sdah
SET DirtyInd = 1
,DataLastReceivedDateTime = GETUTCDATE()
FROM StandardDatasetAggregationHistory sdah
JOIN StandardDatasetAggregation sda ON (sdah.DatasetId = sda.DatasetId AND sdah.AggregationTypeId = sda.AggregationTypeId)
CROSS JOIN #Event e
WHERE (sdah.DatasetId = @DatasetId)
AND (sdah.DirtyInd = 0)
AND (e.[DateTime] >= sdah.AggregationDateTime)
AND (e.[DateTime] < DATEADD(minute, sda.AggregationIntervalDurationMinutes, sdah.AggregationDateTime))
AND (e.WatsonBucket = 1)
-- let's deal with system bucket data now
-- for system bucket: the event details map to system error group
-- the event data did not make into AemRaw table
UPDATE aseg
SET aseg.CrashTime = e.[DateTime]
,aseg.AemUserRowId = e.AemUserRowId
,aseg.AemComputerRowId = e.AemComputerRowId
--,aseg.CabFileName =
FROM AemSystemErrorGroup aseg
JOIN #Event e ON (aseg.ErrorGroupManagedEntityId = e.ErrorGroupManagedEntityId)
-- remove inserted systemBucket related data
SELECT @Statement = 'DELETE aes'
+ ' FROM AemEventStage aes'
+ ' JOIN #Event e ON (e.ErrorGroupManagedEntityId = aes.ErrorGroupManagedEntityId)'
EXECUTE (@Statement)
-- remove inserted data
SELECT @Statement =
'DELETE aes '
+ 'FROM AemEventStage aes '
+ ' JOIN #Event e ON (aes.CrashId = e.CrashId)'
EXECUTE (@Statement)
IF (@DebugLevel > 3)
BEGIN
SET @MessageText = 'Finished inserting AEM event data into table set with GUID ' + CAST(@InsertTableGuid AS varchar(100))
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to insert AEM event data into AEM 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
GRANT EXECUTE ON AemEventProcessStaging TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemGetDailyAggregationDate')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemGetDailyAggregationDate AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[AemGetDailyAggregationDate]
@InDate datetime
,@OutDate datetime out
AS
BEGIN
SET NOCOUNT ON
SET @OutDate = CAST(CONVERT(char(8), @InDate, 112) AS DateTime)
RETURN 1
END
GO
GRANT EXECUTE ON AemGetDailyAggregationDate TO OpsMgrWriter
GO
GRANT EXECUTE ON AemGetDailyAggregationDate TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AemGroom
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@CutoffDateTime datetime
,@MaxRowsToGroom int
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT * FROM StandardDataset WHERE SchemaName = 'Aem' AND DatasetId = @DatasetId)
BEGIN
RETURN
END
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 Aem 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
IF (@AggregationTypeId = 30)
BEGIN
SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('CMAemApplicationDaily' + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', ''))
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT + @RowsDeleted
SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('CMAemErrorGroupDaily' + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', ''))
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT + @RowsDeleted
END
-- groom dynamic dimensions
DECLARE @MaxDataAgeDays int
SELECT @MaxDataAgeDays = MAX(MaxDataAgeDays)
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
DELETE aseg
FROM AemSystemErrorGroup aseg
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE au
FROM AemUser au
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE ac
FROM AemComputer ac
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE aeg
FROM AemErrorGroup aeg
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE aa
FROM AemApplication aa
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
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 Aem 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] = 'AemManagedEntityInsert')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemManagedEntityInsert AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AemManagedEntityInsert
@ManagementGroupId uniqueidentifier
,@DatasetId uniqueidentifier
,@DataXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group permissions
SELECT @ManagementGroupRowId = ManagementGroupRowId
FROM vManagementGroup
WHERE (ManagementGroupGuid = @ManagementGroupId)
AND (WriterLoginName = SUSER_NAME())
IF (@ManagementGroupRowId IS NULL)
BEGIN
RAISERROR(777970003, 16, 1)
RETURN -1
END
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@ResultingState int
SET @ResultingState = 1
BEGIN TRY
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @DataXml
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'CM', @ExecResult)
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 AemManagedEntityInsert TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemManagedEntityProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemManagedEntityProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AemManagedEntityProcessStaging
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure sysname
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
DECLARE
@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@ExecResult int
,@DebugLevel int
,@LockResourceName sysname
,@MessageText nvarchar(max)
,@OperationDurationMs bigint
,@SchemaName sysname
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
BEGIN TRAN
IF (@DebugLevel > 3)
BEGIN
SELECT @MessageText = 'Starting to insert ' + CAST(COUNT(*) AS varchar(30)) + ' managed entity data'
FROM AemManagedEntityStage
-- *****************************************************
-- update non-key properties of ME(s) we already know
-- start with AemApplication
UPDATE AemApplication
SET LastReceivedDateTime = GETUTCDATE()
FROM AemApplication aa, AemManagedEntityStage ames
WHERE aa.ApplicationManagedEntityId = ames.ApplicationManagedEntityId
UPDATE AemApplication
SET CompanyName = ames.CompanyName
FROM AemApplication aa, AemManagedEntityStage ames
WHERE aa.ApplicationManagedEntityId = ames.ApplicationManagedEntityId AND
ames.CompanyName IS NOT NULL AND
len(ames.CompanyName) > 0
UPDATE AemApplication
SET FriendlyApplicationName = ames.FriendlyApplicationName
FROM AemApplication aa, AemManagedEntityStage ames
WHERE aa.ApplicationManagedEntityId = ames.ApplicationManagedEntityId AND
ames.FriendlyApplicationName IS NOT NULL AND
len(ames.FriendlyApplicationName) > 0
-- update AemErrorGroup table with entries that already exist
UPDATE AemErrorGroup
SET LastReceivedDateTime = GETUTCDATE()
FROM AemErrorGroup aeg, AemManagedEntityStage ames
WHERE aeg.ErrorGroupManagedEntityId = ames.ErrorGroupManagedEntityId
UPDATE AemErrorGroup
SET SelectedSolutionResponseType = ames.SelectedSolutionResponseType
FROM AemErrorGroup aeg, AemManagedEntityStage ames
WHERE aeg.ErrorGroupManagedEntityId = ames.ErrorGroupManagedEntityId AND
ames.SelectedSolutionResponseType IS NOT NULL AND
len(ames.SelectedSolutionResponseType) > 0
UPDATE AemErrorGroup
SET SelectedCollectionResponseType = ames.SelectedCollectionResponseType
FROM AemErrorGroup aeg, AemManagedEntityStage ames
WHERE aeg.ErrorGroupManagedEntityId = ames.ErrorGroupManagedEntityId AND
ames.SelectedCollectionResponseType IS NOT NULL AND
len(ames.SelectedCollectionResponseType) > 0
UPDATE AemErrorGroup
SET BucketId = ames.BucketId
FROM AemErrorGroup aeg, AemManagedEntityStage ames
WHERE aeg.ErrorGroupManagedEntityId = ames.ErrorGroupManagedEntityId AND
ames.BucketId IS NOT NULL AND
ames.BucketId > 0
UPDATE AemErrorGroup
SET BucketTableId = ames.BucketTableId
FROM AemErrorGroup aeg, AemManagedEntityStage ames
WHERE aeg.ErrorGroupManagedEntityId = ames.ErrorGroupManagedEntityId AND
ames.BucketTableId IS NOT NULL AND
ames.BucketTableId > 0
UPDATE AemErrorGroup
SET MicrosoftResponseUrl = ames.MicrosoftResponseUrl
FROM AemErrorGroup aeg, AemManagedEntityStage ames
WHERE aeg.ErrorGroupManagedEntityId = ames.ErrorGroupManagedEntityId AND
ames.MicrosoftResponseUrl IS NOT NULL AND
len(ames.MicrosoftResponseUrl) > 0
-- *****************************************************
-- now, we remove duplicate entries of Application from staging table and insert into AemApplication
-- then, we remove duplicate entries of Error Groups from staging table and insert into AemErrorGroup
-- finally, we insert blue/system error groups
-- *****************************************************
-- let's remove duplicate entries for AemApplication
CREATE TABLE #AemTempApplication (
RowId int NOT NULL IDENTITY(1, 1)
,DatasetId uniqueidentifier NOT NULL
,ApplicationManagedEntityId uniqueidentifier NOT NULL
,ApplicationName nvarchar(256) NOT NULL
,ApplicationVersion nvarchar(256)
,CompanyName nvarchar(256)
,FriendlyApplicationName nvarchar(256)
)
-- remove application entries we already know about
DELETE #AemTempApplication
WHERE ApplicationManagedEntityId IN (SELECT ApplicationManagedEntityId FROM AemApplication)
INSERT #TempAppRowIds(RowId1, RowId2)
SELECT t1.RowId, t2.RowId FROM #AemTempApplication t1, #AemTempApplication t2
WHERE t1.RowId < t2.RowId
AND t1.ApplicationManagedEntityId = t2.ApplicationManagedEntityId
DELETE #TempAppRowIds
WHERE RowId1 IN (SELECT RowId2 FROM #TempAppRowIds);
WITH NewData(RowId, CompanyName)
AS (
SELECT atmes1.RowId, atmes2.CompanyName
FROM #AemTempApplication atmes1, #AemTempApplication atmes2
WHERE atmes1.RowId in (SELECT RowId1 from #TempAppRowIds)
AND atmes2.RowId in (SELECT RowId2 from #TempAppRowIds WHERE RowId1 = atmes1.RowId)
AND atmes2.CompanyName IS NOT NULL
AND len(atmes2.CompanyName) > 0
)
UPDATE #AemTempApplication
SET CompanyName = nd.CompanyName
FROM #AemTempApplication AS ata
JOIN NewData AS nd ON ata.RowId = nd.RowId;
WITH NewData(RowId, FriendlyApplicationName)
AS (
SELECT atmes1.RowId, atmes2.FriendlyApplicationName
FROM #AemTempApplication atmes1, #AemTempApplication atmes2
WHERE atmes1.RowId in (SELECT RowId1 from #TempAppRowIds)
AND atmes2.RowId in (SELECT RowId2 from #TempAppRowIds WHERE RowId1 = atmes1.RowId)
AND atmes2.FriendlyApplicationName IS NOT NULL
AND len(atmes2.FriendlyApplicationName) > 0
)
UPDATE #AemTempApplication
SET FriendlyApplicationName = nd.FriendlyApplicationName
FROM #AemTempApplication AS ata
JOIN NewData AS nd ON ata.RowId = nd.RowId;
DELETE #AemTempApplication
WHERE RowId in (SELECT RowId2 from #TempAppRowIds);
-- #AemTempApplication contains non-duplicated entries
-- now, we do the insertion into AemApplication
INSERT AemApplication (
ApplicationManagedEntityId
,ApplicationName
,ApplicationVersion
,CompanyName
,FriendlyApplicationName
)
SELECT
ata.ApplicationManagedEntityId
,ata.ApplicationName
,ata.ApplicationVersion
,ata.CompanyName
,ata.FriendlyApplicationName
FROM #AemTempApplication ata
-- *****************************************************
-- Now, we insert values into AemError Group, starting removal of duplicate entries from staging table 1st
-- remove duplicate error group entries:
CREATE TABLE #AemTempErrorGroup (
RowId int NOT NULL IDENTITY(1, 1)
,DatasetId uniqueidentifier NOT NULL
-- delete error groups we already know about
DELETE #AemTempErrorGroup
WHERE ErrorGroupManagedEntityId IN (SELECT ErrorGroupManagedEntityId FROM AemErrorGroup)
INSERT #TempErrorGroupRowIds(RowId1, RowId2)
SELECT t1.RowId, t2.RowId FROM #AemTempErrorGroup t1, #AemTempErrorGroup t2
WHERE t1.RowId < t2.RowId
AND t1.ErrorGroupManagedEntityId = t2.ErrorGroupManagedEntityId
DELETE #TempErrorGroupRowIds
WHERE RowId1 IN (SELECT RowId2 FROM #TempErrorGroupRowIds);
WITH NewData(RowId, SelectedSolutionResponseType)
AS (
SELECT atmes1.RowId, atmes2.SelectedSolutionResponseType
FROM #AemTempErrorGroup atmes1, #AemTempErrorGroup atmes2
WHERE atmes1.RowId in (SELECT RowId1 from #TempErrorGroupRowIds)
AND atmes2.RowId in (SELECT RowId2 from #TempErrorGroupRowIds WHERE RowId1 = atmes1.RowId)
AND atmes2.SelectedSolutionResponseType IS NOT NULL
AND len(atmes2.SelectedSolutionResponseType) > 0
)
UPDATE #AemTempErrorGroup
SET SelectedSolutionResponseType = nd.SelectedSolutionResponseType
FROM #AemTempErrorGroup AS ateg
JOIN NewData AS nd ON ateg.RowId = nd.RowId;
WITH NewData(RowId, SelectedCollectionResponseType)
AS (
SELECT atmes1.RowId, atmes2.SelectedCollectionResponseType
FROM #AemTempErrorGroup atmes1, #AemTempErrorGroup atmes2
WHERE atmes1.RowId in (SELECT RowId1 from #TempErrorGroupRowIds)
AND atmes2.RowId in (SELECT RowId2 from #TempErrorGroupRowIds WHERE RowId1 = atmes1.RowId)
AND atmes2.SelectedCollectionResponseType IS NOT NULL
AND len(atmes2.SelectedCollectionResponseType) > 0
)
UPDATE #AemTempErrorGroup
SET SelectedCollectionResponseType = nd.SelectedCollectionResponseType
FROM #AemTempErrorGroup AS ateg
JOIN NewData AS nd ON ateg.RowId = nd.RowId;
WITH NewData(RowId, BucketId)
AS (
SELECT atmes1.RowId, atmes2.BucketId
FROM #AemTempErrorGroup atmes1, #AemTempErrorGroup atmes2
WHERE atmes1.RowId in (SELECT RowId1 from #TempErrorGroupRowIds)
AND atmes2.RowId in (SELECT RowId2 from #TempErrorGroupRowIds WHERE RowId1 = atmes1.RowId)
AND atmes2.BucketId IS NOT NULL
AND atmes2.BucketId > 0
)
UPDATE #AemTempErrorGroup
SET BucketId = nd.BucketId
FROM #AemTempErrorGroup AS ateg
JOIN NewData AS nd ON ateg.RowId = nd.RowId;
WITH NewData(RowId, BucketTableId)
AS (
SELECT atmes1.RowId, atmes2.BucketTableId
FROM #AemTempErrorGroup atmes1, #AemTempErrorGroup atmes2
WHERE atmes1.RowId in (SELECT RowId1 from #TempErrorGroupRowIds)
AND atmes2.RowId in (SELECT RowId2 from #TempErrorGroupRowIds WHERE RowId1 = atmes1.RowId)
AND atmes2.BucketTableId IS NOT NULL
AND atmes2.BucketTableId > 0
)
UPDATE #AemTempErrorGroup
SET BucketTableId = nd.BucketTableId
FROM #AemTempErrorGroup AS ateg
JOIN NewData AS nd ON ateg.RowId = nd.RowId;
WITH NewData(RowId, MicrosoftResponseUrl)
AS (
SELECT atmes1.RowId, atmes2.MicrosoftResponseUrl
FROM #AemTempErrorGroup atmes1, #AemTempErrorGroup atmes2
WHERE atmes1.RowId in (SELECT RowId1 from #TempErrorGroupRowIds)
AND atmes2.RowId in (SELECT RowId2 from #TempErrorGroupRowIds WHERE RowId1 = atmes1.RowId)
AND atmes2.MicrosoftResponseUrl IS NOT NULL
AND len(atmes2.MicrosoftResponseUrl) > 0
)
UPDATE #AemTempErrorGroup
SET MicrosoftResponseUrl = nd.MicrosoftResponseUrl
FROM #AemTempErrorGroup AS ateg
JOIN NewData AS nd ON ateg.RowId = nd.RowId;
DELETE #AemTempErrorGroup
WHERE RowId in (SELECT RowId2 from #TempErrorGroupRowIds)
-- #AemTempErrorGroup contains non-duplicated entries
-- now, we do the insertion into AemErrorGroup
INSERT AemErrorGroup (
ApplicationRowId
,ErrorGroupManagedEntityId
,BucketType
,Parameter0
,Parameter1
,Parameter2
,Parameter3
,Parameter4
,Parameter5
,Parameter6
,Parameter7
,Parameter8
,Parameter9
,Parameter10
,SelectedSolutionResponseType
,SelectedCollectionResponseType
,BucketId
,BucketTableId
,MicrosoftResponseUrl
)
SELECT
aa.ApplicationRowId
,ames.ErrorGroupManagedEntityId
,ames.BucketType
,ames.Parameter0
,ames.Parameter1
,ames.Parameter2
,ames.Parameter3
,ames.Parameter4
,ames.Parameter5
,ames.Parameter6
,ames.Parameter7
,ames.Parameter8
,ames.Parameter9
,ames.Parameter10
,ames.SelectedSolutionResponseType
,ames.SelectedCollectionResponseType
,ames.BucketId
,ames.BucketTableId
,ames.MicrosoftResponseUrl
FROM #AemTempErrorGroup ames
JOIN AemApplication aa ON (aa.ApplicationManagedEntityId = ames.ApplicationManagedEntityId)
WHERE ames.BucketType <> 'Blue'
-- *****************************************************
-- inserting blue error groups
INSERT AemSystemErrorGroup (
ErrorGroupManagedEntityId
,MicrosoftResponseUrl
)
SELECT
ErrorGroupManagedEntityId
,MicrosoftResponseUrl
FROM AemManagedEntityStage ames
WHERE ames.BucketType = 'Blue'
-- *****************************************************
-- delete from staging table managed entities we inserted
DELETE AemManagedEntityStage
WHERE
BucketType <> 'Blue'
AND ErrorGroupManagedEntityId IN (SELECT ErrorGroupManagedEntityId
FROM AemErrorGroup)
AND ApplicationManagedEntityId IN (SELECT ApplicationManagedEntityId
FROM AemApplication)
DELETE AemManagedEntityStage
WHERE
BucketType = 'Blue'
AND ErrorGroupManagedEntityId IN (SELECT ErrorGroupManagedEntityId
FROM AemSystemErrorGroup)
IF (@DebugLevel > 3)
BEGIN
SET @MessageText = 'Finished inserting AEM managed entity data'
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
GRANT EXECUTE ON AemManagedEntityProcessStaging TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND [name] = 'AemProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.AemProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.AemProcessStaging
@DatasetId uniqueidentifier
AS
BEGIN
IF EXISTS (SELECT * FROM StandardDataset WHERE SchemaName = 'Aem' AND DatasetId = @DatasetId)
BEGIN
RETURN
END
SET NOCOUNT ON
-- move MEs (error groups and applications from staging table(s)) to domain tables
EXEC dbo.AemManagedEntityProcessStaging @DatasetId
-- move event data from staging to datawarehouse tables
EXEC dbo.AemEventProcessStaging @DatasetId
END
GO
GRANT EXECUTE ON AemProcessStaging TO OpsMgrWriter
GO
SET NOCOUNT ON
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='Aem')
BEGIN
SELECT @SourceTableName = '[AEM].AemRaw_' + sdtm.TableNameSuffix
FROM StandardDataset sd JOIN StandardDatasetTableMap sdtm ON sd.DatasetId = sdtm.DatasetId
WHERE sdtm.AggregationTypeId = 0
AND sd.SchemaName = 'AEM'
SELECT @DestinationTableName = '[CM].CMAemRaw_' + sdtm.TableNameSuffix
FROM StandardDataset sd JOIN StandardDatasetTableMap sdtm ON sd.DatasetId = sdtm.DatasetId
WHERE sdtm.AggregationTypeId = 0
AND sd.SchemaName = 'CM'
EXECUTE ('INSERT INTO ' + @SourceTableName + ' ([DateTime], ErrorGroupRowId, CrashId, AemUserRowId, AemComputerRowId) ' +
' SELECT [DateTime], ErrorGroupRowId, CrashId, AemUserRowId, AemComputerRowId FROM ' + @DestinationTableName +
' WHERE CrashId NOT IN (SELECT CrashId FROM ' + @SourceTableName + ')')
-- setting dirty bit to get aggregations redone as raw data has been transferred
DECLARE @DatasetId uniqueidentifier
SELECT @DatasetId = sd.DatasetId FROM StandardDataset sd WHERE sd.SchemaName = 'CM'
DECLARE @Statement nvarchar(4000)
UPDATE sdah
SET DirtyInd = 1
,DataLastReceivedDateTime = GETUTCDATE()
FROM StandardDatasetAggregationHistory sdah
JOIN StandardDatasetAggregation sda ON (sdah.DatasetId = sda.DatasetId AND sdah.AggregationTypeId = sda.AggregationTypeId)
CROSS JOIN [Aem].vAemRaw e
WHERE (sdah.DatasetId = @DatasetId)
AND (sdah.DirtyInd = 0)
AND (e.[DateTime] >= sdah.AggregationDateTime)
AND (e.[DateTime] < DATEADD(minute, sda.AggregationIntervalDurationMinutes, sdah.AggregationDateTime))
-- deleting old data set aggregations
SELECT @SourceTableName = '[AEM].AemErrorGroupDaily_' + sdtm.TableNameSuffix
FROM StandardDataset sd JOIN StandardDatasetTableMap sdtm ON sd.DatasetId = sdtm.DatasetId
WHERE sdtm.AggregationTypeId = 30
AND sd.SchemaName = 'AEM'
EXECUTE ('DELETE FROM ' + @SourceTableName)
SELECT @SourceTableName = '[AEM].AemApplicationDaily_' + sdtm.TableNameSuffix
FROM StandardDataset sd JOIN StandardDatasetTableMap sdtm ON sd.DatasetId = sdtm.DatasetId
WHERE sdtm.AggregationTypeId = 30
AND sd.SchemaName = 'AEM'
EXECUTE ('DELETE FROM ' + @SourceTableName)
SELECT @SourceTableName = '[AEM].AemDaily_' + sdtm.TableNameSuffix
FROM StandardDataset sd JOIN StandardDatasetTableMap sdtm ON sd.DatasetId = sdtm.DatasetId
WHERE sdtm.AggregationTypeId = 30
AND sd.SchemaName = 'AEM'
EXECUTE ('DELETE FROM ' + @SourceTableName)
SELECT @SourceTableName = '[AEM].AemRaw_' + sdtm.TableNameSuffix
FROM StandardDataset sd JOIN StandardDatasetTableMap sdtm ON sd.DatasetId = sdtm.DatasetId
WHERE sdtm.AggregationTypeId = 0
AND sd.SchemaName = 'AEM'
EXECUTE ('DELETE FROM ' + @SourceTableName)
UPDATE sdas
SET MaxTableRowCount = 10000000
FROM StandardDatasetAggregationStorage sdas
WHERE DatasetId = @DatasetId
Home
ENU