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
Revered Legend

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
Revered Legend

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!

Maximize the Value from Microsoft Defender with Splunk

<P style=" text-align: center; "><span class="lia-inline-image-display-wrapper lia-image-align-center" ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

<FONT size="5"><FONT size="5" color="#FF00FF">Get the latest news and updates from the Splunk Community ...