How to get 1st 3 count

[mahesh27]

Query:

|mstats sum(error.count) as Count where index=metrics_data by provider errorid errorname 
|search errorname=apf

Results:

provider	errorid	errorname	Count
Digital it	401	apf	200.0000
Data St	200	apf	500.0000
dtst	0	apf	18.0000
Digital it	100	apf	55.0000
dtst	501	apf	16.0000
Digital it	0	apf	20.0000
Data St	200	apf	300.0000
dtst	201	apf	12.0000
Data St	404	apf	20.0000
Digital it	201	apf	10.0000
Data St	501	apf	10.0000
dtst	201	apf	9.0000
Data St	401	apf	8.0000
dtst	500	apf	3.0000
Data St	555	apf	5.0000
dtst	200	apf	2.0000

expected results:

provider	errorname	errorid	Count
Digital it	apf	401 100 0	200.0000 55.0000 20.0000
Data St	apf	200 200 404	500.0000 300.0000 20.0000
dtst	apf	0 501 201	18.0000 16.0000 12.0000

[bowesmana]

One way on a small table (less than 100 items per provider) is to use stats list and then keep the first 3, here's an example

| makeresults format=csv data="provider,errorid,errorname,Count
Digital it,401,apf,200.0000
Data St,200,apf,500.0000
dtst,0,apf,18.0000
Digital it,100,apf,55.0000
dtst,501,apf,16.0000
Digital it,0,apf,20.0000
Data St,200,apf,300.0000
dtst,201,apf,12.0000
Data St,404,apf,20.0000
Digital it,201,apf,10.0000
Data St,501,apf,10.0000
dtst,201,apf,9.0000
Data St,401,apf,8.0000
dtst,500,apf,3.0000
Data St,555,apf,5.0000
dtst,200,apf,2.0000"
``` list() will retain order, but has a max of 100 items ```
| stats list(*) as * by provider errorname
``` This just retains the first 3 ```
| foreach Count errorid [ eval <<FIELD>>=mvindex(<<FIELD>>, 0, 2) ]

but if you have more complex data, it may not be suitable.

[bowesmana]

and a final way, again using streamstats, but with a more optimised way of collecting the results - if you have a lot of data, it's worth benchmarking each of these solutions as they may have different performance characteristics

| makeresults...
| streamstats count as seq global=f by provider errorname
| streamstats global=f list(eval(if(seq<4, Count, null()))) as Count list(eval(if(seq<4, errorid, null()))) as errorid by provider errorname
| where seq=3

[ITWhisperer]

| streamstats list(Count) as Count list(errorid) as errorid by provider errorname global=f window=4
| where mvcount(Count) = 3

By setting the window to 4, only the 3rd one will have 3 values in the list

[bowesmana]

Sweet - nice optimisation

[bowesmana]

Or you can use streamstats,

| makeresults...
| streamstats count as seq window=3 global=f list(*) as * by provider errorname
| where seq=3
| fields - seq
| streamstats count as seq window=3 global=f list(*) as * by provider errorname
| where seq=1
| fields - seq