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 variab...
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