Today I created this custom query to help look for devices which do not have a specified software installed.  I create the query to prompt for the collection and then then enter a value for Software.ARPDisplay Name.  Here is the query:


select distinct SMS_R_System.Name, SMS_R_System.OperatingSystemNameandVersion, SMS_G_System_CH_ClientSummary.ClientActiveStatus, SMS_R_System.IPAddresses, SMS_R_System.LastLogonUserName from  SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId

where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId

where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like ##PRM:SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName##)

order by SMS_R_System.Name


Let me know if you find this useful.