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?

0 Karma
1 Solution

Esteemed Legend

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

View solution in original post

Esteemed Legend

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

View solution in original post

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?

0 Karma

Esteemed Legend

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

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*
| 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
0 Karma

Motivator

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!