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. image So there you have it.

 

Leave a Reply

Your email address will not be published. Required fields are marked *