SQL Query

[San_Bangalore]

I am looking for a sql script which fetches all the users in the EV site for whom the Enterprise Vault policy has not been applied. I mean those who are not part of any provision group. We are using EV-10.0.4 and SQL 2008 R2

[JesusWept3]

It would be

USE EnterpriseVaultDirectory
SELECT MbxDisplayName "Display Name", MbxNTUser "Username"
FROM ExchangeMailboxEntry
WHERE (PolicyTargetGroupEntryId IS NULL OR PolicyTargetGroupEntryId = '')

(Edited to add the closing quote properly) 

[San_Bangalore]

Got the below error message after exeucting this sir.

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near 'Username'.

Msg 105, Level 15, State 1, Line 2

Unclosed quotation mark after the character string '

FROM ExchangeMailboxEntry

WHERE (PolicyTargetGroupEntryId IS NULL OR PolicyTargetGroupEntryId = '')

'.

[JesusWept3 · Accepted solution]

Ah sorry it was the Quotes

Change From:
SELECT MbxDisplayName "Display Name, MbxNTUser "Username"

Change To:
SELECT MbxDisplayName "Display Name", MbxNTUser "Username"

[San_Bangalore]

Thank you. This works Great.

[SHI-CRO]

As far as I remember, users who are not part of any provisioning group are not found in the ExchangeMailboxEntry table.

I remember having to export one list from Exchange and export another list from that SQL table, and merging those lists in Excel to compare to see which users aren't known by EV.  I think it was the mailbox alias I used to compare.  You can take the mailbox alias values from each list, merge them in Excel, then use the 'highlight duplicate entries' funtion to see which aliases are only shown once.  These users haven't been provisioned.

There might be an easier way to do it though; I did this a few years ago.