asked on 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
SCCMSQL
Last Comment
Mike T
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you !
Great!
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).
select SMS_R_SYSTEM.ResourceID,SM