Show overall stats with date filter
-- drop tables if still exists
IF OBJECT_ID('dbo.ArchiveEmailIds') IS NOT NULL DROP TABLE ArchiveEmailIds
IF OBJECT_ID('dbo.ExchangeArchiveStats') IS NOT NULL DROP TABLE ExchangeArchiveStats
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @log AS NVARCHAR(4000);
DECLARE @count INT;
SET @count = 10000;
DECLARE @start INT;
SET @start = 0;
DECLARE @total INT;
SET @total = 0;
DECLARE @affectedRowsCount INT;
SET @affectedRowsCount = 1;
DECLARE @msgDateFrom DATETIME;
SET @msgDateFrom = '01.01.2010';
DECLARE @msgDateTo DATETIME;
SET @msgDateTo = '12.31.2020';
-- create tables used to store stats
CREATE TABLE ArchiveEmailIds (
EmailId BIGINT NOT NULL
)
CREATE TABLE ExchangeArchiveStats (
[EmailId] BIGINT NOT NULL,
[Count] BIGINT NULL,
[Total Size MB] BIGINT NULL
)
-- get exchange archive stats
SET @log = 'Started getting archive stats.';
RAISERROR(@log, 0, 0) WITH NOWAIT;
BEGIN TRY
WHILE (@affectedRowsCount > 0)
BEGIN
TRUNCATE TABLE ArchiveEmailIds
INSERT INTO ArchiveEmailIds
select Top(@count) EmailID from
( select EMailId, ROW_NUMBER() over (order by EMailID) as RowId, EmailAddress from EmailAddress ea
where EmailAddress LIKE 'EX%'
) x where RowID > @start
ORDER by RowID
INSERT INTO ExchangeArchiveStats
SELECT [r].EmailId, count(DISTINCT(r.[MD5HashKey])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]
FROM [Route] AS [r]
INNER JOIN [ArchiveEmailIds] as [ids] ON [r].EmailId = [ids].EmailId
INNER JOIN [Message] as [m] ON [r].[MD5HashKey] = [m].[MD5HashKey] and [r].[TimeStamp] = [m].[TimeStamp]
INNER JOIN [VolumeMessage] AS [vm] ON [m].[MD5HashKey] = [vm].[MD5HashKey] and [m].[TimeStamp] = [vm].[TimeStamp]
INNER JOIN [Volume] AS [v] ON [vm].[VolumeId] = [v].[VolumeId]
INNER JOIN [Folder] AS [f] ON [r].[FolderId] = [f].[FolderId]
WHERE m.MsgDate >= @msgDateFrom AND m.MsgDate <= @msgDateTo
group by r.[EmailId];
SET @affectedRowsCount = @@ROWCOUNT;
SET @start = @start + @count;
SET @total = @total + @affectedRowsCount;
SET @log = 'Got stats for ' + CAST(@affectedRowsCount AS CHAR(10)) + ' archives';
RAISERROR(@log, 0, 0) WITH NOWAIT;
END
--drop temp tables
DROP TABLE ArchiveEmailIds
END TRY
BEGIN CATCH
DROP TABLE ArchiveEmailIds
DROP TABLE ExchangeArchiveStats
SELECT
@ErrorMessage=ERROR_MESSAGE();
RAISERROR('Error while getting exchange archives. Error: %s', 15, 1, @ErrorMessage)
END CATCH
SET @log = 'Finished getting stats for Exchange archives. Got total' + CAST(@total AS CHAR(10)) + ' archives';
RAISERROR(@log, 0, 0) WITH NOWAIT;
SET @log = 'Started getting overall stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;
-- get pst and journal stats
-- show all the stats results
SELECT Count(1) as archivesCount, SUM(stats.Count) as TotalCount, SUM(stats.[Total Size MB]) as TotalSizeMb FROM ExchangeArchiveStats stats
INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId
DROP TABLE ExchangeArchiveStats
SET @log = 'Finished getting overal stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;
Show overall stats per single archive
-- drop tables if still exists
IF OBJECT_ID('dbo.ArchiveEmailIds') IS NOT NULL DROP TABLE ArchiveEmailIds
IF OBJECT_ID('dbo.ExchangeArchiveStats') IS NOT NULL DROP TABLE ExchangeArchiveStats
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @log AS NVARCHAR(4000);
DECLARE @count INT;
SET @count = 10000;
DECLARE @start INT;
SET @start = 0;
DECLARE @total INT;
SET @total = 0;
DECLARE @affectedRowsCount INT;
SET @affectedRowsCount = 1;
CREATE TABLE ArchiveEmailIds(
EmailId BIGINT NOT NULL
)
CREATE TABLE ExchangeArchiveStats (
[EmailId] BIGINT NOT NULL,
[Count] BIGINT NULL,
[Total Size MB] BIGINT NULL
)
-- get exchange archive stats
SET @log = 'Started getting archive stats.';
RAISERROR(@log, 0, 0) WITH NOWAIT;
BEGIN TRY
WHILE (@affectedRowsCount > 0)
BEGIN
TRUNCATE TABLE ArchiveEmailIds
INSERT INTO ArchiveEmailIds
select Top(@count) EmailID from
( select EMailId, ROW_NUMBER() over (order by EMailID) as RowId, EmailAddress from EmailAddress ea
where EmailAddress LIKE 'EX%'
) x where RowID > @start
ORDER by RowID
INSERT INTO ExchangeArchiveStats
SELECT [r].EmailId, count(DISTINCT(r.[MD5HashKey])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]
FROM [Route] AS [r]
INNER JOIN [ArchiveEmailIds] as [ids] ON [r].EmailId = [ids].EmailId
INNER JOIN [Message] as [m] ON [r].[MD5HashKey] = [m].[MD5HashKey] and [r].[TimeStamp] = [m].[TimeStamp]
INNER JOIN [VolumeMessage] AS [vm] ON [m].[MD5HashKey] = [vm].[MD5HashKey] and [m].[TimeStamp] = [vm].[TimeStamp]
INNER JOIN [Volume] AS [v] ON [vm].[VolumeId] = [v].[VolumeId]
INNER JOIN [Folder] AS [f] ON [r].[FolderId] = [f].[FolderId]
group by r.[EmailId];
SET @affectedRowsCount = @@ROWCOUNT;
SET @start = @start + @count;
SET @total = @total + @affectedRowsCount;
SET @log = 'Got stats for ' + CAST(@affectedRowsCount AS CHAR(10)) + ' archives';
RAISERROR(@log, 0, 0) WITH NOWAIT;
END
-- drop temp tables
DROP TABLE ArchiveEmailIds
END TRY
BEGIN CATCH
DROP TABLE ArchiveEmailIds
DROP TABLE ExchangeArchiveStats
SELECT
@ErrorMessage=ERROR_MESSAGE();
RAISERROR('Error while getting exchange archives. Error: %s', 15, 1, @ErrorMessage)
END CATCH
SET @log = 'Finished getting stats for Exchange archives. Got total' + CAST(@total AS CHAR(10)) + ' archives';
RAISERROR(@log, 0, 0) WITH NOWAIT;
SET @log = 'Started getting overall stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;
-- get pst and journal stats
-- show all the stats results
SELECT ea.EmailAddress, ea.EmailId, stats.Count, stats.[Total Size MB] FROM ExchangeArchiveStats stats
INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId
DROP TABLE ExchangeArchiveStats
SET @log = 'Finished getting overal stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;
Show overall stats per single archive with date filtering
-- drop tables if still exists
IF OBJECT_ID('dbo.ArchiveEmailIds') IS NOT NULL DROP TABLE ArchiveEmailIds
IF OBJECT_ID('dbo.ExchangeArchiveStats') IS NOT NULL DROP TABLE ExchangeArchiveStats
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @log AS NVARCHAR(4000);
DECLARE @count INT;
SET @count = 10000;
DECLARE @start INT;
SET @start = 0;
DECLARE @total INT;
SET @total = 0;
DECLARE @affectedRowsCount INT;
SET @affectedRowsCount = 1;
DECLARE @msgDateFrom DATETIME;
SET @msgDateFrom = '01.01.2010';
DECLARE @msgDateTo DATETIME;
SET @msgDateTo = '12.31.2020';
-- create tables used to store stats
CREATE TABLE ArchiveEmailIds (
EmailId BIGINT NOT NULL
)
CREATE TABLE ExchangeArchiveStats (
[EmailId] BIGINT NOT NULL,
[Count] BIGINT NULL,
[Total Size MB] BIGINT NULL
)
-- get exchange archive stats
SET @log = 'Started getting archive stats.';
RAISERROR(@log, 0, 0) WITH NOWAIT;
BEGIN TRY
WHILE (@affectedRowsCount > 0)
BEGIN
TRUNCATE TABLE ArchiveEmailIds
INSERT INTO ArchiveEmailIds
select Top(@count) EmailID from
( select EMailId, ROW_NUMBER() over (order by EMailID) as RowId, EmailAddress from EmailAddress ea
where EmailAddress LIKE 'EX%'
) x where RowID > @start
ORDER by RowID
INSERT INTO ExchangeArchiveStats
SELECT [r].EmailId, count(DISTINCT(r.[MD5HashKey])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]
FROM [Route] AS [r]
INNER JOIN [ArchiveEmailIds] as [ids] ON [r].EmailId = [ids].EmailId
INNER JOIN [Message] as [m] ON [r].[MD5HashKey] = [m].[MD5HashKey] and [r].[TimeStamp] = [m].[TimeStamp]
INNER JOIN [VolumeMessage] AS [vm] ON [m].[MD5HashKey] = [vm].[MD5HashKey] and [m].[TimeStamp] = [vm].[TimeStamp]
INNER JOIN [Volume] AS [v] ON [vm].[VolumeId] = [v].[VolumeId]
INNER JOIN [Folder] AS [f] ON [r].[FolderId] = [f].[FolderId]
WHERE m.MsgDate >= @msgDateFrom AND m.MsgDate <= @msgDateTo
group by r.[EmailId];
SET @affectedRowsCount = @@ROWCOUNT;
SET @start = @start + @count;
SET @total = @total + @affectedRowsCount;
SET @log = 'Got stats for ' + CAST(@affectedRowsCount AS CHAR(10)) + ' archives';
RAISERROR(@log, 0, 0) WITH NOWAIT;
END
--drop temp tables
DROP TABLE ArchiveEmailIds
END TRY
BEGIN CATCH
DROP TABLE ArchiveEmailIds
DROP TABLE ExchangeArchiveStats
SELECT
@ErrorMessage=ERROR_MESSAGE();
RAISERROR('Error while getting exchange archives. Error: %s', 15, 1, @ErrorMessage)
END CATCH
SET @log = 'Finished getting stats for Exchange archives. Got total' + CAST(@total AS CHAR(10)) + ' archives';
RAISERROR(@log, 0, 0) WITH NOWAIT;
SET @log = 'Started getting overall stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;
-- get pst and journal stats
-- show all the stats results
SELECT ea.EmailAddress, ea.EmailId, stats.Count, stats.[Total Size MB] FROM ExchangeArchiveStats stats
INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId
DROP TABLE ExchangeArchiveStats
SET @log = 'Finished getting overal stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;
Enterprise Vault Discovery Accelerator
The following queries can be used for Enterprise Vault Discovery Accelerator. All query output units are in MB.
|
|
NOTE: Query outputs utilize compressed values to determine licensing. |
