Splunk Search

How to group by country and concatenate the cities into one row?

maximusdm
Communicator

giving the folowing scenario:

...
| table Country City Population

>     Country       City        Population
>     Spain     Madrid      2,456,000
>     Spain     Barcelona   3,222,000
>     Spain     Valencia    1,111,000
>     England       London      9,222,000
>     England       Oxford      1,211,000

How can I display the same results but grouping by Country and concatenating the cities and population?
Something like:

Spain   Madrid(2,456,000), Barcelona(3,222,000), Valencia(1,111,000)
England London(9,222,000), Oxford(1,211,000)

Thanks for the help

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try like this

... | table Country City Population
| eval Population=City."(".Population.")"
| stats values(Population) as Population by Country delim=","
| nomv Population

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Try like this

... | table Country City Population
| eval Population=City."(".Population.")"
| stats values(Population) as Population by Country delim=","
| nomv Population

maximusdm
Communicator

wow thanks I was doing stats by Country but not getting anywhere. Never heard of nomv command.
Thank you so much.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...