04-19-2012 09:35 AM
I need a SQL query that will report:
Any help appreciated!!
THX!!
Solved! Go to Solution.
04-19-2012 10:06 AM
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
04-19-2012 09:59 AM
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
04-19-2012 10:06 AM
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
04-19-2012 12:08 PM
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'.
04-19-2012 12:17 PM
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
04-19-2012 01:20 PM
Thanks JW2, I was under the assumption they were a later version.
04-19-2012 01:25 PM
So how do I give both of you guys credit for the solution??
04-19-2012 01:28 PM
you can't, but this ones tony's :)
04-24-2012 01:59 PM
Please accept my apologies!