SMSUG.ca

...your virtual communITy
Welcome to SMSUG.ca Sign in | Join | Help
in Search

Garth Jones

How to add AD data to ConfigMgr 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', '<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.

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

Published Dec 03 2008, 03:25 PM by Garth
Filed under: , , ,

Comments

 

Clients Just Wont Die !! System Maintenance Issue | keyongtech said:

Pingback from  Clients Just Wont Die !!  System Maintenance Issue | keyongtech

March 15, 2009 11:07 AM
 

Enhansoft said:

SCCM Guru Webcast Follow Up

March 19, 2012 7:12 AM
 

SCCM Guru Webcast Follow-up said:

Pingback from  SCCM Guru Webcast Follow-up

March 20, 2012 8:23 AM
 

SCCM Guru Webcast Follow-up said:

Pingback from  SCCM Guru Webcast Follow-up

March 20, 2012 8:23 AM
 

Sccm – Find AD computer without sccm client and stale client « Knowledge Is Power said:

Pingback from  Sccm &#8211; Find AD computer without sccm client and stale client &laquo; Knowledge Is Power

April 7, 2012 8:53 AM
 

Maik Koster at myITforum.com said:

Despite all discovery methods, Health scripts and other cleanup efforts it might be helpful from time

September 28, 2012 7:21 AM
 

FIFA 15 Crack said:

How to add AD data to ConfigMgr reporting - Garth Jones

September 11, 2014 9:27 AM

This Blog

Syndication

News

Training

Powered by Community Server (Non-Commercial Edition), by Telligent Systems