KQL query: except where condition1, condition2, and condition3 all evaluate true

%3CLINGO-SUB%20id%3D%22lingo-sub-1533050%22%20slang%3D%22en-US%22%3EKQL%20query%3A%20except%20where%20condition1%2C%20condition2%2C%20and%20condition3%20all%20evaluate%20true%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533050%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sentinel%20friends%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20googled%20and%20read%20through%20many%20guides%20and%20can't%20find%20an%20easy%20way%20to%20perform%20a%20multi-variable%20exclusion%20statement.%20I%20need%20to%20be%20able%20to%20exclude%20a%20result%20if%20multiple%20variables%20ALL%20evaluate%20true.%20The%20pseudo%20logic%20I'm%20looking%20to%20apply%20is%20something%20like%3A%3C%2FP%3E%3CP%3ETable%3C%2FP%3E%3CP%3E%7C%20where%20Event%20%3D%3D%20%2212%22%3C%2FP%3E%3CP%3E(pseudo%20code)%20%7C%20except%20where%20(condition1%20%3D%3D%20x%20AND%20condition2%20%3D%3D%20y%20AND%20condition%203%20%3D%3D%20z)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20things%20like%3A%3C%2FP%3E%3CP%3E1)%20%7C%20!where%20condition1%20%3D%3D%20%22x%22%26nbsp%3B%20and%20condition2%20%3D%3D%20%22y%22%20and%20condition3%20%3D%3D%20%22z%22%20%5Bthis%20doesn't%20work%5D%3C%2FP%3E%3CP%3E2)%20%7C%20where%20!(condition1%20%3D%3D%20%22x%22%26nbsp%3B%20and%20condition2%20%3D%3D%20%22y%22%20and%20condition3%20%3D%3D%20%22z%22)%20%5Bthis%20doesn't%20work%5D%3C%2FP%3E%3CP%3E3)%20%7C%20where%20condition1%20!%3D%20%22x%22%26nbsp%3B%20and%20!condition2%20!%3D%20%22y%22%26nbsp%3Band%20condition3%20%3D%3D%20%22z%22%20%5Bthe%20logic%20here%20evaluates%20all%20conditions%20separately%20where%20I%20need%20it%20to%20function%20where%20all%20of%20them%20to%20evaluate%20true%20for%20the%20specific%20log%20line%20to%20be%20excluded%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20way%20I%20could%20figure%20out%20how%20to%20do%20this%20was%20to%20do%202%20queries%20then%20do%20a%20left%20antijoin%20of%20the%20resulting%20datasets%2C%20but%20it's%20a%20big%20and%20messy%20query.%20I'm%20hoping%20that%20there's%20a%20simpler%20method%20that%20I'm%20missing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEx%3A%3C%2FP%3E%3CP%3ETable%3C%2FP%3E%3CP%3E%7C%20where%20Event%20%3D%3D%20%2212%22%3C%2FP%3E%3CP%3E%7C%20join%20kind%3Dleftanti%20(%3C%2FP%3E%3CP%3ETable%3C%2FP%3E%3CP%3E%7C%20where%20Event%20%3D%3D%20%2212%22%3C%2FP%3E%3CP%3E%7C%20where%20condition1%20%3D%3D%20%22x%22%26nbsp%3B%20and%20condition2%20%3D%3D%20%22y%22%20and%20condition3%20%3D%3D%20%22z%22)%20on%20KEY%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20I%20did%20find%20materialize%20so%20at%20least%20I'm%20not%20querying%20the%20dataset%20twice.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi Sentinel friends,

 

I've googled and read through many guides and can't find an easy way to perform a multi-variable exclusion statement. I need to be able to exclude a result if multiple variables ALL evaluate true. The pseudo logic I'm looking to apply is something like:

Table

| where Event == "12"

(pseudo code) | except where (condition1 == x AND condition2 == y AND condition 3 == z)

 

I tried things like:

1) | !where condition1 == "x"  and condition2 == "y" and condition3 == "z" [this doesn't work]

2) | where !(condition1 == "x"  and condition2 == "y" and condition3 == "z") [this doesn't work]

3) | where condition1 != "x"  and !condition2 != "y" and condition3 == "z" [the logic here evaluates all conditions separately where I need it to function where all of them to evaluate true for the specific log line to be excluded]

 

The only way I could figure out how to do this was to do 2 queries then do a left antijoin of the resulting datasets, but it's a big and messy query. I'm hoping that there's a simpler method that I'm missing.

 

Ex:

Table

| where Event == "12"

| join kind=leftanti (

Table

| where Event == "12"

| where condition1 == "x"  and condition2 == "y" and condition3 == "z") on KEY

 

Note: I did find materialize so at least I'm not querying the dataset twice.

0 Replies