Query help!

[Barty]

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?

[Eswar Koneti]

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.