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

[bayman]

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

[cmerriman · Accepted solution]

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

[bayman]

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

[cmerriman]

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.

[bayman]

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

[cmerriman]

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

[rjthibod]

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

[bayman]

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