Splunk Search

Top 10 values for each field value

matansocher
Contributor

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

0 Karma
1 Solution

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

matansocher
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 '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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...