Video Screencast Help

EV SQL Query to list all the maiboxes (mailboxes With and with out EV archives): Mailbox display name, Exchange server name, Mailbox Database name & Vault store name?

Created: 09 Aug 2012 • Updated: 09 Aug 2012 | 7 comments
This issue has been solved. See solution.

EV SQL Query to list all the maiboxes (mailboxes With and with out EV archives): Mailbox display name, Exchange server name, Mailbox Database name & Vault store name?

Comments 7 CommentsJump to latest comment

JesusWept3's picture
SELECT EME.MbxDisplayName "Mailbox Name",
       ESE.ExchangeComputer "Exchange Server",
       EMS.Name "Exchange Database",
       VSE.VaultStoreName "Vault Store"
FROM   ExchangeMailboxEntry EME,
       ExchangeMailboxStore EMS,
       ExchangeServerEntry ESE,
       Root R,
       Archive A,
       VaultStoreEntry VSE
WHERE  EME.MbxStoreIdentity = EMS.MbxStoreIdentity
  AND  EMS.ExchangeServerIdentity = ESE.ExchangeServerIdentity
  AND  EME.DefaultVaultId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
  AND  A.VaultStoreEntryId = VSE.VaultStoreEntryId
ORDER BY ESE.ExchangeComputer, EMS.Name, EME.MbxDisplayName
SOLUTION
JesusWept3's picture

oh and that above will only report on users that have been enabled for Enterprise Vault.
If they are not, then you can't report on their vault store if they dont have an archive to tie to a vault store to begin with.
 

JesusWept3's picture

It can only report on SQL based on whats been provisioned
so if you have 100,000 mailboxes in your environment, but you only provision say 10,000 mailboxes, you can only possibly report on 10,000 mailboxes

Anywho, you'll have to run two queries.
First one as above being those who have vault stores.
And then for the mailboxes that aren't enabled you would do
 

SELECT EME.MbxDisplayName "Mailbox Name",
       ESE.ExchangeComputer "Exchange Server",
       EMS.Name "Exchange Database"
FROM   ExchangeMailboxEntry EME,
       ExchangeMailboxStore EMS,
       ExchangeServerEntry ESE
WHERE  EME.MbxStoreIdentity = EMS.MbxStoreIdentity
  AND  EMS.ExchangeServerIdentity = ESE.ExchangeServerIdentity
  AND  EME.DefaultVaultId = ''
ORDER BY ESE.ExchangeComputer, EMS.Name, EME.MbxDisplayName
Dushan Gomez's picture

Hi,

can somene please clarify that if the script above script is working for EV 8.0 with Exchange Server 2007 ?

because I got this error:

 

  • Msg 207, Level 16, State 1, Line 12
  • Invalid column name 'exchangeserveridentity'.
  • Msg 207, Level 16, State 1, Line 3
  • Invalid column name 'name'.
  • Msg 207, Level 16, State 1, Line 17
  • Invalid column name 'name'.
 

Dushan Gomez
IT Manager
VCP 4 and 5 | MCITP Exchange Server | MCTS SharePoint Server | MCP Windows XP