Useful SQL Queries

[Chris_Warren]

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!