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

[HattrickNZ]

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?

[woodcock · Accepted solution]

Add this:

| 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

[sachinkiet]

@woodcock your solutions worked for me like a charm.

[HattrickNZ]

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?

[woodcock]

Yes, leading underscore makes them invisible. Answer adjusted for min/max.

[HattrickNZ]

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*
| transpose header_field=Date 
 | 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

[tread_splunk]

Another way...

| 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* 
| untable Date shift count 
| stats max(count) min(count) avg(count) sum(count) by shift

[HattrickNZ]

I also want to be able to get the min and the max, in similar fashion.