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%”)
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
When I paste your query into a membership rule for a collection in Current Branch, I receive a Syntax error.
Check out the quotes… you may need to re-type those.
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.
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!
Did you find a solution?
May be something to do with 32/64 bit software
Thanks for sharing the query Josh, as well as the comment about the quotes. Saved me some time today!