Splunk Search

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

LearningGuy
Builder

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

ClassNameSubjectTotalScoreScore1Score2  Score3
ClassAName2English240809070


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)
Tags (1)
1 Solution

_JP
Contributor

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:

class_max_TotalScore.png

View solution in original post

LearningGuy
Builder

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-ma...

0 Karma

_JP
Contributor

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:

class_max_TotalScore.png

LearningGuy
Builder

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

ClassNameSubjectTotalScoreScore1Score2  Score3
ClassAName2English240809070



Thank you

0 Karma

_JP
Contributor

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:

_JP_1-1696974281322.png

 

 

 

 

Get Updates on the Splunk Community!

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...