How to get TOP 3 values from STATS list()

[rajatsinghbagga]

Hello Everyone,

I am trying to get the top 3 max values of a field "elapseJobTime" for all the instances associated with the field "desc".

In order to achieve this, I first sorted the field "elapseJobTime" in descending order and then executed the STATS command to list out the values of all the respective fields I was looking for. I am getting the output in the sequence as expected but the only issue is that my search lists down 100's of values for the fields JOBNAME JOBID elapseJobTime but I want to restrict my output to just top 3 values. I tried to use HEAD 3 after the STATS but no luck.

Please assist.

| sort  -elapseJobTime
| stats  list(JOBNAME) as JOBNAME list(JOBID) as JOBID list(elapseJobTime) as elapseJobTime  by desc

The output should be like

desc               JOBNAME     JOBID         elapseJobTime  
desc1             JOB1              J1                 .31
                  JOB1              J2                 .27
                  JOB3              J3                 .27
desc2             JOB4              J4                 .71
                  JOB5              J5                 .11
                  JOB5              J6                 .10

Thank you
Rajat

[dpeukert]

The answer is simple and tricky at the same time.

You just need to add limit=3 after stats.

It seem's simple, but limit doesn't get highlighted as you might be used to. And limit is not mentioned in https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/Stats .

This is what you need in more detail:

| sort -elapseJobTime
| stats limit=3 list(JOBNAME) as JOBNAME list(JOBID) as JOBID list(elapseJobTime) as elapseJobTime by desc

[rajatsinghbagga]

Hello @dpeukert
This didn't worked. I am getting "Error in 'stats' command: The argument 'limit=3' is invalid."
Thanks Rajat

[kamlesh_vaghela · Accepted solution]

@rajatsinghbagga

You can use 'mvindex` to get a subset of the multivalue fields. Please check doc for more information.

Can you please try this?

| sort -elapseJobTime 
 | stats list(JOBNAME) as JOBNAME list(JOBID) as JOBID list(elapseJobTime) as elapseJobTime by desc
| eval JOBNAME=mvindex(JOBNAME,0,2), JOBID=mvindex(JOBID,0,2), elapseJobTime=mvindex(elapseJobTime,0,2)

https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/MultivalueEvalFunctions#mvindex.2...

Thanks

[Yepeza]

If you didn't want multiple lines. This is how I would accomplish the same thing.

| sort -elapseJobTime 
 | stats list(eval(myindex(JOBNAME,0,2))) as JOBNAME list(eval(mvindex(JOBID,0,2))) as JOBID list(eval(myindex(elapseJobTime,0,2))) as elapseJobTime by desc

[rajatsinghbagga]

This worked - Perfect !! Thank you Rajat

[rajatsinghbagga]

Thanks @kamlesh_vaghela i accepted the answer. However I noticed that I am not able to apply color codes on the output, I can see the option in the column to format based on the colour codes but when I apply it does nothing. Is there a way I can color the cells of "elapseJobTime" column based on the high and low values appearing in this column... I was able to do it with the normal table values but it seem getting these values in the list format is causing this issue.. Anything if you can suggest that will be great.. Thanks Rajat

[kamlesh_vaghela]

@rajatsinghbagga
Please check below link for table column colour formating. If it's still not helpful then post a new question with table tag from your dashboard. This practice will help other peoples to search for this kind of issue. 🙂

https://docs.splunk.com/Documentation/Splunk/7.3.1/Viz/TableFormatsFormatting

https://docs.splunk.com/Documentation/Splunk/7.3.1/Viz/TableFormatsXML#Table_format_source_code_exam...

[kamlesh_vaghela]

@rajatsinghbagga Glad to help you. Can you please accept the answer to close this question?

[kamlesh_vaghela]

@rajatsinghbagga

Can you please try this?

 | sort  -elapseJobTime
 | stats  list(JOBNAME) as JOBNAME list(JOBID) as JOBID list(elapseJobTime) as elapseJobTime  by desc
 | eval JOBNAME=mvrange(JOBNAME,0,2) ,JOBID=mvrange(JOBID,0,2) elapseJobTime=mvrange(elapseJobTime,0,2)  

[rajatsinghbagga]

Thanks @kamlesh_vaghela , Just tried it now. I am only getting the "desc" values and rest of the fields are coming blank.

[kamlesh_vaghela]

@rajatsinghbagga

Can you please share OP from below search?

| sort -elapseJobTime 
| stats list(JOBNAME) as JOBNAME list(JOBID) as JOBID list(elapseJobTime) as elapseJobTime by desc 
| eval tJOBNAME=typeof(JOBNAME),tJOBID=typeof(JOBID),telapseJobTime=typeof(elapseJobTime)

Is it possible to share a screenshot of your search result??

| sort -elapseJobTime 
| stats list(JOBNAME) as JOBNAME list(JOBID) as JOBID list(elapseJobTime) as elapseJobTime by desc 
| eval JOBNAME1=mvrange(JOBNAME,0,2) ,JOBID1=mvrange(JOBID,0,2) elapseJobTime1=mvrange(elapseJobTime,0,2) 

[rajatsinghbagga]

The output is coming as below along with the list of all the values in the fields

tJOBID  tJOBNAME    telapseJobTime
Multivalue  Multivalue  Multivalue

From the previous query with mvrange the output was

desc     JOBID  JOBNAME elapseJobTime
desc1
desc2