Comma separated field value, adding a carriage return

[jravida]

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?

[gkanapathy]

You can try

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

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

[gkanapathy]

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

[lguinn2 · Accepted solution]

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]

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

[duartet]

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!

[lguinn2]

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

[somesoni2]

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

[jravida]

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?

[jravida]

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

[lguinn2]

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