Splunk Search

How to get TOP 3 values from STATS list()

rajatsinghbagga
Explorer

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

Tags (1)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

View solution in original post

dpeukert
Explorer

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

0 Karma

rajatsinghbagga
Explorer

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

View solution in original post

rajatsinghbagga
Explorer

This worked - Perfect !! Thank you Rajat

0 Karma

rajatsinghbagga
Explorer

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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)  
0 Karma

rajatsinghbagga
Explorer

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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) 
0 Karma

rajatsinghbagga
Explorer

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
0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!