06-18-2019 11:41 PM
Hello all,
I have an environment which is old (11.0.1CHF5) and static. Most configuration information is removed from EV. The databases are available. Services are running, sometime a DA search is done.
I need to find out which archive has which retention assigned. How can I derive this from SQL?
Does anyone know?
Solved! Go to Solution.
06-19-2019 09:09 AM
This might help you:
--This gives the archive name and count of items by retention cat.
--Runs against the VaultStore Database
SELECT a.ArchivePointID, ex.MbxDisplayName, s.ArchivePointIdentity, count(s.) as 'Number of Items', sum (s.itemsize/1024/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 ArchivePointID,s.ArchivePointIdentity, rc.RetentionCategoryName, ex.MbxDisplayName
06-19-2019 09:09 AM
This might help you:
--This gives the archive name and count of items by retention cat.
--Runs against the VaultStore Database
SELECT a.ArchivePointID, ex.MbxDisplayName, s.ArchivePointIdentity, count(s.) as 'Number of Items', sum (s.itemsize/1024/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 ArchivePointID,s.ArchivePointIdentity, rc.RetentionCategoryName, ex.MbxDisplayName
06-19-2019 10:35 PM
Thanks Tony!
What should be : SELECT a.ArchivePointID, ex.MbxDisplayName, s.ArchivePointIdentity, count(s.???) as 'Number of Items', sum (s.itemsize/1024/1024) as 'Size'
I used SaveSetIdentity
The query is close, but not completely. I want to know which retention category the archives have, not how much items I have. I added rc.retentioncategoryname to the select, now shows retention cat.
SELECT a.ArchivePointID, ex.MbxDisplayName, s.ArchivePointIdentity, count(s.savesetidentity) as 'Number of Items', sum (s.itemsize/1024/1024) as 'Size', rc.retentioncategoryname
06-20-2019 05:50 AM
Sorry about the incompleteness, looks like my saved query got updated with one I was customizing.
07-10-2019 03:16 AM
Hi Gertjan,
thi sshould fix it. Where XXXXX is the name of your vault store DB. The "count" attribute should not matter as long as it is an identifier.
Let me know if you are missing something.
--This gives the archive name and count of items by retention cat.
--Runs against the VaultStore Database
SELECT a.ArchivePointID, ex.MbxDisplayName,rc.RetentionCategoryName,s.ArchivePointIdentity, count(s.Savesetidentity) as 'Number of Items', sum(s.itemsize/1024/1024) as 'Size'
FROM XXXXXXX.dbo.Saveset s
Join XXXXXXX.dbo.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
ORDER BY ex.MbxDisplayName