11-11-2014 06:30 AM
I have a requirement from management of my company to find number of total archived items which has permanent retention category assigned. Is there a way to do that inside Enterprise Vault? If not, is there a SQL query which i can run to get this data?
We are running Enterprise Vault 10.0.0 and have Centera and Discovery Accelerator deployed as well.
Solved! Go to Solution.
11-11-2014 06:40 AM
Do you want to know at the mailbox level or just overall?
--This gives the archive name and count by RC.
--Runs against the VaultStore Database
SELECT a.ArchivePointID, ex.MbxDisplayName, s.ArchivePointIdentity, 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 ArchivePointID,s.ArchivePointIdentity, rc.RetentionCategoryName, ex.MbxDisplayName
If you just want an overall count you can run something like this:
--This gives the count by RC.
--Runs against the VaultStore Database
SELECT rc.RetentionCategoryName, COUNT(s.RetentionCategoryIdentity) as Count
FROM Saveset s
Join EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc on rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
GROUP BY rc.RetentionCategoryName
11-11-2014 06:40 AM
Do you want to know at the mailbox level or just overall?
--This gives the archive name and count by RC.
--Runs against the VaultStore Database
SELECT a.ArchivePointID, ex.MbxDisplayName, s.ArchivePointIdentity, 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 ArchivePointID,s.ArchivePointIdentity, rc.RetentionCategoryName, ex.MbxDisplayName
If you just want an overall count you can run something like this:
--This gives the count by RC.
--Runs against the VaultStore Database
SELECT rc.RetentionCategoryName, COUNT(s.RetentionCategoryIdentity) as Count
FROM Saveset s
Join EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc on rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
GROUP BY rc.RetentionCategoryName
11-11-2014 09:07 AM
Thank you, exactly what i was looking for :)