Reformat a field from multiple rows down to one row

[dglinder]

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?

[somesoni2 · Accepted solution]

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]

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.

[yannK]

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