locked
Query SCCM Show only computers in AD RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    In sccm2012 we synchronizes computeraccounts with “active directory Group Discovery”. We synchronizes some OU’s.

    In the AD someone throw all de computers away who are no longer in our Company. Now I want Cleanup all the computers who are in SCCM and not in the AD.

     I make a collection with this Query:

    select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemOUName = "Domain/COMPUTEROBJECTEN/CLIENTS/Windows7"

    The result is that I get all the computers how are in SCCM because it look at the field "Distinguished Name" that is saved in SCCM and it did not really look into the AD.

    Some now how to fix that?

    Friday, October 30, 2015 2:13 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    You don't as there's nothing broken. When you create a query in ConfigMgr, you are querying the ConfigMgr DB, not AD.

    You could use a query like this

    select SMS_R_System.ResourceId, SMS_R_System.ResourceType, 
    SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, 
    SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client 
    from SMS_R_System
    where SMS_R_System.ResourceId not in 
    	(select ResourceID 
    	 from SMS_R_System 
    	 where AgentName in ("SMS_AD_SYSTEM_DISCOVERY_AGENT") 
    		and DATEDIFF(day,AgentTime,GetDate())<=14) 
    and SMS_R_System.ResourceDomainORWorkgroup = "DOMAIN"

    This query checks the last time a system was discovered by AD System Discovery. You will of course need to modify it if you are interested in Group Discovery.

    You can script it also: http://configmgrblog.com/2013/01/25/how-to-cleanup-in-ad-deleted-devices-via-powershell-in-configmgr-2012-sp1/

    Jason | http://blog.configmgrftw.com | @jasonsandys


    Friday, October 30, 2015 2:56 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    You don't as there's nothing broken. When you create a query in ConfigMgr, you are querying the ConfigMgr DB, not AD.

    You could use a query like this

    select SMS_R_System.ResourceId, SMS_R_System.ResourceType, 
    SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, 
    SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client 
    from SMS_R_System
    where SMS_R_System.ResourceId not in 
    	(select ResourceID 
    	 from SMS_R_System 
    	 where AgentName in ("SMS_AD_SYSTEM_DISCOVERY_AGENT") 
    		and DATEDIFF(day,AgentTime,GetDate())<=14) 
    and SMS_R_System.ResourceDomainORWorkgroup = "DOMAIN"

    This query checks the last time a system was discovered by AD System Discovery. You will of course need to modify it if you are interested in Group Discovery.

    You can script it also: http://configmgrblog.com/2013/01/25/how-to-cleanup-in-ad-deleted-devices-via-powershell-in-configmgr-2012-sp1/

    Jason | http://blog.configmgrftw.com | @jasonsandys


    Friday, October 30, 2015 2:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks. The query give me not the correct computers. Later I will try http://configmgrblog.com/2013/01/25/how-to-cleanup-in-ad-deleted-devices-via-powershell-in-configmgr-2012-sp1/ in our testdomain first (;
    Friday, October 30, 2015 4:21 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    As explicitly mentioned, you will probably have to modify the query to suit your purposes; it's simply a starting point/example.

    Jason | http://blog.configmgrftw.com | @jasonsandys

    Friday, October 30, 2015 5:32 PM