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!

Enterprise Security Content Update (ESCU) v3.54.0

The Splunk Threat Research Team (STRT) recently released Enterprise Security Content Update (ESCU) v3.54.0 and ...

Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...

New Learning Videos on Topics Most Requested by You! Plus This Month’s New Splunk ...

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...