SCCM collection Subselected Queries

Have seen lot of questions on how to get list of computers that do not have xxxxxx .This xxxx could be of anything like softwares,file names anything that do not have.

In this post,I will go through step by step procedure how to make it simple.

Step 1: To get list of computers that do not have xxxx,create a collection query that has xxxx. I think this is pretty much easy how to do it using criteria.

Create new collection and edit the query .

Click on Criteria Tab , click Yellow Burst

In the Criterion properties page, click on select

select attribute class and attribute which you want ( here i go with add and remove programs)

select the display name which you like xxxxx .I go with Adobe Acrobat .Why i used % is ,it list all computers that contains word like Adobe Acrobat.

Now Click on Show query Language to get the WQL code:

Here is the code we have got that list all computers with particular software installed :

select *  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 "%Adobe Acrobat%"

so cut the query and paste it in notepad we need this again.You will see blank query now.

Step 2: In this step,we will create collection with subselected criteria to get what we need .

Click on the Yellow Burst once again and select criterian type as "Subselected Values" and click on select

and select the operator as not in and past the query which you copied earlier into it.

query which we created earlier is :

select *  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 "%Adobe Acrobat%"

Replace * with SMS_R_System.ResourceID in your qeury and click on Ok.

You can also filter the above collection to look only for windows 7 computer i.e OS version 6.1 etc .