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

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

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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...