Splunk Search

How to calculate average of specific fields?

Ashwini008
Builder

Hi,

I am formatting data as required and getting it in below format. Now I want to calculate average of only highlighted fields in green color i.e. Q1_score PREPAID,Q2_score PREPAID,Q1_score CONSUMER so on

Example Q1_score CONSUMER ,Count by segment value should be 4.50

Ashwini008_0-1679334983161.png

This is last piece of my query

 

 

| addcoltotals COUNT* Q1* Q2* Q3* Total 
| eval Month=coalesce(Month, "Count by Segment") 

 

 

 

 Please suggest

Labels (2)
Tags (4)
0 Karma
1 Solution

Ashwini008
Builder

@somesoni2 @PickleRick  Below code was my fix 

| eval var=1 
| addcoltotals COUNT* Q1* Q2* Q3* var Total 
| foreach Q1_Score* 
    [ eval '<<FIELD>>' = round('<<FIELD>>'/var,2)] 
| foreach Q2_Score* 
    [ eval '<<FIELD>>' = round('<<FIELD>>'/var,2)] 

 

View solution in original post

0 Karma

somesoni2
Revered Legend

If the field names are static, you can use eventstats to calculate average of those specific fields like this

Your current search generating those columns
| eventstats avg("Q1_score PREPAID") as "Avg Q1_score PREPAID" ,avg("Q2_score PREPAID") as "Avg Q2_score PREPAID",avg("Q1_score CONSUMER") as "Avg Q1_score CONSUMER"
0 Karma

Ashwini008
Builder

@somesoni2  This isn't giving me any output,I don't see any additional field being added to the result.

Also, I want to show the average for Q1_score PREPAID & so on in  "count by segment" row i.e. at the bottom row

| eventstats avg("Q1_score PREPAID") as "Avg Q1_score PREPAID" ,avg("Q2_score PREPAID") as "Avg Q2_score PREPAID",avg("Q1_score CONSUMER") as "Avg Q1_score CONSUMER"

 

0 Karma

Ashwini008
Builder

@somesoni2 @PickleRick  Below code was my fix 

| eval var=1 
| addcoltotals COUNT* Q1* Q2* Q3* var Total 
| foreach Q1_Score* 
    [ eval '<<FIELD>>' = round('<<FIELD>>'/var,2)] 
| foreach Q2_Score* 
    [ eval '<<FIELD>>' = round('<<FIELD>>'/var,2)] 

 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

To be fully honest, I don't understand what you want to do. Especially that I don't see any average that should come down to 4.50

But as a general rule, sometimes it's better to do some additional calculations earlier and then aggregate the results in the end instead of - for example - adding totals and then trying to do additional stuff since totals can get in the way.

 

0 Karma
Get Updates on the Splunk Community!

Demo Day: Strengthen Your SOC with Splunk Enterprise Security 8.1

Today’s threat landscape is more complex than ever. Security operation centers (SOCs) are overwhelmed with ...

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...