Overall statistics per achive type
-- 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
-- Get all Exchange archive stats in batches
-- @count variable defines number of archive for which we get stats in one batch.
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @log AS NVARCHAR(4000);
DECLARE @journal BIGINT = -3515994502517958093;
DECLARE @pstFileType INT = 4;
DECLARE @smtpFileType INT = 3;
DECLARE @exchangeArchiveType INT = 1;
DECLARE @count INT = 10000;
DECLARE @start INT = 0;
DECLARE @total INT = 0;
DECLARE @affectedRowsCount INT = 1;
-- 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 stats for Exchange archives.';
RAISERROR(@log, 0, 0) WITH NOWAIT;
BEGIN TRY
WHILE (@affectedRowsCount > 0)
BEGIN
TRUNCATE TABLE ArchiveEmailIds
;WITH CTE_RouteMasks AS
(
SELECT r.EmailId, COUNT(1) AS RouteCount FROM dbo.Route r
WHERE ([r].[RouteMask] & 4 = 4)
GROUP BY r.EmailId
)
INSERT INTO ArchiveEmailIds
SELECT TOP(@count) [x].[EmailId]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [ea].[EmailId]) AS [ROW_NUMBER], [ea].[EmailId], [ea].[EmailAddress], [ea].[Type]
FROM [EmailAddress] AS [ea]
WHERE (ea.Type = @exchangeArchiveType OR ea.Type IS NULL) AND EXISTS(
SELECT NULL AS [EMPTY]
FROM CTE_RouteMasks AS [r]
WHERE [r].[EmailId] = [ea].[EmailId]
)
) AS [x]
WHERE [x].[ROW_NUMBER] > @start
ORDER BY [x].[ROW_NUMBER]
INSERT INTO ExchangeArchiveStats
SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]
FROM Route r
INNER JOIN ArchiveEmailIds tmp ON tmp.EmailId = r.EmailId
INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId
INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId
INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId
WHERE r.RouteMask & 4 = 4
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
;WITH journalAndPstStats_CTE AS (
SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]
FROM dbo.EmailAddress ea
INNER JOIN dbo.Route r ON r.EmailId = ea.EmailId AND (ea.EmailId = @journal OR ea.Type = @pstFileType)
INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId
INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId
INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId
group by r.[EmailId]
)
-- !!! Enable this query only in case SMTP stats should be shown !!!
-- , statsSmtpArchive_CTE AS (
--
-- SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]
--
-- FROM dbo.EmailAddress ea
--
-- INNER JOIN dbo.Route r ON r.EmailId = ea.EmailId AND ea.Type = @smtpFileType
--
-- INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId
--
-- INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId
--
-- INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId
--
-- group by r.[EmailId]
--
-- )
, statsPerArchive_CTE AS (
SELECT ea.EmailAddress, ea.EmailId, ea.Type, stats.Count, stats.[Total Size MB] FROM ExchangeArchiveStats stats
INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId
UNION
SELECT ea.EmailAddress, ea.EmailId, ea.Type, stats.Count, stats.[Total Size MB] FROM journalAndPstStats_CTE stats
INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId
-- !!! Enable this UNION only in case SMTP stats should be shown !!!
--UNION
--SELECT ea.EmailAddress, ea.EmailId, ea.Type, stats.Count, stats.[Total Size MB]
--FROM statsSmtpArchive_CTE stats
-- INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId
---- !!! use statement to define domain list you are interested in !!!
----WHERE ea.EmailAddress LIKE '%domain1.com%'
---- OR ea.EmailAddress LIKE '%domain2.com%'
)
SELECT 'ExchangeAndPstStats' AS statsDescription, COUNT(1) AS archivesCount, SUM(statsPerArchive_CTE.Count) AS TotalCount, SUM(statsPerArchive_CTE.[Total Size MB]) AS TotalSizeMb
FROM statsPerArchive_CTE
WHERE statsPerArchive_CTE.Type IN (1,4)
UNION
SELECT 'JournalStats' AS statsDescription, COUNT(1) AS archivesCount, SUM(statsPerArchive_CTE.Count) AS TotalCount, SUM(statsPerArchive_CTE.[Total Size MB]) AS TotalSizeMb
FROM statsPerArchive_CTE
WHERE Type = 7
-- !!! Enable this UNION only in case SMTP stats should be shown !!!
--UNION
--SELECT 'SmtpStats' AS statsDescription, COUNT(1) AS archivesCount, SUM(statsPerArchive_CTE.Count) AS TotalCount, SUM(statsPerArchive_CTE.[Total Size MB]) AS TotalSizeMb
--FROM statsPerArchive_CTE
--WHERE Type = 3
DROP TABLE ExchangeArchiveStats
SET @log = 'Finished getting overall stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;
Overall statistics per archive type 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
-- Get all Exchange archive stats in batches
-- @count variable defines number of archive for which we get stats in one batch.
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @log AS NVARCHAR(4000);
DECLARE @journal BIGINT = -3515994502517958093;
DECLARE @pstFileType INT = 4;
DECLARE @smtpFileType INT = 3;
DECLARE @exchangeArchiveType INT = 1;
DECLARE @count INT = 10000;
DECLARE @start INT = 0;
DECLARE @total INT = 0;
DECLARE @affectedRowsCount INT = 1;
DECLARE @msgDateFrom DATETIME = '01.01.2010';
DECLARE @msgDateTo DATETIME = '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 stats for Exchange archives.';
RAISERROR(@log, 0, 0) WITH NOWAIT;
BEGIN TRY
WHILE (@affectedRowsCount > 0)
BEGIN
TRUNCATE TABLE ArchiveEmailIds
;WITH CTE_RouteMasks AS
(
SELECT r.EmailId, COUNT(1) AS RouteCount FROM dbo.Route r
WHERE ([r].[RouteMask] & 4 = 4)
GROUP BY r.EmailId
)
INSERT INTO ArchiveEmailIds
SELECT TOP(@count) [x].[EmailId]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [ea].[EmailId]) AS [ROW_NUMBER], [ea].[EmailId], [ea].[EmailAddress], [ea].[Type]
FROM [EmailAddress] AS [ea]
WHERE (ea.Type = @exchangeArchiveType OR ea.Type IS NULL) AND EXISTS(
SELECT NULL AS [EMPTY]
FROM CTE_RouteMasks AS [r]
WHERE [r].[EmailId] = [ea].[EmailId]
)
) AS [x]
WHERE [x].[ROW_NUMBER] > @start
ORDER BY [x].[ROW_NUMBER]
INSERT INTO ExchangeArchiveStats
SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]
FROM Route r
INNER JOIN ArchiveEmailIds tmp ON tmp.EmailId = r.EmailId
INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId
INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId
INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId
WHERE r.RouteMask & 4 = 4
-- !!! if you don't want to use date filter remove condition below !!!
AND 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 overal stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;
-- get pst and journal stats
-- show all the stats results
;WITH journalAndPstStats_CTE AS (
SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]
FROM dbo.EmailAddress ea
INNER JOIN dbo.Route r ON r.EmailId = ea.EmailId AND (ea.EmailId = @journal OR ea.Type = @pstFileType)
INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId
INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId
INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId
-- !!! if you don't want to use dat filter remove condition below !!!
WHERE m.MsgDate >= @msgDateFrom AND m.MsgDate <= @msgDateTo
group by r.[EmailId]
)
-- !!! Enable this query only in case SMTP stats should be shown !!!
-- , statsSmtpArchive_CTE AS (
--
-- SELECT r.EmailId, COUNT_BIG(DISTINCT(r.[MessageID])) as [Count], ROUND(SUM(CAST(m.MsgSize AS REAL)) / 1024 / 1024, 2) AS [Total Size MB]
--
-- FROM dbo.EmailAddress ea
--
-- INNER JOIN dbo.Route r ON r.EmailId = ea.EmailId AND ea.Type = @smtpFileType
--
-- INNER JOIN dbo.FolderMessage fm ON r.MessageID = fm.MessageID AND r.FolderId = fm.FolderId and r.PartitionId = fm.PartitionId
--
-- INNER JOIN dbo.Message m ON m.MessageID = r.MessageID AND m.PartitionId = r.PartitionId
--
-- INNER JOIN dbo.Volume v ON v.VolumeId = fm.VolumeId
--
-- -- !!! if you don't want to use dat filter remove condition below !!!
--
-- WHERE m.MsgDate >= @msgDateFrom AND m.MsgDate <= @msgDateTo
--
-- group by r.[EmailId]
--
-- )
, statsPerArchive_CTE AS (
SELECT ea.EmailAddress, ea.EmailId, ea.Type, stats.Count, stats.[Total Size MB] FROM ExchangeArchiveStats stats
INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId
UNION
SELECT ea.EmailAddress, ea.EmailId, ea.Type, stats.Count, stats.[Total Size MB] FROM journalAndPstStats_CTE stats
INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId
-- !!! Enable this UNION only in case SMTP stats should be shown !!!
--UNION
--SELECT ea.EmailAddress, ea.EmailId, ea.Type, stats.Count, stats.[Total Size MB]
--FROM statsSmtpArchive_CTE stats
-- INNER JOIN dbo.EmailAddress ea ON ea.EmailId = stats.EmailId
---- !!! use statement to define domain list you are interested in !!!
----WHERE ea.EmailAddress LIKE '%domain1.com%'
---- OR ea.EmailAddress LIKE '%domain2.com%'
)
SELECT 'ExchangeAndPstStats' AS statsDescription, COUNT(1) AS archivesCount, SUM(statsPerArchive_CTE.Count) AS TotalCount, SUM(statsPerArchive_CTE.[Total Size MB]) AS TotalSizeMb
FROM statsPerArchive_CTE
WHERE statsPerArchive_CTE.Type IN (1,4)
UNION
SELECT 'JournalStats' AS statsDescription, COUNT(1) AS archivesCount, SUM(statsPerArchive_CTE.Count) AS TotalCount, SUM(statsPerArchive_CTE.[Total Size MB]) AS TotalSizeMb
FROM statsPerArchive_CTE
WHERE Type = 7
-- !!! Enable this UNION only in case SMTP stats should be shown !!!
--UNION
--SELECT 'SmtpStats' AS statsDescription, COUNT(1) AS archivesCount, SUM(statsPerArchive_CTE.Count) AS TotalCount, SUM(statsPerArchive_CTE.[Total Size MB]) AS TotalSizeMb
--FROM statsPerArchive_CTE
--WHERE Type = 3
DROP TABLE ExchangeArchiveStats
SET @log = 'Finished getting overal stats';
RAISERROR(@log, 0, 0) WITH NOWAIT;
EMX
The following queries can be used for EMX. All query output units are in MB.
|
|
NOTE: Query outputs utilize compressed values to determine licensing. |
Show overall stats
-- 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 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]
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;