Seeking help running excessive DNS queries report

[dharveynswccd]

Hi Splunkers. I'm not very good with writing more complicated searches so I am seeking your help.
I wrote a search to build a report looking for excessive DNS queries. It looks like this:

| from datamodel:"Network_Resolution"."DNS"
| search src="IP" OR src="IP"
| stats count by src,dest,query, _time
| addcoltotals
|dedup _time

What I'd like to be able to do is total all of the results from the "query" column, related to a specific IP, in the "count" column at the end and then subsequently provide a grand total column at the end of the report. Right now I'm being presented with single lines for each hit, which is still helpful but too much for management to peer through.

Any help will be appreciated. Thanks

[mayurr98 · Accepted solution]

try this :

| from datamodel:"Network_Resolution"."DNS" 
| search src="IP" OR src="IP" 
| stats count values(_time) as time by src,dest,query 
| convert ctime(time) as time 
| addcoltotals 

OR

| from datamodel:"Network_Resolution"."DNS" 
| search src="IP" OR src="IP" 
| stats count values(_time) as time values(src) as src values(dest) as dest by query 
| convert ctime(time) as time 
| addcoltotals

[dharveynswccd]

This is great. Much better results and the search ran much faster. Thanks mayurr98

[dharveynswccd]

So this looked great until I realized that the search did not complete. It actually crashed with the following error messages:
"DAG Execution Exception: Search has been cancelled"
"Search auto-canceled"

When the search is run using a Relative Time of "Today" or 4 hours etc, the search completes with no errors. However, if it goes beyond that then it it fails. Could this have something to do with the following lines?
| stats count values(_time) as time by src,dest,query
| convert ctime(time) as time

[mayurr98]

well, as far as I know, it's not related to query. that's pretty simple query.
refer this
https://answers.splunk.com/answers/685827/help-with-error-from-a-custom-command-error-search.html
https://answers.splunk.com/answers/724469/what-causes-search-auto-canceled.html

pls, accept/upvote the answer if it works for you.

if you are getting same error again then you could try running a search on index instead of data model if it's not accelerated.

[mayurr98]

could you pls share the output from the current search query and what is the expected output?
Form the explanation that you've given, I don't really understand your problem.

[dharveynswccd]

@mayurr98, thanks for replying.
So here is a small snippet of what the report looks like:
src dest query _time count

ip address ip address 0.122.168.192.in-addr.arpa 2019-11-20T11:01:29.366-0500 1
ip address ip address 1.219.46.130.in-addr.arpa 2019-11-20T11:06:26.186-0500 1
ip address ip address 101.248.223.199.in-addr.arpa 2019-11-20T11:04:32.154-0500 1
ip address ip address 114.219.46.130.in-addr.arpa 2019-11-20T11:15:58.810-0500 1
ip address ip address 123.200.159.162.in-addr.arpa 2019-11-20T11:15:43.689-0500 1
ip address ip address 123.36.79.45.in-addr.arpa 2019-11-20T11:15:40.626-0500 1
ip address ip address 142.208.169.198.in-addr.arpa 2019-11-20T11:15:40.805-0500 1
ip address ip address 150.63.29.193.in-addr.arpa 2019-11-20T11:15:42.015-0500 1
ip address ip address 183.219.46.130.in-addr.arpa 2019-11-20T11:15:58.318-0500 1
ip address ip address 43.219.46.130.in-addr.arpa 2019-11-20T11:15:58.561-0500 1
ip address ip address 43.219.46.130.in-addr.arpa 2019-11-20T12:15:58.561-0500 1
ip address ip address 45.219.46.130.in-addr.arpa 2019-11-20T11:15:58.435-0500 1
ip address ip address 45.219.46.130.in-addr.arpa 2019-11-20T11:16:58.436-0500 1
ip address ip address 52.227.46.130.in-addr.arpa 2019-11-20T11:14:49.375-0500 1
ip address ip address 4.0.22.224.in-addr.arpa 2019-11-20T11:08:21.380-0500 2

I'm seeing multiple duplicates of the same query, but with only a few seconds, or minute difference in some cases. I am trying to prevent those duplicate query types from showing in the report and only show the total number of hits for each unique query. Hope this is clearer. Thanks