A folyamatállapot-adatok folyamatfigyelési adattárháza.
Accessibility | Internal |
<DataWarehouseDataSet ID="Microsoft.Windows.Server.10.0.HealthStatesCollection.Dataset" Accessibility="Internal">
<Dependencies>
<DataWarehouseScript>SCDW!Microsoft.SystemCenter.DataWarehouse.Script.DatasetDebuggingSupport</DataWarehouseScript>
<DataWarehouseScript>SCDW!Microsoft.SystemCenter.DataWarehouse.Script.StandardDatasetSupport</DataWarehouseScript>
</Dependencies>
<Configuration>
<IncludeSchemaTypes>
<SchemaType>SCDW!Microsoft.SystemCenter.DataWarehouse.StandardDatasetSchema</SchemaType>
</IncludeSchemaTypes>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="Aggregations" type="AggregationsType"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="RawInsertTableCount" type="xsd:positiveInteger"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="DefaultAggregationIntervalCount" type="xsd:positiveInteger"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="BlockingMaintenanceDailyStartTime" type="TimeOfDayType"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="BlockingMaintenanceDurationMinutes" type="xsd:positiveInteger"/>
</Configuration>
<Install>
GO
---------------------------------------------------------------
-- Initialize Process Health 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 = 'HealthStateProcessStaging'
SET @DebugPrefix = 'Process Monitoring Health State DataSet: '
SET @DebugMessage = @DebugPrefix + ' Insert new definition.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
BEGIN TRY
INSERT StandardDataset (
DatasetId
,SchemaName
,DebugLevel
,DefaultAggregationIntervalCount
,RawInsertTableCount
,StagingProcessorStoredProcedureName
,BlockingMaintenanceDailyStartTime
,BlockingMaintenanceDurationMinutes
)
VALUES
(
'$Config/DatasetId$'
,@SchemaName
,@DebugLevel
,$Config/DefaultAggregationIntervalCount$
,$Config/RawInsertTableCount$
,@SchemaName + '.' + @ProcessStagingSp
,'$Config/BlockingMaintenanceDailyStartTime$'
,$Config/BlockingMaintenanceDurationMinutes$
)
END TRY
BEGIN CATCH
SET @ErrorInd = 1
END CATCH
IF (1 = @ErrorInd)
BEGIN
SET @DebugMessage = @DebugPrefix + 'Insert new definition failed.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
END
ELSE
BEGIN
SET @DebugMessage = @DebugPrefix + ' Insert new definition finished successfully.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
END
GO
---------------------------------------------------------------------------------------
-- Create the StandardDatasetAggregation for Process Monitoring HealthState 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 HealthState DataSet: '
SET @DebugMessage = @DebugPrefix + 'Starting aggregation setting deployment.'
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText varchar(max)
,@BuildAggregationStoredProcedureName sysname
,@DeleteAggregationStoredProcedureName sysname
,@GroomStoredProcedureName sysname
,@SchemaName sysname
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
BEGIN TRY
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF @SchemaName IS NULL
BEGIN
SET @DebugMessage = @DebugPrefix + 'Aggregation setting deployment was failed. Process Monitoring Health State Dataset does not exist.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
RAISERROR( @DebugMessage, 16,1)
END
SET @BuildAggregationStoredProcedureName = NULL
SET @DeleteAggregationStoredProcedureName = NULL
SET @GroomStoredProcedureName = @SchemaName + '.' + 'HealthStateFactsGroom'
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
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
ELSE
BEGIN
SET @DebugMessage = @DebugPrefix + 'Aggregation setting deployment was finished successfully.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
END
GO
------------------------------------------------------------------------------------------------------------
-- Create the StandardDataset Aggregation Tables definitions for Process Monitoring HealthState 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 Health State DataSet: '
SET @DebugMessage = @DebugPrefix + 'Starting dataset table definitions deployment.'
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
,@TableCompressionClause nvarchar(max)
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
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
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
RAISERROR(777971000, 16, 1, @DebugMessage, @ExecResult)
END
IF NOT EXISTS (SELECT 1 FROM dbo.StandardDatasetAggregationStorage WHERE DatasetId = '$Config/DatasetId$' )
BEGIN
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
SELECT
'$Config/DatasetId$'
,CASE AggregationType
WHEN 'Raw' THEN 0
WHEN 'Subhourly' THEN 10
WHEN 'Hourly' THEN 20
WHEN 'Daily' THEN 30
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
,CpuHealthState tinyint NOT NULL
,MemoryHealthState tinyint NOT NULL
,HandleCountHealthState tinyint NOT NULL
,AggregateHealthState tinyint NOT NULL
,CpuThreshold float NULL
,MemoryThreshold float NULL
,HandleThreshold float 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
,UserRowId
,PID
,CpuHealthState
,MemoryHealthState
,HandleCountHealthState
,AggregateHealthState
,CpuThreshold
,MemoryThreshold
,HandleThreshold
,DateTime
'
END
,MaxTableRowCount
,MaxTableSizeKb
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname'
,BaseTableName varchar(256)
,MaxTableRowCount int
,MaxTableSizeKb int
,DataFileGroupName varchar(256)
,IndexFileGroupName varchar(256)
)
CROSS JOIN StandardDataset d
WHERE (d.DatasetId = '$Config/DatasetId$' AND AggregationType = 'Raw')
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 + 'Dataset table definitions deployment was failed. Error: ' + @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
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 Monitoring HealthState 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 Health State DataSet: '
SET @DebugMessage = @DebugPrefix + 'Starting dataset indexes deployment.'
SET @AggregationTypeId = -1
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
BEGIN TRY
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregation
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
)
BEGIN
SELECT @IndeGuid = NEWID()
SELECT TOP 1
@AggregationTypeId = AggregationTypeId
,@Statement =
' EXEC StandardDatasetIndexDelete'
+ ' @DatasetId = ''$Config/DatasetId$'''
+ ' ,@IndexGuid = ''' + CAST(@IndeGuid AS varchar(100)) + ''''
+ ' ,@DeleteIndexOnExistingTablesInd = 0'
+ ' EXEC StandardDatasetIndexInsert'
+ ' @DatasetId = ''$Config/DatasetId$'''
+ ' ,@AggregationTypeId = ' + CAST(AggregationTypeId AS varchar(10))
+ ' ,@DependentTableInd = 0'
+ ' ,@TableTag = NULL'
+ ' ,@UniqueInd = 0'
+ ' ,@IndexGuid = ''' + CAST(@IndeGuid AS varchar(100)) + ''''
+ ' ,@IndexDefinition = ''([DateTime])'''
+ ' ,@CreateIndexOnExistingTablesInd = 0'
FROM StandardDatasetAggregation
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
ORDER BY AggregationTypeId
EXECUTE (@Statement)
END
/* raw indexes for UI*/
EXEC StandardDatasetIndexDelete
@DatasetId = '$Config/DatasetId$'
,@IndexGuid = 'F986F345-9191-4B36-845C-606731706848'
,@DeleteIndexOnExistingTablesInd = 0
EXEC StandardDatasetIndexInsert
@DatasetId = '$Config/DatasetId$'
,@AggregationTypeId = 0
,@DependentTableInd = 0
,@TableTag = NULL
,@UniqueInd = 0
,@IndexGuid = 'F986F345-9191-4B36-845C-606731706848'
,@IndexDefinition = '(
ComputerRowId
,ProcessRowId
,ProcessCmdRowId
,UserRowId
,PID
,DateTime
)
INCLUDE
(
CpuHealthState
,MemoryHealthState
,HandleCountHealthState
,AggregateHealthState
)
'
,@CreateIndexOnExistingTablesInd = 0
END TRY
BEGIN CATCH
SET @ErrorInd = 1
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
END CATCH
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
SET @DebugMessage = @DebugPrefix + 'Indexes deployment was failed. Error: ' + @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
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 Health 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 Health 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 HealthState DataSet: '
SET @DebugMessage = @DebugPrefix + 'Starting storage deployment.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
SET @DebugMessage = @DebugPrefix + 'Cannot parse configuration. Text: ' + @XmlDocText
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @XmlDocText
IF @ExecResult <> 0
BEGIN
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
RAISERROR(777971000, 16, 1,@DebugMessage , @ExecResult)
END
DECLARE
@AggregationType varchar(50)
,@Statement nvarchar(max)
SET @AggregationType = ''
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
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
ELSE
BEGIN
SET @DebugMessage = @DebugPrefix + 'Storage deployment was finished successfully.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
END
GO
--------------------------------------------------------------------------------------
--- Start standard Process Monitoring Health 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 Health State DataSet: '
SET @DebugMessage = @DebugPrefix + ' Create staging table.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
-- insert staging definition into StandardDatasetStagingArea
BEGIN TRY
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
,CpuHealthState tinyint NOT NULL
,MemoryHealthState tinyint NOT NULL
,HandleCountHealthState tinyint NOT NULL
,AggregateHealthState tinyint NOT NULL
,CpuThreshold float NULL
,MemoryThreshold float NULL
,HandleThreshold float 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)
,DWCreatedDateTime datetime NULL DEFAULT (GETUTCDATE())
,ProcessStateStageRowId bigint NOT NULL IDENTITY(1, 1) PRIMARY KEY
'
,1
,100000
)
COMMIT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
SET @DebugMessage = @DebugPrefix + 'staging table creation was failed. Error: ' + @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
ELSE
BEGIN
DECLARE
@SchemaName sysname
,@TableName sysname
SET @TableName = 'ProcesStateStaging'
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.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
END
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
SET @DebugMessage = @DebugPrefix + 'staging table creation was failed. Error: ' + @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
END CATCH
IF (0 = @ErrorInd)
BEGIN
SET @DebugMessage = @DebugPrefix + 'staging table created successfully. '
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
END
END
GO
GO
DECLARE
@SchemaName sysname
,@TableName sysname
,@Statement nvarchar(max)
SET @TableName = 'ComputerDim'
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF @SchemaName IS NULL
BEGIN
RAISERROR('Process Monitoring Health 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 +
'(
ComputerRowId int NOT NULL IDENTITY(1,1)
,ManagementEntityRowId int NOT NULL
,Name nvarchar(256) NOT NULL
,NetbiosName nvarchar(256) NULL
,PrincipalName nvarchar(256) NULL
,DWCreatedDateTime datetime NOT NULL DEFAULT (GETUTCDATE())
,CONSTRAINT PK_' + @TableName +' PRIMARY KEY CLUSTERED (ComputerRowId)
)'
EXECUTE(@Statement)
SET @Statement =
'CREATE INDEX IX_' + @TableName + '_LastReceivedDateTime ON ' + @SchemaName + '.' + @TableName + ' (DWCreatedDateTime)'
EXECUTE(@Statement)
SET @Statement =
'CREATE INDEX IX_' + @TableName + '_MeId ON ' + @SchemaName + '.' + @TableName + ' (ManagementEntityRowId)'
EXECUTE(@Statement)
EXEC DomainTableRegisterIndexOptimization
@TableName = @TableName
,@SchemaName = @SchemaName
,@DatasetId = '$Config/DatasetId$'
END
Endproc:
GO
GO
DECLARE
@SchemaName sysname
,@TableName sysname
,@Statement nvarchar(max)
SET @TableName = 'DateDim'
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF @SchemaName IS NULL
BEGIN
RAISERROR('Process Monitoring Health 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 +
'(
DateKey int NOT NULL
,DateValue datetime NOT NULL
,[Day] tinyint NOT NULL
,[DayOfWeek] tinyint NOT NULL
,[DayOfMonth] tinyint NOT NULL
,[WeekOfYear] tinyint NOT NULL
,[Month] tinyint NOT NULL
,[Quarter] tinyint NOT NULL
,[Year] smallint NOT NULL
,CONSTRAINT PK_' + @TableName +' PRIMARY KEY CLUSTERED (DateKey)
)'
EXECUTE(@Statement)
SET @Statement =
'INSERT INTO ' + @SchemaName + '.' + @TableName +
'(DateKey,DateValue,Day,DayOfWeek,DayOfMonth,WeekOfYear,Month,Quarter,Year)
SELECT [Year]*10000 + [Month]*100 + Day(DateValue),DateValue,Day(DateValue),[DayOfWeek],[DayOfMonth],[WeekOfYear],[Month],[Quarter],[Year]
FROM
dbo.Date dt
WHERE
dt.Year > 2015
'
EXECUTE(@Statement)
EXEC DomainTableRegisterIndexOptimization
@TableName = @TableName
,@SchemaName = @SchemaName
,@DatasetId = '$Config/DatasetId$'
END
Endproc:
GO
GO
DECLARE
@SchemaName sysname
,@TableName sysname
,@Statement nvarchar(max)
SET @TableName = 'TimeDim'
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF @SchemaName IS NULL
BEGIN
RAISERROR('Process Monitoring Health 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 +
'(
TimeKey int NOT NULL
,[Hour] tinyint NOT NULL
,[Minute] tinyint NOT NULL
,[Second] tinyint NOT NULL
,CONSTRAINT PK_' + @TableName +' PRIMARY KEY CLUSTERED (TimeKey)
)'
EXECUTE(@Statement)
SET @Statement =
'INSERT INTO ' + @SchemaName + '.' + @TableName +
'(TimeKey,[Hour],[Minute],[Second])
SELECT
(tHour.n -1)* 10000 + (tMinute.n-1) * 100 + tSecond.n -1 AS TimeKey
,tHour.n -1 as [Hour]
,tMinute.n-1 As [Minute]
,tSecond.n -1 AS [Second]
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY L0.C) AS n FROM
(SELECT 1 AS c UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) L0,
(SELECT 1 AS c UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ) L1
)tHour
CROSS JOIN
(SELECT ROW_NUMBER() OVER(ORDER BY L0.C) AS n FROM
(SELECT 1 AS c UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) L0,
(SELECT 1 AS c UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ) L1,
(SELECT 1 AS c UNION ALL SELECT 1 ) L2
)tMinute
CROSS JOIN
(SELECT ROW_NUMBER() OVER(ORDER BY L0.C) AS n FROM
(SELECT 1 AS c UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) L0,
(SELECT 1 AS c UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ) L1,
(SELECT 1 AS c UNION ALL SELECT 1 ) L2
)tSecond
'
EXECUTE(@Statement)
EXEC DomainTableRegisterIndexOptimization
@TableName = @TableName
,@SchemaName = @SchemaName
,@DatasetId = '$Config/DatasetId$'
END
Endproc:
GO
GO
DECLARE
@SchemaName sysname
,@TableName sysname
,@Statement nvarchar(max)
SET @TableName = 'ProcessCmdDim'
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF @SchemaName IS NULL
BEGIN
RAISERROR('Process Monitoring Health 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 +
'(
ProcessCmdRowId int NOT NULL IDENTITY(1,1)
,CommandLine nvarchar(max) NULL
,CommandLineHash varchar(64) NOT NULL
,DWCreatedDateTime datetime NOT NULL DEFAULT (GETUTCDATE())
,CONSTRAINT PK_' + @TableName +' PRIMARY KEY CLUSTERED (ProcessCmdRowId)
)'
EXECUTE(@Statement)
SET @Statement =
'CREATE INDEX IX_' + @TableName + '_LastReceivedDateTime ON ' + @SchemaName + '.' + @TableName + ' (DWCreatedDateTime)'
EXECUTE(@Statement)
SET @Statement =
'CREATE INDEX IX_' + @TableName + '_CommandLineHash ON ' + @SchemaName + '.' + @TableName + ' (CommandLineHash)'
EXECUTE(@Statement)
SET @Statement =
'INSERT INTO ' + @SchemaName + '.' + @TableName +
'(CommandLine,CommandLineHash)
SELECT '''',''E3B0C44298FC1C149AFBF4C8996FB92427AE41E4649B934CA495991B7852B855''
'
EXECUTE(@Statement)
EXEC DomainTableRegisterIndexOptimization
@TableName = @TableName
,@SchemaName = @SchemaName
,@DatasetId = '$Config/DatasetId$'
END
Endproc:
GO
GO
DECLARE
@SchemaName sysname
,@TableName sysname
,@Statement nvarchar(max)
SET @TableName = 'ProcessDim'
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF @SchemaName IS NULL
BEGIN
RAISERROR('Process Monitoring Health 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 +
'(
ProcessRowId int NOT NULL IDENTITY(1,1)
,ProcessName nvarchar(256) NOT NULL
,[Description] nvarchar(2000) NULL
,UserDescription nvarchar(512) NULL
,DWCreatedDateTime datetime NOT NULL DEFAULT (GETUTCDATE())
,CONSTRAINT PK_' + @TableName +' PRIMARY KEY CLUSTERED (ProcessRowId)
)'
EXECUTE(@Statement)
SET @Statement =
'CREATE INDEX IX_' + @TableName + '_LastReceivedDateTime ON ' + @SchemaName + '.' + @TableName + ' (DWCreatedDateTime)'
EXECUTE(@Statement)
SET @Statement =
'CREATE INDEX IX_' + @TableName + '_ProcessName ON ' + @SchemaName + '.' + @TableName + ' (ProcessName)'
EXECUTE(@Statement)
SET @Statement =
'INSERT INTO ' + @SchemaName + '.' + @TableName +
'(ProcessName,[Description])
Values (N''System'',N''NT Kernel and System'')
,(N''System Idle'',N''Percentage of time the processor is Idle'')
,(N''svchost.exe'',N''Host Process for Windows Service'')
,(N''smss.exe'',N''Windows Session Manager'')
,(N''services.exe'',N''Services and Controller app'')
,(N''csrss.exe'',N''Client Server Runtime Process'')
,(N''conhost.exe'',N''Console Windows Host'')
,(N''cmd.exe'',N''Windows Command Processor'')
,(N''HealthService.exe'',N''Microsoft Monitoring Agent Service'')
,(N''LogonUI.exe'',N''Windows Logon User Interface Host'')
,(N''lsass.exe'',N''Local Security Authority Process'')
,(N''MonitoringHost.exe'',N''System Center Management Service Host Process'')
,(N''wininit.exe'',N''Windows Start-Up Application'')
,(N''winlogon.exe'',N''Windows Logon Application'')
,(N''WmiPrvSE.exe'',N''WMI Provider Host'')
'
EXECUTE(@Statement)
EXEC DomainTableRegisterIndexOptimization
@TableName = @TableName
,@SchemaName = @SchemaName
,@DatasetId = '$Config/DatasetId$'
END
Endproc:
GO
GO
DECLARE
@SchemaName sysname
,@TableName sysname
,@Statement nvarchar(max)
SET @TableName = 'UserDim'
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF @SchemaName IS NULL
BEGIN
RAISERROR('Process Monitoring Health 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 +
'(
UserRowId int NOT NULL IDENTITY(1,1)
,LogonName nvarchar(256) NOT NULL
,Domain nvarchar(256) NULL
,UserName nvarchar(256) NULL
,DWCreatedDateTime datetime NOT NULL DEFAULT (GETUTCDATE())
,CONSTRAINT PK_' + @TableName +' PRIMARY KEY CLUSTERED (UserRowId)
)'
EXECUTE(@Statement)
SET @Statement =
'CREATE INDEX IX_' + @TableName + '_LastReceivedDateTime ON ' + @SchemaName + '.' + @TableName + ' (DWCreatedDateTime)'
EXECUTE(@Statement)
SET @Statement =
'CREATE INDEX IX_' + @TableName + '_LogonName ON ' + @SchemaName + '.' + @TableName + ' (LogonName)'
EXECUTE(@Statement)
SET @Statement =
'INSERT INTO ' + @SchemaName + '.' + @TableName +
'(LogonName,Domain,UserName)
SELECT N'''',N'''',''''
UNION ALL
SELECT N''SYSTEM'',N'''',''SYSTEM''
'
EXECUTE(@Statement)
EXEC DomainTableRegisterIndexOptimization
@TableName = @TableName
,@SchemaName = @SchemaName
,@DatasetId = '$Config/DatasetId$'
END
Endproc:
GO
GO
DECLARE
@SchemaName sysname
,@TableName sysname
,@ViewName sysname
,@Statement nvarchar(max)
SET @TableName = 'ComputerDim'
SET @ViewName = 'v' + @TableName
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF @SchemaName IS NULL
BEGIN
RAISERROR('Process Monitoring Health 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
ComputerRowId
,ManagementEntityRowId
,Name
,NetbiosName
,PrincipalName
,DWCreatedDateTime
FROM ' + @SchemaName + '.' + @TableName + ' (NOLOCK)'
EXECUTE(@Statement)
EXECUTE('GRANT SELECT ON ' + @SchemaName + '.' + @ViewName + ' TO OpsMgrReader')
END
Endproc:
GO
GO
DECLARE
@SchemaName sysname
,@TableName sysname
,@ViewName sysname
,@Statement nvarchar(max)
SET @TableName = 'DateDim'
SET @ViewName = 'v' + @TableName
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF @SchemaName IS NULL
BEGIN
RAISERROR('Process Monitoring Health 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
DateKey
,DateValue
,[Day]
,[DayOfWeek]
,[DayOfMonth]
,[WeekOfYear]
,[Month]
,[Quarter]
,[Year]
FROM ' + @SchemaName + '.' + @TableName + ' (NOLOCK)'
EXECUTE(@Statement)
EXECUTE('GRANT SELECT ON ' + @SchemaName + '.' + @ViewName + ' TO OpsMgrReader')
END
Endproc:
GO
GO
DECLARE
@SchemaName sysname
,@TableName sysname
,@ViewName sysname
,@Statement nvarchar(max)
SET @TableName = 'TimeDim'
SET @ViewName = 'v' + @TableName
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF @SchemaName IS NULL
BEGIN
RAISERROR('Process Monitoring Health 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
TimeKey
,[Hour]
,[Minute]
,[Second]
FROM ' + @SchemaName + '.' + @TableName + ' (NOLOCK)'
EXECUTE(@Statement)
EXECUTE('GRANT SELECT ON ' + @SchemaName + '.' + @ViewName + ' TO OpsMgrReader')
END
Endproc:
GO
GO
DECLARE
@SchemaName sysname
,@TableName sysname
,@ViewName sysname
,@Statement nvarchar(max)
SET @TableName = 'ProcessCmdDim'
SET @ViewName = 'v' + @TableName
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF @SchemaName IS NULL
BEGIN
RAISERROR('Process Monitoring Health 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
ProcessCmdRowId
,CommandLine
,CommandLineHash
,DWCreatedDateTime
FROM ' + @SchemaName + '.' + @TableName + ' (NOLOCK)'
EXECUTE(@Statement)
EXECUTE('GRANT SELECT ON ' + @SchemaName + '.' + @ViewName + ' TO OpsMgrReader')
END
Endproc:
GO
GO
DECLARE
@SchemaName sysname
,@TableName sysname
,@ViewName sysname
,@Statement nvarchar(max)
SET @TableName = 'ProcessDim'
SET @ViewName = 'v' + @TableName
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF @SchemaName IS NULL
BEGIN
RAISERROR('Process Monitoring Health 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
ProcessRowId
,ProcessName
,Description
,UserDescription
,DWCreatedDateTime
FROM ' + @SchemaName + '.' + @TableName + ' (NOLOCK)'
EXECUTE(@Statement)
EXECUTE('GRANT SELECT ON ' + @SchemaName + '.' + @ViewName + ' TO OpsMgrReader')
END
Endproc:
GO
GO
DECLARE
@SchemaName sysname
,@TableName sysname
,@ViewName sysname
,@Statement nvarchar(max)
SET @TableName = 'UserDim'
SET @ViewName = 'v' + @TableName
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF @SchemaName IS NULL
BEGIN
RAISERROR('Process Monitoring Health 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
UserRowId
,LogonName
,Domain
,UserName
,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 Health 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 HealthState DataSet'
SET @defaultSchemaName = 'dbo'
SET @index = 0
SET @spLength = 0
----------------------------------------------------------------------------------
--- 0 - Logging disabled
--- 1 - Informational
--- 2 - Warning
--- 3 - Error
-----------------------------------------------------------------------------------
SET @DebugPrefix = 'Process Monitoring HealthState DataSet: '
SET @DebugMessage = @DebugPrefix + 'Trying to create grooming stored procedure.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
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.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
END
GOTO Endproc
END
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.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
END
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.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
END
GOTO Endproc
END
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.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
END
GOTO Endproc
END
SET @fullSpName = @SchemaName + '.' + @spName
END
END
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.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
END
GOTO Endproc
END
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
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @ErrorInd = 0
DECLARE
@TableName sysname
,@Statement nvarchar(max)
,@DebugLevel tinyint
,@SchemaName sysname
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- there are no dependent tables in HealthState 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
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
-- 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 HealthState data set. Error '' + CAST(@ErrorNumber AS varchar(15))
+ '', Procedure '' + @ErrorProcedure
+ '', Line '' + CAST(@ErrorLine AS varchar(15))
+ '', Message: ''+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessageText
END
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
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
IF @DebugLevel > 0
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
ELSE
BEGIN
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Grooming stored procedure was created successfully.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
END
END
Endproc:
GO
--------------------------------------------------------------------------
-- Create Staging procedure for Process Monitoring Health 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 Health State DataSet'
SET @defaultSchemaName = 'dbo'
SET @index = 0
SET @spLength = 0
----------------------------------------------------------------------------------
--- 0 - Logging disabled
--- 1 - Informational
--- 2 - Warning
--- 3 - Error
-----------------------------------------------------------------------------------
SET @DebugPrefix = 'Process Monitoring HealthState DataSet: '
SET @DebugMessage = @DebugPrefix + 'Trying to create stage processing stored procedure.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
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.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
END
GOTO Endproc
END
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.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
END
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.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
END
GOTO Endproc
END
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.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
END
GOTO Endproc
END
SET @fullSpName = @SchemaName + '.' + @spName
END
END
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.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
END
GOTO Endproc
END
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)
SET @ErrorInd = 0
DECLARE
@DebugLevel int
,@StagingTableName sysname
,@ExecResult int
,@SchemaName sysname
,@LockResourceName sysname
,@InsertTableName sysname
,@InsertTableGuid uniqueidentifier
,@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@MessageText varchar(max)
,@OperationDurationMs bigint
,@RawDataMaxAgeDays int
,@MaxStagingRowsToProcess int
,@ErrorLevel int
,@InfoLevel int
,@DebugMessageText nvarchar(max)
,@StageSp nvarchar(max)
,@ExecResultDim int
,@LockResourceNameDim sysname
SET @ErrorLevel = 3
SET @InfoLevel = 1
DECLARE
@defaultHash nvarchar(64) = ''E3B0C44298FC1C149AFBF4C8996FB92427AE41E4649B934CA495991B7852B855'';
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 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)
----------------------------
-- get max rows to process
-----------------------------
SELECT
@Statement = StagingTableColumnDefinition
,@MaxStagingRowsToProcess = ISNULL(MaxRowsToProcessPerTransactionCount,10000)
FROM StandardDatasetStagingArea
WHERE DatasetId = @DatasetId
IF (@Statement IS NULL)
BEGIN
Goto Endproc
END
--------------------------------------------------------------------------
-- Delete old records from staging
--------------------------------------------------------------------------
DELETE ' + @SchemaName + '.ProcesStateStaging
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
EXEC dbo.StandardDatasetGetInsertTableGuid @DatasetId, 0, @InsertTableGuid OUTPUT
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
Goto Staging
SanityCheck:
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessageText
Goto DeleteWrongDataSet
'
SET @Statement = @Statement +
'
Staging:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''ProcessStateStaging2Process'' AND TABLE_SCHEMA = ''' + @SchemaName + ''')
BEGIN
BEGIN TRAN
-- need to process subset of rows
-- create extra column to store original row id
SELECT @Statement =
''CREATE TABLE '' + @SchemaName + ''.ProcessStateStaging2Process (''
+ @Statement
+ '',OriginalProcessStateStageRowId bigint NOT NULL''
+ '')''
EXECUTE (@Statement)
COMMIT
'
SET @Statement = @Statement +
'
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''ProcessStateStaging2Process'' AND TABLE_SCHEMA = ''' + @SchemaName + ''')
BEGIN
Goto Endproc
END
BEGIN TRAN
INSERT INTO ' + @SchemaName + '.ProcessStateStaging2Process
(
DatasetId
,ManagementGroupGuid
,ManagedEntityGuid
,CollectedDate
,DateKey
,TimeKey
,ManagedEntityRowId
,ComputerRowId
,ProcessRowId
,ProcessCmdRowId
,UserRowId
,ProcessId
,CpuHealthState
,MemoryHealthState
,HandleCountHealthState
,AggregateHealthState
,CpuThreshold
,MemoryThreshold
,HandleThreshold
,ProcessName
,[Description]
,CommandLine
,CommandLineHash
,UserName
,DWCreatedDateTime
,OriginalProcessStateStageRowId
)
SELECT TOP (@MaxStagingRowsToProcess)
DatasetId
,ManagementGroupGuid
,ManagedEntityGuid
,CollectedDate
,DateKey
,TimeKey
,ManagedEntityRowId
,ComputerRowId
,ProcessRowId
,ProcessCmdRowId
,UserRowId
,ProcessId
,CpuHealthState
,MemoryHealthState
,HandleCountHealthState
,AggregateHealthState
,CpuThreshold
,MemoryThreshold
,HandleThreshold
,ProcessName
,Description
,CommandLine
,CommandLineHash
,UserName
,DWCreatedDateTime
,ProcessStateStageRowId
FROM
' + @SchemaName + '.ProcesStateStaging
WHERE DatasetId = @DatasetId
ORDER BY CollectedDate
COMMIT
END -- IF NOT EXISTS ProcessMonitoring.ProcessStateStaging2Process
-- create index on datetime field
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_ProcessStateStaging2Process_DateTime'')
BEGIN
CREATE INDEX IX_ProcessStateStaging2Process_DateTime ON ' + @SchemaName + '.ProcessStateStaging2Process(CollectedDate)
END
BEGIN TRAN
-- lock dimension tables to ensure we insert into dimensions opened for insertion
SET @LockResourceNameDim = ''ProcessMonitoringDimensions_TableMap''
EXEC @ExecResultDim = sp_getapplock
@Resource = @LockResourceNameDim
,@LockMode = ''Exclusive''
,@LockOwner = ''Transaction''
IF (@ExecResultDim < 0)
BEGIN
RETURN
END
-------------------------------------------------------------
-- Updating Dimensions
--------------------------------------------------------------
--------------------------------------------------------------
--Update Computer Dimension
--------------------------------------------------------------
--- 2.1 Update Staging Table with ManagedEntityRowId from Microsoft Windows Computer
UPDATE stage
SET
ManagedEntityRowid = me.ManagedEntityRowid
FROM
' + @SchemaName + '.ProcessStateStaging2Process 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 + '.ProcessStateStaging2Process 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 + '.ProcessStateStaging2Process 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 + '.ProcessStateStaging2Process 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 + '.ProcessStateStaging2Process 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 + '.ProcessStateStaging2Process
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 + '.ProcessStateStaging2Process 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 + '.ProcessStateStaging2Process 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 + '.ProcessStateStaging2Process stage
JOIN ' + @SchemaName + '.ProcessCmdDim p ON (ISNULL(stage.CommandLineHash,@defaultHash) = p.CommandLineHash)
WHERE
stage.ManagedEntityRowid <> 0
EXEC sp_releaseapplock @Resource = @LockResourceNameDim
COMMIT
BEGIN TRAN
-- lock table map to ensure we insert into table opened for insertion
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + ''_TableMap''
EXEC @ExecResult = sp_getapplock
@Resource = @LockResourceName
,@LockMode = ''Shared''
,@LockOwner = ''Transaction''
IF (@ExecResult < 0)
BEGIN
RAISERROR(777971001, 16, 1, ''Shared:StandardDatasetTableMap'', @ExecResult)
END
-------------------------------------------------------------------------------------------------
-- Updating Fact Process HealthState Table
--------------------------------------------------------------------------------------------------
SET @Statement =
''INSERT INTO '' + QUOTENAME(@SchemaName) + ''.'' + QUOTENAME(@InsertTableName) +''
(
DateKey
,TimeKey
,ComputerRowId
,ProcessRowId
,ProcessCmdRowId
,UserRowId
,Pid
,CpuHealthState
,MemoryHealthState
,HandleCountHealthState
,AggregateHealthState
,CpuThreshold
,MemoryThreshold
,HandleThreshold
,DateTime
)
SELECT
DateKey
,TimeKey
,ComputerRowId
,ProcessRowId
,ProcessCmdRowId
,UserRowId
,ProcessId
,CpuHealthState
,MemoryHealthState
,HandleCountHealthState
,AggregateHealthState
,CpuThreshold
,MemoryThreshold
,HandleThreshold
,CollectedDate
FROM
' + @SchemaName + '.ProcessStateStaging2Process
WHERE
ManagedEntityRowId <> 0
''
IF (@DebugLevel > 0)
BEGIN
SELECT @MessageText = ''Starting to insert '' + CAST(COUNT(*) AS varchar(30)) + '' process Health State samples into '' + @InsertTableName
FROM ' + @SchemaName + '.ProcessStateStaging2Process
SET @InsertStartedDateTime = GETUTCDATE()
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = @InfoLevel
,@MessageText = @MessageText
END
EXEC(@Statement)
-- 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 + '.ProcessStateStaging2Process 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 + '.ProcesStateStaging ps
JOIN ' + @SchemaName + '.ProcessStateStaging2Process psp ON (ps.ProcessStateStageRowId = psp.OriginalProcessStateStageRowId )
-- delete all inserted data from staging
DROP TABLE ' + @SchemaName + '.ProcessStateStaging2Process
EXEC sp_releaseapplock @Resource = @LockResourceName
COMMIT
IF (@DebugLevel > 0)
BEGIN
SET @MessageText = ''Process Monitroing Health State DataSet: Finished inserting health state samples into '' + @InsertTableName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = @InfoLevel
,@MessageText = @MessageText
,@OperationDurationMs = @OperationDurationMs
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
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
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
SET @DebugMessageText = N''Failed to process data in the Process Monitroing Health State DataSet staging area. Error '' + CAST(@ErrorNumber AS varchar(15))
+ '', Procedure '' + @ErrorProcedure
+ '', Line '' + CAST(@ErrorLine AS varchar(15))
+ '', Message: ''+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessageText
END
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
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 Health State DataSet: Starting deletion of wrong DataSets records from staging ''
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = @InfoLevel
,@MessageText = @MessageText
END
SET @InsertStartedDateTime = GETUTCDATE()
WHILE(1=1)
BEGIN
BEGIN TRY
BEGIN TRAN
DELETE TOP(@delBatchSize) FROM ' + @SchemaName + '.ProcesStateStaging
WHERE (DatasetId != @DatasetId)
IF (@@rowcount < @delBatchSize)
BEGIN
COMMIT
BREAK;
END
COMMIT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), ''-'')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorIndDel = 1
END CATCH
IF (@ErrorIndDel = 1)
BEGIN
BREAK;
END --IF
END --WHILE
IF (@ErrorIndDel = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
SET @DebugMessageText = N''Failed to delete data from wromg dataset in the Process Monitroing Health State DataSet staging area. Error '' + CAST(@ErrorNumber AS varchar(15))
+ '', Procedure '' + @ErrorProcedure
+ '', Line '' + CAST(@ErrorLine AS varchar(15))
+ '', Message: ''+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessageText
END
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
ELSE
BEGIN
IF (@DebugLevel > 0)
BEGIN
SET @MessageText = ''Process Monitroing Health State DataSet: Finished deleting wrong dataset records from staging ''
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = @InfoLevel
,@MessageText = @MessageText
,@OperationDurationMs = @OperationDurationMs
END
END --IF
Endproc:
END --Procedure [ProcessMonitoring].[HealthStateProcessStaging]
'
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
IF @DebugLevel > 0
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @ErrorLevel
,@MessageText = @DebugMessage
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
ELSE
BEGIN
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Stage processing stored procedure was created successfully.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
END
END
Endproc:
GO
</Install>
<Uninstall>
GO
DECLARE
@TableSchemaName sysname
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 = 'HealthStateFactsGroom')
BEGIN
EXECUTE ('DROP PROCEDURE ' + @TableSchemaName +'.HealthStateFactsGroom')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = @TableSchemaName AND ROUTINE_NAME = 'HealthStateProcessStaging')
BEGIN
EXECUTE ('DROP PROCEDURE ' + @TableSchemaName +'.HealthStateProcessStaging ')
END
--drop views
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vComputerDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP VIEW ' + @TableSchemaName +'.vComputerDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vUserDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP VIEW ' + @TableSchemaName +'.vUserDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vTimeDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP VIEW ' + @TableSchemaName +'.vTimeDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vDateDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP VIEW ' + @TableSchemaName +'.vDateDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vProcessDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP VIEW ' + @TableSchemaName +'.vProcessDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vProcessCmdDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP VIEW ' + @TableSchemaName +'.vProcessCmdDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ComputerDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP TABLE ' + @TableSchemaName +'.ComputerDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UserDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP TABLE ' + @TableSchemaName +'.UserDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TimeDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP TABLE ' + @TableSchemaName +'.TimeDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DateDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP TABLE ' + @TableSchemaName +'.DateDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ProcessDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP TABLE ' + @TableSchemaName +'.ProcessDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ProcessCmdDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP TABLE ' + @TableSchemaName +'.ProcessCmdDim')
END
-- delete std dataset related data
EXEC StandardDatasetDelete '$Config/DatasetId$'
GO
</Uninstall>
<Upgrade/>
</DataWarehouseDataSet>