Skip to content

Query help!

Featured Replies

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?

  • 2 weeks later...

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.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

Account

Navigation

Search

Search

Configure browser push notifications

Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.