Forum Discussion

gzsxqz's avatar
gzsxqz
Level 3
10 years ago

Query on what items were archived on a particular date

Hi, 

Can someone please write a query where I can get information on what items were archived on a certain date for a certain ArchiveID?

thx,

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

    Have you checked the Exchange Dumpster to see if the items exist there? could they have been moved and/or deleted?

    For instance i know some people run Managed Folders that may delete the items, or move the items that are a certain age to a new location.

    But if an item has been archived by EV, it should be indexed already and searchable.
    So if i were you i would go to http://yourEVServer/EnterpriseVault/Search.asp?advanced

    There are sort options where you can show by newest archived items first

    A sql query you could use i suppose is the following, just change EVVSMyVaultStore_1 to the Database name to your Vault Store database the user belongs to.
     

    SELECT A.ArchiveName "Archive Name", 
       REPLACE(CAST(AF.FolderPath AS varchar(max)), '?', '\') "Folder Path",
       S.IdDateTime "Sent/Received Date",
       S.ArchivedDate "Archived Date",
       S.ItemSize "Item Size"
  FROM EnterpriseVaultDirectory.dbo.Archive A, 
       EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
       EnterpriseVaultDirectory.dbo.Root R1,
       EnterpriseVaultDirectory.dbo.Root R2,
       EVVSMyVaultStore_1.dbo.ArchivePoint AP,
       EVVSMyVaultStore_1.dbo.Vault V,
       EVVSMyVaultStore_1.dbo.Saveset S
 WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
   AND S.VaultIdentity = V.VaultIdentity
   AND AP.ArchivePointID = R1.VaultEntryId
   AND V.VaultID = R2.VaultEntryId
   AND R1.RootIdentity = A.RootIdentity
   AND R2.RootIdentity = AF.RootIdentity
   AND A.ArchiveName = 'myUser'
   AND S.ArchivedDate > '2015-03-06 00:00:00.000'
ORDER BY "Archived Date"

     

5 Replies

Sort By
  • Rob_Wilcox1's avatar
    Rob_Wilcox1
    Level 6
    10 years ago

    You can use something like this or a variation of it:

     

    SELECT A.ArchiveName, count(S.SavesetIdentity) as 'Num Oversize'
    FROM
                EnterpriseVaultDirectory.dbo.Archive A,
                EnterpriseVaultDirectory.dbo.Root R,
                EVVSvs1_1.dbo.ArchivePoint AP,
                EVVSvs1_1.dbo.Saveset S
                
    WHERE
                S.ArchivePointIdentity = AP.ArchivePointIdentity AND
                AP.ArchivePointId = R.VaultEntryId AND
                R.RootIdentity = A.RootIdentity AND
                convert(char(10), S.ArchivedDate, 103) = '20/11/2014'
    GROUP BY
                A.ArchiveName
    ORDER BY 'Num Oversize' DESC

     

    You can adapt that to be just for one archive, and you'll need to put in the vault store database name inplace of EVVSvs1_1

  • JesusWept3's avatar
    JesusWept3
    Level 6
    10 years ago
    It really depends what infoyoure after, the database only holds things like mailbox name, folder path, sent/received date, item size etc But you won't be able to tell sender, recipients, attachment names, sizes, attachment type, message class of the email etc, all that's held in the database So if you just want a this is how many items were archived for User A, you can do that with tibs query But if you want a how many items were archived with a word document attached, for that you'll need to query the indexes
  • gzsxqz's avatar
    gzsxqz
    Level 3
    10 years ago

    Thanks for the replies.  Background - a user had a number of pending arhicve items that needed to be cleared.  I followed solution 2 as per this article - http://www.symantec.com/business/support/index?page=content&id=TECH35618 

    I expected the pending archive items and the items remain in the Exchange mailbox.  But for some strange reason, the items disappeared from the mailbox and I could not see them in the user's vault.  So I was looking for a query that could would check how many items where archived for the user on that day (Friday March 6) and what time they were archived (as I know what time of day I ran the process to clear pending archive).  That would tell me if the missing items were archived by EV.  I find it strange that they would disappear.  If EV did not archive them, then I will probably have to restore the mailbox.

    I know the user's ArchiveID, if you are able to modify the query above to tell me what time items were archived for this user on March 6, that would be really handy (sorry, I am not very good at SQL queries).  

    Also, if you can think of any plausiable reason why items would disappear from the mailbox when performing solution 2 as per http://www.symantec.com/business/support/index?page=content&id=TECH35618 I'd really appreciate it.  

     

  • JesusWept3's avatar
    JesusWept3
    Level 6
    10 years ago

    Have you checked the Exchange Dumpster to see if the items exist there? could they have been moved and/or deleted?

    For instance i know some people run Managed Folders that may delete the items, or move the items that are a certain age to a new location.

    But if an item has been archived by EV, it should be indexed already and searchable.
    So if i were you i would go to http://yourEVServer/EnterpriseVault/Search.asp?advanced

    There are sort options where you can show by newest archived items first

    A sql query you could use i suppose is the following, just change EVVSMyVaultStore_1 to the Database name to your Vault Store database the user belongs to.
     

    SELECT A.ArchiveName "Archive Name", 
       REPLACE(CAST(AF.FolderPath AS varchar(max)), '?', '\') "Folder Path",
       S.IdDateTime "Sent/Received Date",
       S.ArchivedDate "Archived Date",
       S.ItemSize "Item Size"
  FROM EnterpriseVaultDirectory.dbo.Archive A, 
       EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
       EnterpriseVaultDirectory.dbo.Root R1,
       EnterpriseVaultDirectory.dbo.Root R2,
       EVVSMyVaultStore_1.dbo.ArchivePoint AP,
       EVVSMyVaultStore_1.dbo.Vault V,
       EVVSMyVaultStore_1.dbo.Saveset S
 WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
   AND S.VaultIdentity = V.VaultIdentity
   AND AP.ArchivePointID = R1.VaultEntryId
   AND V.VaultID = R2.VaultEntryId
   AND R1.RootIdentity = A.RootIdentity
   AND R2.RootIdentity = AF.RootIdentity
   AND A.ArchiveName = 'myUser'
   AND S.ArchivedDate > '2015-03-06 00:00:00.000'
ORDER BY "Archived Date"

     

  • gzsxqz's avatar
    gzsxqz
    Level 3
    10 years ago

    Thanks very much for the SQL query, from that I was able to determine what items archived on March 6 and from what folder and I now know the emails did not archive.  I could not find them in the Exchange dumpster.  From here I will be able to restore mailbox from backup and compare and work it out.  At least I know EV now did not archive them.  Thanks for your help.