Subselect syntax in Config Mgr

I'm mostly posting this as a reminder to myself, but someone may find it useful.  This is the subselect syntax for ConfigMgr queries, so you can do a query along the lines of "Show me all the machines that meet criteria X except the machines that meet criteria Y".  My SQL skills are weak, so this is my permanent record of this.

select SMS_R_System.Name from  SMS_R_System where SMS_R_System.SystemGroupName = "Domain\\GroupName" and SMS_R_System.Name not in (select SMS_R_System.Name from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "ApplicationX")

 

This particular subselect looks for machines that are members of a particular AD System Group called "Domain\GroupName" (note that you need two backslashes in the query to escape the special character) that don't have an application called "ApplicationX" present in Add/Remove Programs.  If you're resourceful you'll be able to modify this to do other stuff as well.

 

Thanks Jaimie!

Comments

• Anonymous
  April 30, 2009
  Love your work, Stu.  This also works with identifying the GUID of the installed application, as often the display names are the same across versions.