topic Search to group by Country, City having count sorted for Country and City in Splunk Search

Search to group by Country, City having count sorted for Country and City

danje57 — Wed, 11 Mar 2015 13:51:53 GMT

Hello,

I try to create stats to have all countries and cities that communicate with my servers.

I made this search:

sourcetype="syslog" deviceVendor="Apache" | iplocation ipVisitor |  eval City= if(isnull(City) OR City="", "Unknown_City", City) | stats values(City) AS CityName, count by Country | sort - count

It give me in the first column the Country, then in the second column all cities in this country and in the last third column the total count.

I would like to have such thing instead:

Country name   |   Cities Name (count) | Total Count
United States  |   New York (5)        |     10
               |   Boston (3)          |
               |   Washington (2)      |
 France        | Paris (10)            |     12
               |  Marseille (2)        |     2

I don't know how to do that to append the "(nbr)" to the City name

Re: Search to group by Country, City having count sorted for Country and City

tachifelix — Mon, 23 Mar 2015 12:13:23 GMT

i propose you to make multi-values fields for City and count. look how to use multi-values in search reference manual page 258

Re: Search to group by Country, City having count sorted for Country and City

danje57 — Mon, 23 Mar 2015 14:11:54 GMT

You mean using multikv?

Re: Search to group by Country, City having count sorted for Country and City

somesoni2 — Mon, 23 Mar 2015 18:00:28 GMT

Here you go

sourcetype="syslog" deviceVendor="Apache" | iplocation ipVisitor |  eval City= if(isnull(City) OR City="", "Unknown_City", City) | stats count by Country,City | eval City=City."(".count.")" | stats values(City) as CityName, sum(count) as "Total Count" by Country

Re: Search to group by Country, City having count sorted for Country and City

danje57 — Tue, 24 Mar 2015 12:13:44 GMT

Exactly that I need!

Many thanks!!!!