Splunk Search

How to get 1st 3 count

mahesh27
Communicator

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



Labels (1)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| 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
SplunkTrust
SplunkTrust

Sweet - nice optimisation

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

 

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...