topic Re: How to split multiple values in a column and make into row in Splunk Search

How to split multiple values in a column and make into row

jgcsco — Mon, 22 May 2017 16:40:51 GMT

I have the following search result which has multiple values in a cell:

I would like to format the result into the following:

_time Null0 TenGig0 TenGig39 ...
<273276296> <277830477>...
<0> <273256478> <277810817>...
Is there a way to do this? I have tried "transpose" which messed up the values.

Thanks,

Re: How to split multiple values in a column and make into row

vasanthmss — Tue, 29 Sep 2020 14:10:09 GMT

Hello,

Try something like this,

your base search.. ... | eval temp=mvzip(interface_name,bytes_received,"###") | mvexpand temp | rex field=temp "(?<interface_name>.*)###(?<bytes_received>.*)" | fields - temp | xyseries _time, interface_name,bytes_received

Description:

Use interface_name,bytes_received fields and make a single field called temp by using mvzip. use mvexpand to populate the actual values, extract the fields using rex. use xyseries to populate the values.

Make sure the 2 field names are correct (interface_name,bytes_received )

Re: How to split multiple values in a column and make into row

DalJeanis — Mon, 22 May 2017 17:18:57 GMT

Updated - the mvzip/mvexpand/rex combination is unnecessary art. Use mvrange/mvexpand/eval, especially if there are more than two fields to be correlated.

| makeresults 
| eval mydata="1,Null0,0 1,TenGig/0,273 1,TenGig/1,511 2,Null0,0 2,TenGig/0,277 2,TenGig/1,512" 
| makemv mydata 
| mvexpand mydata 
| makemv delim="," mydata
| eval _time=now()+tonumber(mvindex(mydata,0)) | eval interface=mvindex(mydata,1) | eval bytes=mvindex(mydata,2)
| table _time interface bytes
| stats list(interface) as interface_name list(bytes) as bytes_received by _time
| rename COMMENT as "The above just generates some test data"

| eval myFan=mvrange(0,mvcount(interface_name))    
| mvexpand myFan
| eval interface_name =mvindex(interface_name,myFan)
| eval  bytes_received =mvindex(bytes_received,myFan)
| table _time interface_name bytes_received
| eval {interface_name} = bytes_received
| fields - interface_name bytes_received
| stats values(*) as * by _time

Here's another way...

| makeresults | eval mydata="1,Null0,0 1,TenGig/0,273 1,TenGig/1,511 2,Null0,0 2,TenGig/0,277 2,TenGig/1,512" 
| makemv mydata | mvexpand mydata | makemv delim="," mydata
| eval _time=now()+tonumber(mvindex(mydata,0)) | eval interface=mvindex(mydata,1) | eval bytes=mvindex(mydata,2)
| table _time interface bytes
| stats list(interface) as interface_name list(bytes) as bytes_received by _time
| rename COMMENT as "The above just generates some test data"

| eval mystuff=mvzip(interface_name,bytes_received,"=")
| table _time mystuff
| mvexpand mystuff
| rex field=mystuff "^(?<interface_name>[^=]+)=(?<bytes_received>.*)"
| table _time interface_name bytes_received
| eval {interface_name} = bytes_received
| fields - interface_name bytes_received
| stats values(*) as * by _time

Re: How to split multiple values in a column and make into row

jgcsco — Mon, 22 May 2017 17:19:40 GMT

Thank you so much for your quick response and solution. Works just like what I am looking for.

By the way, do you have any great idea to calculate the incremental of bytes_received between each sampling time? Right now, the number is accumulative.

Re: How to split multiple values in a column and make into row

jgcsco — Tue, 23 May 2017 00:21:47 GMT

Thanks for the detailed steps, vasanthmss's option is more simple and straight forward.