GO
---------------------------------------------------------------
-- Initialize Process Port State Dataset
---------------------------------------------------------------
SET NOCOUNT ON
DECLARE
@SchemaName sysname
,@ProcessStagingSp sysname
,@ErrorInd bit
,@DebugLevel tinyint
,@DebugPrefix nvarchar(max)
,@DebugMessage nvarchar(max)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @ErrorInd = 0
SET @DebugLevel = 0
SET @SchemaName = 'ProcessMonitoring'
SET @ProcessStagingSp = 'PortStateProcessStaging'
SET @DebugPrefix = 'Process Monitoring Port State DataSet: '
SET @DebugMessage = @DebugPrefix + ' Insert new definition.'
---------------------------------------------------------------------------------------
-- Create the StandardDatasetAggregation for Process Monitoring PortState Fact Tables
----------------------------------------------------------------------------------------
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@DebugPrefix nvarchar(max)
,@DebugMessage nvarchar(max)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorInd = 0
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @DebugPrefix = 'Process Monitoring PortState DataSet: '
SET @DebugMessage = @DebugPrefix + 'Starting aggregation setting deployment.'
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText varchar(max)
,@BuildAggregationStoredProcedureName sysname
,@DeleteAggregationStoredProcedureName sysname
,@GroomStoredProcedureName sysname
,@SchemaName sysname
SET @BuildAggregationStoredProcedureName = NULL
SET @DeleteAggregationStoredProcedureName = NULL
SET @GroomStoredProcedureName = @SchemaName + '.' + 'PortStateFactsGroom'
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @XmlDocText
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'Process Monitoring: Health State Aggregation', @ExecResult)
IF NOT EXISTS (SELECT 1 FROM dbo.StandardDatasetAggregation WHERE DatasetId = '$Config/DatasetId$' )
BEGIN
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
,@BuildAggregationStoredProcedureName
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE @DeleteAggregationStoredProcedureName
END
,@GroomStoredProcedureName
,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
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
SET @DebugMessage = @DebugPrefix + 'Aggregation setting deployment was failed. Error: ' + @ErrorMessageText
------------------------------------------------------------------------------------------------------------
-- Create the StandardDataset Aggregation Tables definitions for Process Monitoring PortState Fact Tables
------------------------------------------------------------------------------------------------------------
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@DebugMessage nvarchar(max)
,@DebugPrefix nvarchar(max)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @ErrorInd = 0
SET @DebugPrefix = 'Process Monitoring Port State DataSet: '
SET @DebugMessage = @DebugPrefix + 'Starting dataset table definitions deployment.'
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
,@TableCompressionClause nvarchar(max)
BEGIN TRY
SET @TableCompressionClause = ''
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
-----------------------------------------------------------------------
-- ENBALE PAGE COMPRESSION ON FACT Tables IF AVILABLE
-----------------------------------------------------------------------
IF ( 3 = SERVERPROPERTY('EngineEdition')
OR
('SP1' = SERVERPROPERTY('ProductLevel') AND CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)) like '13.0%')
)
BEGIN
SET @TableCompressionClause = ' WITH (DATA_COMPRESSION = PAGE)'
END
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @XmlDocText
IF @ExecResult <> 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Dataset table definitions deployment was failed. Cannot parse dataset definition. Xml definition: ' + @XmlDocText
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
SELECT
'$Config/DatasetId$'
,CASE AggregationType
WHEN 'Raw' THEN 0
END
,BaseTableName
,0
,CASE AggregationType
WHEN 'Raw' THEN '
CREATE TABLE ' + QUOTENAME(d.SchemaName) + '.' + QUOTENAME(BaseTableName + '_$Guid$') + '
(
' + QUOTENAME(BaseTableName + 'RowId') + ' bigint NOT NULL IDENTITY(1, 1)
,DateKey int NOT NULL
,TimeKey int NOT NULL
,ComputerRowId int NOT NULL
,ProcessRowId int NOT NULL
,ProcessCmdRowId int NOT NULL
,UserRowId int NOT NULL
,PID int NOT NULL
,LocalAddressRowId bigint NOT NULL
,RemoteAddressRowId bigint NOT NULL
,PortType tinyint NOT NULL
,LocalPort int NOT NULL
,RemotePort int NOT NULL
,OldPortState tinyint NOT NULL
,PortState tinyint NOT NULL
,TimeInOldState int NOT NULL
,IPV6 bit NOT NULL
,[DateTime] datetime NOT NULL
,CONSTRAINT ' + QUOTENAME('PK_' + BaseTableName + '_$Guid$') + ' PRIMARY KEY CLUSTERED ('
+ QUOTENAME(BaseTableName + 'RowId') + ') ON $DataFileGroupName$
) ON $DataFileGroupName$
' + @TableCompressionClause
END
,CASE AggregationType
WHEN 'Raw' THEN '
SELECT
DateKey
,TimeKey
,ComputerRowId
,ProcessRowID
,ProcessCmdRowID
,PID
,UserRowId
,LocalAddressRowId
,RemoteAddressRowId
,PortType
,LocalPort
,RemotePort
,OldPortState
,PortState
,TimeInOldState
,[DateTime]
,IPV6
'
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$' AND AggregationType = 'Raw')
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
SET @DebugMessage = @DebugPrefix + 'Dataset table definitions deployment was failed. Error: ' + @ErrorMessageText
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
ELSE
BEGIN
SET @DebugMessage = @DebugPrefix + 'Dataset table definitions deployment was finished successfully.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
END
GO
------------------------------------------------------------------------------------------------------------
-- Create Indexes for the StandardDataset Aggregation Tables for Process MonitoringPortState Fact Tables
------------------------------------------------------------------------------------------------------------
GO
SET NOCOUNT ON
-- create [DateTime] field indexes for all aggregation levels
DECLARE
@AggregationTypeId int
,@Statement nvarchar(max)
,@IndeGuid uniqueidentifier
,@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@DebugMessage nvarchar(max)
,@DebugPrefix nvarchar(max)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @ErrorInd = 0
SET @DebugPrefix = 'Process Monitoring Port State DataSet: '
SET @DebugMessage = @DebugPrefix + 'Starting dataset indexes deployment.'
SET @AggregationTypeId = -1
BEGIN TRY
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregation
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
)
BEGIN
SELECT @IndeGuid = NEWID()
-- Hourly data index to support Top N reports and dashboards
EXEC StandardDatasetIndexDelete
@DatasetId = '$Config/DatasetId$'
,@IndexGuid = '6D30F2F6-82A9-4B38-AFBE-3A1F7A17BA9E'
,@DeleteIndexOnExistingTablesInd = 0
END
ELSE
BEGIN
SET @DebugMessage = @DebugPrefix + 'Indexes deployment was finished successfully.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
END
GO
------------------------------------------------------------------
--- Create SCHEMA for new Process Monitoring Port State DataSet
-------------------------------------------------------------------
GO
DECLARE
@SchemaName sysname
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE [name] = @SchemaName)
BEGIN
EXECUTE('CREATE SCHEMA ' + @SchemaName)
END
DECLARE @Statement nvarchar(max)
-- change schema ownership to allow index rebuild and reorganize
SET @Statement = 'ALTER AUTHORIZATION ON SCHEMA::' + @SchemaName + ' TO OpsMgrWriter'
EXECUTE(@Statement)
SET @Statement = 'GRANT ALTER ON SCHEMA::' + @SchemaName + ' TO OpsMgrWriter'
EXECUTE(@Statement)
-- grant create table permissions
GRANT CREATE TABLE TO OpsMgrWriter
GO
-----------------------------------------------------------------------
--- Start standard Process Monitoring Port State dataset deployment
-----------------------------------------------------------------------
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@DebugMessage nvarchar(max)
,@DebugPrefix nvarchar(max)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorInd = 0
SET @ErrorLevel = 3
SET @InfoLevel = 1
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
SET @DebugPrefix = 'Process Monitoring Performance DataSet: '
SET @DebugMessage = @DebugPrefix + 'Starting storage deployment.'
WHILE EXISTS (SELECT *
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname')
WHERE AggregationType > @AggregationType
)
BEGIN
SELECT TOP 1 @AggregationType = AggregationType
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname')
WHERE AggregationType > @AggregationType
ORDER BY AggregationType
SET @Statement = 'EXEC StandardDatasetAllocateStorage @DatasetId = ''$Config/DatasetId$'', @AggregationTypeId='
+ CASE @AggregationType
WHEN 'Raw' THEN '0'
WHEN 'Subhourly' THEN '10'
WHEN 'Hourly' THEN '20'
WHEN 'Daily' THEN '30'
END
EXECUTE (@Statement)
END
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
SET @DebugMessage = @DebugPrefix + 'Storage deployment was failed. Error: ' + @ErrorMessageText
--------------------------------------------------------------------------------------
--- Start standard Process Monitoring Port State dataset staging tables deployment
--------------------------------------------------------------------------------------
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@Statement nvarchar(max)
,@DebugMessage nvarchar(max)
,@DebugLevel tinyint
,@DebugPrefix nvarchar(max)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorInd = 0
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @DebugPrefix = 'Process Monitoring Port State DataSet: '
SET @DebugMessage = @DebugPrefix + ' Create staging table.'
-- insert staging definition into StandardDatasetStagingArea
BEGIN TRY
-- sometimes SCOM starts this twice...
BEGIN TRAN
IF EXISTS (SELECT * FROM StandardDatasetStagingArea WHERE DatasetId = '$Config/DatasetId$')
BEGIN
DELETE StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$'
END
INSERT StandardDatasetStagingArea
(
DatasetId
,StagingTableColumnDefinition
,BatchedProcessingSupportedInd
,MaxRowsToProcessPerTransactionCount
)
VALUES
(
'$Config/DatasetId$'
,'
DatasetId uniqueidentifier NOT NULL
,ManagementGroupGuid uniqueidentifier NOT NULL
,ManagedEntityGuid uniqueidentifier NOT NULL
,CollectedDate datetime NOT NULL
,DateKey int NOT NULL
,TimeKey int NOT NULL
,ProcessId int NOT NULL
,LocalAddress varchar(256) NULL
,RemoteAddress varchar(256) NULL
,LocalPort int NULL
,RemotePort int NULL
,OldPortState tinyint NOT NULL
,PortState tinyint NOT NULL
,TimeInOldState int NOT NULL
,PortType tinyint NOT NULL
,IPV6 bit NOT NULL
,ProcessName nvarchar(256) NOT NULL
,Description nvarchar(2000) NULL
,CommandLine nvarchar(max) NULL
,CommandLineHash varchar(64) NULL
,UserName varchar(256) NULL
,ManagedEntityRowId int NULL DEFAULT(0)
,ComputerRowId int NULL DEFAULT(0)
,ProcessRowId int NULL DEFAULT(0)
,ProcessCmdRowId int NULL DEFAULT(0)
,UserRowId int NULL DEFAULT(0)
,LocalAddressRowId bigint NULL
,RemoteAddressRowId bigint NULL
,DWCreatedDateTime datetime NULL DEFAULT (GETUTCDATE())
,PortStageRowId bigint NOT NULL IDENTITY(1, 1) PRIMARY KEY
'
,1
,100000
)
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
ELSE
BEGIN
DECLARE
@SchemaName sysname
,@TableName sysname
SET @TableName = 'PortStaging'
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
BEGIN TRY
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @SchemaName)
BEGIN
SELECT @Statement = StagingTableColumnDefinition
FROM StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$'
IF @Statement IS NOT NULL
BEGIN
SELECT @Statement = 'CREATE TABLE ' + @SchemaName + '.' + @TableName + '(' + @Statement + ')'
EXECUTE (@Statement)
END
ELSE
BEGIN
SET @DebugMessage = @DebugPrefix + 'Staging Data definition does not exist.'
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF @SchemaName IS NULL
BEGIN
RAISERROR('Process Monitoring Port State Dataset SCHEMA is not defined. Cannot create dimension tables.', 16,1)
GOTO Endproc
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @SchemaName)
BEGIN
SET @Statement =
'CREATE TABLE ' + @SchemaName + '.' + @TableName +
'(
IpAddressRowId bigint NOT NULL IDENTITY(1,1)
,IpAddress varchar(256) NOT NULL
,DWCreatedDateTime datetime NOT NULL DEFAULT (GETUTCDATE())
GO
DECLARE
@SchemaName sysname
,@TableName sysname
,@ViewName sysname
,@Statement nvarchar(max)
SET @TableName = 'IpAddressDim'
SET @ViewName = 'v' + @TableName
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF @SchemaName IS NULL
BEGIN
RAISERROR('Process Monitoring Port State Dataset SCHEMA is not defined. Cannot create dimension views.', 16,1)
GOTO Endproc
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = @ViewName AND TABLE_SCHEMA = @SchemaName)
BEGIN
EXECUTE ('CREATE VIEW ' + @SchemaName + '.' + @ViewName + ' AS SELECT A = 1')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @SchemaName)
BEGIN
SET @Statement = ' ALTER VIEW ' + @SchemaName + '.' + @ViewName +
'
AS
SELECT
IpAddressRowId
,IpAddress
,DWCreatedDateTime
FROM ' + @SchemaName + '.' + @TableName + ' (NOLOCK)'
EXECUTE(@Statement)
EXECUTE('GRANT SELECT ON ' + @SchemaName + '.' + @ViewName + ' TO OpsMgrReader')
END
Endproc:
GO
GO
-------------------------------------------------------------------------
-- Create Grooming procedure for Process Monitoring Port State DataSet
-------------------------------------------------------------------------
DECLARE
@SchemaName sysname
,@TableSchemaName sysname
,@defaultSchemaName sysname
,@spName sysname
,@fullSpName sysname
,@Statement nvarchar(max)
,@DataSetMessage nvarchar(max)
,@DebugPrefix nvarchar(max)
,@DebugMessage nvarchar(max)
,@index int
,@spLength int
,@DebugLevel tinyint
,@ErrorLevel int
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @DataSetMessage = 'Process Monitoring: Process Port State DataSet'
SET @defaultSchemaName = 'dbo'
SET @index = 0
SET @spLength = 0
SET @DebugPrefix = 'Process Monitoring Port State DataSet: '
SET @DebugMessage = @DebugPrefix + 'Trying to create Grooming stored procedure.'
SELECT
@TableSchemaName = SchemaName
,@DebugLevel = DebugLevel
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
SELECT
@fullSpName = GroomStoredProcedureName
FROM
dbo.StandardDatasetAggregation
WHERE DatasetId = '$Config/DatasetId$'
------------------------------------------------------------------------------------------------
-- Check if ETL procedure is defined in dataset
------------------------------------------------------------------------------------------------
IF @TableSchemaName IS NULL
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Dataset schema is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create Grooming stored procedure. Cannot create ETL stored procedure. Dataset schema is not defined.'
IF @fullSpName IS NULL
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create Grooming stored procedure. Cannot create ETL stored procedure. Stored procedure name is not defined.'
GOTO Endproc
END
ELSE
BEGIN
SET @index = CHARINDEX('.',@fullSpName)
SET @spLength = LEN(@fullSpName)
--------------------------------------------------------------
-- if procedure does not have schema name use dbo by default
--------------------------------------------------------------
IF (0 = @index)
BEGIN
SET @SchemaName = @defaultSchemaName
SET @spName = @fullSpName
SET @fullSpName = @SchemaName + '.' + @fullSpName
END
ELSE
BEGIN
IF (1 = @index)
BEGIN
SET @SchemaName = @defaultSchemaName
END
ELSE
BEGIN
SET @SchemaName = LEFT(@fullSpName,@index - 1)
END
SET @spname = LTRIM(RTRIM(RIGHT(@fullSpName,@spLength - @index)))
IF (0 = LEN(@spname) )
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create Grooming stored procedure. Cannot create ETL stored procedure. Stored procedure name is not defined.'
IF (CHARINDEX('.',@spName) > 0 )
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not correct [%s]', 16, 1, @DataSetMessage,@fullSpName)
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create Grooming stored procedure. Cannot create ETL stored procedure. Stored procedure name is not correct.'
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE NAME = @SchemaName)
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Dataset schema %s is not exist.', 16, 1, @DataSetMessage,@SchemaName )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create Grooming stored procedure. Cannot create ETL stored procedure. Dataset schema is not exist.'
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = @SchemaName AND ROUTINE_NAME = @spName)
BEGIN
EXECUTE ('CREATE PROCEDURE ' + @fullSpName + ' AS RETURN 1')
END
SET @Statement = 'ALTER PROCEDURE ' + @fullSpName +
'
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@CutoffDateTime datetime
,@MaxRowsToGroom int
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
,@ErrorLevel int
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- there are no dependent tables in PortState data set
SELECT @TableName = BaseTableName + ''_'' + REPLACE(CAST(@TableGuid AS varchar(50)), ''-'', '''')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
SET @Statement = ''DELETE TOP ('' + CAST(@MaxRowsToGroom AS varchar(15)) + '')''
+ '' FROM '' + QUOTENAME(@SchemaName) + ''.'' + QUOTENAME(@TableName)
+ '' WHERE ([DateTime] < CONVERT(datetime, '''''' + CONVERT(varchar(50), @CutoffDateTime, 120) + '''''', 120))''
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N''Failed to groom data for Process Monitoring Port State data set. Error '' + CAST(@ErrorNumber AS varchar(15))
+ '', Procedure '' + @ErrorProcedure
+ '', Line '' + CAST(@ErrorLine AS varchar(15))
+ '', Message: ''+ @ErrorMessageText
RETURN @RowsDeleted
END
'
DECLARE @ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
BEGIN TRY
EXECUTE (@Statement)
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
SET @DebugMessage = @DebugPrefix + 'Cannot create Grooming stored procedure. Error: ' + @ErrorMessageText
END CATCH
IF (1 = @ErrorInd)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
--------------------------------------------------------------------------
-- Create Staging procedure for Process Monitoring Port State DataSet
--------------------------------------------------------------------------
GO
DECLARE
@SchemaName sysname
,@TableSchemaName sysname
,@defaultSchemaName sysname
,@spName sysname
,@fullSpName sysname
,@Statement nvarchar(max)
,@DataSetMessage nvarchar(max)
,@DebugPrefix nvarchar(max)
,@DebugMessage nvarchar(max)
,@index int
,@spLength int
,@DebugLevel tinyint
,@ErrorLevel int
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @DataSetMessage = 'Process Monitoring: Process Port State DataSet'
SET @defaultSchemaName = 'dbo'
SET @index = 0
SET @spLength = 0
----------------------------------------------------------------------------------
--- 0 - Logging disabled
--- 1 - Informational
--- 2 - Warning
--- 3 - Error
-----------------------------------------------------------------------------------
SET @DebugPrefix = 'Process Monitoring Port State DataSet: '
SET @DebugMessage = @DebugPrefix + 'Trying to create stage processing stored procedure.'
SELECT
@TableSchemaName = SchemaName
,@fullSpName = StagingProcessorStoredProcedureName
,@DebugLevel = DebugLevel
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
------------------------------------------------------------------------------------------------
-- Check if ETL procedure is defined in dataset
------------------------------------------------------------------------------------------------
IF @TableSchemaName IS NULL
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Dataset schema is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create stage processing stored procedure. Cannot create ETL stored procedure. Dataset schema is not defined.'
IF @fullSpName IS NULL
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create stage processing stored procedure. Cannot create ETL stored procedure. Stored procedure name is not defined.'
GOTO Endproc
END
ELSE
BEGIN
SET @index = CHARINDEX('.',@fullSpName)
SET @spLength = LEN(@fullSpName)
--------------------------------------------------------------
-- if procedure does not have schema name use dbo by default
--------------------------------------------------------------
IF (0 = @index)
BEGIN
SET @SchemaName = @defaultSchemaName
SET @spName = @fullSpName
SET @fullSpName = @SchemaName + '.' + @fullSpName
END
ELSE
BEGIN
IF (1 = @index)
BEGIN
SET @SchemaName = @defaultSchemaName
END
ELSE
BEGIN
SET @SchemaName = LEFT(@fullSpName,@index - 1)
END
SET @spname = LTRIM(RTRIM(RIGHT(@fullSpName,@spLength - @index)))
IF (0 = LEN(@spname) )
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create stage processing stored procedure. Cannot create ETL stored procedure. Stored procedure name is not defined.'
IF (CHARINDEX('.',@spName) > 0 )
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not correct [%s]', 16, 1, @DataSetMessage,@fullSpName)
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create stage processing stored procedure. Cannot create ETL stored procedure. Stored procedure name is not correct.'
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE NAME = @SchemaName)
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Dataset schema %s is not exist.', 16, 1, @DataSetMessage,@SchemaName )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create stage processing stored procedure. Cannot create ETL stored procedure. Dataset schema is not exist.'
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = @SchemaName AND ROUTINE_NAME = @spName)
BEGIN
EXECUTE ('CREATE PROCEDURE ' + @fullSpName + ' AS RETURN 1')
END
SET @Statement = 'ALTER PROCEDURE ' +@fullSpName +
'
@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)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @ErrorInd = 0
DECLARE
@DebugLevel int
,@SchemaName sysname
,@StagingTableName sysname
,@ExecResult int
,@LockResourceName sysname
,@InsertTableName sysname
,@InsertTableGuid uniqueidentifier
,@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@MessageText varchar(max)
,@OperationDurationMs bigint
,@RawDataMaxAgeDays int
,@MaxStagingRowsToProcess int
,@DebugMessageText nvarchar(max)
,@StageSp nvarchar(max)
,@ExecResultDim int
,@LockResourceNameDim sysname
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
,@StageSp = StagingProcessorStoredProcedureName
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
----------------------------------
-- Sanity check for datasetId
----------------------------------
IF (@StageSp <> '''
SET @Statement = @Statement + @fullSpName + ''')
BEGIN
Goto EndProc
END
------------------------------------
-- Get dataset staging settings
----------------------------------
SELECT
@Statement = StagingTableColumnDefinition
,@MaxStagingRowsToProcess = ISNULL(MaxRowsToProcessPerTransactionCount,10000)
FROM StandardDatasetStagingArea
WHERE DatasetId = @DatasetId
IF (@Statement IS NULL)
BEGIN
Goto Endproc
END
-- get the minimum data retention period
-- to make sure we do not re-aggregate something
-- that is too old and all raw data is gone
SELECT @RawDataMaxAgeDays = MaxDataAgeDays
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
--------------------------------------------------------------------------
-- Delete old records from staging
--------------------------------------------------------------------------
DELETE ' + @SchemaName + '.ProcesStaging
WHERE (DatasetId = @DatasetId)
AND ([CollectedDate] < DATEADD(day, -@RawDataMaxAgeDays, GETUTCDATE()) )
'
SET @Statement = @Statement +
'
-------------------------------------------------------
-- Tables Sanity Check
--------------------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''ComputerDim'' AND TABLE_SCHEMA = ''' + @SchemaName + ''')
BEGIN
SET @DebugMessageText = N''Cannot find ComputerDim table exiting..''
Goto SanityCheck
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''UserDim'' AND TABLE_SCHEMA = ''' + @SchemaName + ''')
BEGIN
SET @DebugMessageText = N''Cannot find UserDim table exiting..''
Goto SanityCheck
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''ProcessDim'' AND TABLE_SCHEMA = ''' + @SchemaName + ''')
BEGIN
SET @DebugMessageText = N''Cannot find ProcessDim table exiting..''
Goto SanityCheck
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''ProcessCmdDim'' AND TABLE_SCHEMA = ''' + @SchemaName + ''')
BEGIN
SET @DebugMessageText = N''Cannot find ProcessCMdDim table exiting..''
Goto SanityCheck
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''IpAddressDim'' AND TABLE_SCHEMA = ''' + @SchemaName + ''')
BEGIN
SET @DebugMessageText = N''Cannot find IpAddressDim table exiting..''
Goto SanityCheck
END
SELECT @InsertTableName = BaseTableName + ''_'' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), ''-'', '''')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @InsertTableName AND TABLE_SCHEMA = ''' + @SchemaName + ''')
BEGIN
SET @DebugMessageText = N''Cannot find '' + @InsertTableName + '' table exiting..''
Goto SanityCheck
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''PortStaging2Process'' AND TABLE_SCHEMA = @SchemaName)
BEGIN
BEGIN TRAN
-- need to process subset of rows
-- create extra column to store original row id
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''PortStaging2Process'' AND TABLE_SCHEMA = ''' + @SchemaName + ''')
BEGIN
Goto Endproc
END
-- create index on datetime field
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_PortStaging2Process_DateTime'')
BEGIN
CREATE INDEX IX_PortStaging2Process_DateTime ON ' + @SchemaName + '.PortStaging2Process(CollectedDate)
END
BEGIN TRAN
-- lock dimension tables to ensure we insert into dimensions opened for insertion
SET @LockResourceNameDim = ''ProcessMonitoringDimensions_TableMap''
SELECT @InsertTableName = BaseTableName + ''_'' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), ''-'', '''')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
--------------------------------------------------------------------------
-- Delete old records from staging
--------------------------------------------------------------------------
DELETE ' + @SchemaName + '.PortStaging
WHERE (DatasetId = @DatasetId)
AND ([CollectedDate] < DATEADD(day, -@RawDataMaxAgeDays, GETUTCDATE()) )
-------------------------------------------------------------
-- Updating Dimensions
--------------------------------------------------------------
--------------------------------------------------------------
--Update Computer Dimension
--------------------------------------------------------------
--- 2.1 Update Staging Table with ManagedEntityRowId from Microsoft Windows Computer
UPDATE stage
SET
ManagedEntityRowid = me.ManagedEntityRowid
FROM
' + @SchemaName + '.PortStaging2Process stage
JOIN vManagementGroup mg ON (stage.ManagementGroupGuid = mg.ManagementGroupGuid)
JOIN vManagedEntity me ON ((me.ManagedEntityGuid = stage.ManagedEntityGuid) AND (mg.ManagementGroupRowId = me.ManagementGroupRowId))
JOIN vManagedEntityType met ON (met.ManagedEntityTypeRowId = me.ManagedEntityTypeRowId)
WHERE (stage.DatasetId = @DatasetId) AND ( met.ManagedEntityTypeGuid = ''EA99500D-8D52-FC52-B5A5-10DCD1E9D2BD'')
---2.2 Insert new Computers in Computer Dimension
INSERT INTO ' + @SchemaName + '.ComputerDim
(
ManagementEntityRowId
,Name
,NetbiosName
,PrincipalName
)
SELECT DISTINCT
me.ManagedEntityRowid
,UPPER(me.Name)
, CASE CHARINDEX(''.'',me.Name)
WHEN 0 THEN UPPER(me.Name)
ELSE
UPPER(LEFT(me.Name,CHARINDEX(''.'',me.Name) - 1))
END
,LOWER(me.Name)
FROM
' + @SchemaName + '.PortStaging2Process stage
JOIN vManagedEntity me ON me.ManagedEntityRowId = stage.ManagedEntityRowId
WHERE
NOT EXISTS (SELECT *
FROM ' + @SchemaName + '.ComputerDim
WHERE (ManagementEntityRowId = me.ManagedEntityRowId)
)
AND
stage.ManagedEntityRowid <> 0
AND
me.Name IS NOT NULL
---2.3 Update Staging with Computer Dimension Key
UPDATE stage
SET
ComputerRowId = pc.ComputerRowid
FROM
' + @SchemaName + '.PortStaging2Process stage
JOIN ' + @SchemaName + '.ComputerDim pc ON (stage.ManagedEntityRowId = pc.ManagementEntityRowId)
--------------------------------------------------------------
--Update User Dimension
--------------------------------------------------------------
---3.1 Insert new Users in User Dimension
INSERT INTO ' + @SchemaName + '.UserDim
(
LogonName
,Domain
,UserName
)
SELECT DISTINCT
UPPER(stage.UserName)
,UPPER(
CASE CHARINDEX(''\'',stage.UserName)
WHEN 0 THEN ''''
ELSE
LEFT(stage.UserName,CHARINDEX(''\'',stage.UserName) - 1)
END
)
,UPPER(
CASE CHARINDEX(''\'',stage.UserName)
WHEN 0 THEN stage.UserName
ELSE
RIGHT(stage.UserName,LEN(stage.UserName)- CHARINDEX(''\'',stage.UserName))
END
)
FROM
' + @SchemaName + '.PortStaging2Process stage
LEFT OUTER JOIN ' + @SchemaName + '.UserDim u ON (ISNULL(stage.UserName,'''') = u.LogonName)
WHERE
u.UserRowid IS NULL
AND
stage.ManagedEntityRowid <> 0
---3.3 Update Staging with new User Dimension Key
UPDATE stage
SET
UserRowId = u.UserRowid
FROM
' + @SchemaName + '.PortStaging2Process stage
JOIN ' + @SchemaName + '.UserDim u ON (ISNULL(stage.UserName,'''') = u.LogonName)
WHERE
stage.ManagedEntityRowid <> 0
--------------------------------------------------------------
--Update Process Dimension
--------------------------------------------------------------
---4.1. Insert new Processes in Process Dimension
;WITH ProcessByName AS
(
SELECT ProcessName,ISNULL([Description],'''') AS [Description], ROW_NUMBER() OVER(PARTITION BY ProcessName ORDER BY [Description]) as Id
FROM
' + @SchemaName + '.PortStaging2Process
WHERE
ManagedEntityRowid <> 0
),
Processes AS
(
SELECT ProcessName, [Description]
FROM ProcessByName
WHERE
Id = 1
)
INSERT INTO ' + @SchemaName + '.ProcessDim
(
ProcessName
,[Description]
)
SELECT
stage.ProcessName
,stage.[Description]
FROM
Processes stage
LEFT OUTER JOIN ' + @SchemaName + '.ProcessDim p ON (stage.ProcessName = p.ProcessName)
WHERE
p.ProcessRowId IS NULL
---4.2. Update Staging with new Process Dimension Key
UPDATE stage
SET
ProcessRowId = p.ProcessRowid
FROM
' + @SchemaName + '.PortStaging2Process stage
JOIN ' + @SchemaName + '.ProcessDim p ON (stage.ProcessName = p.ProcessName)
WHERE
stage.ManagedEntityRowid <> 0
--------------------------------------------------------------
--Update Process Command Line Dimension
--------------------------------------------------------------
---5.1. Insert new Process Command Lines in ProcessCmd Dimension
INSERT INTO ' + @SchemaName + '.ProcessCmdDim
(
CommandLine
,CommandLineHash
)
SELECT DISTINCT
stage.CommandLine
,ISNULL(stage.CommandLineHash, @defaultHash)
FROM
' + @SchemaName + '.PortStaging2Process stage
LEFT OUTER JOIN ' + @SchemaName + '.ProcessCmdDim p ON (ISNULL(stage.CommandLineHash,@defaultHash) = p.CommandLineHash)
WHERE
p.ProcessCmdRowId IS NULL
AND
stage.ManagedEntityRowid <> 0
---5.2. Update Staging with new Process Command Line Dimension Key
UPDATE stage
SET
ProcessCmdRowId = p.ProcessCmdRowid
FROM
' + @SchemaName + '.PortStaging2Process stage
JOIN ' + @SchemaName + '.ProcessCmdDim p ON (ISNULL(stage.CommandLineHash,@defaultHash) = p.CommandLineHash)
WHERE
stage.ManagedEntityRowid <> 0
BEGIN TRAN
INSERT INTO ' + @SchemaName + '.IpAddressDim
(
IpAddress
)
SELECT DISTINCT
stage.LocalAddress
FROM
' + @SchemaName + '.PortStaging2Process stage
LEFT OUTER JOIN ' + @SchemaName + '.IpAddressDim i ON (ISNULL(stage.LocalAddress,'''') = i.IpAddress)
WHERE
i.IpAddressRowId IS NULL
AND
stage.ManagedEntityRowid <> 0
INSERT INTO ' + @SchemaName + '.IpAddressDim
(
IpAddress
)
SELECT DISTINCT
stage.RemoteAddress
FROM
' + @SchemaName + '.PortStaging2Process stage
LEFT OUTER JOIN ' + @SchemaName + '.IpAddressDim i ON (ISNULL(stage.RemoteAddress,'''') = i.IpAddress)
WHERE
i.IpAddressRowId IS NULL
AND
stage.ManagedEntityRowid <> 0
UPDATE stage
SET
LocalAddressRowId = i.IpAddressRowid
FROM
' + @SchemaName + '.PortStaging2Process stage
JOIN ' + @SchemaName + '.IpAddressDim i ON (ISNULL(stage.LocalAddress,'''') = i.IpAddress)
WHERE
stage.ManagedEntityRowid <> 0
UPDATE stage
SET
RemoteAddressRowId = i.IpAddressRowid
FROM
' + @SchemaName + '.PortStaging2Process stage
JOIN ' + @SchemaName + '.IpAddressDim i ON (ISNULL(stage.RemoteAddress,'''') = i.IpAddress)
WHERE
stage.ManagedEntityRowid <> 0
COMMIT
BEGIN TRAN
-- lock table map to ensure we insert into table opened for insertion
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + ''_TableMap''
IF (@ExecResult < 0)
BEGIN
RAISERROR(777971001, 16, 1, ''Shared:StandardDatasetTableMap'', @ExecResult)
END
-------------------------------------------------------------------------------------------------
-- Updating Fact Process HealthState Table
--------------------------------------------------------------------------------------------------
SET @Statement =
''INSERT INTO ['' + @SchemaName + ''].[''+ @InsertTableName +'']
(
DateKey
,TimeKey
,ComputerRowId
,ProcessRowId
,ProcessCmdRowId
,UserRowId
,PID
,LocalAddressRowId
,RemoteAddressRowId
,PortType
,LocalPort
,RemotePort
,OldPortState
,PortState
,TimeInOldState
,IPV6
,DateTime
)
SELECT
DateKey
,TimeKey
,ComputerRowId
,ProcessRowId
,ProcessCmdRowId
,UserRowId
,ProcessId
,LocalAddressRowId
,RemoteAddressRowId
,PortType
,LocalPort
,RemotePort
,OldPortState
,PortState
,TimeInOldState
,IPV6
,CollectedDate
FROM
' + @SchemaName + '.PortStaging2Process
WHERE DateKey IS NOT NULL
AND TimeKey IS NOT NULL
AND ComputerRowId IS NOT NULL
AND ProcessRowId IS NOT NULL
AND ProcessCmdRowId IS NOT NULL
AND UserRowId IS NOT NULL
AND LocalAddressRowId IS NOT NULL
AND RemoteAddressRowId IS NOT NULL
AND PortType IS NOT NULL
AND LocalPort IS NOT NULL
AND RemotePort IS NOT NULL
AND ManagedEntityRowid <> 0''
IF (@DebugLevel > 0)
BEGIN
SELECT @MessageText = ''Starting to insert '' + CAST(COUNT(*) AS varchar(30)) + '' process Port State samples into '' + @InsertTableName
FROM ' + @SchemaName + '.PortStaging2Process
-- mark existing affected aggregations as dirty
UPDATE ah
SET DirtyInd = 1
,DataLastReceivedDateTime = GETUTCDATE()
FROM StandardDatasetAggregationHistory ah
JOIN StandardDatasetAggregation a ON (ah.DatasetId = a.DatasetId AND ah.AggregationTypeId = a.AggregationTypeId)
CROSS JOIN ' + @SchemaName + '.PortStaging2Process p
WHERE (ah.DatasetId = @DatasetId)
AND (ah.AggregationDateTime > DATEADD(day, -@RawDataMaxAgeDays, GETUTCDATE()))
AND (p.[CollectedDate] >= ah.AggregationDateTime)
AND (p.[CollectedDate] < DATEADD(minute, a.AggregationIntervalDurationMinutes, ah.AggregationDateTime))
-- delete processed rows from staging
DELETE ps
FROM ' + @SchemaName + '.PortStaging ps
JOIN ' + @SchemaName + '.PortStaging2Process psp ON (ps.PortStageRowId = psp.OriginalPortStageRowId )
-- delete all inserted data from staging
DROP TABLE ' + @SchemaName + '.PortStaging2Process
IF (@DebugLevel > 0)
BEGIN
SET @MessageText = ''Process Monitroing Port State DataSet: Finished inserting port state samples into '' + @InsertTableName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
SET @DebugMessageText = N''Failed to process data in the Process Monitroing Port State DataSet staging area. Error '' + CAST(@ErrorNumber AS varchar(15))
+ '', Procedure '' + @ErrorProcedure
+ '', Line '' + CAST(@ErrorLine AS varchar(15))
+ '', Message: ''+ @ErrorMessageText
DeleteWrongDataSet:
--------------------------------------------------------------------------
-- Delete wrong datasets from staging
--------------------------------------------------------------------------
DECLARE
@delBatchSize int
,@ErrorIndDel int
SET @ErrorIndDel = 0
SET @delBatchSize = @MaxStagingRowsToProcess
IF (@DebugLevel > 0)
BEGIN
SET @MessageText = ''Process Monitroing Port State DataSet: Starting deletion of wrong DataSets records from staging ''
IF (@ErrorIndDel = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
SET @DebugMessageText = N''Failed to delete data from wrong dataset in the Process Monitroing Port State DataSet staging area. Error '' + CAST(@ErrorNumber AS varchar(15))
+ '', Procedure '' + @ErrorProcedure
+ '', Line '' + CAST(@ErrorLine AS varchar(15))
+ '', Message: ''+ @ErrorMessageText
END
ELSE
BEGIN
IF (@DebugLevel > 0)
BEGIN
SET @MessageText = ''Process Monitroing Port State DataSet: Finished deleting wrong dataset records from staging ''
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
END --IF
Endproc:
END --Procedure [ProcessMonitoring].[StatePortStaging]
'
DECLARE @ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
BEGIN TRY
EXECUTE (@Statement)
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
SET @DebugMessage = @DebugPrefix + 'Cannot create stage processing stored procedure. Error: ' + @ErrorMessageText
END CATCH
IF (1 = @ErrorInd)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
SELECT
@TableSchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
-- drop SPs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = @TableSchemaName AND ROUTINE_NAME = 'PortStateProcessStaging')
BEGIN
EXECUTE('DROP PROCEDURE ' + @TableSchemaName + '.PortStateProcessStaging')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = @TableSchemaName AND ROUTINE_NAME = 'PortStateFactsGroom')
BEGIN
EXECUTE ('DROP PROCEDURE ' + @TableSchemaName + '.PortStateFactsGroom ')
END
--drop views
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vIpAddressDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE('DROP VIEW ' + @TableSchemaName + '.vIpAddressDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'IpAddressDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE('DROP Table ' + @TableSchemaName + '.IpAddressDim')
END
-- delete std dataset related data
EXEC StandardDatasetDelete '$Config/DatasetId$'
GO </Uninstall> <Upgrade>
GO
---------------------------------------------------------------
-- Update Port State Dataset
--------------------------------------------------------------
DECLARE
@SchemaName sysname
,@ProcessStagingSp sysname
,@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@DebugMessage nvarchar(max)
,@DebugLevel tinyint
,@DebugPrefix nvarchar(max)
SET @SchemaName = 'ProcessMonitoring'
SET @ProcessStagingSp = 'PortStateProcessStaging'
SET @ErrorInd = 0
SET @DebugLevel = 0
SET @DebugPrefix = 'Process Monitoring Port State DataSet: '
SET @DebugMessage = @DebugPrefix + ' Update dataset definition.'
------------------------------------------------------------------------------------------------------------
-- Create Indexes for the StandardDataset Aggregation Tables for Process MonitoringPortState Fact Tables
------------------------------------------------------------------------------------------------------------
GO
SET NOCOUNT ON
-- create [DateTime] field indexes for all aggregation levels
DECLARE
@AggregationTypeId int
,@Statement nvarchar(max)
,@IndeGuid uniqueidentifier
,@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@DebugMessage nvarchar(max)
,@DebugPrefix nvarchar(max)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @ErrorInd = 0
SET @DebugPrefix = 'Process Monitoring Port State DataSet: '
SET @DebugMessage = @DebugPrefix + 'Starting dataset indexes deployment.'
SET @AggregationTypeId = -1
BEGIN TRY
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregation
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
)
BEGIN
SELECT @IndeGuid = NEWID()
-- Hourly data index to support Top N reports and dashboards
EXEC StandardDatasetIndexDelete
@DatasetId = '$Config/DatasetId$'
,@IndexGuid = '6D30F2F6-82A9-4B38-AFBE-3A1F7A17BA9E'
,@DeleteIndexOnExistingTablesInd = 0
END
ELSE
BEGIN
SET @DebugMessage = @DebugPrefix + 'Indexes deployment was finished successfully.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
END
Home
ENU