Does anyone have a query that shows a list of servers WITHOUT a selected piece of software? i.e. SCOM agent, anti-virus, backup agent.

2 Answers 2


This basic SQL will show all computers that do not have a piece of software installed that matches forefront.

SELECT ResourceID, Name0, SMS_Unique_Identifier0
FROM v_R_System
  SELECT ResourceID
  WHERE ProductName0 LIKE '%forefront%'

You can replace forefront with the name of the software you are interested in.

The idea is to craft a query that will give you all computers that do have the software installed, then use NOT IN to turn it around and get computers that don't have the software installed.

Here it is again, but in WQL for a Query:

SELECT SMS_R_System.Name
WHERE SMS_R_System.ResourceId NOT IN
  SELECT SMS_G_System_Installed_Software.ResourceId
  FROM SMS_G_System_Installed_Software
  WHERE SMS_G_System_Installed_Software.ProductName LIKE "%forefront%"
  • The query seemed to work for the most part, however in the collection there are a lot of servers that do have Sophos (Our AV). When running the Products on a specific computer report, under product name, there are entries LIKE "%Sophos%". Ideas? Jul 5, 2012 at 22:13

I found using ARP more accurate:

    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 where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "%<Application Name>%"
    and SMS_R_System.Client = 1 
    and SMS_R_SYSTEM.OperatingSystemNameAndVersion like 'Microsoft Windows NT%Server%'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.