Video Screencast Help

SQL query to find out archived mail in each 1, 2, 5 year retention periods?

Created: 04 Dec 2013 • Updated: 04 Dec 2013 | 3 comments
This issue has been solved. See solution.

Enterprise Vault version: EV

SQL query to find out archived mail in each 1, 2, 5 year retention periods?

Operating Systems:

Comments 3 CommentsJump to latest comment

rajesh velagapudi's picture

Enterprise Vault version: EV 10

SQL query to find out archived mail in each 1, 2, 5 year retention periods?

TonySterling's picture

I think this should work:

--Runs against the VaultStore Database
SELECT rc.RetentionCategoryName, COUNT(s.RetentionCategoryIdentity) as Count
FROM Saveset s
Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
Join EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc on rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
GROUP BY  rc.RetentionCategoryName

and this one includes size

--This gives the count and size by RC.
--Runs against the VaultStore Database
SELECT rc.RetentionCategoryName, COUNT(s.RetentionCategoryIdentity) as Count, SUM (s.ItemSize)/1024 as 'Size'
FROM Saveset s
Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
Join EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc on rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
GROUP BY  rc.RetentionCategoryName

SOLUTION