Calculate differences between documents in a query

[mbby]

I need to calculate the MTBF of our systems which are monitored using the observatiblity uptime feature of Elasticsearch.

Let's say that I've an index with the following values:
time, system-name, state
10:00, system1, up
10:05, system1, up
10:10, system1, down
10:15, system1, down
10:20, system1, up
10:25, system1, down
10:30, system1, down
10:35, system1, down
10:40, system1, up

How could I count the number of times the state switched from down to up (here: 2).
How could I get the time between two of those state changes (here: 10:35-10:15 = 0:20).

[mbby]

So one of the questions is: is there a query which calculates difference between consecutive documents. E.g.:
time, spent money
10:00, 100
10:05, 105
10:10, 150

the result should be something like, 105-100=5, 150-105=45

[mbby]

It looks like this is not possible in an Elasticsearch query itself, right?

[stephenb (Stephen Brown)]

@mbby

So one of the questions is: is there a query which calculates difference between consecutive documents. E.g.:
time, spent money
10:00, 100
10:05, 105
10:10, 150

Perhaps

Serial differencing aggregation | Elasticsearch Guide [8.12] | Elastic

You can do serial differencing in Lens as well.

[mbby]

Thanks Stephen. Found that and tried it. Unfortunately this won't solve my initial problem of getting the distances between two state changes.