cancel
Showing results for 
Search instead for 
Did you mean: 

Query on what items were archived on a particular date

gzsxqz
Level 3
Partner

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,

1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified

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"

 

https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

5 REPLIES 5

Rob_Wilcox1
Level 6
Partner

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

Working for cloudficient.com

JesusWept3
Level 6
Partner Accredited Certified
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
https://www.linkedin.com/in/alex-allen-turl-07370146

gzsxqz
Level 3
Partner

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
Level 6
Partner Accredited Certified

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"

 

https://www.linkedin.com/in/alex-allen-turl-07370146

gzsxqz
Level 3
Partner

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.