INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
VALUES
(
'$Config/DatasetId$'
,0
,'Event'
,0
,'
CREATE TABLE [Event].[Event_$Guid$]
(
EventRowId int NOT NULL IDENTITY(1, 1)
,EventOriginId uniqueidentifier NOT NULL
,[DateTime] datetime NOT NULL
,EventPublisherRowId int NOT NULL
,EventChannelRowId smallint NOT NULL
,EventCategoryRowId int NOT NULL
,EventLevelId tinyint NOT NULL
,LoggingComputerRowId int NOT NULL
,EventNumber bigint NOT NULL
,EventDisplayNumber int NOT NULL
,UserNameRowId int NOT NULL
,RawDescriptionHash uniqueidentifier NULL
,ParameterHash uniqueidentifier NULL
,EventDataHash uniqueidentifier NULL
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE [name] = 'Event')
BEGIN
EXECUTE('CREATE SCHEMA Event')
END
GO
-- change schema ownership to allow index rebuild and reorganize
ALTER AUTHORIZATION ON SCHEMA::Event TO OpsMgrWriter
GO
-- change schema alter to allow domain table index rebuild
GRANT ALTER ON SCHEMA::dbo TO OpsMgrWriter
GO
-- grant create table permissions
GRANT CREATE TABLE TO OpsMgrWriter
GO
EXEC StandardDatasetAllocateStorage @DatasetId = '$Config/DatasetId$', @AggregationTypeId = 0
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventCategory' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.EventCategory
END
GO
CREATE TABLE dbo.EventCategory
(
EventCategoryRowId int NOT NULL IDENTITY(1,1)
,EventPublisherRowId int NOT NULL
,EventCategoryId int NOT NULL
,EventCategoryTitle nvarchar(255) NOT NULL
,LastReceivedDateTime smalldatetime NOT NULL DEFAULT (GETUTCDATE())
CREATE INDEX IX_EventCategory_LastReceivedDateTime ON EventCategory (LastReceivedDateTime)
GO
EXEC DomainTableRegisterIndexOptimization
@TableName = 'EventCategory'
,@DatasetId = '$Config/DatasetId$'
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventChannel' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.EventChannel
END
GO
CREATE TABLE dbo.EventChannel
(
EventChannelRowId smallint NOT NULL IDENTITY(1,1)
,EventChannelTitle nvarchar(255) NOT NULL
,LastReceivedDateTime smalldatetime NOT NULL DEFAULT (GETUTCDATE())
CREATE INDEX IX_EventChannel_LastReceivedDateTime ON EventChannel (LastReceivedDateTime)
GO
EXEC DomainTableRegisterIndexOptimization
@TableName = 'EventChannel'
,@DatasetId = '$Config/DatasetId$'
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventLevel' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.EventLevel
END
GO
CREATE TABLE dbo.EventLevel
(
EventLevelId tinyint NOT NULL
,EventLevelTitle nvarchar(255) NOT NULL
,CONSTRAINT PK_EventLevel PRIMARY KEY CLUSTERED (EventLevelId)
)
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventLoggingComputer' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.EventLoggingComputer
END
GO
CREATE TABLE dbo.EventLoggingComputer
(
EventLoggingComputerRowId int NOT NULL IDENTITY(1,1)
,ComputerName nvarchar(255) NOT NULL
,LastReceivedDateTime smalldatetime NOT NULL DEFAULT (GETUTCDATE())
CREATE INDEX IX_EventLoggingComputer_LastReceivedDateTime ON EventLoggingComputer (LastReceivedDateTime)
GO
EXEC DomainTableRegisterIndexOptimization
@TableName = 'EventLoggingComputer'
,@DatasetId = '$Config/DatasetId$'
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventPublisher' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.EventPublisher
END
GO
CREATE TABLE dbo.EventPublisher
(
EventPublisherRowId int NOT NULL IDENTITY(1,1)
,EventPublisherGuid uniqueidentifier NOT NULL
,EventPublisherName nvarchar(255) NOT NULL
,LastReceivedDateTime smalldatetime NOT NULL DEFAULT (GETUTCDATE())
CREATE INDEX IX_EventPublisher_LastReceivedDateTime ON EventPublisher (LastReceivedDateTime)
GO
EXEC DomainTableRegisterIndexOptimization
@TableName = 'EventPublisher'
,@DatasetId = '$Config/DatasetId$'
GO
-- insert staging definition into StandardDatasetStagingArea
BEGIN TRAN
IF EXISTS (SELECT * FROM StandardDatasetStagingArea WHERE DatasetId = '$Config/DatasetId$')
BEGIN
DELETE StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$'
END
INSERT StandardDatasetStagingArea
(
DatasetId
,StagingTableColumnDefinition
,MaxRowsToProcessPerTransactionCount
,BatchedProcessingSupportedInd
)
VALUES
(
'$Config/DatasetId$'
,'
DatasetId uniqueidentifier NOT NULL
,ManagementGroupGuid uniqueidentifier NOT NULL
,EventOriginId uniqueidentifier NOT NULL
,[DateTime] datetime NOT NULL
,RuleRowId int NOT NULL
,ManagedEntityRowId int NOT NULL
,EventPublisherRowId int NOT NULL
,EventChannelRowId smallint NOT NULL
,EventCategoryRowId int NOT NULL
,EventLevelId tinyint NOT NULL
,LoggingComputerRowId int NOT NULL
,EventDisplayNumber int NOT NULL
,EventNumber bigint NOT NULL
,UserNameRowId int NOT NULL
,RawDescription nvarchar(max) NULL
,RenderedDescription nvarchar(max) NULL
,EventParameters xml NULL
,EventData xml NULL
,RawDescriptionHash uniqueidentifier NULL
,ParameterHash uniqueidentifier NULL
,EventDataHash uniqueidentifier NULL
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventStage' AND TABLE_SCHEMA = 'Event')
BEGIN
DECLARE @Statement nvarchar(max)
SELECT @Statement = 'CREATE TABLE Event.EventStage (' + StagingTableColumnDefinition + ')'
FROM StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$'
EXECUTE (@Statement)
END
GO
GRANT ALTER, SELECT, INSERT, UPDATE, DELETE ON Event.EventStage TO OpsMgrWriter
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventUserName' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.EventUserName
END
GO
CREATE TABLE dbo.EventUserName
(
EventUserNameRowId int NOT NULL IDENTITY(1,1)
,UserName nvarchar(255) NOT NULL
,LastReceivedDateTime smalldatetime NOT NULL DEFAULT (GETUTCDATE())
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vEventCategory')
BEGIN
EXECUTE ('CREATE VIEW dbo.vEventCategory AS SELECT A = 1')
END
GO
ALTER VIEW dbo.vEventCategory
AS
SELECT *
FROM dbo.EventCategory (NOLOCK)
GO
GRANT SELECT ON dbo.vEventCategory TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vEventChannel')
BEGIN
EXECUTE ('CREATE VIEW dbo.vEventChannel AS SELECT A = 1')
END
GO
ALTER VIEW dbo.vEventChannel
AS
SELECT *
FROM dbo.EventChannel (NOLOCK)
GO
GRANT SELECT ON dbo.vEventChannel TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vEventLevel')
BEGIN
EXECUTE ('CREATE VIEW dbo.vEventLevel AS SELECT A = 1')
END
GO
ALTER VIEW dbo.vEventLevel
AS
SELECT *
FROM dbo.EventLevel (NOLOCK)
GO
GRANT SELECT ON dbo.vEventLevel TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vEventLoggingComputer')
BEGIN
EXECUTE ('CREATE VIEW dbo.vEventLoggingComputer AS SELECT A = 1')
END
GO
ALTER VIEW dbo.vEventLoggingComputer
AS
SELECT *
FROM dbo.EventLoggingComputer (NOLOCK)
GO
GRANT SELECT ON dbo.vEventLoggingComputer TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vEventPublisher')
BEGIN
EXECUTE ('CREATE VIEW dbo.vEventPublisher AS SELECT A = 1')
END
GO
ALTER VIEW dbo.vEventPublisher
AS
SELECT *
FROM dbo.EventPublisher (NOLOCK)
GO
GRANT SELECT ON dbo.vEventPublisher TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vEventUserName')
BEGIN
EXECUTE ('CREATE VIEW dbo.vEventUserName AS SELECT A = 1')
END
GO
ALTER VIEW dbo.vEventUserName
AS
SELECT *
FROM dbo.EventUserName (NOLOCK)
GO
GRANT SELECT ON dbo.vEventUserName TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventCategoryRowIdResolve')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.EventCategoryRowIdResolve AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.EventCategoryRowIdResolve
@ManagementGroupGuid uniqueidentifier
,@ObjectKeyXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group is connected to this DW
DECLARE @CheckExecResult int
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
IF (OBJECT_ID('tempdb..#EventCategory') IS NOT NULL)
DROP TABLE #EventCategory
CREATE TABLE #EventCategory (
NodeOrdinal int NOT NULL
,PublisherGuid uniqueidentifier NOT NULL
,PublisherName nvarchar(256) NOT NULL
,CategoryId int NOT NULL
,CategoryTitle nvarchar(256) NOT NULL
)
INSERT #EventCategory (
NodeOrdinal
,PublisherGuid
,PublisherName
,CategoryId
,CategoryTitle
)
SELECT
NodeOrdinal
,PublisherGuid
,PublisherName
,CategoryId
,CategoryTitle
FROM OPENXML(@XmlDocHandle, 'Keys/Key', 2) WITH (
NodeOrdinal int '@mp:id'
,PublisherGuid uniqueidentifier 'PublisherGuid'
,PublisherName nvarchar(256) 'PublisherName'
,CategoryId int 'CategoryId'
,CategoryTitle nvarchar(256) 'CategoryTitle') xml
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
-- work with publisher domain first
UPDATE ep
SET LastReceivedDateTime = GETUTCDATE()
FROM EventPublisher ep
JOIN #EventCategory e ON (ep.EventPublisherGuid = e.PublisherGuid)
WHERE (ep.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
INSERT EventPublisher (EventPublisherGuid, EventPublisherName)
SELECT DISTINCT e.PublisherGuid, e.PublisherName
FROM #EventCategory e
WHERE NOT EXISTS (SELECT * FROM EventPublisher WHERE (EventPublisherGuid = e.PublisherGuid))
-- update category domain
UPDATE ec
SET LastReceivedDateTime = GETUTCDATE()
FROM EventCategory ec
JOIN EventPublisher ep ON (ec.EventPublisherRowId = ep.EventPublisherRowId)
JOIN #EventCategory e ON (ec.EventCategoryId = e.CategoryId) AND (ep.EventPublisherGuid = e.PublisherGuid)
WHERE (ec.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
INSERT EventCategory (
EventPublisherRowId
,EventCategoryId
,EventCategoryTitle
)
SELECT DISTINCT
ep.EventPublisherRowId
,e.CategoryId
,e.CategoryTitle
FROM #EventCategory e
JOIN EventPublisher ep ON (e.PublisherGuid = ep.EventPublisherGuid)
WHERE NOT EXISTS (SELECT *
FROM EventCategory
WHERE (EventCategoryId = e.CategoryId)
AND (EventPublisherRowId = ep.EventPublisherRowId)
)
SELECT
ec.EventCategoryRowId
FROM #EventCategory e
JOIN EventPublisher ep ON (e.PublisherGuid = ep.EventPublisherGuid)
JOIN EventCategory ec ON (e.CategoryId = ec.EventCategoryId) AND (ec.EventPublisherRowId = ep.EventPublisherRowId)
ORDER BY e.NodeOrdinal
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (OBJECT_ID('tempdb..#EventCategory') IS NOT NULL)
DROP TABLE #EventCategory
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON EventCategoryRowIdResolve TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventChannelRowIdResolve')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.EventChannelRowIdResolve AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.EventChannelRowIdResolve
@ManagementGroupGuid uniqueidentifier
,@ObjectKeyXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group is connected to this DW
DECLARE @CheckExecResult int
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
IF (OBJECT_ID('tempdb..#EventChannel') IS NOT NULL)
DROP TABLE #EventChannel
CREATE TABLE #EventChannel (
NodeOrdinal int NOT NULL
,ChannelName nvarchar(256) NOT NULL
)
INSERT #EventChannel (
NodeOrdinal
,ChannelName
)
SELECT
NodeOrdinal
,ChannelName
FROM OPENXML(@XmlDocHandle, 'Keys/Key', 2) WITH (
NodeOrdinal int '@mp:id'
,ChannelName nvarchar(256) '.') xml
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
UPDATE ec
SET LastReceivedDateTime = GETUTCDATE()
FROM EventChannel ec
JOIN #EventChannel e ON (ec.EventChannelTitle = e.ChannelName COLLATE database_default)
WHERE (ec.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
INSERT EventChannel (EventChannelTitle)
SELECT DISTINCT e.ChannelName
FROM #EventChannel e
WHERE NOT EXISTS (SELECT * FROM EventChannel WHERE (EventChannelTitle = e.ChannelName COLLATE database_default))
SELECT
CAST(ec.EventChannelRowId AS int)
FROM #EventChannel e
JOIN EventChannel ec ON (e.ChannelName = ec.EventChannelTitle COLLATE database_default)
ORDER BY e.NodeOrdinal
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (OBJECT_ID('tempdb..#EventChannel') IS NOT NULL)
DROP TABLE #EventChannel
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON EventChannelRowIdResolve TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.EventGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.EventGroom
@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 sysname
,@ErrorMessageText nvarchar(4000)
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
BEGIN TRAN
CREATE TABLE #EventGroom (
EventOriginId uniqueidentifier NOT NULL
)
SELECT @MainTableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
SET @Statement = 'INSERT #EventGroom (EventOriginId)'
+ ' SELECT TOP ' + CAST(@MaxRowsToGroom AS varchar(15)) + ' EventOriginId'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName)
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE (@Statement)
SET @StandardDatasetAggregationStorageRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 1)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetAggregationStorageRowId = StandardDatasetAggregationStorageRowId
,@TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 1)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
Order By StandardDatasetAggregationStorageRowId Asc
SET @Statement = 'DELETE d'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' d'
+ ' JOIN #EventGroom e ON (d.EventOriginId = e.EventOriginId)'
EXECUTE (@Statement)
END
SET @Statement = 'DELETE d'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName) +' d'
+ ' JOIN #EventGroom e ON (d.EventOriginId = e.EventOriginId)'
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT
DROP TABLE #EventGroom
COMMIT
-- groom dynamic dimentions
DECLARE @MaxDataAgeDays int
SELECT @MaxDataAgeDays = MAX(MaxDataAgeDays)
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
DELETE e
FROM EventCategory e
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE e
FROM EventChannel e
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE e
FROM EventLoggingComputer e
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE e
FROM EventPublisher e
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE e
FROM EventUserName e
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 event 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 = 'EventLoggingComputerRowIdResolve')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.EventLoggingComputerRowIdResolve AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.EventLoggingComputerRowIdResolve
@ManagementGroupGuid uniqueidentifier
,@ObjectKeyXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group is connected to this DW
DECLARE @CheckExecResult int
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
IF (OBJECT_ID('tempdb..#EventLoggingComputer') IS NOT NULL)
DROP TABLE #EventLoggingComputer
CREATE TABLE #EventLoggingComputer (
NodeOrdinal int NOT NULL
,ComputerName nvarchar(256) NOT NULL
)
INSERT #EventLoggingComputer (
NodeOrdinal
,ComputerName
)
SELECT
NodeOrdinal
,LoggingComputerName
FROM OPENXML(@XmlDocHandle, 'Keys/Key', 2) WITH (
NodeOrdinal int '@mp:id'
,LoggingComputerName nvarchar(256) '.') xml
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
UPDATE ec
SET LastReceivedDateTime = GETUTCDATE()
FROM EventLoggingComputer ec
JOIN #EventLoggingComputer e ON (ec.ComputerName = e.ComputerName COLLATE database_default)
WHERE (ec.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
INSERT EventLoggingComputer (ComputerName)
SELECT DISTINCT e.ComputerName
FROM #EventLoggingComputer e
WHERE NOT EXISTS (SELECT * FROM EventLoggingComputer WHERE (ComputerName = e.ComputerName COLLATE database_default))
SELECT
ec.EventLoggingComputerRowId
FROM #EventLoggingComputer e
JOIN EventLoggingComputer ec ON (e.ComputerName = ec.ComputerName COLLATE database_default)
ORDER BY e.NodeOrdinal
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (OBJECT_ID('tempdb..#EventLoggingComputer') IS NOT NULL)
DROP TABLE #EventLoggingComputer
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON EventLoggingComputerRowIdResolve TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.EventProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.EventProcessStaging
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@DebugLevel int
,@SchemaName sysname
,@ExecResult int
,@LockResourceName sysname
,@InsertTableName sysname
,@InsertTableGuid uniqueidentifier
,@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@MessageText varchar(max)
,@OperationDurationMs bigint
,@MaxStagingRowsToProcess int
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventStage2Process' AND TABLE_SCHEMA = 'Event')
BEGIN
BEGIN TRAN
DECLARE @ProcessAllRowsInd bit
SET @ProcessAllRowsInd = 1
-- figure out what we need to do with staging area
SELECT
@Statement = StagingTableColumnDefinition
,@MaxStagingRowsToProcess = MaxRowsToProcessPerTransactionCount
FROM StandardDatasetStagingArea
WHERE DatasetId = @DatasetId
IF (@MaxStagingRowsToProcess IS NOT NULL)
BEGIN
DECLARE @EventStageRowCount int
DECLARE @SpaceUsedInfo TABLE (
[name] sysname
,[rows] int
,reserved nvarchar(100)
,data nvarchar(100)
,index_size nvarchar(100)
,unused nvarchar(100)
)
SELECT @EventStageRowCount = [rows]
FROM @SpaceUsedInfo
IF (@EventStageRowCount >= @MaxStagingRowsToProcess)
BEGIN
SET @ProcessAllRowsInd = 0
END
END
IF (@ProcessAllRowsInd = 1)
BEGIN
-- no limit set for the number of rows to process
-- rename and re-create staging table
-- lock, rename EventStage table and re-create it
EXEC @ExecResult = sp_rename 'Event.EventStage', 'EventStage2Process'
IF (@ExecResult < 0)
BEGIN
RAISERROR(777971003, 16, 1, @ExecResult)
END
SELECT @Statement = 'CREATE TABLE Event.EventStage (' + @Statement + ')'
EXECUTE (@Statement)
END
ELSE
BEGIN
-- need to process subset of rows
-- create extra column to store original row id
SELECT @Statement =
'CREATE TABLE Event.EventStage2Process ('
+ @Statement
+ ',OriginalEventStageRowId int NOT NULL'
+ ')'
EXECUTE (@Statement)
IF (@DebugLevel > 3)
BEGIN
SELECT @MessageText = 'Starting to insert ' + CAST(COUNT(*) AS varchar(30)) + ' events into ' + @InsertTableName
FROM Event.EventStage2Process
-- insert event <-> rule/me relationships
-- NOTE: We do not check for event-rule-ME uniqueness
-- to save time (can produce dupes if event was delivered twice
-- in two different staging processing windows)
-- We also saw Sql having troubles coming up with a good plan
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 1)
AND (TableTag = 'rule')
-- delete events collected more then once - leave latest collected only
DELETE s
FROM Event.EventStage2Process s
WHERE EXISTS (SELECT *
FROM Event.EventStage2Process s_later
WHERE (s.EventOriginId = s_later.EventOriginId)
AND (s_later.EventStageRowId > s.EventStageRowId)
)
-- insert events
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
-- first delete events that were already delivered
SELECT @Statement = 'DELETE s'
+ ' FROM Event.EventStage2Process s, ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' e'
+ ' WHERE (s.EventOriginId = e.EventOriginId)'
EXECUTE (@Statement)
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 1)
AND (TableTag = 'parameter')
SELECT @Statement =
' INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' ('
+ ' EventOriginId'
+ ' ,ParameterIndex'
+ ' ,ParameterValue'
+ ' )'
+ ' SELECT '
+ ' s.EventOriginId'
+ ' ,ROW_NUMBER() OVER(PARTITION BY s.EventOriginId ORDER BY s.EventOriginId)'
+ ' ,p.ParameterValue.value(''.'', ''nvarchar(max)'')'
+ ' FROM Event.EventStage2Process s'
+ ' CROSS APPLY s.EventParameters.nodes(''Params/Param'') as p(ParameterValue)'
EXECUTE(@Statement)
-- insert event details
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 1)
AND (TableTag = 'detail')
-- delete all inserted data from staging
DROP TABLE Event.EventStage2Process
IF (@DebugLevel > 3)
BEGIN
SET @MessageText = 'Finished inserting events into ' + @InsertTableName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to process data in the event data set staging area. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON dbo.EventProcessStaging TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventPublisherRowIdResolve')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.EventPublisherRowIdResolve AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.EventPublisherRowIdResolve
@ManagementGroupGuid uniqueidentifier
,@ObjectKeyXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group is connected to this DW
DECLARE @CheckExecResult int
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
IF (OBJECT_ID('tempdb..#EventPublisher') IS NOT NULL)
DROP TABLE #EventPublisher
CREATE TABLE #EventPublisher (
NodeOrdinal int NOT NULL
,PublisherGuid uniqueidentifier NOT NULL
,PublisherName nvarchar(256) NOT NULL
)
INSERT #EventPublisher (
NodeOrdinal
,PublisherGuid
,PublisherName
)
SELECT
NodeOrdinal
,PublisherGuid
,PublisherName
FROM OPENXML(@XmlDocHandle, 'Keys/Key', 2) WITH (
NodeOrdinal int '@mp:id'
,PublisherGuid uniqueidentifier 'PublisherGuid'
,PublisherName nvarchar(256) 'PublisherName') xml
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
UPDATE ep
SET LastReceivedDateTime = GETUTCDATE()
FROM EventPublisher ep
JOIN #EventPublisher e ON (ep.EventPublisherGuid = e.PublisherGuid)
WHERE (ep.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
INSERT EventPublisher (EventPublisherGuid, EventPublisherName)
SELECT DISTINCT e.PublisherGuid, e.PublisherName
FROM #EventPublisher e
WHERE NOT EXISTS (SELECT * FROM EventPublisher WHERE (EventPublisherGuid = e.PublisherGuid))
SELECT
ep.EventPublisherRowId
FROM #EventPublisher e
JOIN EventPublisher ep ON (e.PublisherGuid = ep.EventPublisherGuid)
ORDER BY e.NodeOrdinal
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (OBJECT_ID('tempdb..#EventPublisher') IS NOT NULL)
DROP TABLE #EventPublisher
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON EventPublisherRowIdResolve TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventUserNameRowIdResolve')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.EventUserNameRowIdResolve AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.EventUserNameRowIdResolve
@ManagementGroupGuid uniqueidentifier
,@ObjectKeyXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group is connected to this DW
DECLARE @CheckExecResult int
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
IF (OBJECT_ID('tempdb..#EventUserName') IS NOT NULL)
DROP TABLE #EventUserName
CREATE TABLE #EventUserName (
NodeOrdinal int NOT NULL
,UserName nvarchar(256) NOT NULL
)
INSERT #EventUserName (
NodeOrdinal
,UserName
)
SELECT
NodeOrdinal
,UserName
FROM OPENXML(@XmlDocHandle, 'Keys/Key', 2) WITH (
NodeOrdinal int '@mp:id'
,UserName nvarchar(256) '.') xml
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
UPDATE ec
SET LastReceivedDateTime = GETUTCDATE()
FROM EventUserName ec
JOIN #EventUserName e ON (ec.UserName = e.UserName COLLATE database_default)
WHERE (ec.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
INSERT EventUserName (UserName)
SELECT DISTINCT e.UserName
FROM #EventUserName e
WHERE NOT EXISTS (SELECT * FROM EventUserName WHERE (UserName = e.UserName COLLATE database_default))
SELECT
ec.EventUserNameRowId
FROM #EventUserName e
JOIN EventUserName ec ON (e.UserName = ec.UserName COLLATE database_default)
ORDER BY e.NodeOrdinal
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (OBJECT_ID('tempdb..#EventUserName') IS NOT NULL)
DROP TABLE #EventUserName
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON EventUserNameRowIdResolve TO OpsMgrWriter
GO
</Install> <Uninstall>
-- drop SPs
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventGroom')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.EventGroom')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventProcessStaging')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.EventProcessStaging')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventCategoryRowIdResolve')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.EventCategoryRowIdResolve')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventChannelRowIdResolve')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.EventChannelRowIdResolve')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventLoggingComputerRowIdResolve')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.EventLoggingComputerRowIdResolve')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventPublisherRowIdResolve')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.EventPublisherRowIdResolve')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventUserNameRowIdResolve')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.EventUserNameRowIdResolve')
END
-- drop views
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vEventCategory')
BEGIN
EXECUTE ('DROP VIEW vEventCategory')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vEventChannel')
BEGIN
EXECUTE ('DROP VIEW vEventChannel')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vEventLevel')
BEGIN
EXECUTE ('DROP VIEW vEventLevel')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vEventLoggingComputer')
BEGIN
EXECUTE ('DROP VIEW vEventLoggingComputer')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vEventPublisher')
BEGIN
EXECUTE ('DROP VIEW vEventPublisher')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vEventUserName')
BEGIN
EXECUTE ('DROP VIEW vEventUserName')
END
-- drop tables
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventCategory' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('DROP TABLE dbo.EventCategory')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventChannel' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('DROP TABLE dbo.EventChannel')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventLevel' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('DROP TABLE dbo.EventLevel')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventLoggingComputer' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('DROP TABLE dbo.EventLoggingComputer')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventPublisher' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('DROP TABLE dbo.EventPublisher')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventUserName' AND TABLE_SCHEMA = 'dbo')
BEGIN
EXECUTE('DROP TABLE dbo.EventUserName')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventStage' AND TABLE_SCHEMA = 'Event')
BEGIN
EXECUTE('DROP TABLE Event.EventStage')
END
-- delete std dataset related data
EXEC StandardDatasetDelete '$Config/DatasetId$'
GO
</Uninstall> <Upgrade>
SET NOCOUNT ON
-- update table definition templates
UPDATE sdas
SET TableTemplate = '
CREATE TABLE [Event].[EventParameter_$Guid$]
(
EventParameterRowId int NOT NULL IDENTITY(1, 1)
,EventOriginId uniqueidentifier NOT NULL
,ParameterIndex tinyint NOT NULL
,ParameterValue nvarchar(max) NULL
,CONSTRAINT [PK_EventParameter_$Guid$] PRIMARY KEY CLUSTERED (EventParameterRowId) ON $DataFileGroupName$
) ON $DataFileGroupName$
'
FROM StandardDatasetAggregationStorage sdas
JOIN StandardDataset sd ON (sdas.DatasetId = sd.DatasetId)
WHERE (sd.DatasetId = '$Config/DatasetId$')
AND (sdas.TableTag = 'parameter')
AND (sdas.DependentTableInd = 1)
GO
-- update all tables that were already created
DECLARE
@StandardDatasetTableMapRowId int
,@Statement nvarchar(max)
,@SchemaName sysname
,@TableNameSuffix sysname
,@BaseTableName sysname
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap tm
WHERE (tm.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (tm.DatasetId = '$Config/DatasetId$')
AND (tm.InsertInd = 1)
)
BEGIN
SELECT TOP 1
@StandardDatasetTableMapRowId = tm.StandardDatasetTableMapRowId
,@SchemaName = sd.SchemaName
,@TableNameSuffix = tm.TableNameSuffix
,@BaseTableName = sdas.BaseTableName
FROM StandardDatasetTableMap tm
JOIN StandardDataset sd ON (tm.DatasetId = sd.DatasetId)
JOIN StandardDatasetAggregationStorage sdas ON (sdas.DatasetId = tm.DatasetId) AND (sdas.AggregationTypeId = tm.AggregationTypeId)
WHERE (tm.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
AND (tm.DatasetId = '$Config/DatasetId$')
AND (tm.InsertInd = 1)
AND (sdas.TableTag = 'parameter')
AND (sdas.DependentTableInd = 1)
ORDER BY tm.StandardDatasetTableMapRowId
-- alter cover views
EXEC StandardDatasetBuildCoverView '$Config/DatasetId$', 0
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventCategoryRowIdResolve')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.EventCategoryRowIdResolve AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.EventCategoryRowIdResolve
@ManagementGroupGuid uniqueidentifier
,@ObjectKeyXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group is connected to this DW
DECLARE @CheckExecResult int
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
IF (OBJECT_ID('tempdb..#EventCategory') IS NOT NULL)
DROP TABLE #EventCategory
CREATE TABLE #EventCategory (
NodeOrdinal int NOT NULL
,PublisherGuid uniqueidentifier NOT NULL
,PublisherName nvarchar(256) NOT NULL
,CategoryId int NOT NULL
,CategoryTitle nvarchar(256) NOT NULL
)
INSERT #EventCategory (
NodeOrdinal
,PublisherGuid
,PublisherName
,CategoryId
,CategoryTitle
)
SELECT
NodeOrdinal
,PublisherGuid
,PublisherName
,CategoryId
,CategoryTitle
FROM OPENXML(@XmlDocHandle, 'Keys/Key', 2) WITH (
NodeOrdinal int '@mp:id'
,PublisherGuid uniqueidentifier 'PublisherGuid'
,PublisherName nvarchar(256) 'PublisherName'
,CategoryId int 'CategoryId'
,CategoryTitle nvarchar(256) 'CategoryTitle') xml
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
-- work with publisher domain first
UPDATE ep
SET LastReceivedDateTime = GETUTCDATE()
FROM EventPublisher ep
JOIN #EventCategory e ON (ep.EventPublisherGuid = e.PublisherGuid)
WHERE (ep.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
INSERT EventPublisher (EventPublisherGuid, EventPublisherName)
SELECT DISTINCT e.PublisherGuid, e.PublisherName
FROM #EventCategory e
WHERE NOT EXISTS (SELECT * FROM EventPublisher WHERE (EventPublisherGuid = e.PublisherGuid))
-- update category domain
UPDATE ec
SET LastReceivedDateTime = GETUTCDATE()
FROM EventCategory ec
JOIN EventPublisher ep ON (ec.EventPublisherRowId = ep.EventPublisherRowId)
JOIN #EventCategory e ON (ec.EventCategoryId = e.CategoryId) AND (ep.EventPublisherGuid = e.PublisherGuid)
WHERE (ec.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
INSERT EventCategory (
EventPublisherRowId
,EventCategoryId
,EventCategoryTitle
)
SELECT DISTINCT
ep.EventPublisherRowId
,e.CategoryId
,e.CategoryTitle
FROM #EventCategory e
JOIN EventPublisher ep ON (e.PublisherGuid = ep.EventPublisherGuid)
WHERE NOT EXISTS (SELECT *
FROM EventCategory
WHERE (EventCategoryId = e.CategoryId)
AND (EventPublisherRowId = ep.EventPublisherRowId)
)
SELECT
ec.EventCategoryRowId
FROM #EventCategory e
JOIN EventPublisher ep ON (e.PublisherGuid = ep.EventPublisherGuid)
JOIN EventCategory ec ON (e.CategoryId = ec.EventCategoryId) AND (ec.EventPublisherRowId = ep.EventPublisherRowId)
ORDER BY e.NodeOrdinal
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (OBJECT_ID('tempdb..#EventCategory') IS NOT NULL)
DROP TABLE #EventCategory
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON EventCategoryRowIdResolve TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventChannelRowIdResolve')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.EventChannelRowIdResolve AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.EventChannelRowIdResolve
@ManagementGroupGuid uniqueidentifier
,@ObjectKeyXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group is connected to this DW
DECLARE @CheckExecResult int
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
IF (OBJECT_ID('tempdb..#EventChannel') IS NOT NULL)
DROP TABLE #EventChannel
CREATE TABLE #EventChannel (
NodeOrdinal int NOT NULL
,ChannelName nvarchar(256) NOT NULL
)
INSERT #EventChannel (
NodeOrdinal
,ChannelName
)
SELECT
NodeOrdinal
,ChannelName
FROM OPENXML(@XmlDocHandle, 'Keys/Key', 2) WITH (
NodeOrdinal int '@mp:id'
,ChannelName nvarchar(256) '.') xml
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
UPDATE ec
SET LastReceivedDateTime = GETUTCDATE()
FROM EventChannel ec
JOIN #EventChannel e ON (ec.EventChannelTitle = e.ChannelName COLLATE database_default)
WHERE (ec.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
INSERT EventChannel (EventChannelTitle)
SELECT DISTINCT e.ChannelName
FROM #EventChannel e
WHERE NOT EXISTS (SELECT * FROM EventChannel WHERE (EventChannelTitle = e.ChannelName COLLATE database_default))
SELECT
CAST(ec.EventChannelRowId AS int)
FROM #EventChannel e
JOIN EventChannel ec ON (e.ChannelName = ec.EventChannelTitle COLLATE database_default)
ORDER BY e.NodeOrdinal
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (OBJECT_ID('tempdb..#EventChannel') IS NOT NULL)
DROP TABLE #EventChannel
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON EventChannelRowIdResolve TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.EventGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.EventGroom
@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 sysname
,@ErrorMessageText nvarchar(4000)
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
BEGIN TRAN
CREATE TABLE #EventGroom (
EventOriginId uniqueidentifier NOT NULL
)
SELECT @MainTableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
SET @Statement = 'INSERT #EventGroom (EventOriginId)'
+ ' SELECT TOP ' + CAST(@MaxRowsToGroom AS varchar(15)) + ' EventOriginId'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName)
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE (@Statement)
SET @StandardDatasetAggregationStorageRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 1)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
)
BEGIN
SELECT TOP 1
@StandardDatasetAggregationStorageRowId = StandardDatasetAggregationStorageRowId
,@TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 1)
AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId)
Order By StandardDatasetAggregationStorageRowId Asc
SET @Statement = 'DELETE d'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' d'
+ ' JOIN #EventGroom e ON (d.EventOriginId = e.EventOriginId)'
EXECUTE (@Statement)
END
SET @Statement = 'DELETE d'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@MainTableName) +' d'
+ ' JOIN #EventGroom e ON (d.EventOriginId = e.EventOriginId)'
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT
DROP TABLE #EventGroom
COMMIT
-- groom dynamic dimentions
DECLARE @MaxDataAgeDays int
SELECT @MaxDataAgeDays = MAX(MaxDataAgeDays)
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
DELETE e
FROM EventCategory e
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE e
FROM EventChannel e
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE e
FROM EventLoggingComputer e
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE e
FROM EventPublisher e
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
DELETE e
FROM EventUserName e
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 event 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 = 'EventLoggingComputerRowIdResolve')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.EventLoggingComputerRowIdResolve AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.EventLoggingComputerRowIdResolve
@ManagementGroupGuid uniqueidentifier
,@ObjectKeyXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group is connected to this DW
DECLARE @CheckExecResult int
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
IF (OBJECT_ID('tempdb..#EventLoggingComputer') IS NOT NULL)
DROP TABLE #EventLoggingComputer
CREATE TABLE #EventLoggingComputer (
NodeOrdinal int NOT NULL
,ComputerName nvarchar(256) NOT NULL
)
INSERT #EventLoggingComputer (
NodeOrdinal
,ComputerName
)
SELECT
NodeOrdinal
,LoggingComputerName
FROM OPENXML(@XmlDocHandle, 'Keys/Key', 2) WITH (
NodeOrdinal int '@mp:id'
,LoggingComputerName nvarchar(256) '.') xml
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
UPDATE ec
SET LastReceivedDateTime = GETUTCDATE()
FROM EventLoggingComputer ec
JOIN #EventLoggingComputer e ON (ec.ComputerName = e.ComputerName COLLATE database_default)
WHERE (ec.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
INSERT EventLoggingComputer (ComputerName)
SELECT DISTINCT e.ComputerName
FROM #EventLoggingComputer e
WHERE NOT EXISTS (SELECT * FROM EventLoggingComputer WHERE (ComputerName = e.ComputerName COLLATE database_default))
SELECT
ec.EventLoggingComputerRowId
FROM #EventLoggingComputer e
JOIN EventLoggingComputer ec ON (e.ComputerName = ec.ComputerName COLLATE database_default)
ORDER BY e.NodeOrdinal
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (OBJECT_ID('tempdb..#EventLoggingComputer') IS NOT NULL)
DROP TABLE #EventLoggingComputer
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON EventLoggingComputerRowIdResolve TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.EventProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.EventProcessStaging
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@DebugLevel int
,@SchemaName sysname
,@ExecResult int
,@LockResourceName sysname
,@InsertTableName sysname
,@InsertTableGuid uniqueidentifier
,@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@MessageText varchar(max)
,@OperationDurationMs bigint
,@MaxStagingRowsToProcess int
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventStage2Process' AND TABLE_SCHEMA = 'Event')
BEGIN
BEGIN TRAN
DECLARE @ProcessAllRowsInd bit
SET @ProcessAllRowsInd = 1
-- figure out what we need to do with staging area
SELECT
@Statement = StagingTableColumnDefinition
,@MaxStagingRowsToProcess = MaxRowsToProcessPerTransactionCount
FROM StandardDatasetStagingArea
WHERE DatasetId = @DatasetId
IF (@MaxStagingRowsToProcess IS NOT NULL)
BEGIN
DECLARE @EventStageRowCount int
DECLARE @SpaceUsedInfo TABLE (
[name] sysname
,[rows] int
,reserved nvarchar(100)
,data nvarchar(100)
,index_size nvarchar(100)
,unused nvarchar(100)
)
SELECT @EventStageRowCount = [rows]
FROM @SpaceUsedInfo
IF (@EventStageRowCount >= @MaxStagingRowsToProcess)
BEGIN
SET @ProcessAllRowsInd = 0
END
END
IF (@ProcessAllRowsInd = 1)
BEGIN
-- no limit set for the number of rows to process
-- rename and re-create staging table
-- lock, rename EventStage table and re-create it
EXEC @ExecResult = sp_rename 'Event.EventStage', 'EventStage2Process'
IF (@ExecResult < 0)
BEGIN
RAISERROR(777971003, 16, 1, @ExecResult)
END
SELECT @Statement = 'CREATE TABLE Event.EventStage (' + @Statement + ')'
EXECUTE (@Statement)
END
ELSE
BEGIN
-- need to process subset of rows
-- create extra column to store original row id
SELECT @Statement =
'CREATE TABLE Event.EventStage2Process ('
+ @Statement
+ ',OriginalEventStageRowId int NOT NULL'
+ ')'
EXECUTE (@Statement)
IF (@DebugLevel > 3)
BEGIN
SELECT @MessageText = 'Starting to insert ' + CAST(COUNT(*) AS varchar(30)) + ' events into ' + @InsertTableName
FROM Event.EventStage2Process
-- insert event <-> rule/me relationships
-- NOTE: We do not check for event-rule-ME uniqueness
-- to save time (can produce dupes if event was delivered twice
-- in two different staging processing windows)
-- We also saw Sql having troubles coming up with a good plan
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 1)
AND (TableTag = 'rule')
-- delete events collected more then once - leave latest collected only
DELETE s
FROM Event.EventStage2Process s
WHERE EXISTS (SELECT *
FROM Event.EventStage2Process s_later
WHERE (s.EventOriginId = s_later.EventOriginId)
AND (s_later.EventStageRowId > s.EventStageRowId)
)
-- insert events
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
-- first delete events that were already delivered
SELECT @Statement = 'DELETE s'
+ ' FROM Event.EventStage2Process s, ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' e'
+ ' WHERE (s.EventOriginId = e.EventOriginId)'
EXECUTE (@Statement)
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 1)
AND (TableTag = 'parameter')
SELECT @Statement =
' INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + ' ('
+ ' EventOriginId'
+ ' ,ParameterIndex'
+ ' ,ParameterValue'
+ ' )'
+ ' SELECT '
+ ' s.EventOriginId'
+ ' ,ROW_NUMBER() OVER(PARTITION BY s.EventOriginId ORDER BY s.EventOriginId)'
+ ' ,p.ParameterValue.value(''.'', ''nvarchar(max)'')'
+ ' FROM Event.EventStage2Process s'
+ ' CROSS APPLY s.EventParameters.nodes(''Params/Param'') as p(ParameterValue)'
EXECUTE(@Statement)
-- insert event details
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 1)
AND (TableTag = 'detail')
-- delete all inserted data from staging
DROP TABLE Event.EventStage2Process
IF (@DebugLevel > 3)
BEGIN
SET @MessageText = 'Finished inserting events into ' + @InsertTableName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to process data in the event data set staging area. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON dbo.EventProcessStaging TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventPublisherRowIdResolve')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.EventPublisherRowIdResolve AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.EventPublisherRowIdResolve
@ManagementGroupGuid uniqueidentifier
,@ObjectKeyXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group is connected to this DW
DECLARE @CheckExecResult int
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
IF (OBJECT_ID('tempdb..#EventPublisher') IS NOT NULL)
DROP TABLE #EventPublisher
CREATE TABLE #EventPublisher (
NodeOrdinal int NOT NULL
,PublisherGuid uniqueidentifier NOT NULL
,PublisherName nvarchar(256) NOT NULL
)
INSERT #EventPublisher (
NodeOrdinal
,PublisherGuid
,PublisherName
)
SELECT
NodeOrdinal
,PublisherGuid
,PublisherName
FROM OPENXML(@XmlDocHandle, 'Keys/Key', 2) WITH (
NodeOrdinal int '@mp:id'
,PublisherGuid uniqueidentifier 'PublisherGuid'
,PublisherName nvarchar(256) 'PublisherName') xml
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
UPDATE ep
SET LastReceivedDateTime = GETUTCDATE()
FROM EventPublisher ep
JOIN #EventPublisher e ON (ep.EventPublisherGuid = e.PublisherGuid)
WHERE (ep.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
INSERT EventPublisher (EventPublisherGuid, EventPublisherName)
SELECT DISTINCT e.PublisherGuid, e.PublisherName
FROM #EventPublisher e
WHERE NOT EXISTS (SELECT * FROM EventPublisher WHERE (EventPublisherGuid = e.PublisherGuid))
SELECT
ep.EventPublisherRowId
FROM #EventPublisher e
JOIN EventPublisher ep ON (e.PublisherGuid = ep.EventPublisherGuid)
ORDER BY e.NodeOrdinal
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (OBJECT_ID('tempdb..#EventPublisher') IS NOT NULL)
DROP TABLE #EventPublisher
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON EventPublisherRowIdResolve TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EventUserNameRowIdResolve')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.EventUserNameRowIdResolve AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.EventUserNameRowIdResolve
@ManagementGroupGuid uniqueidentifier
,@ObjectKeyXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupRowId int
-- check management group is connected to this DW
DECLARE @CheckExecResult int
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
IF (OBJECT_ID('tempdb..#EventUserName') IS NOT NULL)
DROP TABLE #EventUserName
CREATE TABLE #EventUserName (
NodeOrdinal int NOT NULL
,UserName nvarchar(256) NOT NULL
)
INSERT #EventUserName (
NodeOrdinal
,UserName
)
SELECT
NodeOrdinal
,UserName
FROM OPENXML(@XmlDocHandle, 'Keys/Key', 2) WITH (
NodeOrdinal int '@mp:id'
,UserName nvarchar(256) '.') xml
EXEC sp_xml_removedocument @XmlDocHandle
SET @XmlDocHandle = NULL
UPDATE ec
SET LastReceivedDateTime = GETUTCDATE()
FROM EventUserName ec
JOIN #EventUserName e ON (ec.UserName = e.UserName COLLATE database_default)
WHERE (ec.LastReceivedDateTime < DATEADD(day, -1, GETUTCDATE()))
INSERT EventUserName (UserName)
SELECT DISTINCT e.UserName
FROM #EventUserName e
WHERE NOT EXISTS (SELECT * FROM EventUserName WHERE (UserName = e.UserName COLLATE database_default))
SELECT
ec.EventUserNameRowId
FROM #EventUserName e
JOIN EventUserName ec ON (e.UserName = ec.UserName COLLATE database_default)
ORDER BY e.NodeOrdinal
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (OBJECT_ID('tempdb..#EventUserName') IS NOT NULL)
DROP TABLE #EventUserName
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON EventUserNameRowIdResolve TO OpsMgrWriter
GO
SET NOCOUNT ON
UPDATE StandardDatasetStagingArea
SET StagingTableColumnDefinition =
'DatasetId uniqueidentifier NOT NULL
,ManagementGroupGuid uniqueidentifier NOT NULL
,EventOriginId uniqueidentifier NOT NULL
,[DateTime] datetime NOT NULL
,RuleRowId int NOT NULL
,ManagedEntityRowId int NOT NULL
,EventPublisherRowId int NOT NULL
,EventChannelRowId smallint NOT NULL
,EventCategoryRowId int NOT NULL
,EventLevelId tinyint NOT NULL
,LoggingComputerRowId int NOT NULL
,EventDisplayNumber int NOT NULL
,EventNumber bigint NOT NULL
,UserNameRowId int NOT NULL
,RawDescription nvarchar(max) NULL
,RenderedDescription nvarchar(max) NULL
,EventParameters xml NULL
,EventData xml NULL
,RawDescriptionHash uniqueidentifier NULL
,ParameterHash uniqueidentifier NULL
,EventDataHash uniqueidentifier NULL
,EventStageRowId bigint NOT NULL IDENTITY(1, 1) PRIMARY KEY
'
,MaxRowsToProcessPerTransactionCount = CASE WHEN MaxRowsToProcessPerTransactionCount < 20000 THEN MaxRowsToProcessPerTransactionCount ELSE 20000 END
WHERE DatasetId = '$Config/DatasetId$'
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE (TABLE_NAME = 'EventStage')
AND (TABLE_SCHEMA = 'Event')
AND (CONSTRAINT_TYPE = 'PRIMARY KEY')
)
BEGIN
ALTER TABLE Event.EventStage
ADD CONSTRAINT PK_EventStage PRIMARY KEY NONCLUSTERED (EventStageRowId)
END
GO
Home
ENU