topic Re: How to get the average base on line number? in Splunk Search

How to get the average base on line number?

samlinsongguo — Thu, 31 May 2018 05:27:01 GMT

Hi
I have a table as below, each time run the query it may return different result

run 1
day1 10
day2 20
day3 25

run second time
day1 10
day2 20
day3 30
day4 10

I want to calculate the average base on how many line there are. So for each table will be calculate as below
day1 10
day2 20
day3 25
avgT 18.333
calculation: (10+20+25)/3=18.333

day1 10
day2 20
day3 30
day4 10
avgT 17.5
Calculation: (10+20+30+10)/4=17.5

Any suggestion how I can achieve that?
Thanks in advance

Re: How to get the average base on line number?

niketn — Thu, 31 May 2018 14:00:34 GMT

@samlinsongguo please try the following run anywhere search example. Commands from | makeresults till | table Days Count generates the table as per the question:

|  makeresults
|  eval data="day1 10;day2 20;day3 30;day4 10"
|  makemv data delim=";"
|  mvexpand data
|  makemv data delim=" "
|  eval Days=mvindex(data,0),Count=mvindex(data,1)
|  fields - _time data
|  table Days Count
|  streamstats count as sno
|  addcoltotals Count labelfield=Days label=AvgT
|  filldown sno
|  eval Count=if(Days=="AvgT",round(Count/sno,2),Count)
|  fields - sno

If the fields in the table above are Days and Count, you need to add the following code in your existing search:

<yourCurrentSearch>
    |  streamstats count as sno
    |  addcoltotals Count labelfield=Days label=AvgT
    |  filldown sno
    |  eval Count=if(Days=="AvgT",round(Count/sno,2),Count)
    |  fields - sno

Re: How to get the average base on line number?

samlinsongguo — Fri, 01 Jun 2018 00:26:13 GMT

This is so smart Thank you so much