cancel
Showing results for 
Search instead for 
Did you mean: 

Report on how much EV will expire on a certain date?

Gogeta
Level 4

Hi,

We started using EV 7 in 2007 and upgraded to version 9 in 2012.  We set retention to 7 years.  So sometime this year it is going to delete some expired items.  My question is, is there a way to find out how much data will be expired or deleted on a given date.  Can we predict or report on how much will be expired in the year 2014?   Also, I read somewhere that after doing an upgrade, all historical trend is gone. Is it true?  When I ran an "Items Archival Rate" report for 2007, it doesn't look right at all.

Thanks.

Gogeta

1 ACCEPTED SOLUTION

Accepted Solutions

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

IdDateTime is the time of the emai, ArchivedDate would be the time the items was archived.

The getdate() gets todays date but the "YY", -7 tells it to look for items that are older than 7 years as of today.

So is your retention based on the archived date then?

View solution in original post

7 REPLIES 7

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

You could use something like this SQL query
 

--This gives number of items and average size for items older than a certain year
--Runs against the VaultStore Database
select count(*) as 'Number of Items over 7 Years', "Av Size" = sum (itemsize)/count (*)
from saveset
where IdDateTime < dateadd("YY", -7, getdate ())

GabeV
Level 6
Employee Accredited

Hi Gogeta,

What version of Enterprise Vault are you using? In the Enterprise Vault Site Settings > Storage Expiry tab, take a look and run the Storage Expiry report in Report Mode. It should create an event ID in the event viewer with more details about how many items would be expired based on the Vault Store and Retention Category.

I hope this helps.

Pradeep-Papnai
Level 6
Employee Accredited Certified

--set the number of days in @daystillexpiry parameter(like 1 month = 30 days) after expiry status, set to 0/1 (0=all archives, 1=only archives with “delete expired items” on) in @expiryarchivesonly parameter.  

And run below query against vault store database.

--------------------------


declare @daystillexpiry smallint
set @daystillexpiry = '600'
 
declare @expiryarchivesonly bit
set @expiryarchivesonly = '0'

SELECT a.ArchiveName, s.ArchivePointIdentity, RCE.RetentionCategoryName,sum(ItemSize)as TotalKB, count (*) as 'Num Items Eligible' FROM Saveset S
inner join ArchivePoint
On S.ArchivePointIdentity = ArchivePoint.ArchivePointIdentity
INNER JOIN EnterpriseVaultDirectory.dbo.Root r on r.VaultEntryID = ArchivePointID
INNER JOIN EnterpriseVaultDirectory.dbo.Archive a on a.RootIdentity = r.RootIdentity
join EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RCE on S.RetentionCategoryIdentity = RCE.RetentionCategoryIdentity
where RCE.retentionperiod < '99999' and datediff(dd,getdate(),(dbo.fnExpirationDate(-(rce.retentionperiod),(rce.retentionperiodunits),(s.iddatetime))))< @daystillexpiry and a.deleteexpireditems >= @expiryarchivesonly
GROUP BY ArchiveName, ArchivePointID, RCE.RetentionCategoryName, s.ArchivePointIdentity
Order by archivename Asc

Gogeta
Level 4

Thank you for the SQL query. 

If I understand the query correctly, the getdate() gives me the current date, so the query gives me how much will expire today?  I actually don't expect anything to expire until a few months from now.  However, the query returned a number when I ran it. 

So I am wondering what is the idDateTime?  Is it the date that the email was archived by Evault?  Or is it the original sent date of the email?  It would make sense if it is comparing the original sent date with the current date because some sent dates are much older than 7 years, otherwise I should get zero when I run it today because nothing has been in Evault for more than 7 years until a few months later.

Any advice will be appreciated.

Thank you.

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

IdDateTime is the time of the emai, ArchivedDate would be the time the items was archived.

The getdate() gets todays date but the "YY", -7 tells it to look for items that are older than 7 years as of today.

So is your retention based on the archived date then?

Gogeta
Level 4

Yes, our retention is based on the archived date.   It is reporting what we expect now. 

Thanks for your help.

 

This is a great query. But how to add in the report 'Num Items on Hold' so that this factors in items on legal hold in a DA case?