Jump to content


Sign in to follow this  
Barty

Query help!

Recommended Posts

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?

Share this post


Link to post
Share on other sites


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.

Share this post


Link to post
Share on other sites

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...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...