Aggregation bucket sum + max group by time with filtering

[Tim4 (Tim)]

Hi, I'm in need of some help.

Let's start of with some sample data:

Datetime ____________| User | Mbps
18/02/2021 12:00:00 | a ___ | 10
18/02/2021 12:00:00 | b ___ | 5
18/02/2021 12:20:00 | a ___ | 10
18/02/2021 12:45:00 | b ___ | 5
18/02/2021 12:45:00 | a ___ | 5

So every doc is one datetime, user and Mbps
What i want to do is group the data by datetime and get the sum of the Mbps and users.
So far so simple. Now i want to group the datetime per hour and show the max Mbps and max users value.

---

Datetime ____________| User | Mbps | MAX Mbps
18/02/2021 12:00:00 | a ___ | 10 ___ |
18/02/2021 12:00:00 | b ___ | 5 ____ | 15

---

18/02/2021 12:20:00 | a ___ | 10 ___ | 10

---

18/02/2021 12:45:00 | b ___ | 5 ____ |
18/02/2021 12:45:00 | a ___ | 5 ____ | 10

---

Datetime ______________________| User Max | Mbps Max
18/02/2021 12:00:00 - 13:00:00| 2 (a+b) ___| 15 (10 + 5) - This would be one point in the line graph.

I want to show a line graph in kibana showing the result per hour.

So that's my use case.

How can this be done?
And how can this be done with the ability to filter on user. let's say i want the result only for user a.?

[lukas (Lukas Olson)]

These sorts of complicated use cases don't have a great solution using aggregations themselves, but really can be solved better using "entity-centric indexing". Here's a video that describes the concept:

Entity-Centric Indexing - Mark Harwood

The idea here is that every hour we'd have a script that, every hour, looks at the last hour of documents, performs the calculations as you've described, then indexes a new document that looks like the output of your calculations.

[Tim4 (Tim)]

Thanks for your reply. Is it possible to use Kibana to create a graph from an external URL.
I can generate a sum of users and Mbps for the time ranges and know i can create the max in like say PHP. The Flow would be.

Elasticsearch → Query for sum per time range → PHP get max for every hour → out put JSON results → show JSON results in graph in Kibana.

Does anyone have experience in doing the last step "show JSON results in graph in Kibana" from an external source?