Highlighted

Find - Which retention does an archive has?

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?

Regards. Gertjan
1 Solution

Accepted Solutions
Accepted Solution!

Re: Find - Which retention does an archive has?

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

For the best O365 Reporting Solution click here Radar-Reporting

View solution in original post

4 Replies
Accepted Solution!

Re: Find - Which retention does an archive has?

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

For the best O365 Reporting Solution click here Radar-Reporting

View solution in original post

Re: Find - Which retention does an archive has?

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

 

 

Regards. Gertjan

Re: Find - Which retention does an archive has?

Sorry about the incompleteness, looks like my saved query got updated with one I was customizing. 

For the best O365 Reporting Solution click here Radar-Reporting

Re: Find - Which retention does an archive has?

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