topic Re: How to display other fields on the same row when aggregating using stats max(field)? in Splunk Search

How to display other fields on the same row when aggregating using stats max(field)?

LearningGuy — Tue, 10 Oct 2023 20:21:52 GMT

How to display other fields on the same row when aggregating using stats max(field)?
Thank you for your help.
For example:
I am trying to display the same row that has the highest TotalScore=240

Class	Name	Subject	TotalScore	Score1	Score2	Score3
ClassA	Name2	English	240	80	90	70

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.

Class	Name	Subject	TotalScore	Score1	Score2	Score3
ClassA	Name1 Name2 Name3	Math English	240	85	95	80

This is the whole data in table format from scoreindex

Class	Name	Subject	TotalScore	Score1	Score2	Score3
ClassA	Name1	Math	170	60	40	70
ClassA	Name1	English	195	85	60	50
ClassA	Name2	Math	175	50	60	65
ClassA	Name2	English	240	80	90	70
ClassA	Name3	Math	170	40	60	70
ClassA	Name3	English	230	55	95	80

Re: How to display other fields on the same row when aggregating using stats max(field)?

_JP — Tue, 10 Oct 2023 21:07:09 GMT

I am understanding that for your results you want to see who (Names) has the highest TotalScore for all classes.

If my understanding is correct, here is one way you could structure that SPL. I used makeresults to recreate your example table of data (thanks - that table helped me see what you're looking at):

| makeresults format=csv data="Class,Name,Subject,TotalScore,Score1,Score2,Score3 ClassA,Name1, Math, 170, 60 ,40 ,70 ClassA,Name1, English ,195, 85, 60, 50 ClassA,Name2, Math, 175, 50, 60, 65 ClassA,Name2, English ,240, 80, 90, 70 ClassA,Name3, Math, 170, 40, 60 ,70 ClassA,Name3, English ,230, 55, 95, 80" | eventstats max(TotalScore) as max_TotalScore by Class, Subject | where TotalScore=max_TotalScore | table Class Name, Subject, TotalScore, Score1, Score2, Score3

I used the eventstats command to determine the highest scores by Class and Subject. Essentially this will add a new field on each row called max_TotalScore. I then use where to only keep the rows (i.e. Names) for the ones where the TotalScore equals this max_TotalScore - that means this person is the one with the highest score.

Results:

Re: How to display other fields on the same row when aggregating using stats max(field)?

LearningGuy — Tue, 10 Oct 2023 21:25:07 GMT

Hello,
I only need 1 row displaying all fields that has the Max TotalScore of 240

Class	Name	Subject	TotalScore	Score1	Score2	Score3
ClassA	Name2	English	240	80	90	70

Thank you

Re: How to display other fields on the same row when aggregating using stats max(field)?

_JP — Tue, 10 Oct 2023 21:46:21 GMT

In this case if you just care about the max TotalScore, you can just reverse-sort your data by TotalScore and use head to grab to first (aka the max) one:

| makeresults format=csv data="Class,Name,Subject,TotalScore,Score1,Score2,Score3 ClassA,Name1, Math, 170, 60 ,40 ,70 ClassA,Name1, English ,195, 85, 60, 50 ClassA,Name2, Math, 175, 50, 60, 65 ClassA,Name2, English ,240, 80, 90, 70 ClassA,Name3, Math, 170, 40, 60 ,70 ClassA,Name3, English ,230, 55, 95, 80" | sort -TotalScore | head 1 | table Class Name, Subject, TotalScore, Score1, Score2, Score3

Here's a screenshot:

Re: How to display other fields on the same row when aggregating using stats max(field)?

LearningGuy — Wed, 11 Oct 2023 15:01:59 GMT

Thank you for your help for this question
Can you also help this related question? Thank you so much
https://community.splunk.com/t5/Splunk-Search/How-to-calculate-total-when-aggregating-using-stats-max-field/m-p/660403#M227978