TICK script: use from() values after chaining functions

InfluxDB 1 Kapacitor
1

Hi,

I am trying to use the Holt-Winters function of Kapacitor, but I am a little bit stuck.
What I have is something like:

stream
|from()
    .measurement('table_test')
|window()
    .period(1d)
    .every(10m)
    .align()
|holtWinters('value', 1, 1, 1d)
    .as('hw')
|alert()
    .message('{{index .Fields "key"}} {{ index .Fields "value"}}')
    .crit(lambda: sigma("hw") > 3)
    .log('/var/lib/kapacitor/alerts/my.log')
    .stateChangesOnly()

I am not yet sure, If the parameters for the Holt-Winters function are good, but this would be the next step.
I have a script to evaluate the output of the logs, but this script needs to parse the .message string.
When using the Holt-Winters function, the fields ‘key’ and ‘value’ do not have a value (but they are in the table).
When I remove the Holt-Winters function, the message is like expected.
So, what would be a good solution, to keep the values from the ‘from()’ function, even after using the ‘holtWinters()’ function?

Best regards,
Simon

2

When using the Holt-Winters function, the fields ‘key’ and ‘value’ do not have a value (but they are in the table).

What do you mean that they are in the table?
The .as('hw') part of the script is telling the HoltWinters function to name the output field hw so that is its name not value.

3

Thanks for your answer Nathaniel.
You are right, but value and key are in table_test:

table_test key="1",value=-0.021337869145268 0

Probably it is very easy to access them but I can’t figure out how.
As far as I understand, the chaining in TICK scripts is similar to the Unix-Pipe command, where the original input is further processed and in the end, there is a new output. But I need to cache the original input somewhere and combine it with the processed output.
I hope, I was able to express myself understandable.

Best regards,
Simon

4

I think I understand now. The fields key and value are on the original data and you want to preserve them after passing through the holtWinters operation. The key to caching and combining data in TICKscript is to use the join node. It joins data points based of their timestamp.

So something like this could work depending on what you are trying to do:

var data = stream
|from()
    .measurement('table_test')
|window()
    .period(1d)
    .every(10m)
    .align()

// Forecast the value field
var values = data
|holtWinters('value', 1, 1, 1d)
    .as('value')

// Forecast the key field
var keys = data
|holtWinters('key', 1, 1, 1d)
    .as('value')

// Now join both forecasted point back into a single point
values
  |join(keys)
     .as('values','keys')
 |alert()
    .message('{{index .Fields "keys.value"}} {{ index .Fields "values.value"}}')
    .crit(lambda: sigma("values.value") > 3)
    .log('/var/lib/kapacitor/alerts/my.log')
    .stateChangesOnly()

Since holtWinters forecasts the data it creates new points and the other fields are dropped as a result. But you can forecast both fields independently and then join them back together as I have shown above.

Although based on the name of the field key that seems like it is probably not a numeric value you want to be forecasting. If that is the case then first convert the field to a tag and groupBy the tag to preserve it through the holtWinter forecast operation.

var data = stream
 |from()
    .measurement('table_test')
 |window()
    .period(1d)
    .every(10m)
    .align()
 |eval(lambda: string("key"))
    .as('key')
    .tags('key')
    .keep('value')
 |groupBy('key')
 |holtWinters('value', 1, 1, 1d)
    .as('value')
 |alert()
    .message('{{index .Tags  "key"}} {{ index .Fields "value"}}')
    .crit(lambda: sigma("value") > 3)
    .log('/var/lib/kapacitor/alerts/my.log')
    .stateChangesOnly()
5

Thanks for your patience @nathaniel, it’s not yet solved, but we are getting closer :smile:
The field key is not forecasted.

The first three entries from the streaming replay file are:

mydb
autogen
table_test key="1",value=-0.021337869145268 0
mydb
autogen
table_test key="2",value=0.00881807571161336 10000000000
mydb
autogen
table_test key="3",value=0.0758696736565443 20000000000

I have created separate log files for debugging. The script now looks like:

var data = stream
|from()
    .measurement('table_test')
|alert()
    .log('/var/lib/kapacitor/alerts/raw.log')
    .crit(lambda: "value" > 0.8)
|window()
    .period(1d)
    .every(10m)
    .align()

var hw = data
|holtWinters('value', 1, 0, 10m)
    .as('pred')
|alert()
    .log('/var/lib/kapacitor/alerts/hwexport.log')
    .crit(lambda: "pred" > 0.8)

data
|join(hw)
    .as('data', 'hw')
    // Inner join does not work, I have to use .fill, otherwise, no log file will be created.
    .fill('null')
|alert()
    .message('{{index .Fields "data.key"}} {{ index .Fields "data.value"}}')
    .crit(lambda: "hw.pred" > 0.8)
    .log('/var/lib/kapacitor/alerts/join.log')
    .stateChangesOnly()

The first three lines of the “raw.log”, everything seems to be fine:

{"id":"table_test:nil","message":"table_test:nil is CRITICAL","details":"{\u0026#34;Name\u0026#34;:\u0026#34;table_test\u0026#34;,\u0026#34;TaskName\u0026#34;:\u0026#34;bumpInDoublePick\u0026#34;,\u0026#34;Group\u0026#34;:\u0026#34;nil\u0026#34;,\u0026#34;Tags\u0026#34;:{},\u0026#34;ID\u0026#34;:\u0026#34;table_test:nil\u0026#34;,\u0026#34;Fields\u0026#34;:{\u0026#34;key\u0026#34;:\u0026#34;45\u0026#34;,\u0026#34;value\u0026#34;:0.806465263666515},\u0026#34;Level\u0026#34;:\u0026#34;CRITICAL\u0026#34;,\u0026#34;Time\u0026#34;:\u0026#34;2017-04-05T15:31:17.146203883Z\u0026#34;,\u0026#34;Message\u0026#34;:\u0026#34;table_test:nil is CRITICAL\u0026#34;}\n","time":"2017-04-05T15:31:17.146203883Z","duration":0,"level":"CRITICAL","data":{"Series":[{"name":"table_test","columns":["time","key","value"],"values":[["2017-04-05T15:31:17.146203883Z","45",0.806465263666515]]}],"Messages":null,"Err":null}}
{"id":"table_test:nil","message":"table_test:nil is OK","details":"{\u0026#34;Name\u0026#34;:\u0026#34;table_test\u0026#34;,\u0026#34;TaskName\u0026#34;:\u0026#34;bumpInDoublePick\u0026#34;,\u0026#34;Group\u0026#34;:\u0026#34;nil\u0026#34;,\u0026#34;Tags\u0026#34;:{},\u0026#34;ID\u0026#34;:\u0026#34;table_test:nil\u0026#34;,\u0026#34;Fields\u0026#34;:{\u0026#34;key\u0026#34;:\u0026#34;46\u0026#34;,\u0026#34;value\u0026#34;:0.788332170379725},\u0026#34;Level\u0026#34;:\u0026#34;OK\u0026#34;,\u0026#34;Time\u0026#34;:\u0026#34;2017-04-05T15:31:27.146203883Z\u0026#34;,\u0026#34;Message\u0026#34;:\u0026#34;table_test:nil is OK\u0026#34;}\n","time":"2017-04-05T15:31:27.146203883Z","duration":10000000000,"level":"OK","data":{"Series":[{"name":"table_test","columns":["time","key","value"],"values":[["2017-04-05T15:31:27.146203883Z","46",0.788332170379725]]}],"Messages":null,"Err":null}}
{"id":"table_test:nil","message":"table_test:nil is CRITICAL","details":"{\u0026#34;Name\u0026#34;:\u0026#34;table_test\u0026#34;,\u0026#34;TaskName\u0026#34;:\u0026#34;bumpInDoublePick\u0026#34;,\u0026#34;Group\u0026#34;:\u0026#34;nil\u0026#34;,\u0026#34;Tags\u0026#34;:{},\u0026#34;ID\u0026#34;:\u0026#34;table_test:nil\u0026#34;,\u0026#34;Fields\u0026#34;:{\u0026#34;key\u0026#34;:\u0026#34;47\u0026#34;,\u0026#34;value\u0026#34;:0.847472078582032},\u0026#34;Level\u0026#34;:\u0026#34;CRITICAL\u0026#34;,\u0026#34;Time\u0026#34;:\u0026#34;2017-04-05T15:31:37.146203883Z\u0026#34;,\u0026#34;Message\u0026#34;:\u0026#34;table_test:nil is CRITICAL\u0026#34;}\n","time":"2017-04-05T15:31:37.146203883Z","duration":0,"level":"CRITICAL","data":{"Series":[{"name":"table_test","columns":["time","key","value"],"values":[["2017-04-05T15:31:37.146203883Z","47",0.847472078582032]]}],"Messages":null,"Err":null}}

The first three lines of the “hwexport.log”, I can’t find any issues here:

{"id":"table_test:nil","message":"table_test:nil is CRITICAL","details":"{\u0026#34;Name\u0026#34;:\u0026#34;table_test\u0026#34;,\u0026#34;TaskName\u0026#34;:\u0026#34;bumpInDoublePick\u0026#34;,\u0026#34;Group\u0026#34;:\u0026#34;nil\u0026#34;,\u0026#34;Tags\u0026#34;:{},\u0026#34;ID\u0026#34;:\u0026#34;table_test:nil\u0026#34;,\u0026#34;Fields\u0026#34;:{\u0026#34;pred\u0026#34;:1.0480758394807048},\u0026#34;Level\u0026#34;:\u0026#34;CRITICAL\u0026#34;,\u0026#34;Time\u0026#34;:\u0026#34;2017-04-05T15:49:57.146203883Z\u0026#34;,\u0026#34;Message\u0026#34;:\u0026#34;table_test:nil is CRITICAL\u0026#34;}\n","time":"2017-04-05T15:49:57.146203883Z","duration":0,"level":"CRITICAL","data":{"Series":[{"name":"table_test","columns":["time","pred"],"values":[["2017-04-05T15:49:57.146203883Z",1.0480758394807048]]}],"Messages":null,"Err":null}}
{"id":"table_test:nil","message":"table_test:nil is CRITICAL","details":"{\u0026#34;Name\u0026#34;:\u0026#34;table_test\u0026#34;,\u0026#34;TaskName\u0026#34;:\u0026#34;bumpInDoublePick\u0026#34;,\u0026#34;Group\u0026#34;:\u0026#34;nil\u0026#34;,\u0026#34;Tags\u0026#34;:{},\u0026#34;ID\u0026#34;:\u0026#34;table_test:nil\u0026#34;,\u0026#34;Fields\u0026#34;:{\u0026#34;pred\u0026#34;:1.0480758394807048},\u0026#34;Level\u0026#34;:\u0026#34;CRITICAL\u0026#34;,\u0026#34;Time\u0026#34;:\u0026#34;2017-04-05T15:53:17.146203883Z\u0026#34;,\u0026#34;Message\u0026#34;:\u0026#34;table_test:nil is CRITICAL\u0026#34;}\n","time":"2017-04-05T15:53:17.146203883Z","duration":200000000000,"level":"CRITICAL","data":{"Series":[{"name":"table_test","columns":["time","pred"],"values":[["2017-04-05T15:53:17.146203883Z",1.0480758394807048]]}],"Messages":null,"Err":null}}
{"id":"table_test:nil","message":"table_test:nil is CRITICAL","details":"{\u0026#34;Name\u0026#34;:\u0026#34;table_test\u0026#34;,\u0026#34;TaskName\u0026#34;:\u0026#34;bumpInDoublePick\u0026#34;,\u0026#34;Group\u0026#34;:\u0026#34;nil\u0026#34;,\u0026#34;Tags\u0026#34;:{},\u0026#34;ID\u0026#34;:\u0026#34;table_test:nil\u0026#34;,\u0026#34;Fields\u0026#34;:{\u0026#34;pred\u0026#34;:0.8084033385401356},\u0026#34;Level\u0026#34;:\u0026#34;CRITICAL\u0026#34;,\u0026#34;Time\u0026#34;:\u0026#34;2017-04-05T16:09:57.146203883Z\u0026#34;,\u0026#34;Message\u0026#34;:\u0026#34;table_test:nil is CRITICAL\u0026#34;}\n","time":"2017-04-05T16:09:57.146203883Z","duration":1200000000000,"level":"CRITICAL","data":{"Series":[{"name":"table_test","columns":["time","pred"],"values":[["2017-04-05T16:09:57.146203883Z",0.8084033385401356]]}],"Messages":null,"Err":null}}

The messages in the “join.log” file do have a different pattern (every second message has the same pattern). It seems like there are two newly created tables, based on the join function. One with “hw.pred” (ok) and “data.pred” (not available)
and the other with “data.key” (ok), “data.value” (ok), “hw.key” (not available), “hw.value” (not available). So I think joining both tables failes, because creating the predicted values from the holt-winters function will have a new timestamp which does not fit the timestamp of the original values anymore (even with using .tolerance(“1s”)).

{"id":"table_test:nil","message":"\u003cno value\u003e \u003cno value\u003e","details":"{\u0026#34;Name\u0026#34;:\u0026#34;table_test\u0026#34;,\u0026#34;TaskName\u0026#34;:\u0026#34;bumpInDoublePick\u0026#34;,\u0026#34;Group\u0026#34;:\u0026#34;nil\u0026#34;,\u0026#34;Tags\u0026#34;:{},\u0026#34;ID\u0026#34;:\u0026#34;table_test:nil\u0026#34;,\u0026#34;Fields\u0026#34;:{\u0026#34;data.pred\u0026#34;:null,\u0026#34;hw.pred\u0026#34;:1.0480758394807048},\u0026#34;Level\u0026#34;:\u0026#34;CRITICAL\u0026#34;,\u0026#34;Time\u0026#34;:\u0026#34;2017-04-05T15:49:57.146203883Z\u0026#34;,\u0026#34;Message\u0026#34;:\u0026#34;\\u003cno value\\u003e \\u003cno value\\u003e\u0026#34;}\n","time":"2017-04-05T15:49:57.146203883Z","duration":0,"level":"CRITICAL","data":{"Series":[{"name":"table_test","columns":["time","data.pred","hw.pred"],"values":[["2017-04-05T15:49:57.146203883Z",null,1.0480758394807048]]}],"Messages":null,"Err":null}}
{"id":"table_test:nil","message":"45 0.806465263666515","details":"{\u0026#34;Name\u0026#34;:\u0026#34;table_test\u0026#34;,\u0026#34;TaskName\u0026#34;:\u0026#34;bumpInDoublePick\u0026#34;,\u0026#34;Group\u0026#34;:\u0026#34;nil\u0026#34;,\u0026#34;Tags\u0026#34;:{},\u0026#34;ID\u0026#34;:\u0026#34;table_test:nil\u0026#34;,\u0026#34;Fields\u0026#34;:{\u0026#34;data.key\u0026#34;:\u0026#34;45\u0026#34;,\u0026#34;data.value\u0026#34;:0.806465263666515,\u0026#34;hw.key\u0026#34;:null,\u0026#34;hw.value\u0026#34;:null},\u0026#34;Level\u0026#34;:\u0026#34;OK\u0026#34;,\u0026#34;Time\u0026#34;:\u0026#34;2017-04-05T15:50:00Z\u0026#34;,\u0026#34;Message\u0026#34;:\u0026#34;45 0.806465263666515\u0026#34;}\n","time":"2017-04-05T15:50:00Z","duration":2853796117,"level":"OK","data":{"Series":[{"name":"table_test","columns":["time","data.key","data.value","hw.key","hw.value"],"values":[["2017-04-05T15:31:17.146203883Z","45",0.806465263666515,null,null],["2017-04-05T15:31:27.146203883Z","46",0.788332170379725,null,null],["2017-04-05T15:31:37.146203883Z","47",0.847472078582032,null,null],["2017-04-05T15:31:47.146203883Z","48",0.849678013109906,null,null],["2017-04-05T15:31:57.146203883Z","49",0.823709590787247,null,null],["2017-04-05T15:32:07.146203883Z","50",0.831483656114628,null,null],["2017-04-05T15:32:17.146203883Z","51",0.853020620915506,null,null],["2017-04-05T15:32:27.146203883Z","52",0.86259403048251,null,null],["2017-04-05T15:32:37.146203883Z","53",0.936145876802039,null,null],["2017-04-05T15:32:47.146203883Z","54",0.976367890436606,null,null],["2017-04-05T15:32:57.146203883Z","55",0.88937851514957,null,null],["2017-04-05T15:33:07.146203883Z","56",0.977133738890302,null,null],["2017-04-05T15:33:17.146203883Z","57",0.952061050395097,null,null],["2017-04-05T15:33:27.146203883Z","58",0.84955330282307,null,null],["2017-04-05T15:33:37.146203883Z","59",0.968224668406904,null,null],["2017-04-05T15:33:47.146203883Z","60",0.926929870727936,null,null],["2017-04-05T15:33:57.146203883Z","61",0.925827904875194,null,null],["2017-04-05T15:34:07.146203883Z","62",0.938144057185285,null,null],["2017-04-05T15:34:17.146203883Z","63",0.919315520026566,null,null],["2017-04-05T15:34:27.146203883Z","64",0.993264751819099,null,null],["2017-04-05T15:34:37.146203883Z","65",0.953270530442325,null,null],["2017-04-05T15:34:47.146203883Z","66",0.942575471516145,null,null],["2017-04-05T15:34:57.146203883Z","67",0.929756941895965,null,null],["2017-04-05T15:35:07.146203883Z","68",0.975892393115783,null,null],["2017-04-05T15:35:17.146203883Z","69",0.988591960153019,null,null],["2017-04-05T15:35:27.146203883Z","70",0.990990784038134,null,null],["2017-04-05T15:35:37.146203883Z","71",1.02190297565667,null,null],["2017-04-05T15:35:47.146203883Z","72",0.923219959159387,null,null],["2017-04-05T15:35:57.146203883Z","73",0.962900884395427,null,null],["2017-04-05T15:36:07.146203883Z","74",1.03207338826904,null,null],["2017-04-05T15:36:17.146203883Z","75",1.04222719419029,null,null],["2017-04-05T15:36:27.146203883Z","76",0.969899591328141,null,null],["2017-04-05T15:36:37.146203883Z","77",1.00884108655829,null,null],["2017-04-05T15:36:47.146203883Z","78",1.02114604217275,null,null],["2017-04-05T15:36:57.146203883Z","79",1.009287549929,null,null],["2017-04-05T15:37:07.146203883Z","80",0.953589237200079,null,null],["2017-04-05T15:37:17.146203883Z","81",0.877072790193951,null,null],["2017-04-05T15:37:27.146203883Z","82",1.027013269462,null,null],["2017-04-05T15:37:37.146203883Z","83",0.999182678580017,null,null],["2017-04-05T15:37:47.146203883Z","84",1.08939975106345,null,null],["2017-04-05T15:37:57.146203883Z","85",0.872077179472199,null,null],["2017-04-05T15:38:07.146203883Z","86",1.02092882382628,null,null],["2017-04-05T15:38:17.146203883Z","87",1.05118742969453,null,null],["2017-04-05T15:38:27.146203883Z","88",0.932134718462688,null,null],["2017-04-05T15:38:37.146203883Z","89",0.965014672781411,null,null],["2017-04-05T15:38:47.146203883Z","90",0.980338774007495,null,null],["2017-04-05T15:38:57.146203883Z","91",0.944458450389279,null,null],["2017-04-05T15:39:07.146203883Z","92",0.917138313456831,null,null],["2017-04-05T15:39:17.146203883Z","93",0.971758640827266,null,null],["2017-04-05T15:39:27.146203883Z","94",0.950486543744397,null,null],["2017-04-05T15:39:37.146203883Z","95",1.04540082808941,null,null],["2017-04-05T15:39:47.146203883Z","96",1.01173526583407,null,null],["2017-04-05T15:39:57.146203883Z","97",0.858635655149827,null,null],["2017-04-05T15:40:07.146203883Z","98",1.01708545374013,null,null],["2017-04-05T15:40:17.146203883Z","99",0.923138832749929,null,null],["2017-04-05T15:40:27.146203883Z","100",0.970270779116239,null,null],["2017-04-05T15:40:37.146203883Z","101",0.949530049109691,null,null],["2017-04-05T15:40:47.146203883Z","102",0.927943430350348,null,null],["2017-04-05T15:40:57.146203883Z","103",0.850110985256281,null,null],["2017-04-05T15:41:07.146203883Z","104",0.870063426889812,null,null],["2017-04-05T15:41:17.146203883Z","105",0.943297046359508,null,null],["2017-04-05T15:41:27.146203883Z","106",0.875380666427882,null,null],["2017-04-05T15:41:37.146203883Z","107",0.798631552795174,null,null],["2017-04-05T15:41:47.146203883Z","108",0.913025241545264,null,null],["2017-04-05T15:41:57.146203883Z","109",0.908900176552289,null,null],["2017-04-05T15:42:07.146203883Z","110",0.783656340829402,null,null],["2017-04-05T15:42:17.146203883Z","111",0.84428829427349,null,null],["2017-04-05T15:42:27.146203883Z","112",0.74827060391922,null,null],["2017-04-05T15:43:07.146203883Z","116",0.803032788171713,null,null],["2017-04-05T15:43:17.146203883Z","117",0.665459427875805,null,null]]}],"Messages":null,"Err":null}}
{"id":"table_test:nil","message":"\u003cno value\u003e \u003cno value\u003e","details":"{\u0026#34;Name\u0026#34;:\u0026#34;table_test\u0026#34;,\u0026#34;TaskName\u0026#34;:\u0026#34;bumpInDoublePick\u0026#34;,\u0026#34;Group\u0026#34;:\u0026#34;nil\u0026#34;,\u0026#34;Tags\u0026#34;:{},\u0026#34;ID\u0026#34;:\u0026#34;table_test:nil\u0026#34;,\u0026#34;Fields\u0026#34;:{\u0026#34;data.pred\u0026#34;:null,\u0026#34;hw.pred\u0026#34;:1.0480758394807048},\u0026#34;Level\u0026#34;:\u0026#34;CRITICAL\u0026#34;,\u0026#34;Time\u0026#34;:\u0026#34;2017-04-05T15:53:17.146203883Z\u0026#34;,\u0026#34;Message\u0026#34;:\u0026#34;\\u003cno value\\u003e \\u003cno value\\u003e\u0026#34;}\n","time":"2017-04-05T15:53:17.146203883Z","duration":0,"level":"CRITICAL","data":{"Series":[{"name":"table_test","columns":["time","data.pred","hw.pred"],"values":[["2017-04-05T15:53:17.146203883Z",null,1.0480758394807048]]}],"Messages":null,"Err":null}}
{"id":"table_test:nil","message":"45 0.806465263666515","details":"{\u0026#34;Name\u0026#34;:\u0026#34;table_test\u0026#34;,\u0026#34;TaskName\u0026#34;:\u0026#34;bumpInDoublePick\u0026#34;,\u0026#34;Group\u0026#34;:\u0026#34;nil\u0026#34;,\u0026#34;Tags\u0026#34;:{},\u0026#34;ID\u0026#34;:\u0026#34;table_test:nil\u0026#34;,\u0026#34;Fields\u0026#34;:{\u0026#34;data.key\u0026#34;:\u0026#34;45\u0026#34;,\u0026#34;data.value\u0026#34;:0.806465263666515,\u0026#34;hw.key\u0026#34;:null,\u0026#34;hw.value\u0026#34;:null},\u0026#34;Level\u0026#34;:\u0026#34;OK\u0026#34;,\u0026#34;Time\u0026#34;:\u0026#34;2017-04-05T16:00:00Z\u0026#34;,\u0026#34;Message\u0026#34;:\u0026#34;45 0.806465263666515\u0026#34;}\n","time":"2017-04-05T16:00:00Z","duration":402853796117,"level":"OK","data":{"Series":[{"name":"table_test","columns":["time","data.key","data.value","hw.key","hw.value"],"values":[["2017-04-05T15:31:17.146203883Z","45",0.806465263666515,null,null],["2017-04-05T15:31:27.146203883Z","46",0.788332170379725,null,null],["2017-04-05T15:31:37.146203883Z","47",0.847472078582032,null,null],["2017-04-05T15:31:47.146203883Z","48",0.849678013109906,null,null],["2017-04-05T15:31:57.146203883Z","49",0.823709590787247,null,null],["2017-04-05T15:32:07.146203883Z","50",0.831483656114628,null,null],["2017-04-05T15:32:17.146203883Z","51",0.853020620915506,null,null],["2017-04-05T15:32:27.146203883Z","52",0.86259403048251,null,null],["2017-04-05T15:32:37.146203883Z","53",0.936145876802039,null,null],["2017-04-05T15:32:47.146203883Z","54",0.976367890436606,null,null],["2017-04-05T15:32:57.146203883Z","55",0.88937851514957,null,null],["2017-04-05T15:33:07.146203883Z","56",0.977133738890302,null,null],["2017-04-05T15:33:17.146203883Z","57",0.952061050395097,null,null],["2017-04-05T15:33:27.146203883Z","58",0.84955330282307,null,null],["2017-04-05T15:33:37.146203883Z","59",0.968224668406904,null,null],["2017-04-05T15:33:47.146203883Z","60",0.926929870727936,null,null],["2017-04-05T15:33:57.146203883Z","61",0.925827904875194,null,null],["2017-04-05T15:34:07.146203883Z","62",0.938144057185285,null,null],["2017-04-05T15:34:17.146203883Z","63",0.919315520026566,null,null],["2017-04-05T15:34:27.146203883Z","64",0.993264751819099,null,null],["2017-04-05T15:34:37.146203883Z","65",0.953270530442325,null,null],["2017-04-05T15:34:47.146203883Z","66",0.942575471516145,null,null],["2017-04-05T15:34:57.146203883Z","67",0.929756941895965,null,null],["2017-04-05T15:35:07.146203883Z","68",0.975892393115783,null,null],["2017-04-05T15:35:17.146203883Z","69",0.988591960153019,null,null],["2017-04-05T15:35:27.146203883Z","70",0.990990784038134,null,null],["2017-04-05T15:35:37.146203883Z","71",1.02190297565667,null,null],["2017-04-05T15:35:47.146203883Z","72",0.923219959159387,null,null],["2017-04-05T15:35:57.146203883Z","73",0.962900884395427,null,null],["2017-04-05T15:36:07.146203883Z","74",1.03207338826904,null,null],["2017-04-05T15:36:17.146203883Z","75",1.04222719419029,null,null],["2017-04-05T15:36:27.146203883Z","76",0.969899591328141,null,null],["2017-04-05T15:36:37.146203883Z","77",1.00884108655829,null,null],["2017-04-05T15:36:47.146203883Z","78",1.02114604217275,null,null],["2017-04-05T15:36:57.146203883Z","79",1.009287549929,null,null],["2017-04-05T15:37:07.146203883Z","80",0.953589237200079,null,null],["2017-04-05T15:37:17.146203883Z","81",0.877072790193951,null,null],["2017-04-05T15:37:27.146203883Z","82",1.027013269462,null,null],["2017-04-05T15:37:37.146203883Z","83",0.999182678580017,null,null],["2017-04-05T15:37:47.146203883Z","84",1.08939975106345,null,null],["2017-04-05T15:37:57.146203883Z","85",0.872077179472199,null,null],["2017-04-05T15:38:07.146203883Z","86",1.02092882382628,null,null],["2017-04-05T15:38:17.146203883Z","87",1.05118742969453,null,null],["2017-04-05T15:38:27.146203883Z","88",0.932134718462688,null,null],["2017-04-05T15:38:37.146203883Z","89",0.965014672781411,null,null],["2017-04-05T15:38:47.146203883Z","90",0.980338774007495,null,null],["2017-04-05T15:38:57.146203883Z","91",0.944458450389279,null,null],["2017-04-05T15:39:07.146203883Z","92",0.917138313456831,null,null],["2017-04-05T15:39:17.146203883Z","93",0.971758640827266,null,null],["2017-04-05T15:39:27.146203883Z","94",0.950486543744397,null,null],["2017-04-05T15:39:37.146203883Z","95",1.04540082808941,null,null],["2017-04-05T15:39:47.146203883Z","96",1.01173526583407,null,null],["2017-04-05T15:39:57.146203883Z","97",0.858635655149827,null,null],["2017-04-05T15:40:07.146203883Z","98",1.01708545374013,null,null],["2017-04-05T15:40:17.146203883Z","99",0.923138832749929,null,null],["2017-04-05T15:40:27.146203883Z","100",0.970270779116239,null,null],["2017-04-05T15:40:37.146203883Z","101",0.949530049109691,null,null],["2017-04-05T15:40:47.146203883Z","102",0.927943430350348,null,null],["2017-04-05T15:40:57.146203883Z","103",0.850110985256281,null,null],["2017-04-05T15:41:07.146203883Z","104",0.870063426889812,null,null],["2017-04-05T15:41:17.146203883Z","105",0.943297046359508,null,null],["2017-04-05T15:41:27.146203883Z","106",0.875380666427882,null,null],["2017-04-05T15:41:37.146203883Z","107",0.798631552795174,null,null],["2017-04-05T15:41:47.146203883Z","108",0.913025241545264,null,null],["2017-04-05T15:41:57.146203883Z","109",0.908900176552289,null,null],["2017-04-05T15:42:07.146203883Z","110",0.783656340829402,null,null],["2017-04-05T15:42:17.146203883Z","111",0.84428829427349,null,null],["2017-04-05T15:42:27.146203883Z","112",0.74827060391922,null,null],["2017-04-05T15:43:07.146203883Z","116",0.803032788171713,null,null],["2017-04-05T15:43:17.146203883Z","117",0.665459427875805,null,null],["2017-04-05T15:57:07.146203883Z","200",0.821036674196026,null,null],["2017-04-05T15:57:17.146203883Z","201",0.73029505215544,null,null],["2017-04-05T15:58:07.146203883Z","206",0.901157862751018,null,null],["2017-04-05T15:58:17.146203883Z","207",0.867616432372154,null,null],["2017-04-05T15:58:27.146203883Z","208",0.858176951735647,null,null],["2017-04-05T15:58:37.146203883Z","209",0.851089086493786,null,null],["2017-04-05T15:58:47.146203883Z","210",0.943305888631815,null,null],["2017-04-05T15:58:57.146203883Z","211",0.845032948380737,null,null],["2017-04-05T15:59:07.146203883Z","212",0.890394633578303,null,null],["2017-04-05T15:59:17.146203883Z","213",0.886702736749359,null,null],["2017-04-05T15:59:27.146203883Z","214",0.80120704185473,null,null],["2017-04-05T15:59:37.146203883Z","215",0.901836120063745,null,null],["2017-04-05T15:59:47.146203883Z","216",0.9457774288083,null,null],["2017-04-05T15:59:57.146203883Z","217",0.933623554689181,null,null]]}],"Messages":null,"Err":null}}

Kapacitor also writes this many times into the console:

kapacitor_1         | [bumpInDoublePick:alert8] 2017/04/05 15:24:07 E! error evaluating expression for level CRITICAL: no field or tag exists for hw.pred

Thanks again and best regards,
Simon

6

You are correct the join operation is not working the way you have it setup, which is expected. The join operation joins on the time value of the points, since the holtWinters method forecasts point into the future the result will not join back with the original data unless you do something else. You have a few options:

  1. Forecast out all of your data and join all the forecasted data. This was the first example I gave.
  2. Shift the original data forward in time to match with the forecasted data.
  3. Shift the forecasted data backward in time to match with the original data.

For both options 2 and 3 you can use the shift node ShiftNode | InfluxData Documentation Archive.

To determine which method you should use answer the question what is the relationship between the points in the original data and the points in the forecasted data?

7

Thanks for you answer. My description was a little bit wrong, because I have to compare the forecasted value with the next incoming value after the original value (to compare the forecast and the real value).
The values are incoming every 10 seconds, so I have to shift the predicted value to the time of the original value + 10 seconds. I think this can be done by the eval function below:

var hw = data
    |holtWinters('value', 1, 0, 10m)
        .as('pred')
    |eval(10s-(time-'data.time'))
        .keep('pred')
    |shift(eval)

but it’s not proper yet, console output:

invalid TICKscript: unsupported literal type func(time.Duration, ...time.Duration) (kapacitor.TimeDimension, error)

8

No need for the eval step here, the shift node will do what you need.

var hw = data
    |holtWinters('value', 1, 0, 10m)
        .as('pred')
    |shift(-10s)
9

Thanks, but I think this will align the predicted value with the second last real value.
I think, it is just not possible with my desired setup to predict the next value with Holt-Winters and store this value on-the-fly till the real value comes in, and comparing the predicted with the real value (e.g. as an anomaly detection system).
So I will probably have to adjust my test setup.

But thanks again for your great support :thumbsup: