I have two software (A and B) installed on computers so that some have A, some have B and some both A+B installed.

How to make device collection query so that I get all 3 collection shows correct Computers in respective collection.

My problem is that I have not managed to get it working so that A+B compination computers are not included in the A or B collection. I have tried to make A query so that it check the installed software names "A and not B".

You will need to use a sub-select query. These blog will help you. http://www.enhansoft.com/?s=subselect

Sorry, wrong forum as we have sccm 2012 R2 and seems that something changed in the query as the examples does not work but gives syntax errors when trying to test the samples done in the blog.

Sorry, wrong forum as we have sccm 2012 R2 and seems that something changed in the query as the examples does not work but gives syntax errors when trying to test the samples done in the blog.

That is not true. There was nothing that has changed between CM07 and CM12, with respect to queries.

Anyway i got the syntax error when trying those examble in blog. I found other thread where I found similar type of examples that do work when I change the application names, still not finding how to get A+B collection query to work correctly, the blog did not help there.

Anyway i got the syntax error when trying those examble in blog. I found other thread where I found similar type of examples that do work when I change the application names, still not finding how to get A+B collection query to work correctly, the blog did not help there.

Without knowing the error you got or seeing the exact query you used, there is not way for anyone to help you.

As I wrote I found other example which do work and therefore that part is ok.

For query to show Computers with A and B installed  I was trying following but that gives 0 as a result. That query is done for all Computers. I did found blog that shows this type of example but for me it does not give correct result.

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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "A" and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "B"

This query will always give you 0 results. Go back and read my blogs on subslect queries. Also keep in mind that the query above will only show you x86 software titles too.

Start with these blogs.

http://www.enhansoft.com/blog/the-subselect-query

http://www.enhansoft.com/blog/subselect-query-for-sccm-queries-or-collections-wql

What is your positive query for App "A"?

What is your positive query for App "B"?

select SMS_R_System.Name, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserName, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName 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 "A") or(SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "B")

Instead of And i would use or and it works fine and gives output for both else i would use one display name for a query and another for the different one.

So I shoud have query to get software A and from those to get the ones that have B installed?

I just can figure out how to create such query in SCCM, that is the main problem.

Hello,

This is really simplified ...

To get the computers that have application 'A' installed, but not application 'B'

SELECT sys.Name
FROM System sys
    INNER JOIN AddRemoveProgram arp
        ON arp.Id = sys.Id
WHERE arp.Name = 'A'
    AND sys.Id NOT IN (
        SELECT Id
        FROM AddRemoveProgram
        WHERE Name = 'B'
    )

Of course, you will have to use the correct WMI names to get it to work in an actual environment as well as remove all excessive the spaces and breaks. But I am sure you get the idea.

The above query would give you the both outputs ie for A and B  applications i don't know what you mean here but the above ones would work for your requirement.

If you want to know how to write one,please do a training on it.

Keep in mind that the above query will only find x86 software titles.

Thanks but I got these working already based on Garth's answere above.

The one I have not managed to get working is query to show both (a AND b) software installed, they both have to be included not either one.