Splunk Search

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

danje57
Path Finder

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

1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
Path Finder

Exactly that I need!

Many thanks!!!!

0 Karma

danje57
Path Finder

You mean using multikv?

0 Karma

tachifelix
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...