Splunk Search

How to get the average base on line number?

Communicator

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

0 Karma
1 Solution

SplunkTrust
SplunkTrust

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

Communicator

This is so smart Thank you so much