cancel
Showing results for 
Search instead for 
Did you mean: 

vault store query

GTK
Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

I found an easier way just using a view, this runs against the EV Directory db:



SELECT ArchiveName, OldestItemDateUTC, YoungestItemDateUTC FROM IndexView

 

View solution in original post

5 REPLIES 5

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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. 

dcVAST
Level 5
Partner Accredited

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

GTK
Level 6

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

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

I found an easier way just using a view, this runs against the EV Directory db:



SELECT ArchiveName, OldestItemDateUTC, YoungestItemDateUTC FROM IndexView

 

dcVAST
Level 5
Partner Accredited

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