Looking for a SQL Query for generating report on each archiving servers with AD details

[nazirkolliyath]

Hi, 

We have two sites and 3 archiving servers on each sites, I need to generate a report for all active and inactive users EV status with their AD attributes ( such as display name, alias, email address , company, logon name etc) assigned for each archivng servers.

[VirgilDobos]

Hi there,

Can you run this against the Directory database on each of the EV sites and see if it helps.

SELECT EME.MbxDisplayName "Username",
ESE.ExchangeComputer "Exchange Server",
EMS.Name "Exchange DB",
VSE.VaultStoreName "EV 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

[CConsult]

Hi,

you might need a connection between THIS:

select MbxDisplayName, MbxAlias, MbxArchivingState,MbxExchangeState
from ExchangeMailboxEntry

>>

MbxArchivingState

0=Ok, 1=deleted, 2=hidden

MBxExchangeState

0=not enabled, 1=enabled, 2=disabled

so if user has MbxArchivingState=0 &&MbxExchangeState=1 the user is active

if not the user is most likely inactive (since hidden mailboxes have restrictions)

AND

Powershell command "Get-AdUser" or Exchange Powershell "Get-Mailbox" commands, this will return the AD values.