SQL query that reports on Storage Delete

[smlopes]

We don't keep server Application logs for long, so I need help writing a SQL query that reports the same type information for EventID 7085 (Total number of expired items deleted and how much in Mb) for all users since we deployed Evault. We are using version Evault 9.0.2 and SQL Server 2008 SP1 Cu 6. We do Storage Expiry based on Modified date and our Retention is 10 yrs. Sample log event below. I'm not a SQL admin, so any help is appreciated.

Log Name:      Symantec Enterprise Vault

Source:        Enterprise Vault

Event ID:      7085

Task Category: Storage Delete

Level:         Information

Description:Storage Expiry Report

Vault Store Name: Evault Mailbox Store1

Number of vaults processed: 3134

Number of vaults enabled for expiry: 3134

Total number of expired items deleted: 0

Total number of items that could not be deleted: 0

 Items marked with sent\received date based retention categories have been deleted:(none)

 Items marked with archived date based retention categories have been deleted:(none)

[JesusWept3]

once the datas gone, its gone, theres no way to report on it, because it doesn't exist in the database anymore unfortunately, i don't even think Auditing keeps a list of those things either

[Percy_Vere]

There is a storage expiry report generated when the task is run - look in the Program Files|\EV\Reports folder.

[smlopes]

There's nothing in that folder that is obvious. What does the filename begin with?

[Irtehawesome]

Storage expiry does not have a report, at least not in the sense of a log file. There is only an event ID that you posted already. 

Next time you can run this SQL query to see how many items will be expired and how much disk space you will save.

USE EnterpriseVaultDirectory 
USE EVVAULTSTORE [change to your vault store]
SELECT ArchiveName, ArchivePointID, sum(ItemSize) as TotalBytes, count (*) as 'Num Items Eligible' FROM Saveset 
INNER JOIN ArchivePoint 
On saveset.ArchivePointIdentity = ArchivePoint.ArchivePointIdentity
INNER JOIN EnterpriseVaultDirectory.dbo.Root r on r.VaultEntryID = ArchivePointID
INNER JOIN EnterpriseVaultDirectory.dbo.Archive a on a.RootIdentity = r.RootIdentity 
WHERE ArchivedDate <= (getdate() - 3) [change the 3 to whatever your retention cat. is, in days]
GROUP BY ArchiveName, ArchivePointID

[JesusWept3 · Accepted solution]

The query will give you a number but won't be accurate at all since you can expire by modified or sent received date, each of which can depend on the site setting and what the retention category is set to, also some items maybe on legal hold and some may be in an archive that's set to ignore expiry Also if you run that query an hour or so before you do your actual expiry, the numbers expired maybe larger due to the fact that expiry does it to the exact second, so what might not be available to expire now maybe available to expire in an hour As for size, you will never get an accurate count of how much space you will reclaim, especially if you use collections and osis