Is there a command that does Basic linear interpolation in time?

[khevans]

Is there a command that just does linear interpolation? I have data that is logging every 20 seconds or so. I would like to interpolate it to a second-resolution on a table. Is there a single command that does this?

For example, if I do something like:

| mstats
    span=20s
    latest(MyCounter)
    WHERE index=my_metrics

It might return something like:

_time MyCounter
0:00:00 100
0:00:20 120
0:00:40 160

What I would like to do is have it transform to:

_time MyCounter
0:00:00 100
0:00:01 101
0:00:02 102
...
0:00:19 119
0:00:20 120
0:00:21 122
0:00:22 124
...
0:00:39 159
0:00:40 160

I've tried using | predict with a couple of algorithms, but it seems to vary wildly. I just would like a simple NN/linear interpolation between two data points. I was thinking it might be doable using | makecontinuous _time span=1s and a | streamstats but I haven't quite figured it out yet.

[cameronjust]

Thanks for posting this. Solved the problem I was having perfectly.

Only difference was I was trying to convert 30 min data to 5 min data which I did with a modified version of your macro

Macro Def - linear_interpolate_minutes($field$,$minutes$)

sort - _time 
| streamstats window=2 first($field$) as next_value, first(_time) as next_time 
| eval delta = ((next_value - $field$) / (next_time - _time)) * ($minutes$ * 60) 
| makecontinuous span=$minutes$m 
| filldown delta 
| streamstats sum(eval(coalesce($field$, delta))) as $field$_interpolated reset_before="ISNOTNULL($field$)" 
| fields - delta next_value next_time

[khevans · Accepted solution]

Here's what I've come up with so far:

| mstats 
    span=1s
    avg(value) as value
    WHERE index=my_metrics
| sort - _time 
| streamstats window=2 first(value) as next_value, first(_time) as next_time 
| eval inc = (next_value - value) / (next_time - _time) 
| makecontinuous span=1s 
| filldown inc
| streamstats sum(eval(coalesce(value, inc))) as value_interpolated reset_before="ISNOTNULL(value)"
| table _time value value_interpolated

1. |mstats pulls as many points as possible with a min resolution of 1s. It ends up being an item every 20 seconds or so
2. I do a descending sort on time, so that when I do the streamstats to calculate the deltas (inc), it'll be the delta needed between the current and next data point, as opposed to getting the delta between the current and previous data point.
3. |eval inc = just finds the amount it should increment each by each row, aka the slope
4. |makecontinuous just fills in the _time field on a 1 second interval but leaves everything else null
5. |filldown fills the null values with the prev ones
6. |streamstats... takes a running sum of the increments and resets every time it hits an actual data point

---

As a macro, I called it linear_interpolate(1), defined as:

sort - _time 
| streamstats window=2 first($field$) as next_value, first(_time) as next_time 
| eval delta = (next_value - $field$) / (next_time - _time) 
| makecontinuous span=1s 
| filldown delta
| streamstats sum(eval(coalesce($field$, delta))) as $field$_interpolated reset_before="ISNOTNULL($field$)"
| fields - delta next_value next_time

with a single argument field

Then to use it:

| mstats 
    span=1s
    ...
| `linear_interpolate(field=value)`
| table _time value value_interpolated

It works but it really irks me that there doesn't seem to be a simple linear interpolation command, unless I'm completely missing something.

---

It ends up looking something like this: