SCCM: selecting objects not in a collection

Although SCCM provides a way of restricting a result set to a collection, it doesn't provide a way of excluding another collection from your collection.

It's not that hard, if you get the Collection ID of the collection that you want to exclude, which you'll find in the properties of your collection and use WQL to rule out the membership of that collection.

select sms_r_system.resourceid, sms_r_system.name
from sms_r_system
where resourceid not in
(
select sms_r_system.resourceid
from sms_cm_res_coll_[ID], sms_r_system
where sms_r_system.resourceid = sms_cm_res_coll_[ID].resourceid
)

You can also chain them together. So, for example, to execute a query for a collection that you want to patch and automatically reboot the machines, but you want to exclude your 2 collections for manual patching and suppressed reboots and for reporting purposes you want to exclude the machines that don't have a client:

select sms_r_system.resourceid, sms_r_system.name
from sms_r_system
where resourceid not in
(
select sms_r_system.resourceid
from sms_cm_res_coll_sms00001, sms_r_system
where sms_r_system.resourceid = sms_cm_res_coll_sms00001.resourceid
)
and resourceid not in
(
select sms_r_system.resourceid
from sms_cm_res_coll_sms00002, sms_r_system
where sms_r_system.resourceid = sms_cm_res_coll_sms00002.resourceid
)
and resourceid not in
(
select sms_r_system.resourceid
from sms_cm_res_coll_sms00003, sms_r_system
where sms_r_system.resourceid = sms_cm_res_coll_sms00003.resourceid
)

Cheers,
Sean

PS: If anyone can tell me how I can place a comment in WQL code, I'd love to know!

PPS: It looks like the SQL convention of using /* comment */ works as I found it in some of the inbuilt reports. I could have sworn I'd tried this and it didn't work, but maybe I had them the wrong way around. /* embarrassed grin */