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

[browesec]

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.

[#2]

@browesec 

In the better late than never category. A slight miss on the Boolean logic. Never do a join if you can avoid it. This should work:

Table

| where Event == "12"

| where condition1 != ‘x’ OR condition2 != ‘y’ OR condition 3 != ‘z’

if any of the != conditions are true, then the where is true and the row will be included in the result. 

[#3]

Hi @browesec,

I believe the function you are looking for is https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/not-function.

In your example:

Table

| where Event == "12"

| where not(condition1 == x AND condition2 == y AND condition 3 == z)

If all three conditions evaluate to true, the row is not included in the results. If any of the three conditions evaluates to false, the row will be included in the results.

Please let me know if you have any follow-up questions. I hope this helps and wish you a great day!

Kind regards,

Rutger