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.
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:
Find sample Code here.