Is there a way to perform string operators on elements of a list in KQL?

Question

I'm trying to whitelist a bunch of domains from Azure sentinel rules based on the !hassuffix string operator.

Im trying to do something like this:

AzureDiagnostics
| where destinationDomain !hassuffix ".google.com" and destinationDomain !hassuffix ".azure.com"

But because there is going to be a lot of whitelisted domains and subdomains would like to store the root domain/subdomains in a list which will be in blob storage like:

let whitelist = dyanmic([".google.com", ".azure.com" .........])

Does anyone know the syntax to iterate through each of these and check whether the destinationDomain !hassuffix to each of the dynamic array elements? Or is the only way to have a wall of and's? Thanks

Answer 1

There's no such functionality. You should use matches regex instead.

Answer 2

This is perhaps not the most efficient way to do it, but you could do a cross product type join across the whitelist, perform the !hassuffix check on each, then see the count of how many passed (failed I guess?) the check. For a smallish whitelist and table it should do OK and would be easier to modify / maintain.

let AzureDiagnostics = datatable(destinationDomain: string)
[
"test.google.com",
"test.notallowed.com",
"other.azure.com",
"alsonotallowed.azure2.com"
];
let Whitelist = datatable(allowedSuffix: string, dummy: long)
[
".google.com", 1,
".azure.com", 1,
];
AzureDiagnostics
| extend dummy=1 // add a dummy column for cross product join
| lookup Whitelist on dummy // do cross product (lookup used assuming Whitelist is small)
| where destinationDomain !hassuffix(allowedSuffix) // perform the suffix check
| summarize count() by destinationDomain // since the list was broken up, get the count of passes
| where count_ == toscalar(Whitelist | count) // if the !hassuffix was true for all (the count) keep the result
| project destinationDomain // get rid of the count column