Many people have blogged about this – how to find machines that don’t have the most recent version of a package installed.
select SMS_R_System.Name,SMS_R_System.LastLogonUserName from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Client = 1 and SMS_G_System_SYSTEM.SystemRole = "Workstation" and SMS_G_System_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_R_System.Client = 1 and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Mozilla Firefox%")
select SMS_R_System.Name, SMS_R_System.LastLogonUserName, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version 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_R_System.Client = 1 and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Mozilla Firefox%" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version = "3.6.3 (en-US)" order by SMS_R_System.Name
Finally we write a query to show machines that aren’t in the query above
select SMS_R_System.Name, SMS_R_System.LastLogonUserName, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version 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_R_System.Client = 1 and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Mozilla Firefox%" 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_R_System.Client = 1 and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Mozilla Firefox%" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version = "3.6.3 (en-US)") order by SMS_R_System.Name
Import the First and Third queries into a collection and we have a collection that shows machines that need the updated package (including machines that don’t have any version of the package installed.)