Create a report in SCCM 2007 the shows server without a piece of software

Question

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.

Answer 1

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%"
)

Answer 2

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%'