Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
add comment

2 Answers

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
WHERE ResourceID NOT IN
(
  SELECT ResourceID
  FROM v_GS_INSTALLED_SOFTWARE
  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
FROM SMS_R_System
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
add comment

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
add comment

Your Answer

 
discard

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.