WQL Collection query using LIKE and NOT LIKE operators

Question

WQL Collection query using LIKE and NOT LIKE operators

Joshua Wyman 11

I am trying to figure out something to utilize the AD Security Group membership collected by MECM for the systems in our environment.

Long in short I'm trying to create query-based collections that include/exclude systems based on what Security groups the computer accounts belong to.

I'm targeting the SystemGroupName field which contains a comma-separated list of the AD group membership of my devices.

I essentially need to be able to do the following:
(Sample string: MyDom\Group1, MyDom\Group2, MyDom\Group3, MyDom\Group4)

SELECT <my field values> FROM SMS_R_System WHERE (SMS_R_System.SystemGroupName LIKE "%Group1%") AND NOT (SMS_R_System.SystemGroupName LIKE "%Group2%" AND SMS_R_System.SystemGroupName LIKE "%Group4%")

I've tried using equal to, not equal to, and also varying the parenthesis. I can get results, but essentially it's only the result of the first LIKE statement. It seems it's not taking the results of that match, and then pruning it based on the NOT LIKE portion of things.

Viorel 121.3K Reputation points

2023-08-03T07:31:47.9+00:00

Maybe the last AND must be replaced with OR.

3 answers

Your answer

Viorel 121.3K Reputation points

2023-08-03T07:31:47.9+00:00

Maybe the last AND must be replaced with OR.

Answer 1

Ok, so I found a way to make this work which seems excessive. I worked it out with a DB admin to try and figure this out because it was not working when it should have. Maybe it's just a MECM quirk regarding it.

Doing a one-by-one subselect of my criteria worked. So if I had a field that contained four groups, and multiple computers have varying combinations of those values, and I wanted to select certain criteria I had to do this:

SELECT <criteria> FROM SMS_R_System WHERE SecurityGroupName LIKE %Group1%

AND
SMS_R_Sytstem.ResourceID IN (SELECT Select ResourceID FROM SMS_RSystem WHERE SecurityGroupName LIKE %Group3%)

AND
SMS_R_Sytstem.ResourceID NOT IN (SELECT Select ResourceID FROM SMS_RSystem WHERE SecurityGroupName LIKE %Group2%)

AND
SMS_R_Sytstem.ResourceID NOT IN (SELECT Select ResourceID FROM SMS_RSystem WHERE SecurityGroupName LIKE %Group3%)

I'm sure there is a way to make this work without having to iteratively filter the returned data but this at least got me the end result without a long duration for my query. As well as me being able to shift the group names in the structure, and flipping the NOT IN to IN to include/disinclude items for other collections

AllenLiu-MSFT 49,216 Reputation points Microsoft External Staff

2023-08-07T06:41:54.8366667+00:00

We're glad that you found a solution to get the results.

Answer 2

Erland Sommarskog 120.2K MVP

It's not clear what you want to achieve. But the above query means "Give me all rows which contains Group1 unless they also contain Group2 and Group4.

That is, the above will hit

Group1, Group2, Group3
Group1, Group3, Group4
Group1, Group5, Group7

but not

Group1, Group2, Group3, Group4, Group5

Joshua Wyman 11 Reputation points

2023-08-02T22:22:38.99+00:00

So my goal is to check each returned row, for certain conditions in that row of data returned.

The sample I listed above was a single comma-separated value stored in the row

The expected behavior of what I put about is that I'd all items returned that only contained Group1, but if that row also contained Group2 or Group4, then it'd be dropped, but this is not happening.

The current result I have is returning each item that contains Group1, even if it also has Group2 and/or Group4 in it, rather than excluding them.

Erland Sommarskog 120.2K MVP

Then you need to change the AND inside the paren to OR:

SELECT <my field values> 
FROM SMS_R_System 
WHERE  SMS_R_System.SystemGroupName LIKE "%Group1%" 
  AND  NOT (SMS_R_System.SystemGroupName LIKE "%Group2%" OR 
            SMS_R_System.SystemGroupName LIKE "%Group4%")

Joshua Wyman 11 Reputation points

2023-08-03T14:06:15.76+00:00

I gave that a shot and am still in the same situation where I'm getting the results that passed from the first WHERE LIKE check, but it's not pruning out the items that should be excluded
Erland Sommarskog 120.2K Reputation points MVP

2023-08-03T21:07:22.37+00:00
If I understood you correctly, of the sample data below, you only want the row with id = 3, and indeed my query is producing this result:

CREATE TABLE #temp (id int, str nvarchar(MAX)) INSERT #temp (id, str) VALUES(1, 'Group1, Group2, Group3'), (2, 'Group1, Group3, Group4'), (3, 'Group1, Group5, Group7'), (4, 'Group1, Group2, Group3, Group4, Group5') SELECT id, str FROM #temp WHERE str LIKE '%Group1%' AND NOT (str LIKE '%Group2%' OR str LIKE '%Group4%') go DROP TABLE #temp

I noted one funny thing when I tested this query. In your post, the string literals were enclosed in double quotes, but the double quotes in SQL are normally used to enclose identifiers (when they contain special characters or coincide with reserved keywords). But I assume that is an accident that happened when you entered the query into the post, and your actual code has single quotes.
Garth 5,801 Reputation points

2023-08-03T21:13:26.19+00:00

The original query posted is not Sql but wql. They are similar but not the same. E.g. wql is a subset or Sql.
Erland Sommarskog 120.2K Reputation points MVP

2023-08-03T21:20:26.64+00:00

Yeah, but it's tagged with SQL Server, so we people answering from this, will gladly assume that it's an SQL Server query. Hey, we may not even know what WQL is. (I certainly do not.) Anyway, the WQL thing may explain the double quotes, but the logic for AND, OR and NOT should be the same in any environment.

Answer 3

AllenLiu-MSFT 49,216 Microsoft External Staff

Hi, @Joshua Wyman

Thank you for posting in Microsoft Q&A forum.

You may try this sentence:

SELECT <my field values> FROM SMS_R_System WHERE (SMS_R_System.SystemGroupName LIKE "%Group1%") AND (SMS_R_System.SystemGroupName NOT LIKE "%Group2%") AND (SMS_R_System.SystemGroupName NOT LIKE "%Group4%")

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Add comment".

Joshua Wyman 11 Reputation points

2023-08-03T14:07:26.6733333+00:00

Unfortunately this did not work. I'm getting the results that passed from the first WHERE LIKE check, but it's not pruning out the items that should be excluded from the NOT LIKE.

Share via

WQL Collection query using LIKE and NOT LIKE operators

3 answers

Your answer