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?
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
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
On more little snag.... For some reason it is only giving me the top 2.
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
Hmmmmm, ended up saying no results found after removing the extra | in front of chart.
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
That worked! Many thanks somesoni2! Gave me some new commands that I'm not aware of 🙂 Now to study up on them!