Video Screencast Help

Useful SQL Query (Archive Details)

Created: 21 Feb 2013 • Updated: 08 Oct 2013 | 10 comments
Chris Warren's picture
+5 5 Votes
Login to vote

So, I've been cobbling together a few SQL queries lately that help get large amounts of information from EV.  I figured I'd provide one of these to everyone.  As I post these queries I will also provide the information it gathers below so people know what they are looking at.  I hope these queries are as helpful to others as they have been for me.

(Note: As we cannot take into consideration every environment, please run these queries with caution as they MAY cause performance issues while being run)

-- Start Query
-- Prerequisite : Must be run on SQL instance with EnterpriseVaultDirectory DB

USE EnterpriseVaultDirectory 

SELECT Distinct
     CASE WHEN AV.archivename IS NULL THEN '--Folder' ELSE AV.archivename END AS 'ArchiveName/Folder?'
     ,CASE AV.Type
      WHEN 5 THEN 'Shared'
      WHEN 9 THEN 'Mailbox'
      WHEN 17 THEN 'Journal'
      WHEN 33 THEN 'Public Folder'
      WHEN 65 THEN 'SharePointServer' 
      WHEN 129 THEN 'FSA' 
      WHEN 257 THEN 'Sharepoint' 
      WHEN 513 THEN 'LotusJournal' 
      WHEN 1025 THEN 'LotusMbx' 
      ELSE 'Undefined' END AS ArchiveType
     ,CASE WHEN VEMS.MbxAlias IS NULL THEN 'Not Associated' ELSE VEMS.MbxAlias END AS MbxAlias
     ,CASE WHEN AFV.FolderName IS NULL THEN 'N/A' ELSE AFV.FolderName END AS 'FolderName if applicable'
     ,r1.VaultEntryId as 'ArchiveID - FolderID if ArchiveName = Folder'
     ,CASE WHEN VEMS.ExchangeComputer IS NULL THEN 'Not Associated' ELSE VEMS.ExchangeComputer END AS ExchangeServer
     ,ce.ComputerName as StorageServiceComputer
     ,CASE WHEN ce2.ComputerName IS NULL THEN 'Not Associated' ELSE ce2.ComputerName END AS EVTaskServer
     ,CASE WHEN AV.ArchiveStatus = '1' THEN 'Archive Enabled' WHEN AV.ArchiveStatus = '3' THEN 'Archive Disabled/Closed' WHEN AV.ArchiveStatus = '4' THEN 'Marked for Deletion' ELSE 'Other' END AS ArchiveStatus
     ,CASE WHEN AV.HasHistory = '1' THEN 'Moved' WHEN AV.HasHistory = '0' THEN 'Not Moved' END AS 'Moved with MA?'
     ,CASE WHEN PTG.DisplayName IS NULL THEN 'Archive Not Provisioned' ELSE PTG.DisplayName END AS ProvisioningGroup   
     ,CASE WHEN EPV.poName IS NULL THEN 'No MBX Policy' ELSE EPV.poName END AS "Exchange MBX Policy Name"
     ,CASE WHEN PE.poName IS NULL THEN 'No Desktop Policy' ELSE PE.poName END AS "Desktop Policy Name"
     ,CASE WHEN RCE.RetentionCategoryName IS NULL THEN 'Not Assigned' ELSE RCE.RetentionCategoryName END AS "Assigned Default Retention"
     ,CASE WHEN IVV.IndexedItems IS NOT NULL THEN CONVERT(VARCHAR(24), IVV.IndexedItems) ELSE 'N/A' END AS "IndexedItems"
     ,CASE WHEN Archive.Structured = '1' THEN 'Structured' WHEN Archive.Structured = '0' THEN 'Unstructured' ELSE 'Unknown' END AS "Structured?"
     ,CASE WHEN VI.VaultEntryId IS NULL THEN 'Archive Not Of Interest' ELSE 'Archive Of Interest' END AS "OfInterest_CADA"
     EnterpriseVaultDirectory.dbo.Root r1
     left JOIN  EnterpriseVaultDirectory.dbo.Root r2 on r1.rootidentity = r2.containerrootidentity
     inner JOIN EnterpriseVaultDirectory.dbo.Archive Archive on r1.rootidentity = Archive.rootidentity
     inner JOIN EnterpriseVaultDirectory.dbo.VaultStoreEntry VSE on Archive.VaultStoreEntryId = VSE.VaultStoreEntryId
     inner JOIN EnterpriseVaultDirectory.dbo.ArchiveView AV on Archive.ArchiveName = AV.ArchiveName
     inner JOIN EnterpriseVaultDirectory.dbo.view_ExchangeMailbox_By_Server VEMS on AV.VaultEntryId = VEMS.DefaultVaultID
     left JOIN EnterpriseVaultDirectory.dbo.ArchiveFolderView AFV on r1.VaultEntryID = AFV.VaultEntryID
     inner JOIN EnterpriseVaultDirectory.dbo.PolicyTargetGroup PTG on VEMS.PolicyTargetGroupEntryID = PTG.PolicyTargetGroupEntryID
     inner JOIN EnterpriseVaultDirectory.dbo.ExchangePolicyView EPV on VEMS.PolicyEntryID = EPV.poPolicyEntryID
     inner JOIN EnterpriseVaultDirectory.dbo.PolicyEntry PE on VEMS.DesktopPolicyEntryID = PE.poPolicyEntryID
     left JOIN EnterpriseVaultDirectory.dbo.VaultInterest VI on VI.VaultEntryId = r1.VaultEntryID
     inner JOIN EnterpriseVaultDirectory.dbo.StorageServiceEntry sse on vse.StorageServiceEntryId = sse.ServiceEntryId
     inner JOIN EnterpriseVaultDirectory.dbo.ComputerEntry ce on sse.ComputerEntryId = ce.ComputerEntryId
     inner JOIN EnterpriseVaultDirectory.dbo.ExchangeServerEntry ese on VEMS.ExchangeServerIdentity = ese.ExchangeServerIdentity
     inner JOIN EnterpriseVaultDirectory.dbo.ArchivingRetrievalTaskView artv on artv.ExchangeComputer = ese.ExchangeComputer
     inner JOIN EnterpriseVaultDirectory.dbo.ComputerEntry ce2 on ce2.ComputerEntryId = artv.ComputerEntryId
     inner JOIN EnterpriseVaultDirectory.dbo.IndexVolumeView IVV on IVV.VaultEntryID = AV.VaultEntryID
     inner JOIN EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RCE on RCE.RetentionCategoryId = PTG.RetentionCategoryId
--AV.archivename IS NOT NULL AND
AV.archivename LIKE '%John%'

-- End Query

Information gathered:
a. If the entry refers to a folder within the archive or the archive itself.
b. What type of Archive is it (Ex. Mailbox, Journal, FSA, Shared, etc)
c. If the Archive is currently associated with a Mailbox.
- Ex. If there are 2 archives for 1 user, only one archive can be currently associated with the mailbox.  This is the active archive.
d. If the entry associated with the archive is a folder, what is the foldername.
e. 'ArchiveID - FolderID if ArchiveName = Folder' : Archive ID if entry is an archive, FolderID if a folder in the archive.
f. SQLServer : SQL Server Instance
g. VaultStoreName
h. DatabaseName = VaultStoreDBName

i. StorageServiceComputer : What EV Server has the Storage Service associated with the Archive.*
j. EVTaskServer : What EV Server has the Archiving Task associated with the Mailbox.*
- For values i and j, when an archive is created, it is associated with a specific Storage Service. It is possible to move a mailbox to another server.  When these values do not match, this can cause performance issues as the items archived must be moved between EV servers to be processed by a different Storage Services from where the Task is.
k. ArchiveStatus : Active, Closed, Marked for Deletion...
l. Moved with MA? : Identifies if the Archive was previously moved with Move Archive.
m. ProvisioningGroup : Provisioning Group Name associated with Archive.  Not Associated if archive is not associated with a mailbox.
n. Exchange MBX Policy Name : Exchange Mailbox Policy name associated with Archive. Not Associated if archive is not associated with a mailbox.
o. Desktop Policy Name : Desktop Policy Name associated with Archive.  Not Associated if archive is not associated with a mailbox.
p. RetentionCategoryName : Default Retention Category associated with Archive.
q. IndexedItems : How many indexed items are associated with Archive.
r. OldestArchivedDateUTC
s. YoungestArchivedDateUTC
t. Structured? : If the archive is structured or not.
- By default, mailbox archives are Structured. Journal and Shared Mailbox archives are Not Structured.
u. OfInterest_DA : Archives that are related to Discovery Accellerator cases will be 'Of Interest'.
- Archives of interest cannot be deleted due to being locked by Discovery Accellerator searches.

Comments 10 CommentsJump to latest comment

idaman22's picture

Great query Chris! One minor quibble, If their is an "interest" shown in the archive, that can come from either Discovery Accelerator or Compliance Accelerator.

Login to vote
Chris Warren's picture

Thanks for the input.  CA/DA ain't my cup of tea. :)

Login to vote
JesusWept3's picture

this is super duper nit picky of me, but you can change it to be a little bit cleaner

CASE WHEN AV.Type = '5' THEN 'Shared' 
      WHEN AV.Type = '9' THEN 'Mailbox' 
      WHEN AV.Type = '17' THEN 'Journal' 
      WHEN AV.Type = '33' THEN 'PublicFolder' 


WHEN 5 THEN 'Shared'
WHEN 9 THEN 'Mailbox'
WHEN 17 THEN 'Journal'
WHEN 33 THEN 'Public Folder'

Login to vote
John Santana's picture

Cool, many thanks for sharing the SQL script Chris !

Kind regards,

John Santana
IT Professional


Please be nice to me as I'm newbie in this forum.

Login to vote
Chris Warren's picture

All - I have updated the query above with your suggestions and renamed this post to Useful SQL Query - Archive Details.

Login to vote
Pradeep_Papnai's picture

Thanks Chriss, I just tried in my lab & gave amazing result. It will definately save time if need to know details vai VAC for single archive/mailbox.

Login to vote
Ben Shorehill's picture

Impressive Chris!

Ben Shorehill

Login to vote
chriscolden's picture

Hi Chris,

Very impressive query.

I'm having issues with it though. I have a tone of Mailbox and Shared archives. In most instances the archive names are the same (not sure if this matters), and I'm only seeing mailbox archives.

I've tried adding WHERE AV.Type = '5' but it also returns 0 rows.

I'm guessing one of these tables may not return for shared archives which is causing it issues, but I cannot see what.

Thought I would let you know.

Many Thanks,


Chris Colden

Login to vote
Roy123's picture

Hello Team,

I have a query on Enterprise Vault.Do you have SQL query to find out Archive name with the Archive ID ?

Login to vote
Chris Warren's picture

Roy123 - That information is in the EnterpriseVaultDirectory. The best place in SQL is the ArchiveView view.

USE EnterpriseVaultDirectory
SELECT ArchiveName, VaultEntryID as "ArchiveID"
FROM ArchiveView
WHERE VaultEntryID = '<ArchiveID_of_user>'

Though it would be easiest to do this in the VAC:
- Hold Shift+Ctrl and Right-click Archives and you have 'Find Archive or Folder'.
  You can then past any archiveID or folderID and it will give you the details of it.

Login to vote