Is it possible to do calculation on aggregated data in kibana

#1

I have a timeseries (minute granularity) with a string field F1and numeric field F2 indexed ES.
I would like to first do sum(F2) over the string field and then calculate time average. Example below :

For example :
Time, F1, F2
10:05, A, 2,
10:10, A,3
10:10, B,5
10:15, A,3
10:15, B,2

I want to aggregate SUM(F2) group by A, B both
At the same time unique count(Time)

then SUM(F2)/unique count(Time) for A as well as B

My expected result should be
Time , F1, F2
10th hour, A, =SUM(2+3+3)/count unique(:05+:10,:15) => 8/3
10th hour, B, =SUM(5+2)/count unique(:10, :15) => 7/2

Is it doable in kibana?

(Tyler Smalley) #2

Everything except the unique, and that would really not be easy to do at query time since you would need to choose which record to select if there are duplicates.

My suggestion would be to ensure you don't have duplicates to begin with. How about including the time in the record ID, that way it wouldn't be possible to have duplicates?

#3

I am not an expert of kibana. Not sure to understand your logic to implement in kibana. could you please elaborate how to calculate SUM(F2) group by A & B for each time_stamp ?
I think it's like a window function where window is a unique combination of time_Stamp+F1 but don't know how to do it in kibana.