07-29-2015 08:04 AM
hi
we are using EV 8sp5
is it possible to get the the date of the oldest and youngest email in a vault store ?
EG - if an email was ingested on 5th Jan 2015 but the actual creation date of that email is 25th Oct 2011, is it possible to get the original mail creation date from EV of the oldest and youngest mails?
we want to be able to say to person X that we have emails from "this date" to "that date"
thanks
Solved! Go to Solution.
07-30-2015 05:21 AM
I found an easier way just using a view, this runs against the EV Directory db:
SELECT ArchiveName, OldestItemDateUTC, YoungestItemDateUTC FROM IndexView
07-29-2015 08:28 AM
I haven't tested but I think this would work.
SELECT ex.MbxDisplayName,
a.archiveditems as Count,
(a.archiveditemsSize/1024) as 'Size',
min(s.IDDateTime) as 'Date From',
MAX(s.IDDateTime) AS 'Date to'
FROM Saveset s
Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
GROUP BY ex.MbxDisplayName, a.archiveditems, a.archiveditemssize
You will want to run against the Vault Store databases.
07-29-2015 02:34 PM
Just to follow-up, you may want to look at: How the DATE attribute is created for Exchange items archived with Enterprise Vault http://www.symantec.com/docs/TECH198491
If you had some application that modified items after they were sent then EV will take into account those dates in the IdDateTime column in the Saveset table. Not sure how specific you want to be on the item date vs the archived date.
You can even look at the items in the Saveset table, and break it down per user like:
Use YourVaultStoreDB
Select IdTransaction AS 'ID'
,IdDateTime AS 'Message Date'
,ArchivedDate AS 'Date Archived'
,A.ArchiveName AS 'Archive Name'
FROM Saveset
Join Vault on Saveset.VaultIdentity = Vault.VaultIdentity
Join EnterpriseVaultDirectory.dbo.Root R on Vault.VaultID = R.VaultEntryId
Join EnterpriseVaultDirectory.dbo.Archive A on R.ArchiveFolderIdentity = A.RootIdentity
--Where ArchiveName = 'User 3'
ORDER BY
IdDateTime --Or order by ArchiveName
ASC
07-30-2015 01:41 AM
thanks gents ... i forgot to add
the vault store DBs are on a seperate SQL instance to the Directory DB. Due to company policy we are not able to link DBs.
Is my request still possible ?
thanks
07-30-2015 05:21 AM
I found an easier way just using a view, this runs against the EV Directory db:
SELECT ArchiveName, OldestItemDateUTC, YoungestItemDateUTC FROM IndexView
07-30-2015 08:09 AM
Yes, you will have to add the SQL server name to sys.servers and then add the SQL server name to the query.
To get the current name of the SQL Server:
EXEC sp_helpserver
To add the server:
EXEC sp_addlinkedserver @server='SQLServerName'
Example:
Use YourVaultStoreDB
Select IdTransaction AS 'ID'
,IdDateTime AS 'Message Date'
,ArchivedDate AS 'Date Archived'
,A.ArchiveName AS 'Archive Name'
FROM Saveset
Join Vault on Saveset.VaultIdentity = Vault.VaultIdentity
Join [SQLSERVERNAME].EnterpriseVaultDirectory.dbo.Root R on Vault.VaultID = R.VaultEntryId
Join [SQLSERVERNAME].EnterpriseVaultDirectory.dbo.Archive A on R.ArchiveFolderIdentity = A.RootIdentity
--Where ArchiveName = 'User 3'
ORDER BY
IdDateTime --Or order by ArchiveName
ASC