read here more http://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.
