Splunk Search

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

dbcase
Motivator

Hi,

I have one that I've worked around until now..... :slightly_smiling_face:

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
1 Solution

somesoni2
Revered Legend

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

somesoni2
Revered Legend

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

dbcase
Motivator

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

0 Karma

dbcase
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

dbcase
Motivator

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

0 Karma

somesoni2
Revered Legend

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

dbcase
Motivator

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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...