Splunk Search
Highlighted

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

Motivator

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)

alt text

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma
Highlighted

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

Motivator

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

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

Motivator

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

0 Karma
Highlighted

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

Motivator

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

0 Karma
Highlighted

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

Motivator

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

0 Karma