Knowledge Base Article

Useful SQL Query (Archive Details)

 

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'
     ,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_CADA"
     
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!
Published 12 years ago
Version 1.0
Enterprise Vault
Information Governance
Tip-How to
Troubleshooting

Was this article helpful?

10 Comments

Sort By
  • idaman22's avatar
    idaman22
    Level 4
    12 years ago

    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.

  • JesusWept3's avatar
    JesusWept3
    Level 6
    12 years ago

    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' 

    to

    CASE AV.Type
    WHEN 5 THEN 'Shared'
    WHEN 9 THEN 'Mailbox'
    WHEN 17 THEN 'Journal'
    WHEN 33 THEN 'Public Folder'

     

  • Chris_Warren's avatar
    Chris_Warren
    Level 5
    12 years ago

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

  • Pradeep-Papnai's avatar
    Pradeep-Papnai
    Level 6
    12 years ago

    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.

  • chriscolden's avatar
    chriscolden
    Level 4
    11 years ago

    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

  • Roy123's avatar
    Roy123
    10 years ago

    Hello Team,

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

  • Chris_Warren's avatar
    Chris_Warren
    Level 5
    10 years ago

    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.