Visualize changes of an aggregation result over time

Hi there.

I have been using Elastic for the past year but I am relatively new to Kibana.

Basic info on my setup and usecase:
I have my database records streaming into Elastic via Kafka Connect. When a record changes in the db that record is updated in elastic. Each records has a unique identifier (which is used as the key in elastic), a status keyword field and a bunch of other source fields. The status field of records change (from “created” → “processing” → “failed”).

I am able to do aggregations using the current data in elastic at the point I run the query, but how do I see the result of those aggregations over time? In my case, I want to see a graph of how the amount of “processing” records has changed over time.

Potential Solutions:
From this video I see that the data visualizer in ML anomaly detection (elastic cloud) gives me exactly what I want since it has to measure the change of aggregations. I expect though that there must be a more straightforward way to do this though. I feel like I’m missing something fundamental.

I could create a different index in which my key is based on time, essentially making it a log of my database. That way I don’t lose any time series data, but then the issue is removing duplicate items from the queries. I’d need to group all the data by their unique identifier when visualizing.

I could also have a separate process that runs the an aggregation every X seconds and then dumps that result to a new index.

Any would be greatly appreciated :raised_hands:t5: