Description

The following SQL cursor will provide a list of Enterprise Vault server names that are responsible for each 32-bit Journal Archive, including the corresponding Index and Storage service IDs.  The cursor can be modified to allow for 64-bit indexes and a variety of Archive types.

NOTE: Do not remove the WHERE clause.  With no WHERE filter, the cursor results may be too large, resulting in a never ending running query.

/* 
Run the following cursor against the EnterpriseVaultDirectory database
Note: The following line should be modified to adjust for the desired results
WHERE  rt.Type <> 9 AND rt.Type <> 5 AND iv.IndexVolumeType = 0

iv.indexVolumeType
- 0 = 32-bit indexes
- 1 = 64-bit indexes

rt.Types
- 17 = Journal Archives
- 5    = Shared
- 9    = Exchange Mailbox    
- 17   = Exchange Journal Mailbox    
- 33   = Exchange Public Folder
- 65   = SharePoint    
- 129  = File System    
- 257  = SharePoint    
- 513  = Domino Journal    
- 1025 = Domino Mailbox    
- 2049 = SMTP    
- 4097 = Internet Mail   

Author: Kevin Graves 3/14/2019
*/

CREATE TABLE #TmpTable (
tmpArchiveName nvarchar(256),
tmpIndexComputerName nvarchar(72),
tmpStorageComputerName nvarchar(72),
tmpIndexServiceEntryID nvarchar(112),
tmpStorageServiceEntryID nvarchar(112),
tmpArchiveID nvarchar(112),
tmpArchiveType int)

DECLARE @vArchiveName nvarchar(256)
DECLARE @vIndexComputerName nvarchar(72)
DECLARE @vStorageComputerName nvarchar(72)
DECLARE @vIndexServiceEntryID nvarchar(112)
DECLARE @vStorageServiceEntryID nvarchar(112)
DECLARE @vArchiveID nvarchar(112)
DECLARE @vArchiveType int

DECLARE ReadIndexTable CURSOR FOR

SELECT DISTINCT
ar.ArchiveName,
ce.ComputerName,
ise.ServiceEntryID,
rt.VaultEntryID,
rt.Type
 FROM [EnterpriseVaultDirectory].[dbo].[IndexVolume] iv
 JOIN [EnterpriseVaultDirectory].[dbo].[Root] rt
 ON rt.RootIdentity = iv.RootIdentity
 JOIN [EnterpriseVaultDirectory].[dbo].[Archive] ar
 ON ar.RootIdentity = iv.RootIdentity
 JOIN [EnterpriseVaultDirectory].[dbo].[IndexRootPathEntry] irpe
 ON iv.IndexRootPathEntryId = irpe.IndexRootPathEntryId  
 JOIN [EnterpriseVaultDirectory].[dbo].[IndexingServiceEntry] ise
 ON irpe.IndexServiceEntryId = ise.ServiceEntryId
 JOIN [EnterpriseVaultDirectory].[dbo].[ComputerEntry] ce
 ON ise.ComputerEntryId = ce.ComputerEntryId
-- Remark out the next line if Mailbox Archives are needed
WHERE  rt.Type <> 9 AND rt.Type <> 5 AND iv.IndexVolumeType = 0

OPEN ReadIndexTable
FETCH NEXT FROM ReadIndexTable INTO @vArchiveName, @vIndexComputerName, @vIndexServiceEntryID, @vArchiveID, @vArchiveType
WHILE @@FETCH_STATUS = 0
BEGIN

SET @vStorageComputerName =
(SELECT
ce.ComputerName
FROM  [EnterpriseVaultDirectory].[dbo].[ComputerEntry] ce
JOIN [EnterpriseVaultDirectory].[dbo].[StorageServiceEntry] see
ON see.ComputerEntryId = ce.ComputerEntryId
JOIN [EnterpriseVaultDirectory].[dbo].[VaultStoreEntry]vse
ON vse.StorageServiceEntryId = see.ServiceEntryId
JOIN [EnterpriseVaultDirectory].[dbo].[Archive]ar
ON ar.VaultStoreEntryId = vse.VaultStoreEntryId
JOIN [EnterpriseVaultDirectory].[dbo].[root]rt
ON rt.RootIdentity = ar.RootIdentity
WHERE  rt.VaultEntryID = @vArchiveID)

SET @vStorageServiceEntryID =
(SELECT
see.ServiceEntryId
FROM  [EnterpriseVaultDirectory].[dbo].[ComputerEntry] ce
JOIN [EnterpriseVaultDirectory].[dbo].[StorageServiceEntry] see
ON see.ComputerEntryId = ce.ComputerEntryId
JOIN [EnterpriseVaultDirectory].[dbo].[VaultStoreEntry]vse
ON vse.StorageServiceEntryId = see.ServiceEntryId
JOIN [EnterpriseVaultDirectory].[dbo].[Archive]ar
ON ar.VaultStoreEntryId = vse.VaultStoreEntryId
JOIN [EnterpriseVaultDirectory].[dbo].[root]rt
ON rt.RootIdentity = ar.RootIdentity
WHERE  rt.VaultEntryID = @vArchiveID)

    INSERT INTO #TmpTable
    (tmpArchiveName, tmpIndexComputerName, tmpStorageComputerName, tmpIndexServiceEntryID, tmpStorageServiceEntryID, tmpArchiveID, tmpArchiveType)
    VALUES
    (@vArchiveName, @vIndexComputerName, @vStorageComputerName, @vIndexServiceEntryID, @vStorageServiceEntryID, @vArchiveID, @vArchiveType)

FETCH NEXT FROM ReadIndexTable INTO @vArchiveName, @vIndexComputerName, @vIndexServiceEntryID, @vArchiveID, @vArchiveType
END

SELECT  
tmpArchiveName 'Archive Name',
tmpIndexComputerName 'Indexing Computer',
tmpStorageComputerName 'Storage Computer',
tmpIndexServiceEntryID 'Indexing Service ID',
tmpStorageServiceEntryID 'Storage Service ID',
tmpArchiveID 'Archive ID',
tmpArchiveType 'Archive Type'
FROM #tmpTable

CLOSE ReadIndexTable
DEALLOCATE ReadIndexTable
DROP TABLE #tmpTable