Calculate a delta between two time slices

[Kohlman]

Is there a visualization that can calculate a delta between to two most recent records or any two time slices for that matter. Specifically we have metricbeat data from SQL server. Many of the metrics we extract are cumulative. So we'd like to create something where I can show the deltas between two values.

[Marco_Liberati]

Hi @Kohlman,

it depends a bit on what you are looking for.
You might use the differences operation for example to compute the delta between two consequent buckets, or be more explicit with two distinct offsets with a formula:

my_metric(my_field) - my_metric(my_field, shift='1w')

[Kohlman]

Hi @Marco_Liberati ,

Thanks for the quick response. I've spent a few minutes trying to find out where I would do such a thing . . . but to no avail.

I've created something maybe similar in a TSVB visualization where I defined two averages and then created a third with Bucket Script where I defined a function that did a simple calculation of the first/ the second. See attached screenshot. I suspect what you are suggesting must be here, but not sure where I find information about the options for the function my_metric. And where all this goes.

Can you point me to where I might find out more about where exactly you put such a function?

Thanks in advance for your time.

[Marco_Liberati]

That is TSVB, while Formula is a feature available in the Lens editor.
You can find some complete examples of formula in this blog post here: 10 common Kibana visualization questions answered with formulas and time travel | Elastic Blog

[Kohlman]

@Marco_Liberati So this will actually help me with something else I need to create. But I don't think I can use this for my initial need. I realize I left something out of my initial description. The metric we pull out of SQL server and ingest to elastic is a value that constantly increase. For example:

• at T0, the cpu seconds used in say 500
• at T1, the cpu seconds used in say 550

From this I can calculate that the actual cpu seconds used between T0 and T1 is 50. We only import this metric once every 5 minutes. Hence why I'd like to be showing the deltas between the records, if possible.

Thanks in advance.

[Tomo_M (Tomohiro Mitani)]

If you can use date histogram aggregation to guarantee each bucket contains one document, derivatige aggreagation (or serial differencing aggregation) could be an option to be considered.

[Kohlman]

In the process of trying to figure this out I also discovered differences(last_value(sql.metrics.numeric.waits)) which also gives me what I want.

@Tomo_M I could not figure out how to plug those into a lens to create a line graph. How do I plug these into a lens? I'd still like to use these for other things.

[Kohlman]

The one downside to the differences is that you can only break it down by one field. Do the other functions support breaking it down by more then 1 field?

[Tomo_M (Tomohiro Mitani)]

Use Quick function of Differences:

What do you mean by "break down by more then 1 field" ?

[Kohlman]

Hi @Tomo_M,

My previous post wasn't clear. I figured out how to use the Differences function. I did not figure out how to use derivatige aggreagation (or serial differencing aggregation

Regarding the Differences break down by one field see the screen shots below.

There is no way to add a second field.

I tried the "Add layer" but that seems to allow you to create another line on the graph also only "grouped" by one field.

In my case I created a new run time filed where I concatenated the two fields I wanted to see my data by.

Thanks,
Kohlman