cancel
Showing results for 
Search instead for 
Did you mean: 

Total Items With Permanent Retention Category

DJSAJJAD
Level 3

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.

1 ACCEPTED SOLUTION

Accepted Solutions

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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

View solution in original post

2 REPLIES 2

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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

DJSAJJAD
Level 3

Thank you, exactly what i was looking for :)