Splunk Search

How to calculate average based on 2 fields and group the result by values in the third field?

dominhthe110
Explorer

Hi everyone, 

This is the first time, I've used Splunk.  I have the data like this:

ORDER_IDPRICEGROUP
0000110A
0000220B
0000320A
0000415B
0000523.3C

 

And I want to calculate the average price for each group, which returns the result like this:

GROUP AVERAGE_PRICE
A15
B17.5
C23.3

 

In order to do this average calculation, I know that I have to calculate the total number of ORDER_ID and the sum of PRICE for each GROUP. But I don't know how to perform this calculation in SPL. Please kindly guild me through this. 

Regard.

Labels (3)
0 Karma
1 Solution

isoutamo
SplunkTrust
SplunkTrust

Hi

| makeresults
| eval _raw="ORDER_ID, PRICE, GROUP
00001,10,A
00002,20,B
00003,20,A
00004,15,B
00005,23.3,C"
| multikv forceheader=1
| stats avg(PRICE) AS AVERAGE_PRICE by GROUP

View solution in original post

isoutamo
SplunkTrust
SplunkTrust

Hi

| makeresults
| eval _raw="ORDER_ID, PRICE, GROUP
00001,10,A
00002,20,B
00003,20,A
00004,15,B
00005,23.3,C"
| multikv forceheader=1
| stats avg(PRICE) AS AVERAGE_PRICE by GROUP

dominhthe110
Explorer

Thank you a lot. It worked.
But I just wonder is there any other ways that I can calculate it step by step not using the avg() function? 
Assump that the data is indexed so that we don't need the "eval _raw="..." line.

Tags (1)
0 Karma

spammenot66
Contributor

the same solution would work using indexed data. you just have to apply the same concept. He's proved out his point by creating sandbox data basedon your initial question. BTW, i would say his response is the best solution. I wouldn't recommend over complicating it. 

isoutamo
SplunkTrust
SplunkTrust

Yes it works without first lines (like @spammenot66 @said), which only create data to do actual calculation (last line). Just use it with your indexed data. 
r. Ismo

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...