Splunk Search

How to calculate the average for all values in a single column?

jpolcari
Communicator

This one seems pretty straight forward, but I haven't been able to find an answer anywhere. I'm looking to calculate the average for all the values in a single column, kind of like addcoltotals. Example of what I am trying to achieve:

User        Time(Hours)
user1       1.2
user2       2.0
user3       0.5
            (average here)
0 Karma
1 Solution

lguinn2
Legend

You can do it like this

yoursearchhere
| stats sum(Time) as totalTime by User
| appendpipe [ stats avg(totalTime) as totalTime | eval User = "Average Time" ]
| rename totalTime as "Time (Hours)"

The appendpipe commands examines the results in the pipeline, and in this case, calculates an average. The results of the appendpipe command are added to the end of the existing results. Notice that I used the same field names within the appendpipe command, so that the new results would align in the same columns.

View solution in original post

jotne
Builder

I did found an better way to do this.

 

 

| makeresults | eval value = "1.2 2.5 0.5" | makemv value | mvexpand value
| eval count=1
| addcoltotals
| eval value=if(count>1,value/count,value)
| fields - count

 

 

Result

 

 

_time	        value
2022-06-04 10:08:55	1.2
2022-06-04 10:08:55	2.5
2022-06-04 10:08:55	0.5
 	                1.4

 

 

ddrillic
Ultra Champion

You would think that there would be a "family" of commands similar to addcoltotals, such as addcolaverage...

0 Karma

jpolcari
Communicator

That is what I was hoping for. Maybe one day!

0 Karma

lguinn2
Legend

You can do it like this

yoursearchhere
| stats sum(Time) as totalTime by User
| appendpipe [ stats avg(totalTime) as totalTime | eval User = "Average Time" ]
| rename totalTime as "Time (Hours)"

The appendpipe commands examines the results in the pipeline, and in this case, calculates an average. The results of the appendpipe command are added to the end of the existing results. Notice that I used the same field names within the appendpipe command, so that the new results would align in the same columns.

jpolcari
Communicator

Thank you! That is exactly what I needed.

0 Karma

jperezes
Path Finder

I am trying to do something similar, but this solution is not working to me.
avg(totalTime) returns totalTime as it is the average of a single value. So I end up with a table for total times by user instead of the average by user.
I had to add the total number of occurrences and at the end divide the total value for the number of occurrences per user.

rgds,
Juan

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 ...