[TBD]
| Accessibility | Internal |
Home
<DataWarehouseDataSet ID="Microsoft.Exchange.15.MailboxStatsCollection.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>-------------------------------------------------------------------------------
-- Create the StandardDataset for Exchange 2013
-------------------------------------------------------------------------------
SET NOCOUNT ON
GO
IF NOT EXISTS (SELECT * FROM StandardDataset WHERE DatasetId = '$Config/DatasetId$')
BEGIN
INSERT INTO StandardDataset (
DatasetId
,SchemaName
,DefaultAggregationIntervalCount
,RawInsertTableCount
,StagingProcessorStoredProcedureName
,BlockingMaintenanceDailyStartTime
,BlockingMaintenanceDurationMinutes
)
VALUES
(
'$Config/DatasetId$'
,'Exchange2013'
,$Config/DefaultAggregationIntervalCount$
,$Config/RawInsertTableCount$
,'Exchange2013_MailboxStatsProcessStaging'
,'$Config/BlockingMaintenanceDailyStartTime$'
,$Config/BlockingMaintenanceDurationMinutes$
)
END
GO
-------------------------------------------------------------------------------
-- Create the StandardDatasetAggregation for Exchange 2013
-------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
IF NOT EXISTS (SELECT * FROM StandardDatasetAggregation WHERE DatasetId = '$Config/DatasetId$')
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @XmlDocText
IF @ExecResult <> 0 RAISERROR (777971000, 16, 1, 'Cannot parse configuration for Exchange 2013 dataset', @ExecResult)
INSERT INTO StandardDatasetAggregation
(
DatasetId
,AggregationTypeId
,AggregationIntervalDurationMinutes
,AggregationStartDelayMinutes
,BuildAggregationStoredProcedureName
,DeleteAggregationStoredProcedureName
,GroomStoredProcedureName
,IndexOptimizationIntervalMinutes
,MaxDataAgeDays
,GroomingIntervalMinutes
,MaxRowsToGroom
,LastGroomingDateTime
,DataFileGroupName
,IndexFileGroupName
)
SELECT
'$Config/DatasetId$'
,CASE AggregationType
WHEN 'Raw' THEN 0
WHEN 'Hourly' THEN 20
WHEN 'Daily' THEN 30
END
,CASE AggregationType
WHEN 'Raw' THEN 0
WHEN 'Hourly' THEN 60
WHEN 'Daily' THEN 24*60
END
,AggregationStartDelayMinutes
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'Exchange2013_MailboxStatsAggregate'
END
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'Exchange2013_MailboxStatsDelete'
END
,'Exchange2013_MailboxStatsGroom'
,IndexOptimizationIntervalMinutes
,MaxDataAgeDays
,GroomingIntervalMinutes
,MaxRowsToGroom
,GETUTCDATE()
,DataFileGroupName
,IndexFileGroupName
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname'
,MaxTableRowCount int
,MaxTableSizeKb int
,AggregationStartDelayMinutes int
,MaxDataAgeDays int
,GroomingIntervalMinutes int
,MaxRowsToGroom int
,IndexOptimizationIntervalMinutes int
,DataFileGroupName nvarchar(256)
,IndexFileGroupName nvarchar(256)
,AggregationIntervalDurationMinutes int
)
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
GO
-------------------------------------------------------------------------------
-- Define the tables and views for Exchange 2013
-------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE [name] = 'Exchange2013')
BEGIN
EXECUTE('CREATE SCHEMA Exchange2013')
END
GO
-- change schema ownership to allow index rebuild and reorganize
ALTER AUTHORIZATION ON SCHEMA::Exchange2013 TO OpsMgrWriter
GO
-- change schema alter to allow domain table index rebuild
GRANT ALTER ON SCHEMA::dbo TO OpsMgrWriter
GO
GRANT ALTER ON SCHEMA::Exchange2013 TO OpsMgrWriter
GO
-- grant create table permissions
GRANT CREATE TABLE TO OpsMgrWriter
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@MaxTableRowCountRaw INT,
@MaxTableRowCountDaily INT
SELECT
@MaxTableRowCountRaw = NULLIF('$Config/Aggregations/Raw/MaxTableRowCount$', ''),
@MaxTableRowCountDaily = NULLIF('$Config/Aggregations/Daily/MaxTableRowCount$', '')
BEGIN TRY
-- sometimes SCOM starts this twice...
IF NOT EXISTS (SELECT TOP 1 1 FROM StandardDatasetAggregationStorage sd WHERE DatasetId='$Config/DatasetId$')
BEGIN
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = 3
,@MessageText = 'Starting deployment of Exchange2013 data set'
-- Insert table definition into StandardDatasetStagingArea
IF EXISTS (SELECT * FROM StandardDatasetStagingArea WHERE DatasetId = '$Config/DatasetId$')
BEGIN
DELETE StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$'
END
INSERT INTO StandardDatasetStagingArea
(
DatasetId
,StagingTableColumnDefinition
,BatchedProcessingSupportedInd,
MaxRowsToProcessPerTransactionCount
)
VALUES
(
'$Config/DatasetId$'
,'
DatasetId uniqueidentifier NOT NULL
,ManagementGroupGuid uniqueidentifier NOT NULL
,[DateTime] datetime NULL
,[Mailbox_ArchiveName] NVARCHAR(256) NULL
,[Mailbox_ArchiveState] NVARCHAR(32) NULL
,[Mailbox_ArchiveStatus] NVARCHAR(32) NULL
,[Mailbox_CustomAttribute1] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute10] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute11] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute12] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute13] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute14] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute15] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute2] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute3] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute4] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute5] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute6] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute7] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute8] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute9] NVARCHAR(1024) NULL
,[Mailbox_Database] NVARCHAR(1024) NOT NULL
,[Mailbox_DistinguishedName] NVARCHAR(1024) NOT NULL
,[Mailbox_EmailAddresses] NVARCHAR(1024) NOT NULL
,[Mailbox_EmailAddressPolicyEnabled] BIT NULL
,[Mailbox_ExchangeGuid] NVARCHAR(64) NOT NULL
,[Mailbox_ExchangeVersion] NVARCHAR(32) NOT NULL
,[Mailbox_Identity] NVARCHAR(256) NOT NULL
,[Mailbox_IsExcludedFromServingHierarchy] BIT NULL
,[Mailbox_IsLinked] BIT NULL
,[Mailbox_IsMachineToPersonTextMessagingEnabled] BIT NULL
,[Mailbox_IsMailboxEnabled] BIT NULL
,[Mailbox_IsPersonToPersonTextMessagingEnabled] BIT NULL
,[Mailbox_IsResource] BIT NULL
,[Mailbox_IsRootPublicFolderMailbox] BIT NULL
,[Mailbox_IsShared] BIT NULL
,[Mailbox_IsSoftDeletedByDisable] BIT NULL
,[Mailbox_IsSoftDeletedByRemove] BIT NULL
,[Mailbox_IsValid] BIT NULL
,[Mailbox_Name] NVARCHAR(256) NOT NULL
,[Mailbox_ObjectCategory] NVARCHAR(128) NULL
,[Mailbox_ObjectClass] NVARCHAR(128) NULL
,[Mailbox_OrganizationalUnit] NVARCHAR(1024) NOT NULL
,[Mailbox_RecipientType] NVARCHAR(64) NULL
,[Mailbox_ServerName] NVARCHAR(256) NOT NULL
,[Mailbox_UserPrincipalName] NVARCHAR(256) NOT NULL
,[Mailbox_WhenChangedUTC] DATETIME NULL
,[Mailbox_WhenCreatedUTC] DATETIME NULL
,[MailboxStatistics_AssociatedItemCount] Bigint NULL
,[MailboxStatistics_DeletedItemCount] Bigint NULL
,[MailboxStatistics_IsArchiveMailbox] BIT NULL
,[MailboxStatistics_IsMoveDestination] BIT NULL
,[MailboxStatistics_IsQuarantined] BIT NULL
,[MailboxStatistics_IsValid] BIT NULL
,[MailboxStatistics_ItemCount] Bigint NULL
,[MailboxStatistics_LastLogoffTime] DATETIME NULL
,[MailboxStatistics_LastLogonTime] DATETIME NULL
,[MailboxStatistics_MailboxGuid] NVARCHAR(64) NULL
,[MailboxStatistics_MailboxType] NVARCHAR(128) NULL
,[MailboxStatistics_TotalDeletedItemSizeMB] DECIMAL NULL
,[MailboxStatistics_TotalItemSizeMB] DECIMAL NULL
,[ManagedEntityGuid] NVARCHAR(64) NULL
,RawItem nvarchar(max) NULL
,MailboxStatsStagingRowId bigint NOT NULL IDENTITY(1,1) PRIMARY KEY
,DWCreatedDateTime datetime NULL DEFAULT(GETUTCDATE())
'
,0
,80000
)
-- Create the Exchange2013.MailboxStatsStaging table
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MailboxStatsStaging' AND TABLE_SCHEMA = 'Exchange2013')
BEGIN
DECLARE @Statement nvarchar(max)
SELECT @Statement = 'CREATE TABLE Exchange2013.MailboxStatsStaging (' + StagingTableColumnDefinition + ')'
FROM StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$'
EXECUTE (@Statement)
END
GRANT SELECT, INSERT, UPDATE, DELETE ON Exchange2013.MailboxStatsStaging TO OpsMgrWriter
-- Insert table definition of [Exchange2013].[MailboxStatsRaw] into StandardDatasetAggregationStorage
INSERT INTO StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,TableTag
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
VALUES
(
'$Config/DatasetId$'
,0
,'MailboxStatsRaw'
,'MbxStatsRaw'
,0
,'
CREATE TABLE [Exchange2013].[MailboxStatsRaw_$Guid$]
(
[MailboxStatsRawRowId] BIGINT NOT NULL IDENTITY(1, 1)
,[DWCreatedDateTime] datetime NOT NULL
,[DateTime] datetime NOT NULL
,[MailboxRowId] BIGINT NOT NULL
,[AssociatedItemCount] Bigint NULL
,[DeletedItemCount] Bigint NULL
,[ItemCount] Bigint NULL
,[LastLogoffTime] DATETIME NULL
,[LastLogonTime] DATETIME NULL
,[TotalDeletedItemSizeMB] DECIMAL NULL
,[TotalItemSizeMB] DECIMAL NULL
,CONSTRAINT [PK_MailboxStatsRaw_$Guid$] PRIMARY KEY CLUSTERED ([MailboxStatsRawRowId]) ON $DataFileGroupName$
) ON $DataFileGroupName$
'
,'
SELECT
[MailboxStatsRawRowId]
,[DWCreatedDateTime]
,[DateTime]
,[MailboxRowId]
,[AssociatedItemCount]
,[DeletedItemCount]
,[ItemCount]
,[LastLogoffTime]
,[LastLogonTime]
,[TotalDeletedItemSizeMB]
,[TotalItemSizeMB]
'
,@MaxTableRowCountRaw
,NULL
)
-- Insert table definition of [Exchange2013].[MailboxProperties] into StandardDatasetAggregationStorage
INSERT INTO StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,TableTag
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
VALUES
(
'$Config/DatasetId$'
,0
,'MailboxProperties'
,'MbxProperties'
,1
,'
CREATE TABLE [Exchange2013].[MailboxProperties_$Guid$]
(
[MailboxPropertiesRowId] BIGINT NOT NULL IDENTITY(1, 1)
,[DWCreatedDateTime] datetime NOT NULL
,[DateTime] datetime NOT NULL
,[MailboxRowId] BIGINT NOT NULL
,[PropertySetGuid] UNIQUEIDENTIFIER NULL
,[ArchiveName] NVARCHAR(256) NULL
,[ArchiveState] NVARCHAR(32) NULL
,[ArchiveStatus] NVARCHAR(32) NULL
,[CustomAttribute1] NVARCHAR(1024) NULL
,[CustomAttribute10] NVARCHAR(1024) NULL
,[CustomAttribute11] NVARCHAR(1024) NULL
,[CustomAttribute12] NVARCHAR(1024) NULL
,[CustomAttribute13] NVARCHAR(1024) NULL
,[CustomAttribute14] NVARCHAR(1024) NULL
,[CustomAttribute15] NVARCHAR(1024) NULL
,[CustomAttribute2] NVARCHAR(1024) NULL
,[CustomAttribute3] NVARCHAR(1024) NULL
,[CustomAttribute4] NVARCHAR(1024) NULL
,[CustomAttribute5] NVARCHAR(1024) NULL
,[CustomAttribute6] NVARCHAR(1024) NULL
,[CustomAttribute7] NVARCHAR(1024) NULL
,[CustomAttribute8] NVARCHAR(1024) NULL
,[CustomAttribute9] NVARCHAR(1024) NULL
,[Database] NVARCHAR(1024) NOT NULL
,[DistinguishedName] NVARCHAR(1024) NOT NULL
,[EmailAddresses] NVARCHAR(1024) NOT NULL
,[EmailAddressPolicyEnabled] BIT NULL
,[ExchangeVersion] NVARCHAR(32) NOT NULL
,[Identity] NVARCHAR(256) NOT NULL
,[IsExcludedFromServingHierarchy] BIT NULL
,[IsLinked] BIT NULL
,[IsMachineToPersonTextMessagingEnabled] BIT NULL
,[IsMailboxEnabled] BIT NULL
,[IsPersonToPersonTextMessagingEnabled] BIT NULL
,[IsResource] BIT NULL
,[IsRootPublicFolderMailbox] BIT NULL
,[IsShared] BIT NULL
,[IsSoftDeletedByDisable] BIT NULL
,[IsSoftDeletedByRemove] BIT NULL
,[IsValid] BIT NULL
,[Name] NVARCHAR(256) NOT NULL
,[ObjectCategory] NVARCHAR(128) NULL
,[ObjectClass] NVARCHAR(128) NULL
,[OrganizationalUnit] NVARCHAR(1024) NOT NULL
,[RecipientType] NVARCHAR(64) NULL
,[ServerName] NVARCHAR(256) NOT NULL
,[UserPrincipalName] NVARCHAR(256) NOT NULL
,[WhenChangedUTC] DATETIME NULL
,[IsArchiveMailbox] BIT NULL
,[IsMoveDestination] BIT NULL
,[IsQuarantined] BIT NULL
,[MailboxType] NVARCHAR(128) NULL
,[ManagedEntityGuid] NVARCHAR(64) NULL
,CONSTRAINT [PK_MailboxProperties_$Guid$] PRIMARY KEY CLUSTERED ([MailboxPropertiesRowId]) ON $DataFileGroupName$
) ON $DataFileGroupName$
'
,'
SELECT
[MailboxPropertiesRowId]
,[DWCreatedDateTime]
,[DateTime]
,[MailboxRowId]
,[PropertySetGuid]
,[ArchiveName]
,[ArchiveState]
,[ArchiveStatus]
,[CustomAttribute1]
,[CustomAttribute10]
,[CustomAttribute11]
,[CustomAttribute12]
,[CustomAttribute13]
,[CustomAttribute14]
,[CustomAttribute15]
,[CustomAttribute2]
,[CustomAttribute3]
,[CustomAttribute4]
,[CustomAttribute5]
,[CustomAttribute6]
,[CustomAttribute7]
,[CustomAttribute8]
,[CustomAttribute9]
,[Database]
,[DistinguishedName]
,[EmailAddresses]
,[EmailAddressPolicyEnabled]
,[ExchangeVersion]
,[Identity]
,[IsExcludedFromServingHierarchy]
,[IsLinked]
,[IsMachineToPersonTextMessagingEnabled]
,[IsMailboxEnabled]
,[IsPersonToPersonTextMessagingEnabled]
,[IsResource]
,[IsRootPublicFolderMailbox]
,[IsShared]
,[IsSoftDeletedByDisable]
,[IsSoftDeletedByRemove]
,[IsValid]
,[Name]
,[ObjectCategory]
,[ObjectClass]
,[OrganizationalUnit]
,[RecipientType]
,[ServerName]
,[UserPrincipalName]
,[WhenChangedUTC]
,[IsArchiveMailbox]
,[IsMoveDestination]
,[IsQuarantined]
,[MailboxType]
,[ManagedEntityGuid]
'
,@MaxTableRowCountRaw
,NULL
)
-- Insert table definition of [Exchange2013].[MailboxStatsDaily] into StandardDatasetAggregationStorage
INSERT INTO StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,TableTag
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
VALUES
(
'$Config/DatasetId$'
,30
,'MailboxStatsDaily'
,'MbxStatsDaily'
,0
,'
CREATE TABLE [Exchange2013].[MailboxStatsDaily_$Guid$]
(
[MailboxStatsDailyRowId] BIGINT NOT NULL IDENTITY(1, 1)
,[DWCreatedDateTime] datetime NOT NULL
,[DateTime] datetime NOT NULL
,[MailboxRowId] BIGINT NOT NULL
,[AssociatedItemCount] Bigint NULL
,[DeletedItemCount] Bigint NULL
,[ItemCount] Bigint NULL
,[LastLogoffTime] DATETIME NULL
,[LastLogonTime] DATETIME NULL
,[TotalDeletedItemSizeMB] DECIMAL NULL
,[TotalItemSizeMB] DECIMAL NULL
,CONSTRAINT [PK_MailboxStatsDaily_$Guid$] PRIMARY KEY CLUSTERED ([MailboxStatsDailyRowId]) ON $DataFileGroupName$
) ON $DataFileGroupName$
'
,'
SELECT
[MailboxStatsDailyRowId]
,[DWCreatedDateTime]
,[DateTime]
,[MailboxRowId]
,[AssociatedItemCount]
,[DeletedItemCount]
,[ItemCount]
,[LastLogoffTime]
,[LastLogonTime]
,[TotalDeletedItemSizeMB]
,[TotalItemSizeMB]
'
,@MaxTableRowCountDaily
,NULL
)
EXEC StandardDatasetIndexInsert
@DatasetId = '$Config/DatasetId$'
,@AggregationTypeId = 0
,@DependentTableInd = 1
,@TableTag = 'MbxProperties'
,@UniqueInd = 0
,@IndexGuid = 'BE19F5C0-9992-42AC-BFF7-A0747F3F1B1C'
,@IndexDefinition = '([DateTime])'
,@CreateIndexOnExistingTablesInd = 1
EXEC StandardDatasetIndexInsert
@DatasetId = '$Config/DatasetId$'
,@AggregationTypeId = 0
,@DependentTableInd = 1
,@TableTag = 'MbxProperties'
,@UniqueInd = 0
,@IndexGuid = 'FB096E39-8267-48D1-B344-B1C6D17A4882'
,@IndexDefinition = '([MailboxRowId], [PropertySetGuid])'
,@CreateIndexOnExistingTablesInd = 1
EXEC StandardDatasetIndexInsert
@DatasetId = '$Config/DatasetId$'
,@AggregationTypeId = 0
,@DependentTableInd = 1
,@TableTag = 'MbxProperties'
,@UniqueInd = 0
,@IndexGuid = '27495D8D-C6A6-45A2-813A-1CB22445ADA5'
,@IndexDefinition = '([MailboxRowId], [PropertySetGuid])'
,@CreateIndexOnExistingTablesInd = 1
EXEC StandardDatasetIndexInsert
@DatasetId = '$Config/DatasetId$'
,@AggregationTypeId = 30
,@DependentTableInd = 0
,@TableTag = 'MbxStatsRaw'
,@UniqueInd = 0
,@IndexGuid = 'E91648CF-6764-4D52-A59C-A025B8947B4A'
,@IndexDefinition = '([DateTime])'
,@CreateIndexOnExistingTablesInd = 1
EXEC StandardDatasetIndexInsert
@DatasetId = '$Config/DatasetId$'
,@AggregationTypeId = 30
,@DependentTableInd = 0
,@TableTag = 'MbxStatsDaily'
,@UniqueInd = 0
,@IndexGuid = 'E63B4E79-269D-44AD-8625-D3BE207B0E5A'
,@IndexDefinition = '([DateTime])'
,@CreateIndexOnExistingTablesInd = 1
END
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
GO
-------------------------------------------------------------------------------
-- Allocate standard storage for the MailboxStats dataset.
-------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @XmlDocText
IF @ExecResult <> 0 RAISERROR (777971000, 16, 1, 'Cannot parse configuration for Exchange 2013 dataset', @ExecResult)
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 'Hourly' THEN '20'
WHEN 'Daily' THEN '30'
END
EXECUTE (@Statement)
END
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
GO
-------------------------------------------------------------------------------
-- Domain Tables for the MailboxStats dataset.
-------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Mailbox' AND TABLE_SCHEMA = 'Exchange2013')
BEGIN
CREATE TABLE Exchange2013.Mailbox
(
MailboxRowId BIGINT NOT NULL IDENTITY(1,1)
,[ExchangeGuid] NVARCHAR(64) NOT NULL
,[WhenCreatedUTC] DATETIME NULL
,[LatestPropertySet] XML NULL
,[LatestPropertySetGuid] UNIQUEIDENTIFIER NULL
,[LastReceivedDateTime] DATETIME NULL
,CONSTRAINT PK_Exchange2013_Mailbox PRIMARY KEY (MailboxRowId)
)
EXEC('CREATE VIEW Exchange2013.vMailbox AS SELECT * FROM Exchange2013.Mailbox')
IF NOT EXISTS
(
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Exchange2013].[Mailbox]') AND name = N'IX_Mailbox_MailboxRowId_LatestPropertySetGuid'
)
BEGIN
CREATE INDEX IX_Mailbox_MailboxRowId_LatestPropertySetGuid ON Exchange2013.Mailbox
(MailboxRowId, LatestPropertySetGuid)
END
EXEC DomainTableRegisterIndexOptimization
@TableName = 'Mailbox'
,@SchemaName = 'Exchange2013'
,@DatasetId = '$Config/DatasetId$'
END
-- ##### Exchange2013_MailboxStatsAggregate_CreateAlter.sql
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Exchange2013_MailboxStatsAggregate' AND uid = SCHEMA_ID('dbo'))
BEGIN
EXECUTE ('CREATE PROCEDURE [dbo].[Exchange2013_MailboxStatsAggregate] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Exchange2013_MailboxStatsAggregate]
@DatasetId UNIQUEIDENTIFIER,
@AggregationTypeId INT,
@InsertTableGuid VARCHAR(256), -- GUID of the aggregation table
@IntervalStartDateTime DATETIME,
@IntervalEndDateTime DATETIME
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
BEGIN TRY
DECLARE
@Statement nvarchar(max)
,@DebugLevel tinyint
,@InsertTableName sysname
,@DebugMessage nvarchar(max)
,@AggregationStartDateTime datetime
,@CoverViewName sysname
,@SchemaName sysname
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
SELECT @CoverViewName = 'v' + BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + 'UTC to '
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + 'UTC'
SET @AggregationStartDateTime = GETUTCDATE()
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
END
SET @Statement =
'
;WITH cte (MailboxRowId, MailboxStatsRawRowId, RowNo)
AS
(
SELECT
vmsr.MailboxRowId,
vmsr.MailboxStatsRawRowId,
RowNo = ROW_NUMBER() OVER (PARTITION BY vmsr.MailboxRowId ORDER BY vmsr.[DateTime] DESC)
FROM
' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@CoverViewName) +' vmsr
WHERE (vmsr.[DateTime] >= CONVERT(datetime, ''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''', 120))
AND (vmsr.[DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @IntervalEndDateTime, 120) + ''', 120))
)
INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + '
(
DWCreatedDateTime,
[DateTime],
MailboxRowId,
AssociatedItemCount,
DeletedItemCount,
ItemCount,
LastLogoffTime,
LastLogonTime,
TotalDeletedItemSizeMB,
TotalItemSizeMB
)
SELECT
GETUTCDATE(),
CONVERT(datetime, ''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''', 120),
vmsr.MailboxRowId,
vmsr.AssociatedItemCount,
vmsr.DeletedItemCount,
vmsr.ItemCount,
vmsr.LastLogoffTime,
vmsr.LastLogonTime,
vmsr.TotalDeletedItemSizeMB,
vmsr.TotalItemSizeMB
FROM
' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@CoverViewName) +' vmsr
INNER JOIN cte c ON vmsr.MailboxStatsRawRowId=c.MailboxStatsRawRowId AND c.RowNo = 1
'
EXECUTE (@Statement)
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + 'UTC to '
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + 'UTC'
DECLARE @OperationDurationMs bigint
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @AggregationStartDateTime))
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
,@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
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to build aggregates for Exchange2013 data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@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
END
GO
GRANT EXECUTE ON [dbo].[Exchange2013_MailboxStatsAggregate] TO OpsMgrWriter
GO
-- ##### Exchange2013_MailboxStatsDelete_CreateAlter.sql
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Exchange2013_MailboxStatsDelete' AND uid = SCHEMA_ID('dbo'))
BEGIN
EXECUTE ('CREATE PROCEDURE [dbo].[Exchange2013_MailboxStatsDelete] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Exchange2013_MailboxStatsDelete]
@DatasetId UNIQUEIDENTIFIER,
@AggregationTypeId TINYINT,
@TableGuid UNIQUEIDENTIFIER,
@AggregationDateTime DATETIME,
@RowsDeleted INT OUTPUT
AS
BEGIN
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
DECLARE
@TableName sysname
,@Statement nvarchar(max)
,@DebugMessage nvarchar(max)
,@DeleteStartedDateTime datetime
,@OperationDurationMs bigint
,@DebugLevel tinyint
,@SchemaName sysname
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- there are no dependent tables in state data set
SELECT @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
-- Index hint in the query below ensures that SQL won't
-- consider index on DateTime field to be a good source
-- to find rows to delete - it may happen periodically
-- if SQL thinks there's no rows to delete due to
-- lack of [recently updated] statistics
SET @Statement = 'DELETE t' +
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' t /*WITH (INDEX(1))*/'
+ ' WHERE (t.[DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
IF (@DebugLevel > 2)
BEGIN
SELECT @DebugMessage = 'Starting delete of existing performance aggregated data. Table: ' + @TableName + ', aggregation date: ' + CONVERT(varchar(20), @AggregationDateTime, 120)
SET @DeleteStartedDateTime = GETUTCDATE()
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
END
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT
IF (@DebugLevel > 2)
BEGIN
SELECT @DebugMessage = 'Finished delete of existing performance aggregated data. Table: ' + @TableName + ', aggregation date: ' + CONVERT(varchar(20), @AggregationDateTime, 120)
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @DeleteStartedDateTime))
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
,@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
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to delete aggregated data for Exchange2013 data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = @ErrorMessageText
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
GO
GRANT EXECUTE ON [dbo].[Exchange2013_MailboxStatsDelete] TO OpsMgrWriter
GO
-- ##### Exchange2013_MailboxStatsGroom_CreateAlter.sql
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Exchange2013_MailboxStatsGroom' AND uid = SCHEMA_ID('dbo'))
BEGIN
EXECUTE ('CREATE PROCEDURE [dbo].[Exchange2013_MailboxStatsGroom] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Exchange2013_MailboxStatsGroom]
@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)
SET @ErrorInd = 0
DECLARE
@TableName sysname
,@Statement nvarchar(max)
,@DebugLevel tinyint
,@SchemaName sysname
BEGIN TRY
BEGIN TRAN
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
DECLARE @tables TABLE
(
TableName SYSNAME,
Id INT IDENTITY(1,1)
)
INSERT INTO @tables
(
TableName
)
SELECT BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
ORDER BY DependentTableInd DESC
DECLARE @i INT, @maxi INT
SELECT @i = MIN(id), @maxi = MAX(id), @RowsDeleted=0 FROM @tables
WHILE @i<=@maxi
BEGIN
SELECT @TableName = TableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM @tables WHERE Id=@i
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
SET @i += 1
END
-- groom dynamic dimentions
DECLARE @MaxDataAgeDays int
SELECT @MaxDataAgeDays = MAX(MaxDataAgeDays)
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
DELETE
FROM Exchange2013.Mailbox
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
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
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to groom data for Exchange2013 data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@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
GO
GRANT EXECUTE ON [dbo].[Exchange2013_MailboxStatsGroom] TO OpsMgrWriter
GO
-- ##### Exchange2013_MailboxStatsProcessStaging_CreateAlter.sql
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Exchange2013_MailboxStatsProcessStaging' AND uid = SCHEMA_ID('dbo'))
BEGIN
EXECUTE ('CREATE PROCEDURE [dbo].[Exchange2013_MailboxStatsProcessStaging] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Exchange2013_MailboxStatsProcessStaging]
@DatasetId uniqueidentifier
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON
DECLARE
@InsertTableName_Stats sysname
,@InsertTableName_PropertySet sysname
,@InsertTableGuid uniqueidentifier
,@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@MessageText nvarchar(max)
,@OperationDurationMs bigint
,@ExecResult int
,@DebugLevel int
,@LockResourceName sysname
,@TableGuid uniqueidentifier
,@SchemaName sysname
,@MaxRowsToProcessCount INT
,@MaxDataAgeDays INT
,@rowCount INT
SET @InsertStartedDateTime = GETUTCDATE()
-- get schema name and debug level
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- get maximum number of rows to process in one run, default = 10000
SELECT @MaxRowsToProcessCount = ISNULL(MaxRowsToProcessPerTransactionCount,10000)
FROM StandardDatasetStagingArea
WHERE (DatasetId = @DatasetId)
-- Maximum age for raw data - we will not process old records
SELECT @MaxDataAgeDays = sda.MaxDataAgeDays
FROM StandardDatasetAggregation sda
WHERE sda.DatasetId=@DatasetId AND sda.AggregationTypeId=0
-- Groom staging - delete old records
DELETE FROM Exchange2013.MailboxStatsStaging WHERE DWCreatedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE())
-- temporary table for rows to be processed - we don't want excessive locks on staging area
IF OBJECT_ID('Exchange2013.MailboxStatsStaging2Process') IS NOT NULL DROP TABLE Exchange2013.MailboxStatsStaging2Process
CREATE TABLE Exchange2013.MailboxStatsStaging2Process
(
DatasetId uniqueidentifier NOT NULL
,ManagementGroupGuid uniqueidentifier NOT NULL
,[DateTime] datetime NULL
,[Mailbox_ArchiveName] NVARCHAR(256) NULL
,[Mailbox_ArchiveState] NVARCHAR(32) NULL
,[Mailbox_ArchiveStatus] NVARCHAR(32) NULL
,[Mailbox_CustomAttribute1] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute10] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute11] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute12] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute13] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute14] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute15] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute2] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute3] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute4] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute5] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute6] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute7] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute8] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute9] NVARCHAR(1024) NULL
,[Mailbox_Database] NVARCHAR(1024) NOT NULL
,[Mailbox_DistinguishedName] NVARCHAR(1024) NOT NULL
,[Mailbox_EmailAddresses] NVARCHAR(1024) NOT NULL
,[Mailbox_EmailAddressPolicyEnabled] BIT NULL
,[Mailbox_ExchangeGuid] NVARCHAR(64) NOT NULL
,[Mailbox_ExchangeVersion] NVARCHAR(32) NOT NULL
,[Mailbox_Identity] NVARCHAR(256) NOT NULL
,[Mailbox_IsExcludedFromServingHierarchy] BIT NULL
,[Mailbox_IsLinked] BIT NULL
,[Mailbox_IsMachineToPersonTextMessagingEnabled] BIT NULL
,[Mailbox_IsMailboxEnabled] BIT NULL
,[Mailbox_IsPersonToPersonTextMessagingEnabled] BIT NULL
,[Mailbox_IsResource] BIT NULL
,[Mailbox_IsRootPublicFolderMailbox] BIT NULL
,[Mailbox_IsShared] BIT NULL
,[Mailbox_IsSoftDeletedByDisable] BIT NULL
,[Mailbox_IsSoftDeletedByRemove] BIT NULL
,[Mailbox_IsValid] BIT NULL
,[Mailbox_Name] NVARCHAR(256) NOT NULL
,[Mailbox_ObjectCategory] NVARCHAR(128) NULL
,[Mailbox_ObjectClass] NVARCHAR(128) NULL
,[Mailbox_OrganizationalUnit] NVARCHAR(1024) NOT NULL
,[Mailbox_RecipientType] NVARCHAR(64) NULL
,[Mailbox_ServerName] NVARCHAR(256) NOT NULL
,[Mailbox_UserPrincipalName] NVARCHAR(256) NOT NULL
,[Mailbox_WhenChangedUTC] DATETIME NULL
,[Mailbox_WhenCreatedUTC] DATETIME NULL
,[MailboxStatistics_AssociatedItemCount] Bigint NULL
,[MailboxStatistics_DeletedItemCount] Bigint NULL
,[MailboxStatistics_IsArchiveMailbox] BIT NULL
,[MailboxStatistics_IsMoveDestination] BIT NULL
,[MailboxStatistics_IsQuarantined] BIT NULL
,[MailboxStatistics_IsValid] BIT NULL
,[MailboxStatistics_ItemCount] Bigint NULL
,[MailboxStatistics_LastLogoffTime] DATETIME NULL
,[MailboxStatistics_LastLogonTime] DATETIME NULL
,[MailboxStatistics_MailboxGuid] NVARCHAR(64) NULL
,[MailboxStatistics_MailboxType] NVARCHAR(128) NULL
,[MailboxStatistics_TotalDeletedItemSizeMB] DECIMAL NULL
,[MailboxStatistics_TotalItemSizeMB] DECIMAL NULL
,[ManagedEntityGuid] NVARCHAR(64) NULL
,MailboxStatsStagingRowId bigint NOT NULL
,DWCreatedDateTime DATETIME
,PropertySetIteration INT NULL
,MailboxRowId INT NULL
,PropertySetGuid UNIQUEIDENTIFIER NULL
,IsNew BIT DEFAULT 0
,IsPropertySetForInsertion BIT DEFAULT 0
,PropertySetXml XML
)
IF @DebugLevel > 2
BEGIN
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = 'Mailbox stats staging processing transaction - starting'
END
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
-- get destination table
EXEC dbo.StandardDatasetGetInsertTableGuid @DatasetId, 0, @InsertTableGuid OUTPUT
SELECT @InsertTableName_Stats = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
AND TableTag='MbxStatsRaw'
SELECT @InsertTableName_PropertySet = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 1)
AND TableTag='MbxProperties'
INSERT Exchange2013.MailboxStatsStaging2Process
(
DatasetId,
ManagementGroupGuid,
[DateTime],
Mailbox_ArchiveName,
Mailbox_ArchiveState,
Mailbox_ArchiveStatus,
Mailbox_CustomAttribute1,
Mailbox_CustomAttribute10,
Mailbox_CustomAttribute11,
Mailbox_CustomAttribute12,
Mailbox_CustomAttribute13,
Mailbox_CustomAttribute14,
Mailbox_CustomAttribute15,
Mailbox_CustomAttribute2,
Mailbox_CustomAttribute3,
Mailbox_CustomAttribute4,
Mailbox_CustomAttribute5,
Mailbox_CustomAttribute6,
Mailbox_CustomAttribute7,
Mailbox_CustomAttribute8,
Mailbox_CustomAttribute9,
Mailbox_Database,
Mailbox_DistinguishedName,
Mailbox_EmailAddresses,
Mailbox_EmailAddressPolicyEnabled,
Mailbox_ExchangeGuid,
Mailbox_ExchangeVersion,
Mailbox_Identity,
Mailbox_IsExcludedFromServingHierarchy,
Mailbox_IsLinked,
Mailbox_IsMachineToPersonTextMessagingEnabled,
Mailbox_IsMailboxEnabled,
Mailbox_IsPersonToPersonTextMessagingEnabled,
Mailbox_IsResource,
Mailbox_IsRootPublicFolderMailbox,
Mailbox_IsShared,
Mailbox_IsSoftDeletedByDisable,
Mailbox_IsSoftDeletedByRemove,
Mailbox_IsValid,
Mailbox_Name,
Mailbox_ObjectCategory,
Mailbox_ObjectClass,
Mailbox_OrganizationalUnit,
Mailbox_RecipientType,
Mailbox_ServerName,
Mailbox_UserPrincipalName,
Mailbox_WhenChangedUTC,
Mailbox_WhenCreatedUTC,
MailboxStatistics_AssociatedItemCount,
MailboxStatistics_DeletedItemCount,
MailboxStatistics_IsArchiveMailbox,
MailboxStatistics_IsMoveDestination,
MailboxStatistics_IsQuarantined,
MailboxStatistics_IsValid,
MailboxStatistics_ItemCount,
MailboxStatistics_LastLogoffTime,
MailboxStatistics_LastLogonTime,
MailboxStatistics_MailboxGuid,
MailboxStatistics_MailboxType,
MailboxStatistics_TotalDeletedItemSizeMB,
MailboxStatistics_TotalItemSizeMB,
ManagedEntityGuid,
MailboxStatsStagingRowId,
DWCreatedDateTime,
PropertySetIteration,
PropertySetGuid,
PropertySetXml
)
SELECT TOP(@MaxRowsToProcessCount)
DatasetId,
ManagementGroupGuid,
[DateTime],
Mailbox_ArchiveName,
Mailbox_ArchiveState,
Mailbox_ArchiveStatus,
Mailbox_CustomAttribute1,
Mailbox_CustomAttribute10,
Mailbox_CustomAttribute11,
Mailbox_CustomAttribute12,
Mailbox_CustomAttribute13,
Mailbox_CustomAttribute14,
Mailbox_CustomAttribute15,
Mailbox_CustomAttribute2,
Mailbox_CustomAttribute3,
Mailbox_CustomAttribute4,
Mailbox_CustomAttribute5,
Mailbox_CustomAttribute6,
Mailbox_CustomAttribute7,
Mailbox_CustomAttribute8,
Mailbox_CustomAttribute9,
Mailbox_Database,
Mailbox_DistinguishedName,
Mailbox_EmailAddresses,
Mailbox_EmailAddressPolicyEnabled,
Mailbox_ExchangeGuid,
Mailbox_ExchangeVersion,
Mailbox_Identity,
Mailbox_IsExcludedFromServingHierarchy,
Mailbox_IsLinked,
Mailbox_IsMachineToPersonTextMessagingEnabled,
Mailbox_IsMailboxEnabled,
Mailbox_IsPersonToPersonTextMessagingEnabled,
Mailbox_IsResource,
Mailbox_IsRootPublicFolderMailbox,
Mailbox_IsShared,
Mailbox_IsSoftDeletedByDisable,
Mailbox_IsSoftDeletedByRemove,
Mailbox_IsValid,
Mailbox_Name,
Mailbox_ObjectCategory,
Mailbox_ObjectClass,
Mailbox_OrganizationalUnit,
Mailbox_RecipientType,
Mailbox_ServerName,
Mailbox_UserPrincipalName,
Mailbox_WhenChangedUTC,
Mailbox_WhenCreatedUTC,
MailboxStatistics_AssociatedItemCount,
MailboxStatistics_DeletedItemCount,
MailboxStatistics_IsArchiveMailbox,
MailboxStatistics_IsMoveDestination,
MailboxStatistics_IsQuarantined,
MailboxStatistics_IsValid,
MailboxStatistics_ItemCount,
MailboxStatistics_LastLogoffTime,
MailboxStatistics_LastLogonTime,
MailboxStatistics_MailboxGuid,
MailboxStatistics_MailboxType,
MailboxStatistics_TotalDeletedItemSizeMB,
MailboxStatistics_TotalItemSizeMB,
ManagedEntityGuid,
MailboxStatsStagingRowId,
DWCreatedDateTime,
PropertySetIteration = ROW_NUMBER() OVER (PARTITION BY mss.Mailbox_ExchangeGuid ORDER BY mss.[DateTime]),
PropertySetGuid = NEWID(),
PropertySetXml = CAST(RawItem AS XML)
FROM Exchange2013.MailboxStatsStaging mss
ORDER BY mss.[DateTime]
-- calculate property set xml
UPDATE mssp
SET PropertySetXml.modify('delete (/DataItem/@*[local-name()=("time","type","sourceHealthServiceId")])')
FROM Exchange2013.MailboxStatsStaging2Process mssp
UPDATE mssp
SET PropertySetXml.modify('delete (/DataItem/Property[@Name=("DateTime",
"MailboxStatistics_AssociatedItemCount",
"MailboxStatistics_DeletedItemCount",
"MailboxStatistics_ItemCount",
"MailboxStatistics_LastLogoffTime",
"MailboxStatistics_LastLogonTime",
"MailboxStatistics_TotalDeletedItemSizeMB",
"MailboxStatistics_TotalItemSizeMB",
"MailboxStatistics_DeletedItemCount"
)])')
FROM Exchange2013.MailboxStatsStaging2Process mssp
-- resolve known mailboxes
UPDATE mssp
SET
MailboxRowId = m.MailboxRowId
FROM
Exchange2013.Mailbox m
INNER JOIN Exchange2013.MailboxStatsStaging2Process mssp ON m.ExchangeGuid=mssp.Mailbox_ExchangeGuid
-- some old property sets may be already groomed - lets's fix that for mailboxes in the processing queue
UPDATE m
SET
LatestPropertySetGuid = NULL,
LatestPropertySet = NULL
FROM
Exchange2013.Mailbox m
INNER JOIN Exchange2013.MailboxStatsStaging2Process mssp ON mssp.MailboxRowId = m.MailboxRowId
LEFT OUTER JOIN Exchange2013.vMailboxProperties vmp ON m.LatestPropertySetGuid=vmp.PropertySetGuid
WHERE
vmp.PropertySetGuid IS NULL
-- add mailboxes that are missing
INSERT INTO Exchange2013.Mailbox
(
ExchangeGuid,
WhenCreatedUTC,
LatestPropertySetGuid,
LatestPropertySet
)
SELECT
mssp.Mailbox_ExchangeGuid,
mssp.Mailbox_WhenCreatedUTC,
mssp.PropertySetGuid,
mssp.PropertySetXml
FROM
Exchange2013.MailboxStatsStaging2Process mssp
WHERE
mssp.PropertySetIteration = 1
AND mssp.MailboxRowId IS NULL
-- resolve knew mailboxes
UPDATE mssp
SET
MailboxRowId = m.MailboxRowId,
IsNew = 1,
IsPropertySetForInsertion = CASE WHEN PropertySetIteration = 1 THEN 1 ELSE 0 END
FROM
Exchange2013.Mailbox m
INNER JOIN Exchange2013.MailboxStatsStaging2Process mssp ON m.ExchangeGuid=mssp.Mailbox_ExchangeGuid
WHERE
mssp.MailboxRowId IS NULL
-- LastReceivedDateTime
UPDATE m
SET LastReceivedDateTime = x.MaxDT
FROM
Exchange2013.Mailbox m
INNER JOIN
(SELECT mssp.MailboxRowId, MaxDT = MAX(mssp.DWCreatedDateTime) FROM Exchange2013.MailboxStatsStaging2Process mssp GROUP BY mssp.MailboxRowId ) x ON m.MailboxRowId=x.MailboxRowId
-- check which property sets should be saved
IF OBJECT_ID('tempdb..#SetsForInsertion') IS NOT NULL DROP TABLE #SetsForInsertion
CREATE TABLE #SetsForInsertion
(
PropertySetGuid UNIQUEIDENTIFIER
)
DECLARE
@CurrentIteration INT,
@MaxIteration INT
SELECT @CurrentIteration = 1, @MaxIteration = MAX(mssp.PropertySetIteration) FROM Exchange2013.MailboxStatsStaging2Process mssp
WHILE @CurrentIteration <= @MaxIteration
BEGIN
UPDATE m
SET
LatestPropertySet = mssp.PropertySetXml,
LatestPropertySetGuid = mssp.PropertySetGuid
OUTPUT INSERTED.LatestPropertySetGuid INTO #SetsForInsertion(PropertySetGuid)
FROM
Exchange2013.Mailbox m
INNER JOIN Exchange2013.MailboxStatsStaging2Process mssp
ON mssp.MailboxRowId = m.MailboxRowId AND mssp.PropertySetIteration=@CurrentIteration
AND ISNULL(CAST(mssp.PropertySetXml AS NVARCHAR(MAX)),'')<>ISNULL(CAST(m.LatestPropertySet AS NVARCHAR(MAX)),'')
SET @CurrentIteration += 1
END
-- mark property sets for insertion
UPDATE mssp
SET IsPropertySetForInsertion = 1
FROM
Exchange2013.MailboxStatsStaging2Process mssp
INNER JOIN #SetsForInsertion i ON i.PropertySetGuid = mssp.PropertySetGuid
-- insert property sets
SET @Statement =
'INSERT INTO Exchange2013.['+@InsertTableName_PropertySet+']
(
DWCreatedDateTime,
[DateTime],
MailboxRowId,
PropertySetGuid,
ArchiveName,
ArchiveState,
ArchiveStatus,
CustomAttribute1,
CustomAttribute10,
CustomAttribute11,
CustomAttribute12,
CustomAttribute13,
CustomAttribute14,
CustomAttribute15,
CustomAttribute2,
CustomAttribute3,
CustomAttribute4,
CustomAttribute5,
CustomAttribute6,
CustomAttribute7,
CustomAttribute8,
CustomAttribute9,
[Database],
DistinguishedName,
EmailAddresses,
EmailAddressPolicyEnabled,
ExchangeVersion,
[Identity],
IsExcludedFromServingHierarchy,
IsLinked,
IsMachineToPersonTextMessagingEnabled,
IsMailboxEnabled,
IsPersonToPersonTextMessagingEnabled,
IsResource,
IsRootPublicFolderMailbox,
IsShared,
IsSoftDeletedByDisable,
IsSoftDeletedByRemove,
IsValid,
Name,
ObjectCategory,
ObjectClass,
OrganizationalUnit,
RecipientType,
ServerName,
UserPrincipalName,
WhenChangedUTC,
IsArchiveMailbox,
IsMoveDestination,
IsQuarantined,
MailboxType,
ManagedEntityGuid
)
SELECT
mssp.DWCreatedDateTime, mssp.[DateTime], mssp.MailboxRowId, mssp.PropertySetGuid,
Mailbox_ArchiveName,
Mailbox_ArchiveState,
Mailbox_ArchiveStatus,
Mailbox_CustomAttribute1,
Mailbox_CustomAttribute10,
Mailbox_CustomAttribute11,
Mailbox_CustomAttribute12,
Mailbox_CustomAttribute13,
Mailbox_CustomAttribute14,
Mailbox_CustomAttribute15,
Mailbox_CustomAttribute2,
Mailbox_CustomAttribute3,
Mailbox_CustomAttribute4,
Mailbox_CustomAttribute5,
Mailbox_CustomAttribute6,
Mailbox_CustomAttribute7,
Mailbox_CustomAttribute8,
Mailbox_CustomAttribute9,
Mailbox_Database,
Mailbox_DistinguishedName,
Mailbox_EmailAddresses,
Mailbox_EmailAddressPolicyEnabled,
Mailbox_ExchangeVersion,
Mailbox_Identity,
Mailbox_IsExcludedFromServingHierarchy,
Mailbox_IsLinked,
Mailbox_IsMachineToPersonTextMessagingEnabled,
Mailbox_IsMailboxEnabled,
Mailbox_IsPersonToPersonTextMessagingEnabled,
Mailbox_IsResource,
Mailbox_IsRootPublicFolderMailbox,
Mailbox_IsShared,
Mailbox_IsSoftDeletedByDisable,
Mailbox_IsSoftDeletedByRemove,
Mailbox_IsValid,
Mailbox_Name,
Mailbox_ObjectCategory,
Mailbox_ObjectClass,
Mailbox_OrganizationalUnit,
Mailbox_RecipientType,
Mailbox_ServerName,
Mailbox_UserPrincipalName,
Mailbox_WhenChangedUTC,
MailboxStatistics_IsArchiveMailbox,
MailboxStatistics_IsMoveDestination,
MailboxStatistics_IsQuarantined,
MailboxStatistics_MailboxType,
ManagedEntityGuid
FROM
Exchange2013.MailboxStatsStaging2Process mssp
WHERE mssp.IsPropertySetForInsertion=1'
EXEC(@Statement)
-- write mailbox statistics
SET @Statement =
'INSERT INTO Exchange2013.['+@InsertTableName_Stats+']
(
DWCreatedDateTime,
[DateTime],
MailboxRowId,
AssociatedItemCount,
DeletedItemCount,
ItemCount,
LastLogoffTime,
LastLogonTime,
TotalDeletedItemSizeMB,
TotalItemSizeMB
)
SELECT
mssp.DWCreatedDateTime,
mssp.[DateTime],
mssp.MailboxRowId,
mssp.MailboxStatistics_AssociatedItemCount,
mssp.MailboxStatistics_DeletedItemCount,
mssp.MailboxStatistics_ItemCount,
mssp.MailboxStatistics_LastLogoffTime,
mssp.MailboxStatistics_LastLogonTime,
mssp.MailboxStatistics_TotalDeletedItemSizeMB,
mssp.MailboxStatistics_TotalItemSizeMB
FROM
Exchange2013.MailboxStatsStaging2Process mssp
'
EXEC(@Statement)
DELETE
FROM
mss
FROM
Exchange2013.MailboxStatsStaging mss
INNER JOIN Exchange2013.MailboxStatsStaging2Process mssp ON mssp.MailboxStatsStagingRowId = mss.MailboxStatsStagingRowId
COMMIT TRAN
IF OBJECT_ID('Exchange2013.MailboxStatsStaging2Process') IS NOT NULL DROP TABLE Exchange2013.MailboxStatsStaging2Process
DECLARE @totalDurationMS INT
SET @totalDurationMS = DATEDIFF(ms,@InsertStartedDateTime,GETUTCDATE())
IF @DebugLevel > 2
BEGIN
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = 'Mailbox stats staging processing transaction - committed'
,@OperationDurationMs=@totalDurationMS
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
DECLARE
@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to process mailbox stats staging area. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = @DebugMessageText
END
RAISERROR(@ErrorMessageText, 16, 1)
END CATCH
END
GO
GRANT EXECUTE ON [dbo].[Exchange2013_MailboxStatsProcessStaging] TO OpsMgrWriter
GO
</Install>
<Uninstall>IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'Exchange2013_MailboxStatsAggregate' AND ROUTINE_SCHEMA = 'dbo')
BEGIN
DROP PROCEDURE dbo.Exchange2013_MailboxStatsAggregate
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'Exchange2013_MailboxStatsDelete' AND ROUTINE_SCHEMA = 'dbo')
BEGIN
DROP PROCEDURE dbo.Exchange2013_MailboxStatsDelete
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'Exchange2013_MailboxStatsGroom' AND ROUTINE_SCHEMA = 'dbo')
BEGIN
DROP PROCEDURE dbo.Exchange2013_MailboxStatsGroom
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'Exchange2013_MailboxStatsProcessStaging' AND ROUTINE_SCHEMA = 'dbo')
BEGIN
DROP PROCEDURE dbo.Exchange2013_MailboxStatsProcessStaging
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vMailbox' AND TABLE_SCHEMA = 'Exchange2013')
BEGIN
DROP VIEW Exchange2013.vMailbox
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Mailbox' AND TABLE_SCHEMA = 'Exchange2013')
BEGIN
DROP TABLE [Exchange2013].[Mailbox]
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MailboxStatsStaging' AND TABLE_SCHEMA = 'Exchange2013')
BEGIN
DROP TABLE [Exchange2013].[MailboxStatsStaging]
END
IF EXISTS (SELECT * FROM StandardDataset sd WHERE sd.SchemaName='Exchange2013' AND sd.StagingProcessorStoredProcedureName='Exchange2013_MailboxStatsProcessStaging')
BEGIN
DECLARE @DatasetGuid UNIQUEIDENTIFIER
SELECT @DatasetGuid = sd.DatasetId FROM StandardDataset sd WHERE sd.SchemaName='Exchange2013' AND sd.StagingProcessorStoredProcedureName='Exchange2013_MailboxStatsProcessStaging'
EXEC StandardDatasetDelete @DatasetGuid
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'Exchange2013') AND NOT EXISTS (SELECT * FROM StandardDataset sd WHERE sd.SchemaName='Exchange2013')
BEGIN
DROP SCHEMA Exchange2013
END
</Uninstall>
<Upgrade>-- ##### Exchange2013_MailboxStatsAggregate_CreateAlter.sql
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Exchange2013_MailboxStatsAggregate' AND uid = SCHEMA_ID('dbo'))
BEGIN
EXECUTE ('CREATE PROCEDURE [dbo].[Exchange2013_MailboxStatsAggregate] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Exchange2013_MailboxStatsAggregate]
@DatasetId UNIQUEIDENTIFIER,
@AggregationTypeId INT,
@InsertTableGuid VARCHAR(256), -- GUID of the aggregation table
@IntervalStartDateTime DATETIME,
@IntervalEndDateTime DATETIME
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
BEGIN TRY
DECLARE
@Statement nvarchar(max)
,@DebugLevel tinyint
,@InsertTableName sysname
,@DebugMessage nvarchar(max)
,@AggregationStartDateTime datetime
,@CoverViewName sysname
,@SchemaName sysname
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
SELECT @CoverViewName = 'v' + BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
SELECT @InsertTableName = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Starting aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + 'UTC to '
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + 'UTC'
SET @AggregationStartDateTime = GETUTCDATE()
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
END
SET @Statement =
'
;WITH cte (MailboxRowId, MailboxStatsRawRowId, RowNo)
AS
(
SELECT
vmsr.MailboxRowId,
vmsr.MailboxStatsRawRowId,
RowNo = ROW_NUMBER() OVER (PARTITION BY vmsr.MailboxRowId ORDER BY vmsr.[DateTime] DESC)
FROM
' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@CoverViewName) +' vmsr
WHERE (vmsr.[DateTime] >= CONVERT(datetime, ''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''', 120))
AND (vmsr.[DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @IntervalEndDateTime, 120) + ''', 120))
)
INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@InsertTableName) + '
(
DWCreatedDateTime,
[DateTime],
MailboxRowId,
AssociatedItemCount,
DeletedItemCount,
ItemCount,
LastLogoffTime,
LastLogonTime,
TotalDeletedItemSizeMB,
TotalItemSizeMB
)
SELECT
GETUTCDATE(),
CONVERT(datetime, ''' + CONVERT(varchar(50), @IntervalStartDateTime, 120) + ''', 120),
vmsr.MailboxRowId,
vmsr.AssociatedItemCount,
vmsr.DeletedItemCount,
vmsr.ItemCount,
vmsr.LastLogoffTime,
vmsr.LastLogonTime,
vmsr.TotalDeletedItemSizeMB,
vmsr.TotalItemSizeMB
FROM
' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@CoverViewName) +' vmsr
INNER JOIN cte c ON vmsr.MailboxStatsRawRowId=c.MailboxStatsRawRowId AND c.RowNo = 1
'
EXECUTE (@Statement)
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = 'Finished aggregation of type ' + CAST(@AggregationTypeId AS varchar(10)) + ' for interval from '
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + 'UTC to '
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + 'UTC'
DECLARE @OperationDurationMs bigint
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @AggregationStartDateTime))
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
,@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
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to build aggregates for Exchange2013 data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@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
END
GO
GRANT EXECUTE ON [dbo].[Exchange2013_MailboxStatsAggregate] TO OpsMgrWriter
GO
-- ##### Exchange2013_MailboxStatsDelete_CreateAlter.sql
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Exchange2013_MailboxStatsDelete' AND uid = SCHEMA_ID('dbo'))
BEGIN
EXECUTE ('CREATE PROCEDURE [dbo].[Exchange2013_MailboxStatsDelete] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Exchange2013_MailboxStatsDelete]
@DatasetId UNIQUEIDENTIFIER,
@AggregationTypeId TINYINT,
@TableGuid UNIQUEIDENTIFIER,
@AggregationDateTime DATETIME,
@RowsDeleted INT OUTPUT
AS
BEGIN
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
DECLARE
@TableName sysname
,@Statement nvarchar(max)
,@DebugMessage nvarchar(max)
,@DeleteStartedDateTime datetime
,@OperationDurationMs bigint
,@DebugLevel tinyint
,@SchemaName sysname
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- there are no dependent tables in state data set
SELECT @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
-- Index hint in the query below ensures that SQL won't
-- consider index on DateTime field to be a good source
-- to find rows to delete - it may happen periodically
-- if SQL thinks there's no rows to delete due to
-- lack of [recently updated] statistics
SET @Statement = 'DELETE t' +
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' t /*WITH (INDEX(1))*/'
+ ' WHERE (t.[DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
IF (@DebugLevel > 2)
BEGIN
SELECT @DebugMessage = 'Starting delete of existing performance aggregated data. Table: ' + @TableName + ', aggregation date: ' + CONVERT(varchar(20), @AggregationDateTime, 120)
SET @DeleteStartedDateTime = GETUTCDATE()
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
END
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT
IF (@DebugLevel > 2)
BEGIN
SELECT @DebugMessage = 'Finished delete of existing performance aggregated data. Table: ' + @TableName + ', aggregation date: ' + CONVERT(varchar(20), @AggregationDateTime, 120)
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @DeleteStartedDateTime))
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
,@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
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to delete aggregated data for Exchange2013 data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = @ErrorMessageText
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
GO
GRANT EXECUTE ON [dbo].[Exchange2013_MailboxStatsDelete] TO OpsMgrWriter
GO
-- ##### Exchange2013_MailboxStatsGroom_CreateAlter.sql
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Exchange2013_MailboxStatsGroom' AND uid = SCHEMA_ID('dbo'))
BEGIN
EXECUTE ('CREATE PROCEDURE [dbo].[Exchange2013_MailboxStatsGroom] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Exchange2013_MailboxStatsGroom]
@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)
SET @ErrorInd = 0
DECLARE
@TableName sysname
,@Statement nvarchar(max)
,@DebugLevel tinyint
,@SchemaName sysname
BEGIN TRY
BEGIN TRAN
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
DECLARE @tables TABLE
(
TableName SYSNAME,
Id INT IDENTITY(1,1)
)
INSERT INTO @tables
(
TableName
)
SELECT BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
ORDER BY DependentTableInd DESC
DECLARE @i INT, @maxi INT
SELECT @i = MIN(id), @maxi = MAX(id), @RowsDeleted=0 FROM @tables
WHILE @i<=@maxi
BEGIN
SELECT @TableName = TableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM @tables WHERE Id=@i
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
SET @i += 1
END
-- groom dynamic dimentions
DECLARE @MaxDataAgeDays int
SELECT @MaxDataAgeDays = MAX(MaxDataAgeDays)
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
DELETE
FROM Exchange2013.Mailbox
WHERE LastReceivedDateTime < DATEADD(day, -@MaxDataAgeDays, GETUTCDATE())
OPTION (RECOMPILE)
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
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to groom data for Exchange2013 data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@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
GO
GRANT EXECUTE ON [dbo].[Exchange2013_MailboxStatsGroom] TO OpsMgrWriter
GO
-- ##### Exchange2013_MailboxStatsProcessStaging_CreateAlter.sql
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Exchange2013_MailboxStatsProcessStaging' AND uid = SCHEMA_ID('dbo'))
BEGIN
EXECUTE ('CREATE PROCEDURE [dbo].[Exchange2013_MailboxStatsProcessStaging] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Exchange2013_MailboxStatsProcessStaging]
@DatasetId uniqueidentifier
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON
DECLARE
@InsertTableName_Stats sysname
,@InsertTableName_PropertySet sysname
,@InsertTableGuid uniqueidentifier
,@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@MessageText nvarchar(max)
,@OperationDurationMs bigint
,@ExecResult int
,@DebugLevel int
,@LockResourceName sysname
,@TableGuid uniqueidentifier
,@SchemaName sysname
,@MaxRowsToProcessCount INT
,@MaxDataAgeDays INT
,@rowCount INT
SET @InsertStartedDateTime = GETUTCDATE()
-- get schema name and debug level
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- get maximum number of rows to process in one run, default = 10000
SELECT @MaxRowsToProcessCount = ISNULL(MaxRowsToProcessPerTransactionCount,10000)
FROM StandardDatasetStagingArea
WHERE (DatasetId = @DatasetId)
-- Maximum age for raw data - we will not process old records
SELECT @MaxDataAgeDays = sda.MaxDataAgeDays
FROM StandardDatasetAggregation sda
WHERE sda.DatasetId=@DatasetId AND sda.AggregationTypeId=0
-- Groom staging - delete old records
DELETE FROM Exchange2013.MailboxStatsStaging WHERE DWCreatedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE())
-- temporary table for rows to be processed - we don't want excessive locks on staging area
IF OBJECT_ID('Exchange2013.MailboxStatsStaging2Process') IS NOT NULL DROP TABLE Exchange2013.MailboxStatsStaging2Process
CREATE TABLE Exchange2013.MailboxStatsStaging2Process
(
DatasetId uniqueidentifier NOT NULL
,ManagementGroupGuid uniqueidentifier NOT NULL
,[DateTime] datetime NULL
,[Mailbox_ArchiveName] NVARCHAR(256) NULL
,[Mailbox_ArchiveState] NVARCHAR(32) NULL
,[Mailbox_ArchiveStatus] NVARCHAR(32) NULL
,[Mailbox_CustomAttribute1] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute10] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute11] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute12] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute13] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute14] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute15] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute2] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute3] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute4] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute5] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute6] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute7] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute8] NVARCHAR(1024) NULL
,[Mailbox_CustomAttribute9] NVARCHAR(1024) NULL
,[Mailbox_Database] NVARCHAR(1024) NOT NULL
,[Mailbox_DistinguishedName] NVARCHAR(1024) NOT NULL
,[Mailbox_EmailAddresses] NVARCHAR(1024) NOT NULL
,[Mailbox_EmailAddressPolicyEnabled] BIT NULL
,[Mailbox_ExchangeGuid] NVARCHAR(64) NOT NULL
,[Mailbox_ExchangeVersion] NVARCHAR(32) NOT NULL
,[Mailbox_Identity] NVARCHAR(256) NOT NULL
,[Mailbox_IsExcludedFromServingHierarchy] BIT NULL
,[Mailbox_IsLinked] BIT NULL
,[Mailbox_IsMachineToPersonTextMessagingEnabled] BIT NULL
,[Mailbox_IsMailboxEnabled] BIT NULL
,[Mailbox_IsPersonToPersonTextMessagingEnabled] BIT NULL
,[Mailbox_IsResource] BIT NULL
,[Mailbox_IsRootPublicFolderMailbox] BIT NULL
,[Mailbox_IsShared] BIT NULL
,[Mailbox_IsSoftDeletedByDisable] BIT NULL
,[Mailbox_IsSoftDeletedByRemove] BIT NULL
,[Mailbox_IsValid] BIT NULL
,[Mailbox_Name] NVARCHAR(256) NOT NULL
,[Mailbox_ObjectCategory] NVARCHAR(128) NULL
,[Mailbox_ObjectClass] NVARCHAR(128) NULL
,[Mailbox_OrganizationalUnit] NVARCHAR(1024) NOT NULL
,[Mailbox_RecipientType] NVARCHAR(64) NULL
,[Mailbox_ServerName] NVARCHAR(256) NOT NULL
,[Mailbox_UserPrincipalName] NVARCHAR(256) NOT NULL
,[Mailbox_WhenChangedUTC] DATETIME NULL
,[Mailbox_WhenCreatedUTC] DATETIME NULL
,[MailboxStatistics_AssociatedItemCount] Bigint NULL
,[MailboxStatistics_DeletedItemCount] Bigint NULL
,[MailboxStatistics_IsArchiveMailbox] BIT NULL
,[MailboxStatistics_IsMoveDestination] BIT NULL
,[MailboxStatistics_IsQuarantined] BIT NULL
,[MailboxStatistics_IsValid] BIT NULL
,[MailboxStatistics_ItemCount] Bigint NULL
,[MailboxStatistics_LastLogoffTime] DATETIME NULL
,[MailboxStatistics_LastLogonTime] DATETIME NULL
,[MailboxStatistics_MailboxGuid] NVARCHAR(64) NULL
,[MailboxStatistics_MailboxType] NVARCHAR(128) NULL
,[MailboxStatistics_TotalDeletedItemSizeMB] DECIMAL NULL
,[MailboxStatistics_TotalItemSizeMB] DECIMAL NULL
,[ManagedEntityGuid] NVARCHAR(64) NULL
,MailboxStatsStagingRowId bigint NOT NULL
,DWCreatedDateTime DATETIME
,PropertySetIteration INT NULL
,MailboxRowId INT NULL
,PropertySetGuid UNIQUEIDENTIFIER NULL
,IsNew BIT DEFAULT 0
,IsPropertySetForInsertion BIT DEFAULT 0
,PropertySetXml XML
)
IF @DebugLevel > 2
BEGIN
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = 'Mailbox stats staging processing transaction - starting'
END
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
-- get destination table
EXEC dbo.StandardDatasetGetInsertTableGuid @DatasetId, 0, @InsertTableGuid OUTPUT
SELECT @InsertTableName_Stats = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
AND TableTag='MbxStatsRaw'
SELECT @InsertTableName_PropertySet = BaseTableName + '_' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 1)
AND TableTag='MbxProperties'
INSERT Exchange2013.MailboxStatsStaging2Process
(
DatasetId,
ManagementGroupGuid,
[DateTime],
Mailbox_ArchiveName,
Mailbox_ArchiveState,
Mailbox_ArchiveStatus,
Mailbox_CustomAttribute1,
Mailbox_CustomAttribute10,
Mailbox_CustomAttribute11,
Mailbox_CustomAttribute12,
Mailbox_CustomAttribute13,
Mailbox_CustomAttribute14,
Mailbox_CustomAttribute15,
Mailbox_CustomAttribute2,
Mailbox_CustomAttribute3,
Mailbox_CustomAttribute4,
Mailbox_CustomAttribute5,
Mailbox_CustomAttribute6,
Mailbox_CustomAttribute7,
Mailbox_CustomAttribute8,
Mailbox_CustomAttribute9,
Mailbox_Database,
Mailbox_DistinguishedName,
Mailbox_EmailAddresses,
Mailbox_EmailAddressPolicyEnabled,
Mailbox_ExchangeGuid,
Mailbox_ExchangeVersion,
Mailbox_Identity,
Mailbox_IsExcludedFromServingHierarchy,
Mailbox_IsLinked,
Mailbox_IsMachineToPersonTextMessagingEnabled,
Mailbox_IsMailboxEnabled,
Mailbox_IsPersonToPersonTextMessagingEnabled,
Mailbox_IsResource,
Mailbox_IsRootPublicFolderMailbox,
Mailbox_IsShared,
Mailbox_IsSoftDeletedByDisable,
Mailbox_IsSoftDeletedByRemove,
Mailbox_IsValid,
Mailbox_Name,
Mailbox_ObjectCategory,
Mailbox_ObjectClass,
Mailbox_OrganizationalUnit,
Mailbox_RecipientType,
Mailbox_ServerName,
Mailbox_UserPrincipalName,
Mailbox_WhenChangedUTC,
Mailbox_WhenCreatedUTC,
MailboxStatistics_AssociatedItemCount,
MailboxStatistics_DeletedItemCount,
MailboxStatistics_IsArchiveMailbox,
MailboxStatistics_IsMoveDestination,
MailboxStatistics_IsQuarantined,
MailboxStatistics_IsValid,
MailboxStatistics_ItemCount,
MailboxStatistics_LastLogoffTime,
MailboxStatistics_LastLogonTime,
MailboxStatistics_MailboxGuid,
MailboxStatistics_MailboxType,
MailboxStatistics_TotalDeletedItemSizeMB,
MailboxStatistics_TotalItemSizeMB,
ManagedEntityGuid,
MailboxStatsStagingRowId,
DWCreatedDateTime,
PropertySetIteration,
PropertySetGuid,
PropertySetXml
)
SELECT TOP(@MaxRowsToProcessCount)
DatasetId,
ManagementGroupGuid,
[DateTime],
Mailbox_ArchiveName,
Mailbox_ArchiveState,
Mailbox_ArchiveStatus,
Mailbox_CustomAttribute1,
Mailbox_CustomAttribute10,
Mailbox_CustomAttribute11,
Mailbox_CustomAttribute12,
Mailbox_CustomAttribute13,
Mailbox_CustomAttribute14,
Mailbox_CustomAttribute15,
Mailbox_CustomAttribute2,
Mailbox_CustomAttribute3,
Mailbox_CustomAttribute4,
Mailbox_CustomAttribute5,
Mailbox_CustomAttribute6,
Mailbox_CustomAttribute7,
Mailbox_CustomAttribute8,
Mailbox_CustomAttribute9,
Mailbox_Database,
Mailbox_DistinguishedName,
Mailbox_EmailAddresses,
Mailbox_EmailAddressPolicyEnabled,
Mailbox_ExchangeGuid,
Mailbox_ExchangeVersion,
Mailbox_Identity,
Mailbox_IsExcludedFromServingHierarchy,
Mailbox_IsLinked,
Mailbox_IsMachineToPersonTextMessagingEnabled,
Mailbox_IsMailboxEnabled,
Mailbox_IsPersonToPersonTextMessagingEnabled,
Mailbox_IsResource,
Mailbox_IsRootPublicFolderMailbox,
Mailbox_IsShared,
Mailbox_IsSoftDeletedByDisable,
Mailbox_IsSoftDeletedByRemove,
Mailbox_IsValid,
Mailbox_Name,
Mailbox_ObjectCategory,
Mailbox_ObjectClass,
Mailbox_OrganizationalUnit,
Mailbox_RecipientType,
Mailbox_ServerName,
Mailbox_UserPrincipalName,
Mailbox_WhenChangedUTC,
Mailbox_WhenCreatedUTC,
MailboxStatistics_AssociatedItemCount,
MailboxStatistics_DeletedItemCount,
MailboxStatistics_IsArchiveMailbox,
MailboxStatistics_IsMoveDestination,
MailboxStatistics_IsQuarantined,
MailboxStatistics_IsValid,
MailboxStatistics_ItemCount,
MailboxStatistics_LastLogoffTime,
MailboxStatistics_LastLogonTime,
MailboxStatistics_MailboxGuid,
MailboxStatistics_MailboxType,
MailboxStatistics_TotalDeletedItemSizeMB,
MailboxStatistics_TotalItemSizeMB,
ManagedEntityGuid,
MailboxStatsStagingRowId,
DWCreatedDateTime,
PropertySetIteration = ROW_NUMBER() OVER (PARTITION BY mss.Mailbox_ExchangeGuid ORDER BY mss.[DateTime]),
PropertySetGuid = NEWID(),
PropertySetXml = CAST(RawItem AS XML)
FROM Exchange2013.MailboxStatsStaging mss
ORDER BY mss.[DateTime]
-- calculate property set xml
UPDATE mssp
SET PropertySetXml.modify('delete (/DataItem/@*[local-name()=("time","type","sourceHealthServiceId")])')
FROM Exchange2013.MailboxStatsStaging2Process mssp
UPDATE mssp
SET PropertySetXml.modify('delete (/DataItem/Property[@Name=("DateTime",
"MailboxStatistics_AssociatedItemCount",
"MailboxStatistics_DeletedItemCount",
"MailboxStatistics_ItemCount",
"MailboxStatistics_LastLogoffTime",
"MailboxStatistics_LastLogonTime",
"MailboxStatistics_TotalDeletedItemSizeMB",
"MailboxStatistics_TotalItemSizeMB",
"MailboxStatistics_DeletedItemCount"
)])')
FROM Exchange2013.MailboxStatsStaging2Process mssp
-- resolve known mailboxes
UPDATE mssp
SET
MailboxRowId = m.MailboxRowId
FROM
Exchange2013.Mailbox m
INNER JOIN Exchange2013.MailboxStatsStaging2Process mssp ON m.ExchangeGuid=mssp.Mailbox_ExchangeGuid
-- some old property sets may be already groomed - lets's fix that for mailboxes in the processing queue
UPDATE m
SET
LatestPropertySetGuid = NULL,
LatestPropertySet = NULL
FROM
Exchange2013.Mailbox m
INNER JOIN Exchange2013.MailboxStatsStaging2Process mssp ON mssp.MailboxRowId = m.MailboxRowId
LEFT OUTER JOIN Exchange2013.vMailboxProperties vmp ON m.LatestPropertySetGuid=vmp.PropertySetGuid
WHERE
vmp.PropertySetGuid IS NULL
-- add mailboxes that are missing
INSERT INTO Exchange2013.Mailbox
(
ExchangeGuid,
WhenCreatedUTC,
LatestPropertySetGuid,
LatestPropertySet
)
SELECT
mssp.Mailbox_ExchangeGuid,
mssp.Mailbox_WhenCreatedUTC,
mssp.PropertySetGuid,
mssp.PropertySetXml
FROM
Exchange2013.MailboxStatsStaging2Process mssp
WHERE
mssp.PropertySetIteration = 1
AND mssp.MailboxRowId IS NULL
-- resolve knew mailboxes
UPDATE mssp
SET
MailboxRowId = m.MailboxRowId,
IsNew = 1,
IsPropertySetForInsertion = CASE WHEN PropertySetIteration = 1 THEN 1 ELSE 0 END
FROM
Exchange2013.Mailbox m
INNER JOIN Exchange2013.MailboxStatsStaging2Process mssp ON m.ExchangeGuid=mssp.Mailbox_ExchangeGuid
WHERE
mssp.MailboxRowId IS NULL
-- LastReceivedDateTime
UPDATE m
SET LastReceivedDateTime = x.MaxDT
FROM
Exchange2013.Mailbox m
INNER JOIN
(SELECT mssp.MailboxRowId, MaxDT = MAX(mssp.DWCreatedDateTime) FROM Exchange2013.MailboxStatsStaging2Process mssp GROUP BY mssp.MailboxRowId ) x ON m.MailboxRowId=x.MailboxRowId
-- check which property sets should be saved
IF OBJECT_ID('tempdb..#SetsForInsertion') IS NOT NULL DROP TABLE #SetsForInsertion
CREATE TABLE #SetsForInsertion
(
PropertySetGuid UNIQUEIDENTIFIER
)
DECLARE
@CurrentIteration INT,
@MaxIteration INT
SELECT @CurrentIteration = 1, @MaxIteration = MAX(mssp.PropertySetIteration) FROM Exchange2013.MailboxStatsStaging2Process mssp
WHILE @CurrentIteration <= @MaxIteration
BEGIN
UPDATE m
SET
LatestPropertySet = mssp.PropertySetXml,
LatestPropertySetGuid = mssp.PropertySetGuid
OUTPUT INSERTED.LatestPropertySetGuid INTO #SetsForInsertion(PropertySetGuid)
FROM
Exchange2013.Mailbox m
INNER JOIN Exchange2013.MailboxStatsStaging2Process mssp
ON mssp.MailboxRowId = m.MailboxRowId AND mssp.PropertySetIteration=@CurrentIteration
AND ISNULL(CAST(mssp.PropertySetXml AS NVARCHAR(MAX)),'')<>ISNULL(CAST(m.LatestPropertySet AS NVARCHAR(MAX)),'')
SET @CurrentIteration += 1
END
-- mark property sets for insertion
UPDATE mssp
SET IsPropertySetForInsertion = 1
FROM
Exchange2013.MailboxStatsStaging2Process mssp
INNER JOIN #SetsForInsertion i ON i.PropertySetGuid = mssp.PropertySetGuid
-- insert property sets
SET @Statement =
'INSERT INTO Exchange2013.['+@InsertTableName_PropertySet+']
(
DWCreatedDateTime,
[DateTime],
MailboxRowId,
PropertySetGuid,
ArchiveName,
ArchiveState,
ArchiveStatus,
CustomAttribute1,
CustomAttribute10,
CustomAttribute11,
CustomAttribute12,
CustomAttribute13,
CustomAttribute14,
CustomAttribute15,
CustomAttribute2,
CustomAttribute3,
CustomAttribute4,
CustomAttribute5,
CustomAttribute6,
CustomAttribute7,
CustomAttribute8,
CustomAttribute9,
[Database],
DistinguishedName,
EmailAddresses,
EmailAddressPolicyEnabled,
ExchangeVersion,
[Identity],
IsExcludedFromServingHierarchy,
IsLinked,
IsMachineToPersonTextMessagingEnabled,
IsMailboxEnabled,
IsPersonToPersonTextMessagingEnabled,
IsResource,
IsRootPublicFolderMailbox,
IsShared,
IsSoftDeletedByDisable,
IsSoftDeletedByRemove,
IsValid,
Name,
ObjectCategory,
ObjectClass,
OrganizationalUnit,
RecipientType,
ServerName,
UserPrincipalName,
WhenChangedUTC,
IsArchiveMailbox,
IsMoveDestination,
IsQuarantined,
MailboxType,
ManagedEntityGuid
)
SELECT
mssp.DWCreatedDateTime, mssp.[DateTime], mssp.MailboxRowId, mssp.PropertySetGuid,
Mailbox_ArchiveName,
Mailbox_ArchiveState,
Mailbox_ArchiveStatus,
Mailbox_CustomAttribute1,
Mailbox_CustomAttribute10,
Mailbox_CustomAttribute11,
Mailbox_CustomAttribute12,
Mailbox_CustomAttribute13,
Mailbox_CustomAttribute14,
Mailbox_CustomAttribute15,
Mailbox_CustomAttribute2,
Mailbox_CustomAttribute3,
Mailbox_CustomAttribute4,
Mailbox_CustomAttribute5,
Mailbox_CustomAttribute6,
Mailbox_CustomAttribute7,
Mailbox_CustomAttribute8,
Mailbox_CustomAttribute9,
Mailbox_Database,
Mailbox_DistinguishedName,
Mailbox_EmailAddresses,
Mailbox_EmailAddressPolicyEnabled,
Mailbox_ExchangeVersion,
Mailbox_Identity,
Mailbox_IsExcludedFromServingHierarchy,
Mailbox_IsLinked,
Mailbox_IsMachineToPersonTextMessagingEnabled,
Mailbox_IsMailboxEnabled,
Mailbox_IsPersonToPersonTextMessagingEnabled,
Mailbox_IsResource,
Mailbox_IsRootPublicFolderMailbox,
Mailbox_IsShared,
Mailbox_IsSoftDeletedByDisable,
Mailbox_IsSoftDeletedByRemove,
Mailbox_IsValid,
Mailbox_Name,
Mailbox_ObjectCategory,
Mailbox_ObjectClass,
Mailbox_OrganizationalUnit,
Mailbox_RecipientType,
Mailbox_ServerName,
Mailbox_UserPrincipalName,
Mailbox_WhenChangedUTC,
MailboxStatistics_IsArchiveMailbox,
MailboxStatistics_IsMoveDestination,
MailboxStatistics_IsQuarantined,
MailboxStatistics_MailboxType,
ManagedEntityGuid
FROM
Exchange2013.MailboxStatsStaging2Process mssp
WHERE mssp.IsPropertySetForInsertion=1'
EXEC(@Statement)
-- write mailbox statistics
SET @Statement =
'INSERT INTO Exchange2013.['+@InsertTableName_Stats+']
(
DWCreatedDateTime,
[DateTime],
MailboxRowId,
AssociatedItemCount,
DeletedItemCount,
ItemCount,
LastLogoffTime,
LastLogonTime,
TotalDeletedItemSizeMB,
TotalItemSizeMB
)
SELECT
mssp.DWCreatedDateTime,
mssp.[DateTime],
mssp.MailboxRowId,
mssp.MailboxStatistics_AssociatedItemCount,
mssp.MailboxStatistics_DeletedItemCount,
mssp.MailboxStatistics_ItemCount,
mssp.MailboxStatistics_LastLogoffTime,
mssp.MailboxStatistics_LastLogonTime,
mssp.MailboxStatistics_TotalDeletedItemSizeMB,
mssp.MailboxStatistics_TotalItemSizeMB
FROM
Exchange2013.MailboxStatsStaging2Process mssp
'
EXEC(@Statement)
DELETE
FROM
mss
FROM
Exchange2013.MailboxStatsStaging mss
INNER JOIN Exchange2013.MailboxStatsStaging2Process mssp ON mssp.MailboxStatsStagingRowId = mss.MailboxStatsStagingRowId
COMMIT TRAN
IF OBJECT_ID('Exchange2013.MailboxStatsStaging2Process') IS NOT NULL DROP TABLE Exchange2013.MailboxStatsStaging2Process
DECLARE @totalDurationMS INT
SET @totalDurationMS = DATEDIFF(ms,@InsertStartedDateTime,GETUTCDATE())
IF @DebugLevel > 2
BEGIN
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = 'Mailbox stats staging processing transaction - committed'
,@OperationDurationMs=@totalDurationMS
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
DECLARE
@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to process mailbox stats staging area. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 1
,@MessageText = @DebugMessageText
END
RAISERROR(@ErrorMessageText, 16, 1)
END CATCH
END
GO
GRANT EXECUTE ON [dbo].[Exchange2013_MailboxStatsProcessStaging] TO OpsMgrWriter
GO
</Upgrade>
</DataWarehouseDataSet>