Getting Data In

## Is there a command that does 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.

Labels (1)
• ### other

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

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``````
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:

Get Updates on the Splunk Community!

#### Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

#### Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...

#### Splunk Observability Cloud | Enhancing Your Onboarding Experience with the ...

We understand that your initial experience with getting data into Splunk Observability Cloud is crucial as it ...