WQL query that results in an AND in the WHERE clause?-CodePudding

I'm trying to create a query-based collection in SCCM that will target all computers with four specific programs on them. These four programs can also be installed on computers that have nothing to do with the application I'm trying to target, so the collection shouldn't include those. Only PCs with ALL 4 of the programs installed should be included in the collection.

The naïve implementation below doesn't work, but here's the logic of what I want:

SELECT 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
    INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS_64
        ON SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId
WHERE SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "ArcGIS Engine%" AND
      SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "Java 8%" AND
      SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName LIKE "Microsoft SQL Server%" AND
      SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "SQL Server Management Studio"

How do you get a logical AND here?

Note that SCCM's interface requires WQL, not T-SQL for collection queries. Therefore, the COUNT operator is not available (and I believe the HAVING clause works differently).

CodePudding user response：

Not the best way but this will be easy to debug for you. You can join to the same table multiple times, you just need to give it different aliases each time, each of these joins can be used to filter on a particular program:

SELECT S.Name
FROM SMS_R_System s
INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS p1
    On p1.ResourceID = S.ResourceId
INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS p2
    On p2.ResourceID = S.ResourceId
INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS p3
    On p3.ResourceID = S.ResourceId
INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS p4
    On p4.ResourceID = S.ResourceId
WHERE p1.DisplayName LIKE 'ArcGIS Engine%'
AND P2.DisplayName LIKE 'Java 8%'
AND p3.DisplayName LIKE 'Microsoft SQL Server%'
AND p4.DisplayName = 'SQL Server Management Studio'

CodePudding user response：

You could use 4 EXISTS clauses to look up each individual program. That essentially the same solution as the one that Andrew posted.

Alternatively, you could use something like this:

SELECT 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 "ArcGIS Engine%" OR
      SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "Java 8%" OR
      SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "Microsoft SQL Server%" OR
      SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "SQL Server Management Studio"
GROUP BY SMS_R_SYSTEM.Name
HAVING COUNT(DISTINCT SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName) = 4

CodePudding user response：

 SELECT 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 "ArcGIS Engine%" OR
          SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "Java 8%" OR
          SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "Microsoft SQL Server%" OR
          SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "SQL Server Management Studio" 
GROUP BY SMS_R_System.ResourceId 
HAVING COUNT(DISTINCT SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName) >= 4;

Here you

Select all resources and programs which match your criteria.
Then you group by resourse_id and count how many programs (which meet your criteria) were found per each resource_id.
Check that number. If the number of the distinct programs will be less than 4 - it is the case we are not interested in. If equal to 4 or even greater than 4 (in case your user has ArcGIS Engine v1 and ArcGIS Engine v2 isntalled, for example) - this is the case we are looking for.