- SQL
- Microsoft SQL Server
- SCCM
- Query Syntax
SCCM Collection Query.
SCCM Collection Query.
I have 2 domain controllers [DC1 and DC2 ]Discovered in SCCM and with SCCM client installed on both of them.
![a]()
I would like to create a query based collections for Domain Controllers. I used this query:
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
I have 2 domain controllers [DC1 and DC2 ]Discovered in SCCM and with SCCM client installed on both of them.
I 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%"
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.
Any help will be very much appreciated.
Thank you
The syntax is correct, however, the agent installation is required.
Also, see the comment section here:
https://exchange12rocks.org/2014/08/18/sccm-all-domain-controllers-collection/
But there are other options to achiveve what you want. You can create the collection based on:
Domain Controllers AD GroupID
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"
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"
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"
followed this link...And it worked : https://blog.thesysadmins.co.uk/sccm-2012-creating-device-collections.html
select SMS_R_SYSTEM.ResourceID,SM
select SMS_R_SYSTEM.ResourceID,SM
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).
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).
Premium Content
You need an Expert Office subscription to comment.Start Free Trial