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

[rajesh_velagapu]

Enterprise Vault version: EV

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

[rajesh_velagapu]

Enterprise Vault version: EV 10

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

[TonySterling · Accepted solution]

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

[rajesh_velagapu]

Thanks!