subselect query with installed application

Hi,

I am trying to create a subselect query for test purpose.

select distinct SMS_R_System.Name from  SMS_R_System 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_64.DisplayName not in (select SMS_R_System.Name from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Software_x" and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows 8.1 Entreprise") order by SMS_R_System.Name

My problem is that query did not excluse computers who already have Software_x.

I create a query with computers who have Software_x and they are appearing correctly but the second query who don't have software_x is not excluding these computers.

Any idea?

Thanks,

April 29th, 2015 11:38am

Garth has a blog series on this topic.  Check it out for guidance: http://www.enhansoft.com/blog/subselect-query-for-sccm-queries-or-collections-wql

Jeff

Free Windows Admin Tool Kit Click here and download it now
April 29th, 2015 11:45am

"select distinct SMS_R_System.Name from  SMS_R_System 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_64.DisplayName not in (select SMS_R_System.Name ..."

You are trying to exclude Names (SMS_R_System) from DisplayNames (SMS_G_System_ADD_REMOVE_PROGRAM_64). That does not work obviously. 

Untested: 

"select distinct SMS_R_System.Name from  SMS_R_System 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.Name not in (select SMS_R_System.Name ..."

April 29th, 2015 11:54am

Hi,

I am not sure to following you.

You are trying to exclude Names (SMS_R_System) from DisplayNames (SMS_G_System_ADD_REMOVE_PROGRAM_64). That does not work obviously. 


Now it is working. I was trying to exclude Display names instead of computer names!!

The first query (Computer who have Software_x):

This query is returning 2 computername.

select SMS_R_System.Name 
from  

SMS_R_System 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_64.DisplayName = "Software_x"

From the first one, the second one will return who don't have software_x.

select distinct SMS_R_System.Name 
from  

SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Name 

not in (select SMS_R_System.Name from  SMS_R_System 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_64.DisplayName = "Software_x") 

and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows 8.1 Entreprise"

order by SMS_R_System.Name

Thanks!

Franois


  • Edited by FRacine 13 hours 37 minutes ago
Free Windows Admin Tool Kit Click here and download it now
April 29th, 2015 2:08pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics