topic Re: How to edit my search to show the count of a field per country? in Splunk Search

How to edit my search to show the count of a field per country?

bayman — Thu, 18 May 2017 17:36:28 GMT

I have a search below that shows the number of events by Country. I want to show the count of each dest_port per country. How do I do this?

eventtype=cisco-firewall host="*" action="*" src_ip="*" dest_ip="*" src_port="*" dest_port="*" transport="*" service="*" | iplocation src_ip | stats count by Country

Country | Count
------------------------ 
USA     | 150
Canada  | 82 
Mexico  | 11

Re: How to edit my search to show the count of a field per country?

rjthibod — Thu, 18 May 2017 17:38:16 GMT

Do you mean something like this?

eventtype=cisco-firewall host="*" action="*" src_ip="*" dest_ip="*" src_port="*" dest_port="*" transport="*" service="*" 
| iplocation src_ip 
| stats count by dest_port Country

Re: How to edit my search to show the count of a field per country?

bayman — Tue, 29 Sep 2020 14:08:17 GMT

I need it to show Country only once and each dest_port count once for the corresponding country.

Something like this, but I need it to show the count of dest_port.

eventtype=cisco-firewall host="*" action="*" src_ip="*" dest_ip="*" src_port="*" dest_port="*" transport="*" service="*" | iplocation src_ip | stats count values(dest_port) as dest_port by Country

Re: How to edit my search to show the count of a field per country?

cmerriman — Thu, 18 May 2017 18:03:04 GMT

what about something like this:

 eventtype=cisco-firewall host="*" action="*" src_ip="*" dest_ip="*" src_port="*" dest_port="*" transport="*" service="*" | iplocation src_ip|eventstats count by dest_port Country|eval dest_port_count=dest_port+" - "+count|stats values(dest_port_count) as dest_port_count by Country

Re: How to edit my search to show the count of a field per country?

bayman — Tue, 29 Sep 2020 14:08:34 GMT

Thanks! Is there a way to sort the count for dest_port_count for this search?

Re: How to edit my search to show the count of a field per country?

cmerriman — Thu, 18 May 2017 20:37:29 GMT

if you put the count before the dest_port in the eval statement, it should sort it ascending. if you want it descending, i might have to do some thinking.

Re: How to edit my search to show the count of a field per country?

bayman — Tue, 29 Sep 2020 14:08:37 GMT

I'm not sure if I'm understanding.. What I have isn't working but descending would also be nice. It sorts it but by first digit only so (e.g. 1, 2, 33, 42, 5, 😎

eventtype=cisco-firewall host="" action="" src_ip="" dest_ip="" src_port="" dest_port="" transport="" service="" | iplocation src_ip|eventstats count by dest_port Country|eval dest_port_count=count+" - "+dest_port|stats values(dest_port_count) as dest_port_count by Country

Re: How to edit my search to show the count of a field per country?

cmerriman — Fri, 19 May 2017 11:49:51 GMT

sorry, i should have known it would sort alphanumerically. try this:

eventtype=cisco-firewall host="" action="" src_ip="" dest_ip="" src_port="" dest_port="" transport="" service="" 
| iplocation src_ip 
| eventstats count by dest_port Country 
| eval dest_port_count=count+" - "+dest_port 
| stats values(dest_port_count) as dest_port_count by Country 
| mvexpand dest_port_count 
| sort 0 Country - dest_port_count 
| streamstats count by Country 
| eval count_dest_port_count=count+" : "+dest_port_count 
| stats values(count_dest_port_count) as count_dest_port_count by Country 
| makemv count_dest_port_count 
| eval dest_port_count=mvfilter(match(count_dest_port_count,"-")) 
| fields - count_dest_port_count