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!

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