01-08-2014 11:25 AM
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
Solved! Go to Solution.
01-13-2014 11:00 PM
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?
01-08-2014 11:34 AM
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 ())
01-08-2014 11:47 AM
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.
01-10-2014 01:04 AM
--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
01-13-2014 08:01 AM
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.
01-13-2014 11:00 PM
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?
01-16-2014 06:19 AM
Yes, our retention is based on the archived date. It is reporting what we expect now.
Thanks for your help.
09-01-2020 02:07 PM
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?