KQL Search

let query_frequency = 1d;
let query_period = 7d;
let malicious_categories = dynamic([
    "Adware",
    "Command and Control",
    "Cryptomining",
    "DNS Tunneling VPN",
    "Dynamic DNS",
    "Malware",
    "Newly Seen Domains",
    "Phishing",
    "Potentially Harmful"
]);
let _ExpectedBlockedDomainsPartial = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "ExpectedBlockedDomain" and Notes has "[PartialDomain]"
    | summarize RegEx = make_list(regex_quote(DestinationAddress))
    | extend RegEx = strcat(@'\.(', strcat_array(RegEx, '|'), @')$')
);
let _ExpectedBlockedDomainsComplete = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "ExpectedBlockedDomain" and Notes has "[CompleteDomain]"
    | summarize make_list(DestinationAddress)
);
CiscoUmbrellaLogs
| where TimeGenerated between (ago(query_frequency) .. now())
| where UrlCategory has_any (malicious_categories)
| where not(DnsQuery in (_ExpectedBlockedDomainsComplete))
| where not(DnsQuery matches regex _ExpectedBlockedDomainsPartial)
| extend
    HostName = tostring(Identities["AD Computers"]),
    UserPrincipalName = tostring(Identities["AD Users"])
| summarize
    StartTime = min(EventStartTime),
    EndTime = max(EventStartTime),
    EventCount = count(),
    HostNames = make_set(HostName, 100),
    UserPrincipalNames = make_set(UserPrincipalName, 100),
    SrcIpAddrs = make_set(SrcIpAddr, 100),
    SrcNatIpAddrs = make_set(SrcNatIpAddr, 100),
    DnsQueryTypeNames = make_set(DnsQueryTypeName, 100),
    take_any(EventResult, UrlCategory, ThreatCategory)
    by EventResultDetails, DvcAction, DnsQuery, PolicyIdentityType, PolicyIdentity
| extend Domain_s = strcat(DnsQuery, ".")
| join kind=leftanti (
    Cisco_Umbrella_dns_CL
    | where TimeGenerated  between (ago(query_period) .. ago(query_frequency))
    | where Categories_s has_any (malicious_categories)
    ) on Domain_s
| project
    StartTime,
    EndTime,
    EventCount,
    HostNames,
    UserPrincipalNames,
    SrcIpAddrs,
    SrcNatIpAddrs,
    DnsQueryTypeNames,
    DnsQuery,
    SLD = strcat_array(array_slice(split(DnsQuery, "."), -2, -1), "."),
    EventResult,
    EventResultDetails,
    DvcAction,
    UrlCategory,
    ThreatCategory,
    PolicyIdentityType,
    PolicyIdentity