AD data to SCCM reporting

First off, this will not be supported by Microsoft or me.

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’, ‘

2) Modify the security for ADSI using SSMS

exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’ADSI’, @locallogin = NULL ,@useself = N’False’, @rmtuser = N’\', @rmtpassword = N”

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://”

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.

Now for the bad news!

While researching this there was some indication that AD will only return 1000 records! There was also some indication that there are some complicated queries out on the Internet to solve this problem but I did not find them. So…