Video Screencast Help

SQL query that will list all the archives with "the oldest item by sent/received date" that is based on the creation date of the oldest e-mail in the archive

Created: 20 Aug 2013 • Updated: 25 Aug 2013 | 4 comments
This issue has been solved. See solution.

Hi guys,

I found a great SQL Query that will list all the archives, the oldest item by sent/received date and oldest item by archived date

It works fine but "the oldest item by sent/received date" is based in many cases on the"the creation date of the oldest attachment to an e-mail message in the archive"

as per:

Is there a SQL query that will  list all the archives with "the oldest item by sent/received date" that is based on the creation date of the oldest e-mail in the archive ?

Operating Systems:

Comments 4 CommentsJump to latest comment

Ben Watts's picture

Hi tnt,

To be honest I do not think it is possible to run a SQL query to pull back information around the sent or received dates due to that information being held in the items and the indexes but not the DBs.

I am sure we can put together a query that looks for the oldest ARCHIVED item, based on either the item itself or the attachment that was with the item.

The technote you have linked in relation to DA searches and the Oldest Archived Item field talks about just that when it reports that value to the VAC and the properties of the Archives.

It looks at the dates that those items were archived hence the 'creation date', as in the date the item was created/added in the archive.

I hope I am not barking up the wrong tree, if I am just correct me.

Actually just looked at your post more closely, you have mentioned the first query which does pull back info for items based on Sent/Received dates, will look at it more closely once I am back in the office tomorrow.

Pradeep_Papnai's picture

Your request cannot be done via SQL queries as we just keep archive date of item but NOT received / sent date of emails. The information will there with indexes. Now to achieve what you exactly want, I would follow the steps below.

Give permission to all required archive to single user (could be EV service a/c ) by using EVPM (REFER TN

Perform advanced search (http://evserver/enterprisevault/search.asp?advanced=3) with following creteria.

Vaults = <all vaults>
Other Result Attributes = mdat adat date
Sort Results By= Sent/Received Date, oldest First

Like the screenshot-1
Where mdate is Modified Date, adat is Archive Date and date is Sent date.

Result would be like the screenshot 2.

You can make your search more granular by specify other attributes as well, I hope this would help.

advance_search.jpg Result.jpg
JesusWept3's picture

Unless i'm completely mistaken, the IdDateTime represents the submit date, no? 
and if that isn't available then a range of others etc

So the query would be like

SELECT  A.ArchiveName "Archive Name",
        AP.CreatedDate "Archive Created",
        AP.ModifiedDate "Archived Last Modified",
        MIN(S.IdDateTime) "Oldest Sent/Received Item",
        MAX(S.IdDateTime) "Newest Sent/Received Item",
        MIN(S.ArchivedDate) "Oldest Archived Item",
        MAX(S.ArchivedDate) "Newest Archived Item"
FROM    EnterpriseVaultDirectory.dbo.Archive A,
        EnterpriseVaultDirectory.dbo.Root R,
        EVVSYourVaultStore_01.dbo.ArchivePoint AP,
        EVVSYourVaultStore_01.dbo.Saveset S
WHERE   A.RootIdentity = R.RootIdentity
  AND   R.VaultEntryId = AP.ArchivePointId
  AND   AP.ArchivePointIdentity = S.ArchivePointIdentity
  AND   A.ArchiveName = 'My User'
GROUP BY A.ArchiveName, AP.CreatedDate, AP.ModifiedDate
tnt015's picture

Thanks to everyone who responded to my post.


You supplied a SQL query that exactly meet my requirements.

Thank you very much for your help!!!