To deploy software you normally put some computers on a collection and then target the software to it. Sometimes you need to deploy a particular application to any system that doesn’t have it now or in the future (perhaps for compliance reasons). One efficient way to accomplish this in SCCM is to create a dynamic collection that has as members all the systems that are missing this particular application. Then you can advertise the appropriate SCCM program to the collection so the application gets installed on them.
If you configure the collection to update on a schedule (for example every 1 day), the next time the collection membership is evaluated, the systems that received your advertisement, installed the application and afterwards reported hardware inventory to SCCM, will automatically leave the collection. Likewise, new systems detected as not having the application will become members of the collection and be targeted.
As long as the advertisement does not expire, it will continue targeting the collection and installing the application only on the systems that are missing it.
To demonstrate this, we’ll create a collection with systems that are missing Forefront Endpoint Protection 2010 (FEP 2010) based on the data reported by a system in Add/Remove Programs. Here we can see the application displayed in Add/Remove Programs.
When creating the collection, make sure that you enable “Update this collection on a schedule” and select the appropriate frequency. To create a query-based rule click on the database symbol (marked with a red circle below).
Next click on “Edit Query Statement”
Then click on “Show Query Language”
And here’s where we’ll enter our SQL Query.
To start working on our SQL query, let’s first create a query that pulls all systems that report having FEP 2010 installed in Add/Remove Programs.
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_ADD_REMOVE _PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System. ResourceId 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.DisplayName LIKE "%Forefront Endpoint Protection 2010%" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName LIKE "%Forefront Endpoint Protection 2010%")
An important comment about this query is that it queries both Add/Remove Programs tables. One for x32 and one for x64 as there are separate storage locations for each platform. It also uses the LIKE operator so we don’t have to enter the exact name as it appears in Add/Remove Programs in control panel (as the name displayed in Add/Remove Programs in Control Panel may vary on different systems). This is why we use the percent signs wrapping the name, meaning that the actual name in Add/Remove Programs in Control Panel may have more data before “Forefront” and more data after “2010”. You can replace the application name in red with any application that you are interested in deploying using this method (make sure that you first see how it appears in Add/Remove Programs in Control Panel on a system that has it installed).
Now we’ll use the query above as a sub-query. We’ll create a query that says give me all systems that are NOT IN the result from the query above. This will give us all the systems that are not reporting FEP 2010 installed in Add/Remove Programs to SCCM.
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 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.DisplayName LIKE "%Forefront Endpoint Protection 2010%" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName LIKE "%Forefront Endpoint Protection 2010%"))
This query will pull all systems that are not reporting to SCCM having FEP 2010 installed in Add/Remove Programs. However, there is a practical problem with it. It will also pull systems that have no inventory data in SCCM. This could be systems that have been discovered in SCCM but don’t have the SCCM client installed. It could also be systems that have the SCCM client installed but for some reason their inventory data is not in SCCM. These systems without inventory data in SCCM may or may not have FEP 2010 installed. To be sure that our dynamic collection includes only systems that are reporting inventory data to SCCM and their reported data indicates that FEP 2010 is not installed in Add/Remove Programs, use the query below.
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM. Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomain ORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System join SMS_G_System _WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceID where SMS_R_System.ClientType =1 and SMS_G_System_WORKSTATION_STATUS.LastHardwareScan is not null and SMS_R_SYSTEM.ResourceID in (select ResourceID from SMS_G_System _SERVICE) and 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 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.DisplayName LIKE "%Forefront Endpoint Protection 2010%" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName LIKE "%Forefront Endpoint Protection 2010%"))
Remember, to deploy another application, replace what’s in red above with your application name as it appears in Add/Remove Programs in Control Panel.
