kqlsentinel

(Microsoft Sentinel - KQL) How can I use iif() or another statement to return a specific subset of results from a filter-out

Let's assume I am using Microsoft Sentinel to monitor a Windows Computer.

That computer is producing a number of Windows Alerts that all share the same general form e.g

Windows - Account Modification(Locked)(user: #)

Windows - Account Modification(Unlocked)(user: #)

Windows - Account Modification(Password Change)(user: #)

Windows - Policy Change( #)

...etc

And I want to create a query that filters out all windows events except the Policy Change. Could you help me with the syntax of the Query using iff() or any other similar command?

The way I had been doing it so far was to include EVERYTHING BUT the one I wanted.

so something like

My_example_table

| where not ( EventName startswith "Windows - Account Modification" or EventName startswith "Windows - Computer Account" or EventName startswith "Windows - Folder Action" )

which is both impractical as new alerts are added and inefficient as far as resources are concerned.

Again i was wondering if there is a way to filter-out things with

not ( EventName startswith "Windows" )

but include results with

EventName startswith "Windows - Policy Change"

or something of the sort

Thank you for your time!

Solution

  • If I understand your verbal description correctly, you only want to include events that start with Windows - Policy Change.

    In which case, you can include that as the only filter:

    T
| where EventName startswith "Windows - Policy Change"

    In case you need the inverse (all Windows events, except for policy change), you can do this:

    T
| where EventName startswith "Windows"
| where EventName !startswith "Windows - Policy Change"

    And, in case you want to get all non-windows events, and only windows policy change events, you can run:

    T
| where EventName !startswith "Windows" or
        EventName startswith "Windows - Policy Change"