timechart rank by top 3 averages

[Sukisen1981]

I have streaming data, including fields called APPID and DURATION, here DURATION is the duration in ms for the APPID.
Now, I want a timechart which shows only the top 3 appids by average DURATION.
So, assume there are 10 events split across 4 APPIDs, something like this:

APPID   DURATION
appid1  10
appid2  20
appid3  30
appid1  40
appid1  50
appid4  60
appid2  70
appid4  80
appid3  90
appid2  100

In this case my avg of the timechart should show only APPIDs 2, 3, and4 since the average duration of these 3 are the top 3.
I know using eventstats i can get the avg by APPID, but am stuck at the ranking stage,,,

[woodcock]

Like this:

| makeresults
| eval _raw="APPID DURATION
appid1 10
appid2 40
appid3 30
appid1 40
appid1 50
appid4 60
appid2 70
appid4 80
appid3 90
appid2 100"
| multikv forceheader=1
| table APPID DURATION

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| rename COMMENT AS "Calculate average by APPID"
| eventstats avg(DURATION) AS APP_DURATION BY APPID
| sort 0 - APP_DURATION APPID
| rename COMMENT AS "Calculate rank"
| streamstats dc(APPID) AS rank
| rename COMMENT "Account for ties"
| eventstats first(rank) AS rank BY APP_DURATION
| where rank < 4

[woodcock]

NOTE: I modified your data to create a tie for rank number 1 so that you can see the need for it (probably all other answers will not work correctly).

[to4kawa · Accepted solution]

| makeresults
| eval _raw="APPID DURATION
appid1 10
appid2 20
appid3 30
appid1 40
appid1 50
appid4 60
appid2 70
appid4 80
appid3 90
appid2 100"
| multikv
| timechart useother=f cont=f eval(round(avg(DURATION),2)) by APPID WHERE avg in top3

Hi, folks.
try timechart with where clause.

reference: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Timechart

[Sukisen1981]

works...i must be really rusty and missed the use of the WHERE clause, many thanks

[niketn]

I forgot about where clause. Getting old!

[woodcock]

Nicely done.

[niketn]

@Sukisen1981 try the following where sort by - AVG_DURATION is used to find top APPIDs using streamstats and filter results based on top 3:

 <yourCurrentSearch>
| fields _time APPID DURATION 
| eventstats avg(DURATION) as AVG_DURATION by APPID
| sort - AVG_DURATION
| streamstats dc(AVG_DURATION) as RANK_BY_AVG
| search RANK_BY_AVG IN (1,2,3)
| timechart avg(DURATION) as AVG_DURATION by APPID

Following is a run anywhere search based on the sample data provided (cont=f added to timechart for example perspective) :

| makeresults
| eval delta=300
| accum delta
| eval _time=_time-delta
| fields - delta
| eval data="appid1,10;appid2,20;appid3,30;appid1,40;appid1,50;appid4,60;appid2,70;appid4,80;appid3,90;appid2,100"
| makemv data delim=";"
| mvexpand data
| makemv data delim=","
| eval APPID=mvindex(data,0), DURATION=mvindex(data,1)
| fields _time APPID DURATION
| eventstats avg(DURATION) as AVG_DURATION by APPID
| sort - AVG_DURATION
| streamstats dc(AVG_DURATION) as RANK_BY_AVG
| search RANK_BY_AVG IN (1,2,3)
| timechart avg(DURATION) as AVG_DURATION by APPID cont=f

Please try out and confirm!