Video Screencast Help
Archiving and eDiscovery Community Blog

Useful SQL Queries

Created: 20 Feb 2013 • Updated: 29 May 2014 • 2 comments
Chris Warren's picture
+2 2 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 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 AV.Type = '65' THEN 'SharePointServer' 
      WHEN AV.Type = '129' THEN 'FSA' 
      WHEN AV.Type = '257' THEN 'Sharepoint' 
      WHEN AV.Type = '513' THEN 'LotusJournal' 
      WHEN AV.Type = '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'
     ,SQLServer
     ,VSE.VaultStoreName
     ,VSE.DatabaseName
     ,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"
     ,IVV.OldestArchivedDateUTC
     ,IVV.YoungestArchivedDateUTC
     ,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_DA
     
FROM
     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
     
WHERE
--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.
 
Enjoy!

Comments 2 CommentsJump to latest comment

AndrewB's picture

nice one Chris!

Andy Becker | Authorized Symantec Consultant | Trace3 | Symantec National Partner | www.trace3.com

0
Login to vote
Rob.Wilcox's picture
0
Login to vote