Splunk Search

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

Path Finder

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

alt text

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,

1 Solution

Motivator

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 interfacename,bytesreceived 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 (interfacename,bytesreceived )

View solution in original post

SplunkTrust
SplunkTrust

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

Path Finder

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

Motivator

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 interfacename,bytesreceived 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 (interfacename,bytesreceived )

View solution in original post

Path Finder

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.

0 Karma