[SOLVED] Combine queries (WQL subselect query - Powershell

Combine queries (WQL subselect query - Powershell - SCCM)

Right now i'm using two different queries and compare the resulting objects, however I would much prefer a unique query that does all which is needed as I would love to use it directly in SCCM and not just PowerShell.

(First query creates an object with all computers which HAVE a certain x64 software installed, second object creates a query with all computers which DON'T HAVE a certain x86 software installed)

The two objects are compared and I can get the list I need (which machines are in both objects)

However, how would I combine these two queries so it's all in one ?

As in :

All computers which DO HAVE SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "SOFTWARE1" AND DON'T HAVE SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "SOFTWARE2"

$SiteCode = "x"
$SiteServer = "x"

$query = @"

select *  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 = "SOFTWARE1"
"@

$postes_xx = (Get-WmiObject -namespace root\sms\site_$SiteCode -computer $SiteServer -query $query).SMS_R_SYSTEM.Name


$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_COMPUTER_SYSTEM 
on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 
where SMS_G_System_COMPUTER_SYSTEM.Name 
not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name 
       from  SMS_R_System
       inner join SMS_G_System_COMPUTER_SYSTEM
       on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
       inner join SMS_G_System_ADD_REMOVE_PROGRAMS
       on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
       where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "SOFTWARE2" )

"@


$postes_32x = Get-WmiObject -namespace root\sms\site_$SiteCode -computer $SiteServer -query $query | select -ExpandProperty name

Compare-Object $postes_xx $postes_x32 -IncludeEqual -ExcludeDifferent

Solution

It seems that there is no need to use SMS_G_System_Computer_System class. Here is a simple version of WQL that should meet your requirement.

select SMS_R_System.Name 
from SMS_R_System 
where SMS_R_System.ResourceID in 
(select SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID 
from SMS_G_System_ADD_REMOVE_PROGRAMS_64 
where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "SOFTWARE1") 
and SMS_R_System.ResourceID not in 
(select SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID 
from SMS_G_System_ADD_REMOVE_PROGRAMS 
where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "SOFTWARE2")

Hope my answer could help you and look forward to your feedback.

Best Regards, Ray