topic Basic linear interpolation in time in Archive
https://community.splunk.com/t5/Archive/Basic-linear-interpolation-in-time/m-p/404666#M69799
<P>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?</P>
<P>For example, if I do something like:</P>
<PRE><CODE>| mstats
span=20s
latest(MyCounter)
WHERE index=my_metrics
</CODE></PRE>
<P>It might return something like:</P>
<PRE><CODE>_time MyCounter
0:00:00 100
0:00:20 120
0:00:40 160
</CODE></PRE>
<P>What I would like to do is have it transform to:</P>
<PRE><CODE>_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
</CODE></PRE>
<P>I've tried using <CODE>| predict</CODE> 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 <CODE>| makecontinuous _time span=1s</CODE> and a <CODE>| streamstats</CODE> but I haven't quite figured it out yet.</P>Tue, 23 Jul 2019 17:23:43 GMTkhevans2019-07-23T17:23:43ZBasic linear interpolation in time
https://community.splunk.com/t5/Archive/Basic-linear-interpolation-in-time/m-p/404666#M69799
<P>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?</P>
<P>For example, if I do something like:</P>
<PRE><CODE>| mstats
span=20s
latest(MyCounter)
WHERE index=my_metrics
</CODE></PRE>
<P>It might return something like:</P>
<PRE><CODE>_time MyCounter
0:00:00 100
0:00:20 120
0:00:40 160
</CODE></PRE>
<P>What I would like to do is have it transform to:</P>
<PRE><CODE>_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
</CODE></PRE>
<P>I've tried using <CODE>| predict</CODE> 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 <CODE>| makecontinuous _time span=1s</CODE> and a <CODE>| streamstats</CODE> but I haven't quite figured it out yet.</P>Tue, 23 Jul 2019 17:23:43 GMThttps://community.splunk.com/t5/Archive/Basic-linear-interpolation-in-time/m-p/404666#M69799khevans2019-07-23T17:23:43ZRe: Basic linear interpolation in time
https://community.splunk.com/t5/Archive/Basic-linear-interpolation-in-time/m-p/404667#M69800
<P>Here's what I've come up with so far:</P>
<PRE><CODE>| 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
</CODE></PRE>
<OL>
<LI><CODE>|mstats</CODE> pulls as many points as possible with a min resolution of 1s. It ends up being an item every 20 seconds or so</LI>
<LI>I do a descending sort on time, so that when I do the streamstats to calculate the deltas (<CODE>inc</CODE>), 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.</LI>
<LI><CODE>|eval inc =</CODE> just finds the amount it should increment each by each row, aka the slope</LI>
<LI><CODE>|makecontinuous</CODE> just fills in the _time field on a 1 second interval but leaves everything else null</LI>
<LI><CODE>|filldown</CODE> fills the null values with the prev ones</LI>
<LI><CODE>|streamstats...</CODE> takes a running sum of the increments and resets every time it hits an actual data point</LI>
</OL>
<HR />
<P>As a macro, I called it <CODE>linear_interpolate(1)</CODE>, defined as:</P>
<PRE><CODE>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
</CODE></PRE>
<P>with a single argument <CODE>field</CODE></P>
<P>Then to use it:</P>
<PRE><CODE>| mstats
span=1s
...
| `linear_interpolate(field=value)`
| table _time value value_interpolated
</CODE></PRE>
<P>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.</P>
<HR />
<P>It ends up looking something like this:</P>
<P><IMG src="https://i.imgur.com/KFlKYjY.png" alt="screenshot of data fit" /></P>Tue, 23 Jul 2019 18:13:38 GMThttps://community.splunk.com/t5/Archive/Basic-linear-interpolation-in-time/m-p/404667#M69800khevans2019-07-23T18:13:38Z