LIst all universal forwarder hosts sending to an index but limit the host count to 5

[davidaj]

Hello

I'm looking to modify this search I've found and using. I like the result set but would like to limit the host count to just five for each index it reports to. The .csv export of the original search is really messy and just unusable. My SPL skills are limited at the moment so any help is much appreciated. 

| tstats values(host) as host where index=* by index

[davidaj]

Update: 

Thanks for all the help. I was able to get an assist from a colleague and wanted to provide his search incase it works for anyone else.  

In a nutshell, the line rows=5, you can change to whatever number you need, but in the output if there are more than 5 hosts it will show you ... at the bottom so you know there is more, the columns on the right show you the initial amount of hosts and the amount if truncated

| tstats values(host) as host where index=* by index 
| foreach host ``` This code will only show you the first n values in the values() command```
    [ eval rows=5
    | eval mvCountInitial=mvcount('<<FIELD>>') 
    | eval <<FIELD>>=if(mvcount('<<FIELD>>')>$rows$, mvappend(mvindex('<<FIELD>>',0,$rows$-1),"..."), '<<FIELD>>') 
| eval mvCountTruncated=if(mvcount('<<FIELD>>')>$rows$,mvcount('<<FIELD>>')-1,mvcount('<<FIELD>>')) 
| fields - rows]
| rename mvCountInitial as "Total Host Count" mvCountTruncated as Truncated

[livehybrid · Accepted solution]

If you want a list of the top 5 hosts reporting into each index then I would look to use the following search:

| tstats count where index=* by host, index
| sort - count
| streamstats count as n by index
| search n<=5
| stats values(host) by index

This Splunk search starts by using tstats to efficiently count events for each host and index, retrieving data across all indexes. It then sorts the results in descending order by event count so that the most active hosts appear first. The streamstats command assigns a running count (n) to each record within its respective index, effectively numbering the hosts within each index. The search n<=5 step filters the results to include only the top 5 hosts per index based on event count. Finally, stats values(host) by index consolidates the results to display the top 5 hosts for each index in a clean format.

Please let me know how you get on and consider accepting this answer or adding karma this answer if it has helped.
Regards

Will

[davidaj]

Hello livehybrid

Thanks you. This gives me exactly what I an looking for. 

[richgalloway]

There is no option in tstats or values to limit the number of values.  You can, however, expand the host field and then limit the number displayed.

| tstats values(host) as host where index=* by index 
| mvexpand host 
| dedup 5 index host