Splunk Search

How to calculate total when aggregating using stats max(field)?

LearningGuy
Builder

How to calculate total when aggregating using stats max(field)?
Thank you for your help. 

Max Total Score is the total score of maximum score for each Score field when aggregating all rows using stats: max(Score1), max(Score2), max(Score3).   
TotalScore is the total of each Score field for each row (without aggregation) 

This is the output I need

ClassNameSubjectTotalScoreScore1Score2  Score3Max TotalScore
ClassAgroupedgrouped240859580260


My Splunk Search

 

| index=scoreindex   
| stats values(Name) as Name, values(Subject) as Subject,  max(TotalScore) as TotalScore, max(Score1) as Score1, max(Score2) as Score2, max(Score3) as Score3 by Class
| table Class Name, Subject, Total Score, Score1, Score2, Score3

 


I think my search below is going to display the following.

ClassNameSubjectTotalScoreScore1Score2  Score3
ClassAName1 Name2 Name3Math English240859580


This is the whole data in table format from scoreindex

ClassNameSubjectTotalScoreScore1Score2  Score3
ClassAName1Math170604070
ClassAName1English195856050
ClassAName2Math175506065
ClassAName2English240809070
ClassAName3Math170406070
ClassAName3English230559580
Labels (1)
0 Karma
1 Solution

_JP
Contributor

How is this different than what you asked here:

Solved: Re: How to display other fields on the same row wh... - Splunk Community

It's nearly the same question, and that linked post seems to have the answer to this post in it.

View solution in original post

LearningGuy
Builder

I tested your suggestion and it worked even on real data with multiple "Classes" (Class A, B, C).
I thought eval would not work after passing "stats" pipe, so I tried to sum (Score1+Score2+Score3) within the stats, but it would not let me.   I accepted this as a solution.
Could you give an explanation why it worked after passing "stats" function?
Thank you so much

0 Karma

richgalloway
SplunkTrust
SplunkTrust

I'm curious about why you thought eval would not work after stats.

There's nothing particularly magical about stats.  It's a transforming command so only the fields used in the command are available to later commands.  They are still fields, however, and can be processed as such.  Note that some stats functions produce multi-value fields, which don't work well in all commands so they may require additional processing.

---
If this reply helps you, Karma would be appreciated.

LearningGuy
Builder

Hello,

Sorry I made a mistake on my original post. I just updated my question
I am looking for Max Total Score, a total score after the aggregation (260), before the aggregation Max(TotalScore) is 240 only for 1 row.     
Please suggest. Thank you

Max Total Score
 = Max(Score1) + Max(Score2) + Max(Score3) = 85+95+80 = 260

This is the output I need

ClassNameSubjectTotalScoreScore1Score2  Score3Max TotalScore
ClassAgroupedgrouped240859580260
0 Karma

richgalloway
SplunkTrust
SplunkTrust

You *think* your search will produce that output?  Why not run the search and remove the doubt?

To calculate a total, use the sum function.

| index=scoreindex   
| stats values(Name) as Name, values(Subject) as Subject,  sum(TotalScore) as TotalScore, max(Score1) as Score1, max(Score2) as Score2, max(Score3) as Score3, max(TotalScore) as "Max TotalScore" by Class
| table Class, Name, Subject, TotalScore, Score1, Score2, Score3, "Max TotalScore"

 

---
If this reply helps you, Karma would be appreciated.

LearningGuy
Builder

Hello,

Sorry I made a mistake on my original post. I just updated my question
I am looking for Max Total Score, a total score after the aggregation
Please suggest. Thank you

Max Total Score
 = Max(Score1) + Max(Score2) + Max(Score3) = 85+95+80 = 260

This is the output I need

ClassNameSubjectTotalScoreScore1Score2  Score3Max TotalScore
ClassAgroupedgrouped240859580260
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Something like that can be done using eval.

| index=scoreindex   
| stats values(Name) as Name, values(Subject) as Subject,  sum(TotalScore) as TotalScore, max(Score1) as Score1, max(Score2) as Score2, max(Score3) as Score3 by Class
| eval "Max TotalScore"=Score1 + Score2 + Score3
| table Class, Name, Subject, TotalScore, Score1, Score2, Score3, "Max TotalScore"

 

---
If this reply helps you, Karma would be appreciated.

_JP
Contributor

How is this different than what you asked here:

Solved: Re: How to display other fields on the same row wh... - Splunk Community

It's nearly the same question, and that linked post seems to have the answer to this post in it.

Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...