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

[dbcase]

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?

[somesoni2 · Accepted solution]

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

[dbcase]

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

[dbcase]

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

[dbcase]

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

[somesoni2]

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

[dbcase]

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