Splunk Search

Top 10 values for each field value

Contributor

Hi,

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

Let me know if I am not clear enough.

Thanks

0 Karma
1 Solution

SplunkTrust
SplunkTrust

@matansocher, following is a run anywhere example which generates a mock series of 8 value per regressiontarget. sort and retains unique regressiontest 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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

@matansocher, following is a run anywhere example which generates a mock series of 8 value per regressiontarget. sort and retains unique regressiontest 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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

Contributor

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 'regressiontests' field) for each value of 'regressiontarget' field.
for example, if I have 3 values of 'regression_target' field, I would like to get 30 rows in my new table.

0 Karma

Legend

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

0 Karma