read here more https://smsug.ca/blogs/garth_jones/archive/2008/12/03/how-to-add-ad-data-to-configmgr-reporting.aspx AD it just another database, just like SQL server is. With that in mind there is nothing stopping you from using SQL to link to AD to give you data about your AD environment! 1) Create Linked Server using SSMS
exec master.dbo.sp_addlinkedserver ‘ADSI’, ‘Active Directory Service Interfaces’, ‘ADSDSOObject’, ‘<DC Name FQDN>’
2) Modify the security for ADSI using SSMS
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’ADSI’, @locallogin = NULL , @useself = N’False’, @rmtuser = N'<Domian><User Id>’, @rmtpassword = N'<Password>’
3) The hard part is over! 4) Create query to query AD and ConfigMgr This query will list all PCs within AD that are NOT within ConfigMgr (or SMS)
select AD.cn as ‘PC Name(AD)’, AD.operatingSystem as ‘OS (AD)’, AD.operatingSystemServicePack as ‘SP (AD)’ from openquery (ADSI, ‘SELECT cn, operatingSystem, operatingSystemServicePack FROM ”LDAP://<DC Name FQDN>” WHERE objectCategory = ”Computer”’) as AD Where AD.cn not in (Select name0 from v_GS_Computer_System as CS) order by AD.cn, AD.operatingSystem, AD.operatingSystemServicePack
This query will give you a count of all OS that are NOT within ConfigMgr (or SMS)
select AD.operatingSystem as ‘OS (AD)’, count(AD.operatingSystem) from openquery (ADSI, ‘SELECT cn, operatingSystem, operatingSystemServicePack FROM ”LDAP://gartek-dc.gartek.tst” WHERE objectCategory = ”Computer”’) as AD Where AD.cn not in (Select name0 from v_GS_Computer_System as CS) Group by AD.operatingSystem order by AD.operatingSystem
So what does this report look like. 
So there you have it.
