Multiple Excessive Rdns Queries
KQL detection rule by ep3p · MIT
No description provided
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