KQL query question: Filter out results where condition1, condition2, condition3 all evaluate true

%3CLINGO-SUB%20id%3D%22lingo-sub-1533081%22%20slang%3D%22en-US%22%3EKQL%20query%20question%3A%20Filter%20out%20results%20where%20condition1%2C%20condition2%2C%20condition3%20all%20evaluate%20true%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533081%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%2C%20instead%20I%20need%20it%20to%20only%20exclude%20only%20when%20all%20of%20the%20variables%20evaluate%20true%20for%20a%20specific%20log%20line%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%7Cwhere%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%3CLINGO-SUB%20id%3D%22lingo-sub-1533145%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20query%20question%3A%20Filter%20out%20results%20where%20condition1%2C%20condition2%2C%20condition3%20all%20evaluate%20tru%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533145%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F543693%22%20target%3D%22_blank%22%3E%40browesec%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CBR%20%2F%3EThis%20would%20compare%20three%20conditions%20and%20then%20only%20show%20results%20for%20other%20EventIDs%20(as%20an%20example)%3F%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%0A%7C%20where%20EventID%20!%3D%204688%20and%20EventID%20!%3D8002%20and%20EventID%20!%3D4624%0A%7C%20summarize%20count()%20by%20EventID%0A%7C%20order%20by%20count_%20desc%20%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1533196%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20query%20question%3A%20Filter%20out%20results%20where%20condition1%2C%20condition2%2C%20condition3%20all%20evaluate%20tru%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533196%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20for%20the%20poor%20summary%20of%20what%20I'm%20after...%20it%20has%20proven%20hard%20to%20explain.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%20what%20I%20need%20isn't%20unique%20ID%20exclusion%20like%20in%20your%20example%20but%20something%20more%20like%3A%3C%2FP%3E%3CP%3ESecurityEvent%3CBR%20%2F%3E%7C%20where%20EventID%20%3D%3D%20%224688%22%3C%2FP%3E%3CP%3E%7C%20where%20Computer%20!%3D%20host1%20and%20ProcessName%20!%3D%20example.exe%20and%20AccountName%20!%3D%20Bob%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20with%20the%20above%20syntax%20is%20that%20it%20will%20exclude%20all%20results%20of%20EventID%204688%20from%3A%3C%2FP%3E%3CP%3Ehost1%3C%2FP%3E%3CP%3Eall%20systems%20with%20the%20process%20name%20example.exe%3C%2FP%3E%3CP%3Eall%20processes%20on%20all%20systems%20that%20were%20started%20by%20Bob.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20instead%20want%20to%20see%20is%20all%20SecurityEvent's%20matching%20EventID%204688%20except%20if%20this%20specific%20situation%20occurs%3A%3C%2FP%3E%3CP%3EBob%20created%20the%20process%20example.exe%20on%20host1%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1533311%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20query%20question%3A%20Filter%20out%20results%20where%20condition1%2C%20condition2%2C%20condition3%20all%20evaluate%20tru%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533311%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F543693%22%20target%3D%22_blank%22%3E%40browesec%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20about%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA6WRzUvDQBDF74H8D5PFQwvBJiDSSw5Vi1RrU%25252BLnoSDr%25252BqBBN1t2NvYD%25252F%25252Fg2rU2pQlGc65v3mzczt1Clzd28%25252B4HC%25252Bd4nTcewoHOjJ6WDpSQhkXXvOr3%25252Bw00UCzJ2X7s6S5%25252BiSOyca1DvohJPTtvtSsDMoXitff2cHSWkJKPhe3Sg6knBtxRyhRtgOrRGgXkgNSgYSyahWNl84o4xw6aro5QpC7ciiMc0u77M0vvhaLRjHYkwDv%25252BQ4mvffyfYcH4xvdXCO4MWsOZwY9T8%25252Bb71qYMkrhQutZY2X4C0fMMzwzX2F2jSy7x2htvc4fajvrcEzgVh2CwCAAA%25253D%2Ftimespan%2FP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%0A%7C%20where%20Computer%20%3D%3D%20%22RETAILVM01%22%20or%20Computer%20%3D%3D%20%22JBOX00%22%0A%7C%20where%20EventID%20%3D%3D%204688%0A%7C%20extend%20ComputerList%20%3D%20case(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Computer%20!%3D%20%22RETAILVM01%22%20and%20NewProcessName%20!has%20%22cscript.exe%22%20and%20Account%20!%3D%22WORKGROUP%5C%5CRETAILVM01%24%22%2C1%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Computer%20!%3D%20%22JBOX00%22%20and%20NewProcessName%20!has%20%22cscript.exe%22%20and%20Account%20!%3D%22WORKGROUP%5C%5CJBOX00%24%22%2C1%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2Felse%20zero%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%200)%0A%7C%20where%20ComputerList%20!%3D0%0A%7C%20summarize%20make_set(NewProcessName)%20by%20Computer%2C%20Account%2C%20EventID%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3BChange%26nbsp%3B%3CBR%20%2F%3E%7C%20where%20ComputerList%20!%3D0%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eto%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%7C%20where%20ComputerList%20!%3D!%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20just%20need%20to%20see%20a%20match%2C%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3ENote%3A%20the%20%22%5C%5C%22%20in%20the%20account%20name%20-%20%22%5C%22%20is%20a%20special%20character%20so%20you%20have%20to%20add%20a%20second%20one%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1553361%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20query%20question%3A%20Filter%20out%20results%20where%20condition1%2C%20condition2%2C%20condition3%20all%20evaluate%20tru%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1553361%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F543693%22%20target%3D%22_blank%22%3E%40browesec%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3A%20I%20think%20it%20might%20be%20simpler.%20You%20should%20use%20not%20and%20not%20%22!%22%20as%20the%20not%20unary%20operators.%20%22!%22%20is%20just%20part%20of%20the%20binary%20%22not%20equal%22%20operator.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3CSPAN%3E%7C%20where%20%3CSTRONG%3Enot%3C%2FSTRONG%3E(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%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eshould%20work%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E~%20Ofer%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, 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.

4 Replies

@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 

@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

@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 

@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