Archive: SCCM collection query for missing software

sccm2012icon

Today, I found myself creating yet another collection to find systems in Configuration Manager 2012 that were missing a particular piece of software. I have done this in the past several times since it’s particularly useful to target for software deployments, but I always used the sub-collection method using a somewhat unnecessary parent collection.

The following query is a bit more straightforward and can function on its own. Just change the software you are looking for between the ‘%’s in the last line.

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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select SMS_G_System_COMPUTER_SYSTEM.Name from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 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 “%.NET Framework 4%”)

8 thoughts on “Archive: SCCM collection query for missing software”

  1. Awesome query!! Thank you, got my results quickly. I believe there might be an extra space in the last query statement between the words -like and “% When I copied it exactly from your page, it made an extra space. just an FYI – Please copy to Notepad first then paste to your collection.

    SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “%.NET Framework 4%”) command. For some reason

  2. When I paste your query into a membership rule for a collection in Current Branch, I receive a Syntax error.

  3. Syntax error comes from the double quotes on “%.NET Framework 4%”). When rendored and copied from a browser they are not the same double quotes. Simply copy the query, delete the quotes and add them back manually and the query will be accepted.

  4. Very strange.. It worked for me for one application I ran it against but it’s not working for this one:

    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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 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 “%Symantec%”)

    It returns 95% of my servers saying Symantec isn’t installed, when it is.. Any ideas?

    Thanks!

  5. Thanks for sharing the query Josh, as well as the comment about the quotes. Saved me some time today!

Comments are closed.