Splunk Search

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

jgcsco
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

vasanthmss
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 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 )

V

View solution in original post

DalJeanis
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

jgcsco
Path Finder

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

vasanthmss
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 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 )

V

jgcsco
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
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...