topic Re: How to get TOP 3 values from STATS list() in Splunk Search

How to get TOP 3 values from STATS list()

rajatsinghbagga — Fri, 20 Sep 2019 10:37:22 GMT

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

Re: How to get TOP 3 values from STATS list()

kamlesh_vaghela — Fri, 20 Sep 2019 11:06:25 GMT

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

Re: How to get TOP 3 values from STATS list()

rajatsinghbagga — Fri, 20 Sep 2019 11:22:57 GMT

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

Re: How to get TOP 3 values from STATS list()

kamlesh_vaghela — Fri, 20 Sep 2019 11:38:40 GMT

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

Re: How to get TOP 3 values from STATS list()

rajatsinghbagga — Fri, 20 Sep 2019 11:49:26 GMT

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

Re: How to get TOP 3 values from STATS list()

kamlesh_vaghela — Fri, 20 Sep 2019 12:13:05 GMT

@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.28MVFIELD.2CSTARTINDEX.2C_ENDINDEX.29

Thanks

Re: How to get TOP 3 values from STATS list()

dpeukert — Fri, 20 Sep 2019 13:26:48 GMT

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

Re: How to get TOP 3 values from STATS list()

rajatsinghbagga — Sun, 22 Sep 2019 23:43:08 GMT

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

Re: How to get TOP 3 values from STATS list()

rajatsinghbagga — Sun, 22 Sep 2019 23:47:41 GMT

This worked - Perfect !! Thank you Rajat

Re: How to get TOP 3 values from STATS list()

kamlesh_vaghela — Mon, 23 Sep 2019 06:29:24 GMT

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

Re: How to get TOP 3 values from STATS list()

rajatsinghbagga — Mon, 23 Sep 2019 07:39:10 GMT

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

Re: How to get TOP 3 values from STATS list()

kamlesh_vaghela — Mon, 23 Sep 2019 08:56:37 GMT

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

Re: How to get TOP 3 values from STATS list()

Yepeza — Tue, 02 Apr 2024 13:23:24 GMT

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