Splunk Search

Seeking help running excessive DNS queries report

dharveynswccd
Path Finder

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

Tags (1)
0 Karma
1 Solution

mayurr98
Super Champion

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

View solution in original post

0 Karma

mayurr98
Super Champion

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
0 Karma

dharveynswccd
Path Finder

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

0 Karma

dharveynswccd
Path Finder

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

0 Karma

mayurr98
Super Champion

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.

0 Karma

mayurr98
Super Champion

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.

0 Karma

dharveynswccd
Path Finder

@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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...