Splunk Search

## A table with fields as lines

Communicator

We got some events ,with an extract we got

``````Event 1 : Field1=VALUE11,Field2=VALUE12,Field3=VALUE13,...
Event 2 : Field1=VALUE21,Field2=VALUE22,Field3=VALUE23,...
Event 3 : Field1=VALUE31,Field2=VALUE32,Field3=VALUE33,...
etc...
``````

What we would like is obtain a table like this:

``````Metric    |     Field1        |      Field 2      |    Field 3 ...
----------------------------------------------------------------------------------
Avg | avg(Field1)       | avg(Field2)       | avg(Field3) ...
Median | median(Field1)    | median(Field2)    | median(Field3) ...
Sparkline | sparkline(Field1) | sparkline(Field2) | sparkline(Field3) ...
``````

What the best way to do that?
Seems impossible to find an easy way.
We tried in this direction :

``````| eval metric=mvappend("avg","median") | mvexpand metric | eval value=case(metric=="swFCPortRxC3Frames",swFCPortRxC3Frames,metric=="swFCPortRxC2Frames",swFCPortRxC2Frames) | chart latest(value) by metric swFCPortIndex
``````

Tags (2)
1 Solution
Influencer

Lots of metrics in the same event require you to know what the field names are if you're going to perform stats on them. The workaround is to use untable. Whenever you think "How the heck .." - untable is usually the answer. xyseries is its inverse.

I have data similar to yours. every 2 minute I have a feed of disk utilization that looks like :

``````Event 1: sda=0.0;sda1=0.0;sda2=0.0
Event 2: sda=10.3;sda1=1.0;sda2=9.3
``````

Here's the search that only hits the index once, and works for arbitrarily named fields :
The 1st "table" is where you include / filter the fields you are interested in - this allows far more flexibility than using wildcards in the stats commands.

``````initial search
| table _time sd*
| untable _time field value
| eval metric="raw"
| appendpipe [ | stats avg(value) as data by field | eval metric="Average" ]
| appendpipe [ | stats median(value) as data by field | eval metric="Median" ]
| appendpipe [ | stats sparkline(avg(value)) as data by field | eval metric="Sparkline" ]
| where metric !="raw"
| xyseries metric field data
``````

result :

You can save yourself a lot of headache by transposing the columns and rows

``````initial search
| table _time sd*
| untable _time field value
| chart avg(value) as Average median(value) as Median sparkline(avg(value)) as sparkline by field
``````

Communicator

You 're right, but i needed the untable() function that i didn't know to do that 🙂

Influencer

Lots of metrics in the same event require you to know what the field names are if you're going to perform stats on them. The workaround is to use untable. Whenever you think "How the heck .." - untable is usually the answer. xyseries is its inverse.

I have data similar to yours. every 2 minute I have a feed of disk utilization that looks like :

``````Event 1: sda=0.0;sda1=0.0;sda2=0.0
Event 2: sda=10.3;sda1=1.0;sda2=9.3
``````

Here's the search that only hits the index once, and works for arbitrarily named fields :
The 1st "table" is where you include / filter the fields you are interested in - this allows far more flexibility than using wildcards in the stats commands.

``````initial search
| table _time sd*
| untable _time field value
| eval metric="raw"
| appendpipe [ | stats avg(value) as data by field | eval metric="Average" ]
| appendpipe [ | stats median(value) as data by field | eval metric="Median" ]
| appendpipe [ | stats sparkline(avg(value)) as data by field | eval metric="Sparkline" ]
| where metric !="raw"
| xyseries metric field data
``````

result :

You can save yourself a lot of headache by transposing the columns and rows

``````initial search
| table _time sd*
| untable _time field value
| chart avg(value) as Average median(value) as Median sparkline(avg(value)) as sparkline by field
``````

Legend

I agree with @zeroactive. But this may do what you want

``````yoursearchhere
| stats avg(Field*) as Field*
| eval Metric="Average"
| append [ search yoursearchhere
| stats median(Field*) as Field*
| eval Metric="Median" ]
| append [ search yoursearchhere
| stats sparkline(count(Field*)) as Field*
| eval Metric="Sparkline" ]
| table Metric Field*
``````
Influencer

The sparklines will be the count of the samples, and wont plot the actual values.

Path Finder

Odd way to look at data... Why not do the table by Field ID, with columns for Avg, Median, and Sparkline?

Register for .conf21 Now! Go Vegas or Go Virtual!

### How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20. Learn More or Register Now >

Get Updates on the Splunk Community!