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

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