KQL Search

let query_frequency = 1d;
let query_period = 14d;
let threshold = 50;
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 _ExpectedPTRQueriesIPAddresses = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "PTRQuery"
    | summarize make_list(SourceAddress)
);
let _Queries = (start_time:datetime, end_time:datetime){
    union
        (DnsEvents
        | where TimeGenerated between (start_time..end_time)
        | where Name has_any (".in-addr.arpa", ".ip6.arpa")
        | lookup _ResultCodeTable on ResultCode
        | project
            TimeGenerated,
            SrcIpAddr = ClientIP,
            DnsQueryTypeName = QueryType,
            DnsQuery = Name,
            EventResult = iff(EventResultDetails =~ 'NOERROR', 'Success', 'Failure'),
            EventResultDetails,
            DnsResponseName = IPAddresses,
            Dvc = Computer,
            Type
        ),
        (Cisco_Umbrella_dns_CL
        | where TimeGenerated between (start_time..end_time)
        | where QueryType_s has_any ("PTR") and Domain_s has_any (".in-addr.arpa", ".ip6.arpa") //and not(InternalIp_s == ExternalIp_s)
        | 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
        )
    | extend QueriedIpAddr = case(
        DnsQuery has ".in-addr.arpa", strcat_array(array_reverse(array_slice(split(DnsQuery, "."), -6, -3)), "."),
        DnsQuery has ".ip6.arpa", translate('[]",', "", strcat_array(array_split(array_reverse(array_slice(split(DnsQuery, "."), -34, -3)), dynamic([4,8,12,16,20,24,28])), ":")),
        ""
        )
    | project
        TimeGenerated,
        Identities,
        SrcIpAddr,
        SrcNatIpAddr,
        DnsQueryType,
        DnsQueryTypeName,
        DnsQuery,
        QueriedIpAddr,
        EventResult,
        EventResultDetails,
        DnsResponseName,
        DvcAction,
        UrlCategory,
        ThreatCategory,
        PolicyIdentityType,
        PolicyIdentity,
        Dvc,
        Type
};
_Queries(ago(query_frequency), now())
| join kind=leftanti _Queries(ago(query_period), ago(query_frequency)) on SrcIpAddr, DnsQuery
| summarize
    StartTime = min(TimeGenerated),
    EndTime = max(TimeGenerated),
    Identities = make_set(Identities, 5),
    take_any(SrcNatIpAddr),
    DnsQueryDistinctCount = dcount(DnsQuery),
    QueriedIpAddrSample = array_sort_asc(make_set(QueriedIpAddr, 200)),
    EventResultDetails = array_sort_asc(make_set(EventResultDetails)),
    Dvc = make_set(Dvc, 250),
    take_any(DnsQueryTypeName)
    by SrcIpAddr
| where not(SrcIpAddr in (_ExpectedPTRQueriesIPAddresses))
| where DnsQueryDistinctCount > threshold
| project
    StartTime,
    EndTime,
    Identities,
    SrcIpAddr,
    SrcNatIpAddr,
    DnsQueryTypeName,
    DnsQueryDistinctCount,
    QueriedIpAddrSample,
    EventResultDetails,
    Dvc