KQL Search

let query_frequency = 1d;
let query_period = 7d;
let _ResultCodeTable = datatable(ResultCode:int, EventResultDetails:string)[
   0,   'NOERROR'
 , 1,   'FORMERR'
 , 2,   'SERVFAIL'
 , 3,   'NXDOMAIN'
 , 4,   'NOTIMP'
 , 5,   'REFUSED'
 , 6,   'YXDOMAIN'
 , 7,   'YXRRSET'
 , 8,   'NXRRSET'
 , 9,   'NOTAUTH'
 , 10,  'NOTZONE'
 , 11,  'DSOTYPENI'
 , 16,  'BADVERS'
 , 16,  'BADSIG'
 , 17,  'BADKEY'
 , 18,  'BADTIME'
 , 19,  'BADMODE'
 , 20,  'BADNAME'
 , 21,  'BADALG'
 , 22,  'BADTRUNC'
 , 23,  'BADCOOKIE'
];
let _IPv4Regex = toscalar(
    _GetWatchlist('RegEx-SingleRegularExpressions')
    | where UseCase == "Threat Intelligence Indicator IPv4"
    | project RegEx
);
let _IPv6Regex = toscalar(
    _GetWatchlist('RegEx-SingleRegularExpressions')
    | where UseCase == "Threat Intelligence Indicator IPv6"
    | project RegEx
);
let _Queries = (start_time: datetime, end_time: datetime) {
    let _WindowsDNSEvents =
        DnsEvents
        | where TimeGenerated between (start_time..end_time)
        | extend QueriedIPAddress = extract(strcat(@"(", _IPv4Regex, @"|", _IPv6Regex, @")\b.+$"), 1, Name)
        | where not(isempty(QueriedIPAddress) or QueriedIPAddress == "127.0.0.1")// or (isnotempty(parse_ipv4(QueriedIPAddress)) and ipv4_is_private(QueriedIPAddress))
        | lookup _ResultCodeTable on ResultCode
        | project
            TimeGenerated,
            SrcIpAddr = ClientIP,
            DnsQueryTypeName = QueryType,
            DnsQuery = Name,
            EventResult = iff(EventResultDetails =~ 'NOERROR', 'Success', 'Failure'),
            EventResultDetails,
            DnsResponseName = IPAddresses,
            Dvc = Computer,
            Type
    ;
    let _UmbrellaDNSEvents =
        Cisco_Umbrella_dns_CL
        | where TimeGenerated between (start_time..end_time)
        | extend QueriedIPAddress = extract(strcat(@"(", _IPv4Regex, @"|", _IPv6Regex, @")\b.+\.$"), 1, Domain_s)
        | where not(isempty(QueriedIPAddress) or QueriedIPAddress == "127.0.0.1")// or (isnotempty(parse_ipv4(QueriedIPAddress)) and ipv4_is_private(QueriedIPAddress))
        | parse QueryType_s with DnsQueryType:int " (" DnsQueryTypeName:string ")"
        | mv-apply Identities_s_aux = todynamic(Identities_s) to typeof(string), Identity_Types_s_aux = todynamic(Identity_Types_s) to typeof(string) on ( 
            summarize Identities = make_bag(bag_pack(Identity_Types_s_aux, Identities_s_aux))
            )
        | project
            TimeGenerated = todatetime(column_ifexists('Timestamp_t', column_ifexists('Timestamp_s',''))),
            Identities,
            SrcIpAddr = column_ifexists('InternalIp_s', ''),
            SrcNatIpAddr = column_ifexists('ExternalIp_s', ''),
            DnsQueryType,
            DnsQueryTypeName,
            DnsQuery = trim_end(@'\.', column_ifexists('Domain_s', '')),
            EventResult = iff(ResponseCode_s =~ 'NOERROR', 'Success', 'Failure'),
            EventResultDetails = ResponseCode_s,  // => ResponseCodeNames
            DvcAction = column_ifexists('Action_s', ''),
            UrlCategory = column_ifexists('Categories_s', ''),
            ThreatCategory = column_ifexists('Blocked_Categories_s', ''),
            PolicyIdentityType = column_ifexists('Policy_Identity_Type_s', ''),
            PolicyIdentity = column_ifexists('Policy_Identity_s', ''),
            Dvc = 'CiscoUmbrella',
            Type
    ;
    union _WindowsDNSEvents, _UmbrellaDNSEvents
    | where not(DnsQueryTypeName in ("PTR", "SOA", "NS", "TXT"))
    | summarize
        TimeGenerated = min(TimeGenerated),
        Identities = make_set(Identities, 250),
        SrcIpAddr = array_sort_asc(make_set(SrcIpAddr, 250)),
        SrcNatIpAddr = make_set_if(SrcNatIpAddr, isnotempty(SrcNatIpAddr), 250),
        DnsResponseName = take_anyif(DnsResponseName, isnotempty(DnsResponseName)),
        UrlCategory = take_anyif(UrlCategory, isnotempty(UrlCategory)),
        ThreatCategory = take_anyif(UrlCategory, isnotempty(ThreatCategory)),
        PolicyIdentityType = make_set_if(PolicyIdentityType, isnotempty(PolicyIdentityType), 250),
        PolicyIdentity = make_set_if(PolicyIdentity, isnotempty(PolicyIdentity), 250),
        Dvc = make_set(Dvc, 250),
        take_any(DnsQueryType, EventResult, DvcAction)
        by DnsQueryTypeName, DnsQuery, EventResultDetails
    | extend SrcIpAddr = set_difference(SrcIpAddr, SrcNatIpAddr)
    | project
        TimeGenerated,
        Identities,
        SrcIpAddr,
        SrcNatIpAddr,
        DnsQueryType,
        DnsQueryTypeName,
        SLD = strcat_array(array_slice(split(DnsQuery, "."), -2, -1), "."),
        DnsQuery,
        EventResult,
        EventResultDetails,
        DnsResponseName,
        DvcAction,
        UrlCategory,
        ThreatCategory,
        PolicyIdentityType,
        PolicyIdentity,
        Dvc
};
_Queries(ago(query_frequency), now())
| join kind=leftanti _Queries(ago(query_period), ago(query_frequency)) on SLD