Configmgr Report - Computers that don't have a specific software installed

Thought i do a short writeup on how to use the mystical sub select query to find computers in a collection that don't have a specific software installed.

Software - Computers missing software.rdl

Download the RDL and change the "Datasource1"  to suit your environment.

The report uses a simple search filter to limit the number of software titles in the "Select Software" list.

Finding computers with some software installed is easy ,but people often get it wrong when they try to flip the query to find computers without the software.

The query to find computers with a specific software is:

SELECT ResourceID FROM   v_add_remove_programs 
WHERE  displayname0 LIKE '%Java Update 67%'

When people try to change the query to look for computers without some software they try to change the "Like" to "Not Like and expect it to work. The query is valid and will output a list of computers ,but the result will basically be all computers....
To get this to work we need to use a sub-select query.
Subselect querys are not that hard. you just wrap the query to find all computers with Java installed in parentheses and use the where not in clause.

In it's simplest form the sub select query looks like this ,notice the part in the parentheses is the same as above: 

SELECT ResourceID,Name0 FROM v_r_system 
WHERE ResourceID NOT IN
(

    SELECT ResourceID FROM   v_add_remove_programs 

    WHERE  displayname0 LIKE '%Java Update 67%'

)

Main Query for the Report:

SELECT v_r_system.name0, 
       .v_r_system.user_name0, 
       v_gs_operating_system.caption0 
FROM   v_r_system 
       JOIN v_gs_operating_system 
         ON v_r_system.resourceid = v_gs_operating_system.resourceid 
WHERE  v_r_system.resourceid NOT IN (SELECT resourceid 
                                     FROM   v_add_remove_programs 
                                     WHERE  displayname0 LIKE @Software) 
       AND v_r_system.resourceid IN (SELECT resourceid 
                                     FROM   dbo.v_fullcollectionmembership 
                                     WHERE 
           dbo.v_fullcollectionmembership.collectionid 
           = 
           @CollectionID)