Splunk Search

Reformat a field from multiple rows down to one row

dglinder
Path Finder

I want to produce a search that returns basic information about our indexes, specifically the index name, the splunk_server(s) that have the index data, and the hosts that provided the data.

Right now I have this search:

index=* | dedup index splunk_server host | table index splunk_server host

This returns a very large table where each row contains a single "host" entry:

index         splunk_server     host
index_a       server001         client001
index_a       server001         client002
index_a       server001         client003
index_a       server002         client001
index_a       server001         client002
index_b       server001         client001

What I would like is to group all of the hosts together when the index and splunk_server match.

index         splunk_server     host
index_a       server001         client001, client002, client003
index_a       server002         client001, client002
index_b       server001         client001

Adding the mvcombine option helps:

 index=* | dedup index splunk_server host | sort index splunk_server | mvcombine delim="," host | table index splunk_server host

..but, there are still many places where the index+splunk_server are the same but the hosts between these lines aren't combined.

Any ideas?

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try to use following.

|metasearch host=* | stats count by host, index, splunk_server| stats values(host) as source by index, splunk_server

This will definitely be faster. You can use list(host) instead of values(host), if you want to keep the order in which the hosts were added/sent data to index.

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Try to use following.

|metasearch host=* | stats count by host, index, splunk_server| stats values(host) as source by index, splunk_server

This will definitely be faster. You can use list(host) instead of values(host), if you want to keep the order in which the hosts were added/sent data to index.

dglinder
Path Finder

Thanks, I had looked at the metasearch output but passed it over in favor of my usual search commands. And it is definitely much faster than my initial search, order of 10x at least on my tests.

0 Karma

yannK
Splunk Employee
Splunk Employee

I confirm values(myfield) is your friend.
Each unique value will be displayed one.

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 ...