cancel
Showing results for 
Search instead for 
Did you mean: 

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?

rajesh_velagapu
Level 4

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?

1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified
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
https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

7 REPLIES 7

JesusWept3
Level 6
Partner Accredited Certified
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
https://www.linkedin.com/in/alex-allen-turl-07370146

JesusWept3
Level 6
Partner Accredited Certified

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.
 

https://www.linkedin.com/in/alex-allen-turl-07370146

rajesh_velagapu
Level 4

Thanks!!!

 

is that possible to report all mailboxes in the organisation? if EV is not enabled then EV vault store should be blank.

JesusWept3
Level 6
Partner Accredited Certified

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
https://www.linkedin.com/in/alex-allen-turl-07370146

rajesh_velagapu
Level 4

Thanks!!!

rajesh_velagapu
Level 4

For the first query, I also need OU. Can you please add OU to it?

Dushan_Gomez
Level 6

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'.