Trouble building a query off software inventory

[Loushius]

I'm pretty new to building queries and am currently trying to familiarize myself with SQL and the SQL Views to further help run queries.

 

I've been having a hard time trying to build a collection of computers that have one of our inventoried software items on it.

 

Within Inventoried Software, I have several products with different names all containing the word Coupon. Coupon toolbars, programs, etc. These are all security threats, and I have positive software counts for each. I'd like to create a collection (or run a report) of all machines with this software installed so I can remove the software.

 

I've tried different variations of criteria in my query statement without success. Installed software, installed application, etc. each with the product name or publisher name return no results. I'll paste my most recent attempt below:

 

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 inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "Coupon"

 

Any help would be appreciated!

[Loushius]

Ok, so I ended up referencing some CBT Nuggets about creating views again in SQL. It ended up being really easy. I figured I'd post some screenshots of my view and the ending result of the WQL Query in case someone else ends up viewing the post in the future. I ended up getting all the different versions of coupon related software installed on machines. Now I can get it all uninstalled!

 

My view inside SQL: http://imgur.com/mOGGqUI

 

 

My WQL: http://imgur.com/SmyGiv0

 

 

How simple the statement ended up being: http://imgur.com/APkiGWL