Top n plus others

[lpolo]

Let's say we have the following log events:

time1 text=g  count=82
time2 text=f  count=80
time3 text=c  count=14
time4 text=e  count=13
time5 text=b  count=11
time6 text=a  count=10
time7 text=d  count=6

The following query will get the Top N results:

earliest=time1 latest=time7 index=blabla |
stat sum(count) as count by text

Result:
text | count
g   82
f   80
c  14
e  13
b  11
a  10
d  6

I need a query to get the Top 3 plus others result example:

text | count
g   82
f   80
c  14
others 40

[richgalloway · Accepted solution]

See if this helps.

| makeresults | eval data="time1 text=g  count=82|time2 text=f  count=80|time3 text=c  count=14|time4 text=e  count=13|time5 text=b  count=11|time6 text=a  count=10|time7 text=d  count=6" | eval data=split(data,"|") | mvexpand data | eval _raw=data | extract pairdelim=" " kvdelim="="
`comment("Above just sets up test data")`
`comment("Get the top 3 counts and put everything else in 'other'")`
| timechart span=1d limit=3 useother=t sum(count) as count by text
`comment("Throw out fields we don't need")`
| fields - _*
`comment("Rotate the results")`
| transpose column_name="text" header_field=count
| rename "row 1" as count

[lpolo]

Hi,
Thanks for your response. I forgot about the limit=n in the timechart command. Thanks 🙂
To complement your response I just added this after the timeshare command

| timechart span=1d limit=1000 sum(count) by text 
| transpose
| search NOT column=_span NOT column=_time
| rename "row 1" as count
| sort - count