Forum Discussion

browesec's avatar
browesec
Copper Contributor
Jul 20, 2020

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

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.

5 Replies

  • Ofer_Shezaf's avatar
    Ofer_Shezaf
    Icon for Microsoft rankMicrosoft
    Jul 29, 2020

    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

  • CliveWatson's avatar
    CliveWatson
    Former Employee
    Jul 20, 2020

    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
    • browesec's avatar
      browesec
      Copper Contributor
      Jul 20, 2020

      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

      • CliveWatson's avatar
        CliveWatson
        Former Employee
        Jul 20, 2020

        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 

Resources