SCCM - Query/Collection by MSI Installed

Most of the time, when adding an SCCM Collection based on installed software, I’ll simply use the Add/Remove Programs Attribute Class.  However, in a recent case, the installed software doesn’t show up in Add/Remove Programs, but it is an MSI installer.  In this particular instance, I wanted to remove the Java Updater.

Since approximately 1.6.18 / 6.0 update 18, Oracle (formerly Sun) has included Java Auto Updater as a separate package that is automatically installed with the JRE [ref].  This makes it really easy to remove:

msiexec /qn /x {4A03706F-666A-4037-7777-5F2748764D10}

Now, I just needed to figure out how to target all computers with that package code installed.  After a lot of Googling, and turning up nothing useful, I decided to dig into SCCM myself.  Here’s how to set-up the Criterion Properties, for a Collection, to look for machines with Java Updater installed:

If you’d rather just have the SQL, here’s what my system generated:

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 UPPER(SMS_G_System_INSTALLED_SOFTWARE.ProductCode) = "{4A03706F-666A-4037-7777-5F2748764D10}"

Hope this helps others out there. Cheers!