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?

Tags (2)
1 Solution
Legend

@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!!!"
Legend

@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!!!"
Communicator

This is so smart Thank you so much

Get Updates on the Splunk Community!

#### Infographic provides the TL;DR for the 2023 Splunk Career Impact Report

We’ve been shouting it from the rooftops! The findings from the 2023 Splunk Career Impact Report showing that ...

#### Splunk Lantern | Getting Started with Edge Processor, Machine Learning Toolkit ...

Splunk Lantern is Splunk’s customer success center that provides advice from Splunk experts on valuable data ...

#### Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...