Query to Detect if Software Is Installed
I'm using the following combination of queries to detect pieces of software installed on some of our systems and systems without that same software. By combining these two queries I'm able to create collection groups to deploy the software to the servers which don't have it installed.
Query1 (Detect DPM Agent) - This query detects the presence of the Data Protection Manager (DPM) Agent. The result simply lists the System Resource, Resource ID. The criteria uses a simple value of Add/Remove Programs, Display Name OR Add/Remove Programs 64, Display Name which is equal to. (this covers both bases)
select distinct 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 inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft System Center DPM Protection Agent" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Microsoft System Center DPM Protection Agent")
Query 2 (Detect No DPM Agent) - This query similarly to Query 1, returns only the System Resource, Resource ID. The criteria in this case is a SubSelected Values where System Resource, Resource ID is not in Subselect Query 1. This will then return the Resource ID's for systems which do not have the Software installed. Which allows us to build our Collection Group and install it!
select SMS_R_System.ResourceId from SMS_R_System where SMS_R_System.ResourceId not in (select distinct SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft System Center DPM Protection Agent" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Microsoft System Center DPM Protection Agent")
