Splunk Search

How can I calculate the average after calculating the total for the fields in a table?

rajeswarir
New Member
 IDS          Entry Time in ms   Exit Time in ms
   1                30                            40
   2                15                            10
   3                05                            11
   4                10                            05
   5                08                            09
Total Time  68                             75
Average Time13.6                     15 

I want the result in the above way table data to be displayed i.e Total in one row to be calculated and average in one row to be calculated. But for me, it is displaying average by including total time as well so I am getting answer Average 27.2 for Entry Time and 30 for Exit time.

Can anyone help me to get the average correctly by neglecting Total Time of Entry and Exit Time.

0 Karma
1 Solution

somesoni2
Revered Legend

Try like this

source="test.log" |table IDS,Entry Time in ms,Exit Time in ms
|appendpipe[| stats sum(*) as *|eval IDS="Total Time"]
|appendpipe[where IDS!="Total Time" |stats avg(*) as *|eval IDS="Average Time" ]

View solution in original post

somesoni2
Revered Legend

Try like this

source="test.log" |table IDS,Entry Time in ms,Exit Time in ms
|appendpipe[| stats sum(*) as *|eval IDS="Total Time"]
|appendpipe[where IDS!="Total Time" |stats avg(*) as *|eval IDS="Average Time" ]

rajeswarir
New Member

Thanks. This is what i expected as a result.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Hi @rajeswarir,

Try this

"your base search to list table" 
| appendpipe [stats sum("Entry Time in ms") as "Entry Time in ms",sum("Exit Time in ms") as "Exit Time in ms"|eval IDS="Total"]
| appendpipe [stats avg("Entry Time in ms") as "Entry Time in ms",avg("Exit Time in ms") as "Exit Time in ms"|eval IDS="Average"]
Happy Splunking!
0 Karma

rajeswarir
New Member

This Search query is giving me result of Total and Average in seperate column. but i want Total and Average should get calculated below the fields column. For Eg: Total Time and Average Time should get calculated in the same Entry Time in ms column and Total Time and Average Time should get calculated in the same Exit Time in ms column.

Is it possible? bcoz i tried with the above query that i have added but that does not gives the expected result for average.

0 Karma

rajeswarir
New Member

This is the query i have used to get the result

source="test.log" |table IDS,Entry Time in ms,Exit Time in ms|addcoltotals Entry Time in ms,Exit Time in ms,Total labelfield=IDS|addtotals Entry Time in ms Exit Time in ms|appendpipe[stats avg(*) as *|eval IDS="Average Time"]

0 Karma
Get Updates on the Splunk Community!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

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

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