Splunk Search

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

asish_100
New Member

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.

Tags (1)
0 Karma

sergeye
New Member

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.

0 Karma

FrankVl
Ultra Champion

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

0 Karma

sergeye
New Member

Hi FrankVl,

yea, I missed it.
So the query should be:
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

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