CODEWITHSUNDEEP
kqlazure-sentinel
JohnnyMnemonic
JohnnyMnemonic

Reputation: 5

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.

Upvotes: 0

Views: 276

Answers (1)

decius
decius

Reputation: 1445

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.

Upvotes: 1

Related Questions