Splunk Search

Calculate total and average and display the results in single column

Rajik31
New Member

Having the following search result, I need to calculate total for few rows and average for few rows and both results should be displayed in same column as below:

i_count 310 914 168 31  166 263 
r_count 931 900 310 25  231 752 
avg_score  4.67   4.36   4.59   4.5   4.76   4.44 
prev_score 4.77   4.58   4.44   4.0   4.67   4.63 

for the above table , i have to calculate sum for row 1 &2, whereas calculate average for row 3 & 4 in a new column.
the expected result is like
Total
1852
3149
4.55
4.51

I tried using addtotals, but its performing addition for all the rows. Is it possible to add only specific rows and calculate average for specific rows?

Tags (4)
0 Karma

mayurr98
Super Champion

well you should try,

| transpose header_field=data 
| addcoltotals i_count r_count 
| eventstats avg(avg_score) as avgscore avg(prev_score) as prevscore 
| eval avg_score=if(like(avg_score,"%%"),avg_score,avgscore),prev_score=if(like(prev_score,"%%"),prev_score,prevscore) 
| table i_count r_count avg_score prev_score 
| transpose 
| rename row3 as ...

here data is the column name of first column.

let me know if this helps!

0 Karma

KailA
Contributor

Hello,

You can try using transpose and some eval calculation after.
And when you are done with your math you can transpose it back.

you will be able to do something like that after the first transpose :

| eval Total = i_count + r_count
| eval Average = (avg_score + prev_score) / 2
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 ...