topic Re: A table with fields as lines in Splunk Search

A table with fields as lines

timmalos — Fri, 13 Sep 2013 14:13:51 GMT

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

Thx for your help !

Re: A table with fields as lines

zeroactive — Fri, 13 Sep 2013 14:44:45 GMT

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

Re: A table with fields as lines

lguinn2 — Fri, 13 Sep 2013 16:32:59 GMT

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*

Re: A table with fields as lines

jonuwz — Fri, 13 Sep 2013 18:11:35 GMT

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

Re: A table with fields as lines

jonuwz — Fri, 13 Sep 2013 19:00:53 GMT

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

Re: A table with fields as lines

timmalos — Mon, 16 Sep 2013 07:37:34 GMT

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