Splunk Search

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

HattrickNZ
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

woodcock
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

woodcock
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

sachinkiet
Explorer

@woodcock your solutions worked for me like a charm.

HattrickNZ
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

woodcock
Esteemed Legend

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

HattrickNZ
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

tread_splunk
Splunk Employee
Splunk Employee

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

HattrickNZ
Motivator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...