SCCM 2007: Creating a collection of systems that do not have a certain EXE file present

This page will go though how to create a “Microsoft System Center Configuration Manager 2007” collection that contains all systems that do not have a specific exe file at a certain version present on any local drive.

I personally use this method to distribute software to systems that either do that have it already or have an old version installed.

Before we begin you will need two bits of information the filename of the executable and the exact version number. The best way to get the file version is to bring up  the file properties for the exe and then select “File Version” under “Other version information”, as below occasionally you will find the number differs slightly from the version at the top of the window. SCCM uses the version under “Other version information” so it is best to use that

For the purpose of this example I will be trying to find all systems that do that do not have a exe called “DLOClientu.exe” at version “3.10.338.7401” installed, please change the filename and version number accordingly

Essentially what we are going to do it create a new collection which finds all the systems which are not in a list of systems that have the exe.

Note: Before clicking Edit Query Statement you could limit to query to another collection rather than searching all systems

1. Open SCCM and expand “Site Database” -> “Computer Management”
2. Right-click “Collections” and select “New Collection”
3. On the General screen give the Collection a name such as “All Systems Without DLO Agent 3.10.338.7401” ideally give the collection a comment and click Next
4. On the Membership Rules screen click the little yellow data symbol to create a new query rule
5. On the Query Rule Properties screen give the query a name such as “All Systems Without DLO Agent 3.10.338.7401”, change the resource class to “System Resource” and click “Edit Query Statement”
6. On the Query Statement Properties screen click “Show Query Language”
7. Over write the current query statement with the below, change the filename and version number as required.
8. Click OK, OK, Next, Next, Finish

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_SYSTEM ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId
 WHERE SMS_G_System_SYSTEM.Name 
NOT IN (SELECT SMS_G_System_SYSTEM.Name
FROM  SMS_R_System INNER JOIN SMS_G_System_SYSTEM ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId INNER JOIN SMS_G_System_SoftwareFile ON SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId
 WHERE SMS_G_System_SoftwareFile.FileName = "DLOClientu.exe" AND SMS_G_System_SoftwareFile.FileVersion = "3.10.338.7401" AND SMS_R_System.Client = 1)  

The above SQL Query will find all Systems that do not have the exe  “DLOClientu.exe” at the exact verion “3.10.338.7401” present

If you don’t care about the version number and as long as they have the EXE don’t show the system in the collection then delete the below section from the SQL query

AND SMS_G_System_SoftwareFile.FileVersion = "3.10.338.7401" 

If for example tou only want to show systems that do that have “DLOClientu.exe” at any version starting with “3.10” (systems that do not have the exe or systems with version “2.05”, not systems with the exe at version “3.10.338.6101” etc) then change the SQL section as below;

AND SMS_G_System_SoftwareFile.FileVersion LIKE "3.10.%"

Thanks for reading and as always please comment if you have any questions.