Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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%"
share|improve this answer
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? – Matthew Halliday Jul 5 '12 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%'
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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