Wednesday, February 09, 2011

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

7 comments :

  1. Hey man, thanks for your post. This has really helped me. But I think I might have an issue in that not all of the machines are showing up in my collection that should be. Because the same machines show up using either method 1 or method 2 I am 99% positive that the queries are correct but I think I may have an issue elsewhere.

    So just to be certain the WQL above targets information acquired from the software inventory task? Or is is somewhere else?

    ReplyDelete
  2. Thanks, you are probably missing records because the software inventory hasn't uploaded to SCCM from the client yet or the data software inventory cycle hasn't run.

    Check how frequently your collecting your software inventory data from your SCCM console.

    ReplyDelete
  3. What limits this to workstations?

    What I need to do is create a collection to deploy FEP, so I need to show all workstations running a desktop OS that dont have FEP installed

    ReplyDelete
  4. I would start by creating a collection of workstations for desktop OS's I.e clients that are Desktop OS, valid clients, not expired or obsolete. Then create a second collection limited to the first collection that looks for FEP not installed.

    This would get you what you are looking for.

    ReplyDelete
  5. I'm trying to used this but can prove that there are machines in the "is installed" group that do not actually have the software installed. Any suggestions?

    ReplyDelete
  6. BBA you'll have to re-type your question, I'm not really sure what you are trying to ask.

    ReplyDelete
  7. Hi Joe,

    Thanks a lot for a great post.

    Just wonder, how would you modify it to include both SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName and SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName?

    Thanks!

    ReplyDelete