Splitting Enterprise Vault data based on date (4274531)

Issue
Sometimes you may want to review large Enterprise Vault archives based on date. This article gives some examples of how to query Enterprise Vault databases to review migrations in this way.
Solution
The query below shows a journal vault store, containing two journal archives, splitting the data by month:

SELECT
left(convert (varchar, S.archiveddate,20),7) "Archived Date",
EME.MbxDisplayName "Mailbox Name",
COUNT(S.ItemSize) "No. Items Archived",
SUM(S.ItemSize)/1024 "Archived Item Size (MB)",
SUM(SP.OriginalSize)/1024/1024 "Original Item Size (MB)"
FROM
dbo.Saveset S,
dbo.SavesetProperty SP,
dbo.ArchivePoint AP,
EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME
WHERE
S.SavesetIdentity = SP.SavesetIdentity
AND S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointID = EME.DefaultVaultId
GROUP BY left(convert (varchar, archiveddate,20),7), EME.MbxDisplayName
ORDER BY EME.MbxDisplayName

The output of that will be similar to this:

This query will output archives that have data in them that is more recent than a particular date, grouped monthly:

SELECT
EME.MbxDisplayName "Mailbox Name",
left(convert (varchar, S.archiveddate,20),10) "Archived Date",
COUNT(S.ItemSize) "No. Items Archived",
SUM(S.ItemSize)/1024 "Archived Item Size (MB)",
SUM(SP.OriginalSize)/1024/1024 "Original Item Size (MB)"
FROM
dbo.Saveset S,
dbo.SavesetProperty SP,
dbo.ArchivePoint AP,
EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME
WHERE
S.SavesetIdentity = SP.SavesetIdentity
AND S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointID = EME.DefaultVaultId
AND S.ArchivedDate >= '2014-01-10'
GROUP BY left(convert (varchar, archiveddate,20),10), EME.MbxDisplayName
ORDER BY EME.MbxDisplayName

Here’s an example of the output: