cancel
Showing results for 
Search instead for 
Did you mean: 

Find - Which retention does an archive has?

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

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 ACCEPTED SOLUTION

Accepted Solutions

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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

View solution in original post

4 REPLIES 4

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

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

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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

CConsult
Moderator
Moderator
Partner    VIP   

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