A common question I see at my customers or in the forums is "How can I create a Collection based on a Service". The question is a fair one since it can help you to detect if a certain application with a service is installed or not.

To make the Collection dynamic we need to create a 'Query based collection membership rule'. These types of rules are hosting a WQL query and the results of the query are updated on a schedule. By default the Collection will be updated once a day but you can change this on the 'Properties' for the Collection. Keep in mind though that the higher the update schedule the bigger the load is on the database.

In the following example we create a Collection to select all systems running the System Center Operations Manager 2007 Agent and that have a ConfigMgr client installed. The OpsMgr agent is running a service named 'HealthService'. This is the ‘Service name’ and not the ‘Display name’ as you can see in the Services (services.msc) MMC console. When you double-click a service in the console you can see the actual ‘Service name’. It is important that you use this name in your query.

To select all systems running the OpsMgr agent and that have the ConfigMgr client installed the query for the Collection looks like this:

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 inner join SMS_G_System_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SERVICE.Name = "HealthService" and SMS_R_System.Client = 1

To select all systems that are NOT running the OpsMgr agent but that have the ConfigMgr client installed the query for the Collection looks like this:

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 Name not in (SELECT SMS_R_System.Name
FROM SMS_R_System INNER JOIN SMS_G_System_SERVICE ON SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceID WHERE SMS_G_System_SERVICE.Name = "HealthService") and Client = 1

Looking at the last query example you can create a package containing the OpsMgr Agent installation MSI file and advertise it with a mandatory assignment to this Collection. This way you make sure that all systems that are not having the OpsMgr agent installed will be a member of this Collection and so automatically receive the OpsMgr agent package.
Note: In this scenario you might want to filter out the desktops.