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

[danje57]

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

[somesoni2 · Accepted solution]

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

[danje57]

Exactly that I need!

Many thanks!!!!

[danje57]

You mean using multikv?

[tachifelix]

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