KQL query question: Filter out results where condition1, condition2, 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, instead I need it to only exclude only when all of the variables evaluate true for a specific log line]

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 

This would compare three conditions and then only show results for other EventIDs (as an example)? 

SecurityEvent
| where EventID != 4688 and EventID !=8002 and EventID !=4624
| summarize count() by EventID
| order by count_ desc 

[#3]

@CliveWatson 

Sorry for the poor summary of what I'm after... it has proven hard to explain.

Unfortunately what I need isn't unique ID exclusion like in your example but something more like:

SecurityEvent
| where EventID == "4688"

| where Computer != host1 and ProcessName != example.exe and AccountName != Bob

The problem with the above syntax is that it will exclude all results of EventID 4688 from:

host1

all systems with the process name example.exe

all processes on all systems that were started by Bob.

What I instead want to see is all SecurityEvent's matching EventID 4688 except if this specific situation occurs:

Bob created the process example.exe on host1

[#4]

@browesec 

How about Go to Log Analytics and run query

SecurityEvent
| where Computer == "RETAILVM01" or Computer == "JBOX00"
| where EventID == 4688
| extend ComputerList = case(
                            Computer != "RETAILVM01" and NewProcessName !has "cscript.exe" and Account !="WORKGROUP\\RETAILVM01$",1,
                            Computer != "JBOX00" and NewProcessName !has "cscript.exe" and Account !="WORKGROUP\\JBOX00$",1,
                            //else zero
                            0)
| where ComputerList !=0
| summarize make_set(NewProcessName) by Computer, Account, EventID

 Change 
| where ComputerList !=0

to

| where ComputerList !=! 

If you just need to see a match,


Note: the "\\" in the account name - "\" is a special character so you have to add a second one 

[#5]

@browesec , @CliveWatson : I think it might be simpler. You should use not and not "!" as the not unary operators. "!" is just part of the binary "not equal" operator.

| where not(condition1 == "x"  and condition2 == "y" and condition3 == "z") 

should work 

~ Ofer

[#6]

Thanks Ofer, this worked for me!