Splunk Search

Calculate total and average and display the results in single column

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

SplunkTrust
SplunkTrust

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

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