Splunk Search

Comma separated field value, adding a carriage return

jravida
Communicator

Hi folks,

I think this should be easy, but it is hard to search for the solution because the terms I'm using are broad.

I have a table, and one of the columns contains field value(s) that are separated by a comma and a space. Word wrapping them looks ugly, but If I don't then they disappear off to the right of the panel, when there are more than 3-4 values in one field.

I want them to each have their own line in the panel, instead of one after another wrapping when needed. Right now I'm using "eval hosts replace(hosts,"," ,", ")" which is only adding a space after the commas so it is easier to read.

So in the table it looks like:
appsvr-01, appsvr-32, exchsvr-32

But I want it to look like (comma is optional at this point):
appsvr-01
appsvr-32
exchsvr-32

Is there an easy way to do this?

Tags (4)
0 Karma
1 Solution

lguinn2
Legend

Try this

index = servers 
| where host != "N/A" 
| makemv delim="," hosts
| stats count by hosts status
| stats list(hosts) as hosts by status count
| table hosts status count

Or

index = servers 
| where host != "N/A" 
| makemv delim="," hosts
| stats count by hosts status
| stats list(hosts) as hosts list(count) as count by status
| table hosts count status

Or even...

index = servers 
| where host != "N/A" 
| makemv delim="," hosts
| stats count by hosts status
| eval host_count = hosts . "=" . tostring(count)
| stats list(host_count) as Hosts by status

View solution in original post

gkanapathy
Splunk Employee
Splunk Employee

You can try

... | eval hosts=split(hosts,",") | ...

to turn it into a multivalue field and see if that renders more how you need it.

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

that is, instead of the eval hosts=replace(...) that you currently have.

0 Karma

lguinn2
Legend

Try this

index = servers 
| where host != "N/A" 
| makemv delim="," hosts
| stats count by hosts status
| stats list(hosts) as hosts by status count
| table hosts status count

Or

index = servers 
| where host != "N/A" 
| makemv delim="," hosts
| stats count by hosts status
| stats list(hosts) as hosts list(count) as count by status
| table hosts count status

Or even...

index = servers 
| where host != "N/A" 
| makemv delim="," hosts
| stats count by hosts status
| eval host_count = hosts . "=" . tostring(count)
| stats list(host_count) as Hosts by status

jravida
Communicator

I tried the first one and it worked great. I have a much better understanding as well. Thank you so much!

duartet
Path Finder

The makemv command combined with the mvexpand is awesome when you have something like this:

column1 | column2
car | tire, door

and you want to transform it to:

column1 | column2
car | tire
car | door

Thanks for the help!

0 Karma

lguinn2
Legend

What if you do this

index = servers 
| where host != "N/A" 
| eval hosts = replace(hosts,"," ,"\n") 
| stats count by hosts status 
| sort -hosts

Or this

index = servers 
| where host != "N/A" 
| makemv delim="," hosts
| stats count by hosts status
| sort -hosts
0 Karma

somesoni2
SplunkTrust
SplunkTrust

On second solution, try moving the '|makemv delim="," hosts' line to the end (after sort)

0 Karma

jravida
Communicator

The second one lists the status, then the host, but a separate on for each line. I've been asked to put the status in one column, along with all the hosts (which are currently separated by comma).

The first one is what I've been trying to do, but the replace command just puts (backslash)\n as the delimiter, literally. Is there a way to call newline in replace command?

0 Karma

jravida
Communicator

index = servers | where host != "N/A" | eval hosts replace(hosts,"," ,", ") | stats count by status hosts | table hosts status | sort -hosts

0 Karma

lguinn2
Legend

There is almost certainly an easy way to do this, but we need to see the original search to figure it out...

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...