let IP = (_GetWatchlist('PaloAltoDevice')
| project SearchKey);
let starttime = 10d;
let endtime = 1d;
let threshold = 500;
let nxDomainDnsEvents = DnsEvents;
workspace("787d7230-15d6-4e95-b2d6-b38fd004fd0e").DnsEvents
|union workspace("c876584a-693c-422b-9755-5d2a53e93def").DnsEvents
| where ResultCode == 3
| where QueryType in ("A", "AAAA")
| where ipv4_is_match("127.0.0.1", ClientIP) == False
| where Name !contains "/"
| where Name contains ".";
nxDomainDnsEvents
| where TimeGenerated > ago(endtime)
| extend sld = tostring(split(Name, ".")[-2])
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), dcount(sld) by ClientIP
| where dcount_sld > threshold
// Filter out previously seen IPs
| join kind=leftanti (nxDomainDnsEvents
| where TimeGenerated between(ago(starttime) .. ago(endtime))
| extend sld = tostring(split(Name, ".")[-2])
| summarize dcount(sld) by ClientIP
| where dcount_sld > threshold)
on ClientIP
// Pull out sample NXDomain responses for those remaining potentially infected IPs
| join kind = inner (nxDomainDnsEvents
| summarize by Name, ClientIP)
on ClientIP
| summarize
StartTimeUtc = min(StartTimeUtc),
EndTimeUtc = max(EndTimeUtc),
sampleNXDomainList=make_list(Name, 100)
by ClientIP, dcount_sld
| extend timestamp = StartTimeUtc, IPCustomEntity = ClientIP
| where ClientIP !in (IP)
Error Screenshot: