I would like to use a query that will query my systems that have a particular piece of software (Flash), but I do not want it to include servers that are in a particular group.

Explanation: I have about 20 servers with Flash installed that require it.  About half of them I can update regularly, the other half I can't.  So, I have an AD group for each - the ones I can patch, and the ones I can't.  I want the query to look for systems with "Adobe Flash" in the display name, but exclude the servers in both of these groups.  That way I can create a collection of computers that have Flash installed where it shouldn't be and can keep an eye on where it is being installed.

The query looks like this in the criteria tab;

Add/Remove Programs.Display Name is like "%Adobe Flash%"
and
System Resource.System Group Name is not equal to "LAB\FlashPatchGroup"

The query language looks like this;

select distinct SMS_R_System.Name, SMS_R_System.description, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version 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 Flash%" and SMS_R_System.SystemGroupName != "LAB\\FlashPatchGroup"

The problem I am having is that when I run the query, it is pulling everything with Flash, including the ones in the AD group.  Anyone see a problem with the way I'm doing this?

Hi,

As the computers probably are members of other Groups you must use a sub-select Query instead of the "not equal to" statement, it is explained here: http://scug.be/sccm/2010/08/19/configmgr-query-not-installed-software-amp-subselect-query-s/ the blog post covers Installed software but the solution is the same here, create a Query that queries all computers that are members of the AD Group then create a subselect to select all computers that are not included in the first Query.

Regards,
Jrgen

Hi Jorgen,

   Thank you for that - very helpful because I actually knew nothing about sub-select queries.  So now the overall logic makes sense, but unfortunately the only examples I've been able to find don't cover exactly what I want to do.

The overall examples I have found work like this;
- Create a query to find computers with a particular application installed
- Create a subselect query that says find users that are NOT subject to the preceeding query

What I need to do, however, is this;
- A query finds computers with a particular piece of software installed
- It compares the computer to 2 Active Directory groups I have created.
- The query produces all computers with that particular piece of software that is NOT in the AD groups I have created.  This way I can easily identify servers that have Flash installed that shouldn't.

Any ideas?  I have been trying to follow the subselect queries to do something similar but I just cannot make it work.  I've tried to create a subselect query that says "Add/Remove Programs Display Name contains "%Adobe Flash%" but is not in (subselect query)" and it just doesn't like it.

Regards

Juice

Sorry, one more reply.  I ended up with 2 queries - 1 to find servers with FLash, another to find servers in the FLash Patching AD groups.  So I ended up creating a query with 2 subselect queries.  THis is how it looks (and it doesn't work, I might add);

select distinct SMS_R_System.Name from  SMS_R_System where SMS_R_System.Name in (select distinct SMS_R_System.Name 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 Flash%") and SMS_R_System.Name not in (select distinct SMS_R_System.ResourceId, SMS_R_System.Name, SMS_R_System.description from  SMS_R_System where SMS_R_System.SystemGroupName = "LAB\\Flash_AllUpdates" or SMS_R_System.SystemGroupName = "LAB\\_Flash_NoUpdates")

Hi,

as your Query for the installed application is for computer with Flash installed you don't have to use a subselect Query for the Add remove programs entry just for the AD Group Query, then it should work just fine.

Regards,
Jrgen

Sorry Jorgen, I don't follow.  I think I know what you mean regarding add/remove since we're looking for a server WITH Flash and not one without - but I don't follow on the subselect query for AD group.

This is my query with what I understood from what you said;

select distinct SMS_R_System.Name 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 Flash%" and SMS_R_System.Name not in (select distinct SMS_R_System.ResourceId, SMS_R_System.Name, SMS_R_System.description from  SMS_R_System where SMS_R_System.SystemGroupName = "LAB\\Flash_AllUpdates" or SMS_R_System.SystemGroupName = "LAB\\Flash_NoUpdates")

It returned no results.

Then I created the same query with no subselect query, that strictly said "Servers with Flash AND Not in group LAB\\FLASH_NoUpdates" to no avail.

I have had the same issue and my sub-select query was pulling in too many fields.

You only want the System Name to be returned from your Sub-select, so you don't need all the other values.

Try this query:

select distinct SMS_R_System.Name 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 Flash%" and SMS_R_System.Name not in (select distinct SMS_R_System.Name from  SMS_R_System where SMS_R_System.SystemGroupName = "LAB\\Flash_AllUpdates" or SMS_R_System.SystemGroupName = "LAB\\Flash_NoUpdates")

You should be able to test the sub-select query first to make sure that it returns the correct set of machine names.

• Edited by Grumpy_Monkey 3 hours 6 minutes ago