Forum Discussion

GTK's avatar
GTK
Level 6
10 years ago

vault store query

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

Enterprise Vault
Information Governance
Windows Server (2003-2008)
  • TonySterling's avatar
    TonySterling
    10 years ago

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



    SELECT ArchiveName, OldestItemDateUTC, YoungestItemDateUTC FROM IndexView

     

  • TonySterling's avatar
    TonySterling
    Moderator
    10 years ago

    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's avatar
    dcVAST
    Level 5
    10 years ago

    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's avatar
    GTK
    Level 6
    10 years ago

    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's avatar
    TonySterling
    Moderator
    10 years ago

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



    SELECT ArchiveName, OldestItemDateUTC, YoungestItemDateUTC FROM IndexView

     

  • dcVAST's avatar
    dcVAST
    Level 5
    10 years ago

    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