Splunk Search

## How do I get the average of all the individual rows and append those values as a column dynamically?

Motivator

How do I get the average of all the individual rows (like the `addtotals` but average) and append those values as a column (like `appendcols`) dynamically

Some simple data to work with

``````  | makeresults
| eval data = "
1    2017-12    A    155749    131033    84.1;
2    2017-12    B    24869    23627    95;
3    2017-12    C    117618    117185    99.6;
"
| makemv delim=";" data
| mvexpand data
| rex field=data "(?<serial>\d)\s+(?<date>\d+-\d+)\s+(?<type>\w)\s+(?<attempts>\d+)\s+(?<successfullAttempts>\d+)\s+(?<sr>\d+)"
| fields + date serial type attempts successfullAttempts sr
| search serial=*
| eval x=round((attempts+successfullAttempts)/2,0)
``````

Gives the following:

``````    date    serial  type    attempts    successfullAttempts sr  _time   x
1   2017-12 1   A   155749  131033  84  2018-04-24 10:01:20 143391
2   2017-12 2   B   24869   23627   95  2018-04-24 10:01:20 24248
3   2017-12 3   C   117618  117185  99  2018-04-24 10:01:20 117402
``````

X gets the average of 2 columns `attempts` and `successfullAttempts` but how do I make this dynamic so that I can get the average of all columns except for the 1st column that would have the date?

I was thinking `eval x = avg(*)` but that does not work.
What I am looking for is addtotals but instead of adding all the values I want to get average. Maybe I need to use addtotals and then divide by the number of columns I added. I wonder if there is a way to count the number of columns?

Tags (3)
1 Solution
Esteemed Legend

``````| eval _col_count="0"
| eval _col_sum="0"
| foreach *
[ eval col_min = min(col_min, '<<FIELD>>')
| eval col_max = max(col_max, '<<FIELD>>')
| eval _col_count = _col_count + if(isnum('<<FIELD>>'), 1, 0)
| eval _col_sum   = _col_sum   + if(isnum('<<FIELD>>'), '<<FIELD>>', 0)]
| eval col_avg = _col_sum / _col_count
``````
Esteemed Legend

``````| eval _col_count="0"
| eval _col_sum="0"
| foreach *
[ eval col_min = min(col_min, '<<FIELD>>')
| eval col_max = max(col_max, '<<FIELD>>')
| eval _col_count = _col_count + if(isnum('<<FIELD>>'), 1, 0)
| eval _col_sum   = _col_sum   + if(isnum('<<FIELD>>'), '<<FIELD>>', 0)]
| eval col_avg = _col_sum / _col_count
``````
Motivator

tks, I did find another way
`...addtotals fieldname=Tot_avg | eval avgT_21=Tot_avg/21 | fields + _time avgT_21`

but yours is more elegant!

Can you just clarify this tho please?
Does the underscore not show these as a column?
| eval _col_count="0"
| eval _col_sum="0"

And can it be slightly adjusted to get the max and min?

Esteemed Legend

Motivator

tks - the max and min had to be adjusted slightly to handle the 1st column which is a string in this new dataset here

``````| makeresults
| eval Date="12/08", shiftA=102, shiftB=10, shiftC=200
| append
[| makeresults
| eval Date="25/08", shiftA=240, shiftB=102, shiftC=52]
| append
[| makeresults
| eval Date="15/08", shiftA=158, shiftB=15, shiftC=178]
| table Date shift*
| eval _col_count="0"
| eval _col_sum="0"
| foreach *
[ eval col_min = min(col_min, if(isnum('<<FIELD>>'), '<<FIELD>>', "n/a"))
| eval col_max = max(col_max, if(isnum('<<FIELD>>'), '<<FIELD>>', 0))
| eval _col_count = _col_count + if(isnum('<<FIELD>>'), 1, 0)
| eval _col_sum   = _col_sum   + if(isnum('<<FIELD>>'), '<<FIELD>>', 0)]
| eval col_avg = _col_sum / _col_count
``````
Motivator

I also want to be able to get the min and the max, in similar fashion. Don’t Miss Global Splunk
User Groups Week!