topic Re: Top 10 values for each field value in Splunk Search

Top 10 values for each field value

matansocher — Tue, 29 Sep 2020 16:42:31 GMT

Hi,

I have a data that contains the field 'regression_target'. I want to get the top 10 rows by 'regression_tests' field.
for example, if I have 4 values of 'regression_target' field, I would like to get 40 rows in my new table. 10 rows for the first value of 'regression_target', which they are the top 10 of the field 'regression_tests'.

Let me know if I am not clear enough.

Thanks

Re: Top 10 values for each field value

gcusello — Sun, 12 Nov 2017 09:51:33 GMT

Hi matansocher,
I hope to correctly understand: if you have data in two different indexes, try something like this:

index=my_index1 [seach index=my_index2 | dedup regression_target | rename regression_target  AS regression_tests| fields regression_tests ]
| top count BY regression_tests

If you ave data in the same index use it in both the searches.

Bye.
Giuseppe

Re: Top 10 values for each field value

matansocher — Tue, 29 Sep 2020 16:42:34 GMT

Hi Giuseppe, thanks for your reply.
I am not sure you fully understood me.
let say that is the table from my index

regression_target|regression_tests
aaa|500
bbb|200
aaa|700
ccc|600

and many more rows...

I need to get the top 10 rows (by the 'regression_tests' field) for each value of 'regression_target' field.
for example, if I have 3 values of 'regression_target' field, I would like to get 30 rows in my new table.

Re: Top 10 values for each field value

niketn — Tue, 29 Sep 2020 16:46:05 GMT

@matansocher, following is a run anywhere example which generates a mock series of 8 value per regression_target. sort and retains unique regression_test values using values() statistical function. It then counts the values per series using streamstats after reversing the series (or sort descending). Finally filters top 3 values for each series using where condition.

PS: Commands until | table generate mock data as per question. You can hook in your base search instead. Also change where counter<=3 with 10 as per your use case.

|  makeresults
|  eval data="bbb|770;ccc|870;bbb|970;ccc|780;aaa|780;bbb|670;ccc|950;aaa|320;bbb|230;ccc|345;aaa|500;bbb|200;ccc|600;aaa|200;bbb|150;ccc|300;aaa|800;bbb|600;aaa|400;ccc|900;bbb|200;aaa|300;ccc|400;aaa|900;bbb|800;ccc|600"
|  makemv data delim=";"
|  mvexpand data
|  eval data=split(data,"|")
|  eval regression_target=mvindex(data,0)
|  eval regression_tests=mvindex(data,1)
|  table regression_target regression_tests
|  stats values(regression_tests) as regression_tests by regression_target
|  mvexpand regression_tests
|  reverse
|  streamstats count as counter by regression_target
|  where counter<=3