EV11- Looking for an SQL query to show the proccessing EV server for each archive

[SutterKane]

Hello.

I already nearly blew my (small) mind. Also google has given up in trying to provide me useful results. :)

The problem is I am in need of a SQL  query showing a list of all archives and the "associated" EV server.

I know this can be looked up manually / reversible ( Archive -> Mailbox -> Archiving Task for MS Exchangeserver -> EV Server).

But because we have 16 EV Servers and more than 40.000 archives, a query is much appreciated.

Does anyone have such a query at hand, maybe ?

Thanks !

SK

[CConsult]

Powershell will not give you the results.

You will need to scipt a SQL query on the table where the archives are saved. 

As far as I remember this script should do it:

SELECT VaultStoreName AS 'Vault Store', ArchiveName AS Archive, DatabaseDSN AS 'Database', SQLServer AS 'SQL Server'
FROM VaultStoreEntry vse, Archive a
WHERE vse.VaultStoreEntryId = a.VaultStoreEntryId
GROUP BY VaultStoreName, ArchiveName, DatabaseDSN, SQLServer

Syntax can be different depending on the EV-version you are using.

[AndrewB · Accepted solution]

give this a try. it will list the associated exchange server, mailbox, archive, and vault store. since each vault store can only be on 1 EV server, you should have what you need.

Use EnterpriseVaultDirectory
SELECT ESE.ExchangeComputer as 'Exchange Server', EME.MbxDisplayName as 'Mailbox', A.ArchiveName as 'Archive', VSE.VaultStoreName as 'Vault Store'
FROM dbo.ExchangeMailboxEntry AS EME
INNER JOIN dbo.ExchangeMailboxStore EMS ON EMS.MbxStoreIdentity = EME.MbxStoreIdentity
INNER JOIN dbo.ExchangeServerEntry ESE ON ESE.ExchangeServerIdentity = EMS.ExchangeServerIdentity
INNER JOIN dbo.Root AS R ON R.VaultEntryId = EME.DefaultVaultId
INNER JOIN dbo.Archive AS A ON A.RootIdentity = R.RootIdentity
INNER JOIN dbo.VaultStoreEntry AS VSE ON VSE.VaultStoreEntryId = A.VaultStoreEntryId