How to write a search to find the top 10 results based on a calculated field?

[nibinabr]

Hi Splunkers,

I had a question

ID       N1     N2    USER  CALCULATED_NUM
001      10     2     user_1    8
002      8      4     user_2    4
003      7      9     user_1   -2
.
.

CALCULATED_NUM=N1-N2

I need to write a search query that returns the top 10 CALCULATED_NUM by each USER.

....| table ID,CALCULATED_NUM,USER| sort by USER,-CALCULATED_NUM

gives me all the ID's sorted by user with CALCULATED_NUM sorted in desc order, but I need only the top 10 IDs per USER.

Thanks

[MuS · Accepted solution]

Hi nibinabr,

Look at this answer http://answers.splunk.com/answers/208658/how-to-limit-results-with-multiple-group-by-condit.html#ans... to get an example how this can be done.

cheers, MuS

[nibinabr]

Thanks MuS,

I found a similar post that helped me get into the solution.

http://answers.splunk.com/answers/148651/how-to-get-top-2-mb-users-per-website.html

[nibinabr]

I think I found a solution to the problem.

...| streamstats count by USER |table ID,CALCULATED_NUM,USER,count| sort by USER,-CALCULATED_NUM| where count<=10

Please let me know if there is straightforward way of doing this.