Here is the Dynamic Device Collection query….
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 🙂