topic Re: How can I limit the results of a stats values() function? in Splunk Search

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

thisissplunk — Wed, 04 May 2016 17:13:39 GMT

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.

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

thisissplunk — Wed, 04 May 2016 17:33:49 GMT

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

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

sundareshr — Wed, 04 May 2016 17:34:57 GMT

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

thisissplunk — Wed, 04 May 2016 17:36:18 GMT

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

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

pj — Fri, 08 Jul 2016 19:05:06 GMT

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)]

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

CSmoke — Tue, 10 Oct 2017 11:15:34 GMT

Thanks, the search does exactly what I needed.

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

benton — Wed, 01 Nov 2017 02:40:57 GMT

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.

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

sjbriggs — Fri, 12 Apr 2019 19:59:20 GMT

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

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

kartikaykv1 — Tue, 19 Sep 2023 16:56:23 GMT

Excellent Job!!!