Microsoft Sentinel Watchlists and wildcard/partial/substring table joins in KQL

Watchlists are a great way to house data in a table format to be used for various purposes, be it analytic creation or incident enrichment.  If you are using a watchlist to enrich incident entities there are some things to watch out for.

Sometimes the entity in the incident will have multiple formats for the same device/account.  i.e. a machine may be referenced as its plain hostname or as its fully qualified name format in an incident.  An account may use the samAccountName, UPN, email or Account display name.  Handling these in watchlists would result in multiple entries for each possible format for every entity which would become problematic to manage the watchlists.

Ideally Your important accounts and devices should conform to a naming standard in your organisation.  So for administrator and service accounts you might have a suffix or prefix to distinguish these.  For devices you naming standard should identity a DHCP server or Domain controller and if it is Production or Development, i.e. if it contains PRDDHCP it is a production DHCP server.

At a large organisation you might have 20 domain controllers so instead of adding each individually in the watchlist you could instead add the piece of the naming standard which identitifes them as the watchlist SearchKey, i.e. PRDDC and a descripton “Production Domain Controller”.  Not only does this use 1 entry instead of 20, it also future proofs you for additions and deletions of production domain controllers so less chance the watchlist will be out of date.

Instead of adding every system admins samAccountName you can just add the piece of the naming standard which identifies them as an admin in one entry.  Partial watchlist entries are not a one size fits all, care must be taken that they key doesnt create false postivie matches, i.e. DC to identify a domain controller could easily match other server names so you should check before adding that it identifies the devices you want.  Also some servers still warrant their own unqiue watchlist entry to display specific information to be surfaced to the perosn investigating the incident.

Now do the partial KQL Table Joins

Using mv-apply

Now you have partial entries in your watchlist you now have to join them to you analytic or enrichment logic app with a partial match and not the standard $left.field == $right.field join.

The best way to do this is if the left hand table is small enough to use mv-apply

let AlertEvid = datatable(devices:string) 
[
    "MDPRDDHCP01", 
    "MDPRDDC01",
    "MDPRDEXC02" 
];
let filtertable = (_GetWatchlist('deviceWL') | summarize make_set(SearchKey));
AlertEvid
| mv-apply id=toscalar(filtertable) to typeof(string) on (where set_ioc contains id)

This query above is using an array for AlertEvid more reminiscient of using it in an analytic but you can easily replace with a single string value say if you wanted to loop through incident entities in an enrichment logic app.

Using a cross-join

Also as the KQL join only supports equality you can consider using a cross-join, then filtering using contains.

let watchlistE = datatable(nskey:string)
[
"PRDDHCP",
"PRDDC",
"PRDEXC"
];
let AlertEvid = datatable(devices:string)
[
"MSPRDEXC01.local",
"MSPRDEXC02",
"MSPRDDHCP08"
];
watchlistE
| extend empty = 1
| join kind=inner (AlertEvid | extend empty = 1) on empty
| where devices contains nskey
| project-away empty*