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*fromSMS_R_Systeminner join SMS_G_System_COMPUTER_SYSTEMon SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceIdwhere 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.
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"
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!
select SMS_R_SYSTEM.ResourceID,SM