topic Re: How to edit my search to sort a count by a field to get the top 3 ? in Splunk Search

How to edit my search to sort a count by a field to get the top 3 ?

dbcase — Mon, 08 Aug 2016 18:49:35 GMT

Hi,

I have one that I've worked around until now..... 🙂

The scenario is:

Row is URI
/a
/b
/c
/d
/e
/f

Column is IP
1.1.1.1 2.2.2.2 3.3.3.3 4.4.4.4 5.5.5.5.

What I need to do is sort by the count of URI by IP so that I get the top 3.

I've used addtotals and then sort by the total which works ,but the problem is the chart is already displayed, so while 4.4.4.4 is sorted correctly, 1.1.1.1 has the value of 0 in all its columns because its counts are lower and not included in the top 3, yet the IP is still displayed in the chart

The search is:

earliest=-6h host="*beta*" source="/etc/httpd/logs/portal-access_log*" index=main | rex "HTTP.\d.\d.\s+(?< status >\d+)" | search status=404 |rex "(?< ip >\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})"|rex "GET\s(?[^\"]+)"|eval URI=if(URI="\"GET",URL,URI)|chart useother=f count as Count by URI ip|addtotals|sort -Total|head 10

Here is a screenshot (note the first 4 columns)

How can I get the chart so that the top 3 show by URI AND IP?

Re: How to edit my search to sort a count by a field to get the top 3 ?

somesoni2 — Mon, 08 Aug 2016 19:02:25 GMT

Give this a try

earliest=-6h host="beta" source="/etc/httpd/logs/portal-access_log*" index=main | rex "HTTP.\d.\d.\s+(?<status>\d+)" | search status=404 |rex "(?<ip>\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})"|rex "GET\s(?<URL>[^\"]+)"|eval URI=if(URI="\"GET",URL,URI)
| stats count by URI ip | |chart useother=f count as Count by URI ip|addtotals |sort -Total|head 10 | untable URI IP count | sort 3 -count by URI | xyseries URI IP count

Re: How to edit my search to sort a count by a field to get the top 3 ?

dbcase — Mon, 08 Aug 2016 19:24:12 GMT

Hmmmmm, ended up saying no results found after removing the extra | in front of chart.

Re: How to edit my search to sort a count by a field to get the top 3 ?

somesoni2 — Mon, 08 Aug 2016 19:27:52 GMT

There may be a typo in my answer (in rex to extract URL), I wasn't sure about the field name. To here is what you should do

...your current search as you mentioned in question | untable URI IP count | sort 3 -count by URI | xyseries URI IP count

Re: How to edit my search to sort a count by a field to get the top 3 ?

dbcase — Mon, 08 Aug 2016 19:38:04 GMT

That worked! Many thanks somesoni2! Gave me some new commands that I'm not aware of 🙂 Now to study up on them!

Re: How to edit my search to sort a count by a field to get the top 3 ?

dbcase — Mon, 08 Aug 2016 19:57:38 GMT

On more little snag.... For some reason it is only giving me the top 2.

Re: How to edit my search to sort a count by a field to get the top 3 ?

dbcase — Mon, 08 Aug 2016 20:52:27 GMT

Found it!

earliest=-6h host="beta" source="/etc/httpd/logs/portal-access_log*" index=main | rex "HTTP.\d.\d.\s+(?< status >\d+)" | search status=404 |rex "(?< ip >\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})"|rex "GET\s(?< URL >[^\"]+)"|eval URI=if(URI="\"GET",URL,URI)|where ip!="54.174.106.18"|where ip!="54.210.253.21"|where ip!="54.210.253.139"|chart count as Count by URI ip |untable URI ip count | sort 10 -count by URI | xyseries URI ip count