Wednesday, August 21, 2013

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.
image
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…