Forum Discussion

RichardF14's avatar
RichardF14
Level 3
14 years ago

Storage Expiry Report

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.

 

  • 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)

  • 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

  • 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

  • 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)

  • 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.