Find a domain within URL with Kusto (Defender ATP Advanced Hunting)

Question

There's an external list of malicious domains/URL's, and I want to periodically search the logs, but there's an obvious problem:

let abuse_domain = (externaldata(sentinel_domain: string )
[@"https://managedsentinel.com/downloads/covid19_domains.txt"]
with (format="txt"))
| where sentinel_domain !startswith "#"
| project sentinel_domain;
abuse_domain
| join 
(
DeviceNetworkEvents
| where Timestamp > ago(1h) 
) on $left.sentinel_domain == $right.RemoteUrl
| project Timestamp,DeviceName,RemoteUrl,DeviceId,ReportId

The On clause isn't going to work because the two items will never completely match. How can I get a match when $left.sentinel_domain is a substring of $rightRemoteUrl ?

Answer 1

Try using parse_url to extract the domain (Host) from RemoteUrl first.

Like so:

let abuse_domain = (externaldata(sentinel_domain: string )
[@"https://managedsentinel.com/downloads/covid19_domains.txt"]
with (format="txt"))
| where sentinel_domain !startswith "#"
| project sentinel_domain;
abuse_domain
| join 
(
DeviceNetworkEvents
| where Timestamp > ago(1h)
| extend Host = tostring(parse_url(RemoteUrl).Host)
) on $left.sentinel_domain == $right.Host
| project Timestamp,DeviceName,RemoteUrl,DeviceId,ReportId