We help IT Professionals succeed at work.

SCCM Collection Query.

jskfan
jskfan asked
on
SCCM Collection Query.

I have 2 domain controllers [DC1 and DC2 ]Discovered in SCCM and with SCCM client installed on both of them.
aI would like to create a query based collections for Domain Controllers. I used this query:
select
*
from
SMS_R_System
inner join SMS_G_System_COMPUTER_SYSTEM
on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_COMPUTER_SYSTEM.Roles like "%Domain_Controller%"

Open in new window


however when I click Update Membership on the collection I created and named DC, it shows only one Domain Controller which is DC1, it does not show DC2.

s
Any help will be very much appreciated.

Thank you
Comment
Watch Question

System Administrator
BRONZE EXPERT
Distinguished Expert 2018
Commented:
The syntax is correct, however, the agent installation is required.

The Roles attribute in the ComputerSystem class is filled by the Configuration Manager agent after installing it; if the agent is not installed in the system, the attribute is empty, so any query involving it always fails. You need to deploy the agent on your domain controllers; then, you'll be able to filter on that attribute in order to automatically identify DCs.
https://social.technet.microsoft.com/Forums/lync/en-US/02f219cc-0b8f-4c65-8bbf-c91050d5a456/sccm-query-for-domain-controller-not-working?forum=configmgrgeneral

Also, see the comment section here:
https://exchange12rocks.org/2014/08/18/sccm-all-domain-controllers-collection/
... it requires the client to be installed and Hardware Inventory to run... If you use Method 1, you will have to wait for hardware inventory to be reported and the collection to update again before a new client on a domain controller ends up in the collection.

But there are other options to achiveve what you want. You can create the collection based on:
Domain Controllers AD GroupID
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.PrimaryGroupID = "516"

Open in new window

Domain Controllers AD group name
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.SystemGroupName = "domain\Domain Controllers"

Open in new window

AD feature name
select *  from  SMS_R_System inner join SMS_G_System_SERVER_FEATURE on SMS_G_System_SERVER_FEATURE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SERVER_FEATURE.Name = "Active Directory Domain Controller"

Open in new window

Author

Commented:
followed this link...And it worked : https://blog.thesysadmins.co.uk/sccm-2012-creating-device-collections.html

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 = "TEST.LOCAL/DOMAIN CONTROLLERS"

Author

Commented:
Thank you !
Hello ThereSystem Administrator
BRONZE EXPERT
Distinguished Expert 2018

Commented:
Great!
Mike TLeading Engineer
BRONZE EXPERT

Commented:
Hi,

I see you have the answer already. One extra tip I will make: Avoid the both like and %, e.g. like "%Domain_Controller%"

why? 1) SQL has to work much harder*, meaning it is slower, meaning you get to sit there watching the hourglass more. You don't want that.
2) The %DC% is a double wildcard and is not even necessary. The more % wildcards you use, the slower things are
3) That query relies on the target successfully getting hardware inventory (HWINV) and returning it. You will find two failures spoil that - machines with no agent at all and machines where HWINV is delayed or broken. Either way I like all the other Expert's answers. All work and it's down to taste which you use!

*it's only seconds vs milliseconds but 100 collections in you will be sat at the console waiting. and waiting. Avoid the pain and tune collections.
This is a good read:  https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/sccm-housekeeping-collections/ba-p/1227039 (and the original post).