Barty Posted January 21, 2012 Report post Posted January 21, 2012 Hello! I want to have an uninstall collection where the query check if computers is NOT a member of a group but DOES have the application installed. The problem is i cant get it to work with the SMS_G_System_ADD_REMOVE_PROGRAMS_64 table only regular SMS_G_System_ADD_REMOVE_PROGRAMS. I have a computer that is now a member of the group but does have the program installed. This query works: 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_R_System.SystemGroupName != "DOMAIN\\GROUP" and SMS_G_System_ADD_REMOVE_PROGRAMS.ProdID = "{C0B728CE-BF48-48C2-A19C-01563CCEDD9B}" But if i want to query the 64-bit aswell i does not work. 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where SMS_R_System.SystemGroupName != "DOMAIN\\GROUP" and (SMS_G_System_ADD_REMOVE_PROGRAMS.ProdID = "{C0B728CE-BF48-48C2-A19C-01563CCEDD9B}" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.ProdID = "{C0B728CE-BF48-48C2-A19C-01563CCEDD9B}") Any Ideas why? Quote Share this post Link to post Share on other sites More sharing options...
Eswar Koneti Posted January 30, 2012 Report post Posted January 30, 2012 sometimes != query may not give perfect results rather going this way ,i would do other way. 1.Create collection that list machines who are member of AD sec group 2.create another collection named uninstall that pulls all the computers with application installed but resource ID not in Above collection.This should give you 100% correct result. Collection Query : 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where (SMS_G_System_ADD_REMOVE_PROGRAMS.ProdID = "{AC76BA86-7AD7-1053-7B44-A90000000001}" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.ProdID = "{AC76BA86-7AD7-1053-7B44-A90000000001}") and SMS_R_System.ResourceId not in (select ResourceID from SMS_CM_RES_COLL_CollID) change the collID and product ID here. Quote Share this post Link to post Share on other sites More sharing options...