01-05-2011 03:20 AM
Hello,
Does someone know how to generate a report that shows the items that will be removed from the Vault stores when the next storage expiry job runs.
(to generate a report 1 week before the expiry job runs)
Richard.
Solved! Go to Solution.
01-05-2011 05:00 AM
Take a look at the link that i attached and try that SQL Query, it will give you more accurate counts based on the fact that it looks as to whether users have expiry enabled for their archive and whether the item is on hold and such.
As for your query though, you really need to link ArchivePoint to the Root table as opposed to ExchangeMailboxEntry as you can have archives that don't exist in the EME table (maybe because they have been deleted etc)
01-05-2011 04:44 AM
if you run the expiry report 1 week before it is set to run you will get inaccurate results, for the fact that Storage expiry calculates to the very second
So for instance if you run based off of Archived Date and your retention is set to 1 year
Lets say you archived items have
1. 01/01/2010 23:59:20
2. 01/01/2010 23:59:40
3. 01/01/2010 23:59:50
4. 01/01/2010 23:59:59
5. 01/02/2010 00:00:05
6. 01/02/2010 00:00:10
If you were to run the expiry report dead on midnight of 01/02/2011 what would happen is it would tell you there were 4 items to expire, regardless of the fact that there are 2 other items right behind it.
If you run the report a week early, then you can expect there to be more items that would expire than what was reported.
Enterprise Vault actually has two ways to tell how much or what will be expired
From the Vault Admin Console you would be able to generate a report from a Run Now that would show each vault store and each retention category how many items would have been expired (the same events would generate after the fact on regular runs as well)
From the Enterprise Vault Outlook Add In you can also add a Show Expiry Report button that queries the index and will tell thatt individual user how many items and what items will be expired, however its just for that individual user
You can run SQL Queries also against each individual vault store, but there are some additional complexities, however SQL Queries will not tell you the full info on what will be expired (such as subject title, etc), the only way you could do that would be with the Index API and make some expensive queries to generate a report.
http://98.129.119.162/connect/forums/sql-query-search-expired-savesets
01-05-2011 04:53 AM
Richard
I'm working on a similar idea right now.
FYI, The Outlook Plug-in does support a report for items expiring in the next 60 days.
We have the Outlook plug-in installed however our Legal team wants a email message to appear in the inbox prompting the team to press on the report button to review items that are going to expire.
So I've been playing with SQL Queries and have this so far. This is a work in progress still looking for the just the right info to finalize it.
SELECT EME.mbxDisplayName, COUNT(S.IdTransaction) AS ItemsToExpire
FROM EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
EVVSeVaultStore1_1.dbo.Saveset S,
EVVSeVaultStore1_1.dbo.ArchivePoint AP
WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointId = EME.DefaultVaultId
AND S.IdDateTime < getDate() - 1
GROUP BY EME.MbxDisplayName
This appeared to work consistently in EV8 but now on EV9 I'm getting some oddities. Hope to have it finalized by the end of the week.
If anyone else thinks I'm barking up the wrong tree I'd love to hear other ideas too.
Tom
01-05-2011 05:00 AM
Take a look at the link that i attached and try that SQL Query, it will give you more accurate counts based on the fact that it looks as to whether users have expiry enabled for their archive and whether the item is on hold and such.
As for your query though, you really need to link ArchivePoint to the Root table as opposed to ExchangeMailboxEntry as you can have archives that don't exist in the EME table (maybe because they have been deleted etc)
01-06-2011 12:14 AM
Thanks for the information.
I was hoping that it was part of the ev reporting.
We have a customer that will have a report with all the items that will be deleted the next expiry run. So that they can recall items that may not be deleted.
Richard.
01-06-2011 05:01 AM
Thanks Jesus!
Much helps and explains some of the issues I was having.
Tom
07-18-2018 05:17 AM
Hey @JesusWept3 : what was the link, please?