SQL Query to get all System discovery containers OU

Question

Hi,

I need an sql query to get OU list for every primary site within CAS

I have following piwershell script but need sql query to do same

cls

$listAdContainers = (get-cmdiscoveryMethod -name ActiveDirectorySystemDiscovery -sitecode xxx).proplists | where-Object {$_.PropertyListName -eq "AD Containers"}

($listAdContainers.values -like 'ldap*')

Answer 1

Everything that you need is listed within the online docs. https://learn.microsoft.com/en-us/mem/configmgr/develop/core/understand/sqlviews/sql-server-views-configuration-manager

Answer 2

I have this from years ago; assume it still works:

Select
RV.Netbios_Name0,
case
When (Max(OU.System_OU_Name0) != Null or Max(OU.System_OU_Name0) != '')
Then Max(OU.System_OU_Name0)
Else max(cn.System_Container_Name0)
end as 'OU'
From
dbo.v_R_System_Valid RV
left outer join dbo.v_RA_System_SystemContainerName CN on RV.Resourceid = cn.resourceID
left outer join dbo.v_RA_System_SystemOUName OU on RV.Resourceid = OU.resourceID
Group by
RV.Netbios_Name0

Answer 3

Hi, @lalajee

Thank you for posting in Microsoft Q&A forum.

Sherry's query is for checking the OU for each client, it doesn't seem to meet your requirements.
I tried to find a view to query the AD containers for the Active Direstory System Discovery, but I did not find it.
I also tried your powershell command, it did query the AD Containers list. Why do you also need the sql query?

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.