topic Re: Search Top value of a previuos Top Result in Splunk Search

Search Top value of a previuos Top Result

acedeno — Mon, 28 Sep 2020 14:12:45 GMT

Hi,

I'm trying to create a table of data which draws upon a subsearch and a join in order to have more completely representation of my data.

first, I created a table that shows the TOP 5 Destination IP, Destination IP Country, Destination Port, and Protocol. My search is something like this:

And my result is something like:
dest_ip dest_ip_country_name dest_port protocol count percent
1 aaa.aaa.aaa.aaa China 161 udp 336834 10.501823
2 bb.bbb.bbb.bbb United Kingdom 16386 udp 184701 5.758615
3 ccc.ccc.ccc.ccc United Kingdom 3544 udp 182193 5.680420
4 dd.ddd.ddd.ddd United Kingdom 16385 udp 180451 5.626108
5 ee.eee.eee.eee United Kingdom 16384 udp 180332 5.622398

If I drill down, I'll be able to find for each one of my results the TOP Source IP.

What I'm trying to do, is to eliminate to drill down in order to find out what is the Source IP on each result. so my table should look like:

src_ip          dest_ip         dest_ip_country_name    dest_port   protocol    count   percent

1 xxx.xxx.xxx.xxx aaa.aaa.aaa.aaa China 161 udp 336834 10.501823
2 yy.yyy.yyy.yyy bb.bbb.bbb.bbb United Kingdom 16386 udp 184701 5.758615
3 zz.zzz.zzz.zzz ccc.ccc.ccc.ccc United Kingdom 3544 udp 182193 5.680420
4 www.www.www.www dd.ddd.ddd.ddd United Kingdom 16385 udp 180451 5.626108
5 uu.uuu.uuu.uuu ee.eee.eee.eee United Kingdom 16384 udp 180332 5.622398

I've tried changing the join parameters a few times and in a few ways, but I'm missing something about the logic of what I'm doing, so I'm obviously going about it in the wrong way.

I just want to find TOP 5 Destination IP, Destination IP Country, Destination Port, and Protocol., then look for them TOP Source IP for each one, and create a nice table of information...

any Ideas..... I need more coffee....

Re: Search Top value of a previuos Top Result

sideview — Mon, 28 Sep 2020 14:12:53 GMT

Don't use join. This query should work.

sourcetype="cisco_asa" (host="11.11.11.11" OR host="10.10.10.10" OR host="12.12.12.12" OR host="13.13.13.14") (actual_action="Deny" OR actual_action="Denied" OR actual_action="denied") dest_dom="Outside" | geoip dest_ip | eval protocol=lower(protocol) | stats count by dest_ip,dest_ip_country_name,dest_port,protocol,src_ip | sort - count | streamstats count as src_ip_index by dest_ip,dest_ip_country_name,dest_port,protocol | where src_ip_index=1 | fields - src_ip_index

That first stats command is the same as yours except that it also calculates count by src_ip. Then we sort by the overall count, then we use streamstats to basically paint little numbers so the first row for a given combination of all the other fields will have src_ip_index of 1, and the second row will have 2, etc... because the "by" clause of the streamstats matches the "by" clause of the stats, and because of our sort command, it turns out that all the rows painted with "1" will represent the highest count src_ip for that combination of dest_ip,dest_ip_country_name,dest_port,protocol.
Which means we can then use where to filter to the set of top results.

Admittedly the count values are not the same as they are in your report, but it's quick and simple.

If you care about the counts being correct, you can stitch in one more eventstats command.

This will end up with a totalCount field which will have the overall count values that you had in your original results. Hope this helps.

Between eval, stats, streamstats and eventstats you can usually eliminate join and append. Although I'll admit join and append are super intuitive, whereas these faster more efficient "splunk-style" searches can feel pretty weird until you get used to them.

Re: Search Top value of a previuos Top Result

acedeno — Mon, 28 Sep 2020 14:13:04 GMT

Thanks! works but I still need to remove some duplicates. Here what I'm getting with your help:

dest_ip         Country port    protocol    src_ip          count   totalCount

1 124.124.124.1 Japan 161 udp 10.10.10.9 53051 135377
4 178.178.178.87 UK 843 tcp 10.10.10.75 18632 20345
5 124.124.124.1 Japan 161 udp 10.10.10.161 8648 135377

And I would like to get this:
dest_ip Country port protocol src_ip count totalCount
1 124.124.124.1 Japan 161 udp 10.10.10.9 53051 135377
4 178.178.178.87 UK 843 tcp 10.10.10.75 18632 20345

How can I remove the duplicates and keep the higher ones?

Re: Search Top value of a previuos Top Result

acedeno — Mon, 28 Sep 2020 14:13:08 GMT

I believe I'm on the right path...
I add at the end of the search:
... |dedup dest_ip,dest_ip_country_name,dest_port,protocol

But, in some cases the count values are not the same as they are in my report. any ideas?

Re: Search Top value of a previuos Top Result

sideview — Sun, 30 Jun 2013 16:29:00 GMT

Just a quick check - I edited my answer shortly after posting it. My first search had a problem much like this. Are you using the unedited answer that probably generated an email to you, or the edited one on the site?