Splunk Search
Highlighted

Chart one value field and multiple value field within same time bucket

Contributor

Hi,
Thanks up front your time
I have duration field generated from some transaction command and I would love to draw a chart that presenting avg()- one value within same time bucket and values() - values that average is calculated. My search is :

<some search> | where duration > 10 
| bin _time span=1d| stats avg(duration) as avgDurs values(duration) as valDurs by _time,session_name | 

it is resulting following data set: (valDur has multiple values)
time| sessionname | avgDurs | valDurs
2017-04-26|s1|22.500000|12 33
2017-04-27|s2|16.500000|11 14 30

My question is how can i chart this table with
single avgDurs line (it appears on all charts, issue is on multiple fields)
and multiple values for valDurs on same chart
within same time frame

I tried couple other examples (xyseries) i found on answers and documentation. Here are my trials incase if i am missing something:

| bin _time span=1d| stats avg(duration) as avgDur values(duration) as valDur by session_name,_time 
| eval s1="AvgDurs ValDurs" |makemv s1 | mvexpand s1| eval yval=case(s1=="AvgDurs",avgDur, "ValDurs",valDur)| eval series=session_name+":"+s1 | xyseries _time, series, yval

please note that first stats without session name is closest to what is desired. it only display valDurs if there is only one value
Thanks again

0 Karma
Highlighted

Re: Chart one value field and multiple value field within same time bucket

SplunkTrust
SplunkTrust

If you wish to have a separate line for each value of duration, you may end up with bunch of lines (duration is a number and it can have any possible integer value). Any specific benefit that you see with showing all those duration values?

Also, see if something like this would work

Give this a try

<some search> | where duration > 10 
| bin _time span=1d
| eval series=strftime(_time,"%m/%d/%Y")."##".session_name."##".avgDurs
| stats avg(duration) as avgDurs by series
0 Karma
Highlighted

Re: Chart one value field and multiple value field within same time bucket

Contributor

@somesoni2 , thanks for the quick response, I think i need to make it more clear, if this is a line timechart,
within the same time window, I see avg as it is one value for each time window, however, duration values, they could be in same color (legend) too, just want to draw each.
I checked your suggestion also, it didn't bring desired output, check this one,
|bin time span=1d | eventstats avg(duration) as DailyAverage values(duration) as DurValues by _time, sessionname
|convert ctime(time)| chart values(DurValues) values(DailyAverage) over sessionname by _time useother=false

output of this wrong as well, however closer, just need need to show each value , I am wondering if we can change _time span to 1h let's say and find a way in Timechart to show it daily still?

0 Karma
Highlighted

Re: Chart one value field and multiple value field within same time bucket

Esteemed Legend

You cannot chart both (after all, what would the chart of the multi-valued field even look like; what would the units of the Y-Axis be?). But you can make 2 charts from one search by using your search as a base search:

<some search> | where duration > 10 
| bin _time span=1d
| stats avg(duration) as avgDurs values(duration) as valDurs BY _time session_name

Then, in the panel that needs to have the chart, extend the base with a reference plus this:

| timechart span=1d first(avgDurs) AS avgDurs
Highlighted

Re: Chart one value field and multiple value field within same time bucket

Contributor

@woodcock , thanks for the answer, your answer is valid as well,however I didn't want to deal with flash charts in dashboard. Thanks for your time.

Highlighted

Re: Chart one value field and multiple value field within same time bucket

Esteemed Legend

You can always UpVote, too.

0 Karma
Highlighted

Re: Chart one value field and multiple value field within same time bucket

SplunkTrust
SplunkTrust

Give this a try then

<some search> | where duration > 10 
 | bin _time span=1d| stats avg(duration) as avgDurs values(duration) as valDurs by _time,session_name | mvexpand valDurs

View solution in original post

Highlighted

Re: Chart one value field and multiple value field within same time bucket

Contributor

This works 🙂 Thanks man, I knew it is one command away 😄 mvexpand.

thanks again, wish you a great day. I am not sure if i can validate it as correct answer here.

0 Karma
Highlighted

Re: Chart one value field and multiple value field within same time bucket

SplunkTrust
SplunkTrust

Here you go.

0 Karma