Archive

## Basic linear interpolation in time

Path Finder

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.

Tags (1)
1 Solution
Path Finder

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: Path Finder

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:   State of Splunk Careers