Microsoft Security Tech Accelerator
Dec 06 2023, 07:00 AM - 12:00 PM (PST)
Microsoft Tech Community

Kusto query

Copper Contributor

I am using below query in analytics rule and it is giving error "Query returned more than one result set. Only one result set is supported per rule". Please suggest.



let IP = (_GetWatchlist('PaloAltoDevice')
| project SearchKey);
let starttime = 10d;
let endtime = 1d;
let threshold = 500;
let nxDomainDnsEvents = DnsEvents;
|union workspace("c876584a-693c-422b-9755-5d2a53e93def").DnsEvents
    | where ResultCode == 3
    | where QueryType in ("A", "AAAA")
    | where ipv4_is_match("", ClientIP) == False
    | where Name !contains "/"
    | where Name contains ".";
| 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:


2 Replies
best response confirmed by Bhavini (Copper Contributor)
Looks like the line starting with "workspace" will return one result set while the line starting with "nxDomainDnsEvents" returns another. Did you mean to set up the first one as temp table?
Yes. Previously, it was working fine. But by using cross workspace query, I started getting this error message.