SCCM collection Software not installed. Two methods same outcome.

I needed to quickly create a collection based on AppX not installed on workstations.

There are 2 ways achieve this;

1. Create a collection of PC's that have AppX installed then create a second collection excluding the installed.

OR

2. Do all the above in a single collection.

Both are acceptable answers and will lead you to same outcome. Step 1 will allow you to quickly report how many of 'AppX' is installed which might be handy to some.

I'll show you how to do both methods.

Method 1: Create 2 collections.

--Collection 1 determine the workstations that have APPX installed.
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
where SMS_R_System.ResourceId
in
(select SMS_R_System.ResourceID
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_G_System_ADD_REMOVE_PROGRAMS.DisplayName
LIKE 'APPX%')


--Collection 2 shows clients not in first collection.
-- Also client must = 1
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
where SMS_R_System.ResourceId not in
(select ResourceId from SMS_CM_RES_COLL_SSDXXXXX)
and SMS_R_System.Client = 1


Method 2: Create a single collection based query.
Base

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 like "%AppX%")