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
Communicator

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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...