kqlazure-sentinel

How to create a whitelist with two fields in KQL with a Watchlist?


I need to make a query that checks if an event doesn't match two fields from a watchlist. Context: I have a watchlist with two fields, one is a user email and other is a country code, this allows me to ignore events from users who are in an specific country and I don't need to see.

I know how to validate watchlists with one single field:

| where Username !in (_GetWatchlist("query_WL")|project Username)

I am translating queries from Splunk, but this logic doesn't work at all:

Splunk:

| search NOT  [ | inputlookup query_WL.csv | fields Username CountryCode]

KQL

| where Username !in (_GetWatchlist("query_WL")|project Username) and CountryCode !in (_GetWatchlist("query_WL")|project CountryCode)

This is blowing my mind, I don't know how to do the same in KQL.


Solution

  • You could anti-join the fields UserName and CountryCode:

    let Blacklist = datatable(
        UserName: string,
        CountryCode: string
    )
        [
        "John", "DE",
        "John", "US"
    ];
    let Data = datatable(
        UserName: string,
        CountryCode: string
    )
        [
        "John", "DE",
        "John", "US",
        "Mat", "DE",
        "William", "UK",
    ];
    Data
    | join kind = anti(Blacklist) on UserName, CountryCode
    

    Result:

    enter image description here

    Find sample Code here.