Need help w SQL Query

[waldowilliams]

I need a SQL query that will report:

• All Archives in the Site

• The Exchange Server Associated with that Archive (may be more realisic to report that task associated with the archive?)

• The Vault Store where that archive resides.

Any help appreciated!!

THX!!

[RahulG]

If you just need the information about archive and the store where they belong you may simply view it in vault store summary report 

referhttp://www.symantec.com/business/support/index?page=content&id=HOWTO28269

http://www.symantec.com/business/support/index?page=content&id=HOWTO57925

[TonySterling · Accepted solution]

This should work:

SELECT    
ESE.ExchangeComputer,
EME.MbxDisplayName,
A.ArchiveName,
VSE.VaultStoreName
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


 

[waldowilliams]

Tony, when I run this against a V8 SP4 Directory Database, I get this:

Msg 208, Level 16, State 1, Line 2

Invalid object name 'dbo.ExchangeMailboxEntry'.

[JesusWept3]

it would be something like

SELECT ESE.ExchangeComputer, EME.MbxDisplayame, VSE.VaultStoreName
FROM ExchangeMailboxEntry EME, Archive A, Root R, ExchangeServerEntry ESE, VaultStoreEntry VSE
WHERE EME.ExchangeServerIdentity = ESE.ExchangeServerIdentity
AND EME.DefaultVaultId = R.VaultEntryID
AND R.RootIdentity = A.RootIdentity
AND A.VaultStoreEntryId = VSE.VaultStoreEntryId
ORDER BY ESE.ExchangeComputer, EME.MbxDisplayName

In EV9 they had to record the mailbox store as well as the server for exchange 2010, hence why this query doesn't work in EV8

That being said, the query will ONLY give you results from currently active mailboxes.
If you have had users who have left the company, no longer have a mailbox, been deleted from AD etc, then their archive will not be tied to a task or an exchange server, so you wont get all the results you might want

[TonySterling]

Thanks JW2, I was under the assumption they were a later version. 

[waldowilliams]

So how do I give both of you guys credit for the solution??

[JesusWept3]

you can't, but this ones tony's :)

[waldowilliams]

Please accept my apologies!