Avatar of jskfan
jskfan
Flag for Cyprus 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.
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
SCCMSQL

Avatar of undefined
Last Comment
Mike T

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Hello There

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
jskfan

ASKER
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"
jskfan

ASKER
Thank you !
Hello There

Great!
Your help has saved me hundreds of hours of internet surfing.
fblack61
Mike T

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).