Here is the Dynamic Device Collection query….
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.ResourceId not in (select
SMS_R_System.ResourceId from SMS_R_System inner join
SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID
= SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE
“%System Center Operations Manager 2012 Agent%“) and
SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId from
SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on
SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where
SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName LIKE “%System Center
Operations Manager 2012 Agent%“)and SMS_R_System.Client = 1 and
SMS_R_SYSTEM.OperatingSystemNameAndVersion like ‘%Server%‘
In the example above, the query will create a device collection that lists computers that do NOT have the SCOM 2012 agent installed on them. See the parts bolded above, you can easily substitute other software titles and create several device collections to suit your particular needs.
Notice that the software in question is listed twice in this query, this query will look in both the x86 and x64 Add/Remove Programs listing. The last part of the query, “%Server%” limits the query to only return results for machines with Server based operating systems.
Applications for the use of a dynamic device collection query such as the one shown above? Well if you happen to create an SCCM application and deploy it against a dynamic collection like this, the collection will update itself and automatically remove the computers that eventually get this software installed. In this example we have a collection showing computers that do NOT yet have the SCOM 2012 Agent installed on them, we’ve deployed an SCCM application against this collection that installs this SCOM agent, the collection updates itself when the software eventually populates the Add/Remove programs listing on the server and what you’re left with is a current up to date device collection of machines that don’t have the software that will eventually receive the update we’ve deployed. Any new machines that we deploy in the future will automatically add themselves to this collection and eventually automatically receive the software we’ve deployed against this collection.
– Automation, what a novel idea 🙂
Rob's IT blog 
I wish this query would stop being posted all over the internet. It contains a SYNTAX ERROR and is causing many people to waste lots of time.
There is nothing wrong with the query itself,
the problem lies in copying it and pasting the query into SCCM,
unfortunately some spaces or line breaks get included during the process and that is what’s causing the syntax error when you try to run or save the query in SCCM. As for your time, I can’t do much about that, I’ve invested plenty of time researching and working in SCCM and I post that stuff that works/has worked for me, hopefully you’ll have more success in the future 🙂
Some genuinely prize content on this internet site, bookmarked. agaekafkdagb
Thank you for the great feedback, I’ll be adding more content soon! 🙂
Thank you for the great feedback, I plan to add more content very soon!!! 🙂
Hi there, just became aware of your blog through Google, and found that it is really informative. Im going to watch out for brussels. Ill be grateful if you continue this in future. Lots of people will be benefited from your writing. Cheers! begkgfcgdgcb
Thank you, I’m going to start adding more content soon! 🙂
Thank you for the great feedback, I plan to add more content very soon!!! 🙂
Keep working ,fantastic job! kekedkacdacdgdek