How can I limit the results of a stats values() function?

[thisissplunk]

I want to list about 10 unique values of a certain field in a stats command. I cannot figure out how to do this. I figured stats values() would work, and it does... but I'm getting hundred of thousands of results. I only want the first ten!

Of course, a top command or simple head command won't work because I need the values of a field, keyed off of another field.

Example:

index=* | stats values(IPs) count by hostname

I want the first ten IP values for each hostname. NOT all (hundreds) of them! Imagine a crazy dhcp scenario. I'm also open to other ways of displaying the data.

[pj]

If you have multiple fields that you want to chop (i.e. to show a sample across all) you can also use something like this:

| stats values(*) as *
| foreach * [eval <<FIELD>>=mvindex('<<FIELD>>',0,10)]

[benton]

That's clean! Here's a small enhancement:

| foreach * [eval <<FIELD>>=if(mvcount('<<FIELD>>')>10, mvappend(mvindex('<<FIELD>>',0,9),"..."), '<<FIELD>>')]

This will display the first 10 values and if there are more than that it will display a "..." making it clear that the list was truncated.

[kartikaykv1]

Excellent Job!!!

[CSmoke]

Thanks, the search does exactly what I needed.

[sundareshr]

Try this
index=* | stats values(IPs) a ip by hostname | mvexpand ip | streamstats count by host | where count<=10 | stats values(ip) as IPs by host

[thisissplunk]

That's what I was thinking initially, but I don't want to actually filter any events out, which is what the "where" does.

[thisissplunk · Accepted solution]

I've figured it out. You need to use a mvindex command to only show say, 1 through 10 of the values() results:

| stats values(IP) AS unique_ip_list_sample dc(IP) AS actual_unique_ip_count count as events by hostname 
| eval unique_ip_list_sample=mvindex(unique_ip_value_sample, 0, 10)
| sort -events

[sjbriggs]

Great solution. I was able to get my top 10 bandwidth users by business location and URL after a few modifications.