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.
This basic SQL will show all computers that do not have a piece of software installed that matches
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%" )
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%'