topic Re: How do you find the top 5% of data in a table? in Splunk Search

How do you find the top 5% of data in a table?

asish_100 — Mon, 03 Dec 2018 12:50:42 GMT

I have a table that contains hours worked against each task.

Now i want to estimate the top 5% of the task(like if there are 100 tasks i want to display top 20 tasks which consumed maximum hours).

My query is given below

base search|stats sum(hours) by task,domain,group.

Please help me to estimate the top 5% of the task.

Re: How do you find the top 5% of data in a table?

sergeye — Mon, 03 Dec 2018 16:32:28 GMT

Hi,

probably this is not a best solution but it works.
just add this to the end of your query:

base search|stats sum(hours) as hours by task,domain,group
| sort - hours 
| streamstats count
| eventstats count as max_count
| eval percent = round(exact(exact(count * 100) / max_count), 0)
| search percent <= 5
| fields - count, max_count, percent

PS as FrankVl said I missed the "sort" command,
so I edited this post to add it.

Re: How do you find the top 5% of data in a table?

FrankVl — Tue, 04 Dec 2018 12:13:04 GMT

You probably would want to do a sort on the sum(hours) first.

Re: How do you find the top 5% of data in a table?

sergeye — Tue, 29 Sep 2020 22:17:18 GMT

Hi FrankVl,