"Middle" parameter for stats command?

[KyleMcDougall]

Hi all! 

We use stats commands to pull in data from our APIs. But, our APIs get called multiple times in a single session. This works well if you want to use the first or last API call, using first(variable) or last(variable).

However, we want to pull in the middle API call. Is there a way to do this?

I realize there's no param for middle(variable), but I'm looking for possible alternatives. Any help would be much appreciated!

index=conversation sourcetype="cui-orchestration-log" botId=123456
| stats 
first(experiments__40000) as treatment
middle(case_number) as case_ID
by sessionId

[bowesmana · Accepted solution]

How many events will contain the field case_number and will this case_number change in anyway during the sequence of API calls?

The simple solution is to collect all possible values of the case_number from the requests for the session id, i.e.

index=conversation sourcetype="cui-orchestration-log" botId=123456
| stats first(experiments__40000) as treatment
        values(case_number) as case_ID by sessionId

but I suspect you do not actually want to use first/middle/last anyway as those are intended to take values from the sequence of events, where those values may change and you want a specific variant of that field.

If the fields 'experiments_40000' and 'case_number' simply occur as different fields in the different events, then use vales(X) or list(X) to get the variants.

[KyleMcDougall]

Thank you!! This is exactly what I was looking for. 

[skramp]

I've got problem understanding this middle thing because it will only work if there are 3 or 5 results.

Let's start the easier way. If you have 5 results, you can do a streamstats count as counting by sessionID. After this, you can do something like a sorting - counting sessionID  and write down the max counting by sessionID in each line. Then you calculate marker=(maxcounting+1)/2 and search where marker=counting. That means, if you have 5 results, for a sessionID it will look like this:

ID=1234, value=a, counting=1, maxcount=5, marker=3

ID=1234, value=b, counting=2, maxcount=5, marker=3

ID=1234, value=c, counting=3, maxcount=5, marker=3

ID=1234, value=d, counting=4, maxcount=5, marker=3

ID=1234, value=e, counting=5, maxcount=5, marker=3

But what will you do if there are 6 events, what's the middle?

[KyleMcDougall]

Thanks for the reply! Interesting solution. Do you have an example using the query I posted above? I'm not sure I know how to put this into practice. Also, in my situation, there's only 3 events, so it should work.