KQL Search

let query_frequency = 1d;
let query_period = 14d;
let suspicious_categories = dynamic([
    "Child Abuse Content",
    "Dating",
    "Gambling",
    "German Youth Protection",
    "Hate Speech",
    "Hate/Discrimination",
    "Illegal Activities",
    "Illegal Downloads",
    "P2P/File sharing",
    "Peer File Transfer",
    "Pornography",
    "Sexuality",
    "Tasteless",
    "Terrorism"
    //"Terrorism and Violent Extremism"
]);
let excluding_categories = dynamic([
    "Blogs"
]);
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 (suspicious_categories) and not(UrlCategory has_any (excluding_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 SLD = strcat_array(array_slice(split(DnsQuery, "."), -2, -1), ".")
| join kind=leftanti (
    Cisco_Umbrella_dns_CL
    | where TimeGenerated  between (ago(query_period) .. ago(query_frequency))
    | where Categories_s has_any (suspicious_categories) and not(Categories_s has_any (excluding_categories))
    | distinct Domain_s
    | extend SLD = strcat_array(array_slice(split(Domain_s, "."), -3, -2), ".")
    ) on SLD
| project
    StartTime,
    EndTime,
    EventCount,
    HostNames,
    UserPrincipalNames,
    SrcIpAddrs,
    SrcNatIpAddrs,
    DnsQueryTypeNames,
    DnsQuery,
    SLD,
    EventResult,
    EventResultDetails,
    DvcAction,
    UrlCategory,
    ThreatCategory,
    PolicyIdentityType,
    PolicyIdentity