How to create an Modbus-TCP alarm status dashboard

hehe .. and then Steve proceeds to analyse the packets with wireshark and writes his own implementation of modbus requests / replies :wink:

yea .. possibly the modbus node was a little buggy with the reply length.
judging from his replies on github its a pitty the developer doesnt have much free time to develop it further.

I read a bit on the modbus specifications and apparently there is a limit of 256 bytes for the reply.
One interesting post i found was of a person making some calculations taking into account the bytes needed for the headers and deducts that the max number of registers that can be read in a single Modbus/RTU query is 125 and 123 for Modbus TCP. ARTICLE

2 Likes

Based on your suggestions, here is the flow:

It can be extended to as many addresses needed to be read.
In function node I mage the following change:

from

var current = [...msg.payload];
for (var i = 0; i < msg.payload.length; i++) { ...

to

var current = [...msg.data1,...msg.data2,...msg.data3];
for (var i = 0; i < current.length; i++) {....

thanks for your help

[{"id":"5860dff5.9728c8","type":"modbus-getter","z":"1ac2afc6.199a18","name":"0 ~ 99","showStatusActivities":true,"showErrors":true,"logIOActivities":false,"unitid":"1","dataType":"HoldingRegister","adr":"0","quantity":"100","server":"2979eedb.de0842","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":true,"keepMsgProperties":false,"x":330,"y":960,"wires":[["68d2243e.eca644"],[]]},{"id":"7e92c260.6f4cc4","type":"modbus-getter","z":"1ac2afc6.199a18","name":"100 ~ 199","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"unitid":"1","dataType":"HoldingRegister","adr":"100","quantity":"100","server":"2979eedb.de0842","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"keepMsgProperties":true,"x":330,"y":1020,"wires":[["9ffd9b1c.94276"],[]]},{"id":"1c8d5b97.10df34","type":"inject","z":"1ac2afc6.199a18","name":"5 sec","props":[{"p":"topic","vt":"str"},{"p":"payload"}],"repeat":"5","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"true","payloadType":"bool","x":190,"y":960,"wires":[["5860dff5.9728c8"]]},{"id":"afcec612.c1d05","type":"modbus-getter","z":"1ac2afc6.199a18","name":"200 ~ 299","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"unitid":"1","dataType":"HoldingRegister","adr":"200","quantity":"100","server":"2979eedb.de0842","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"keepMsgProperties":true,"x":330,"y":1080,"wires":[["5aaf8adf.7150f4"],[]]},{"id":"68d2243e.eca644","type":"change","z":"1ac2afc6.199a18","name":"","rules":[{"t":"set","p":"data1","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":500,"y":960,"wires":[["7e92c260.6f4cc4"]]},{"id":"9ffd9b1c.94276","type":"change","z":"1ac2afc6.199a18","name":"","rules":[{"t":"set","p":"data2","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":500,"y":1020,"wires":[["afcec612.c1d05"]]},{"id":"5aaf8adf.7150f4","type":"change","z":"1ac2afc6.199a18","name":"","rules":[{"t":"set","p":"data3","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":500,"y":1080,"wires":[["772be6c0.996ca"]]},{"id":"772be6c0.996ca","type":"buffer-array","z":"1ac2afc6.199a18","name":"","bufferLen":"3","startWhenFilled":true,"x":490,"y":1140,"wires":[["9f1d845b.532d8"]]},{"id":"9f1d845b.532d8","type":"debug","z":"1ac2afc6.199a18","name":"Modbus Read","active":false,"tosidebar":true,"console":true,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":670,"y":1140,"wires":[]},{"id":"2979eedb.de0842","type":"modbus-client","name":"","clienttype":"tcp","bufferCommands":true,"stateLogEnabled":false,"queueLogEnabled":false,"tcpHost":"172.16.10.71","tcpPort":"502","tcpType":"DEFAULT","serialPort":"/dev/ttyUSB","serialType":"RTU-BUFFERD","serialBaudrate":"9600","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"100","unit_id":"1","commandDelay":"1","clientTimeout":"1000","reconnectOnTimeout":true,"reconnectTimeout":"2000","parallelUnitIdsAllowed":true}]

I want to display on the dashboard the modebus connection status.

How can I extract the msg :error that is display by debugging node?
I try to use the status node, I'm getting the node status but not the error message.

Screenshot_2021-03-28 Node-RED 172 16 10 226(1)

Screenshot_2021-03-28 Node-RED 172 16 10 226(2)

Thanks.

Hi .. the way I handled Modbus errors from these nodes was to enable in their configuration the option to output an Empty msg on fail.

image

After that i wired a function node on each of the reads to check whether the msg was an error or valid modbus read and route it to the appropriate function output.
( top output if it was an error, bottom output if it was valid )

Example flow :

[{"id":"5860dff5.9728c8","type":"modbus-getter","z":"c7c6d4d7.58fc3","name":"0 ~ 99","showStatusActivities":true,"showErrors":false,"logIOActivities":false,"unitid":"1","dataType":"HoldingRegister","adr":"0","quantity":"100","server":"2979eedb.de0842","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":true,"keepMsgProperties":true,"x":310,"y":180,"wires":[["50f4d5f8.e58de4"],[]]},{"id":"7e92c260.6f4cc4","type":"modbus-getter","z":"c7c6d4d7.58fc3","name":"100 ~ 199","showStatusActivities":true,"showErrors":false,"logIOActivities":false,"unitid":"1","dataType":"HoldingRegister","adr":"100","quantity":"100","server":"","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":true,"keepMsgProperties":true,"x":290,"y":300,"wires":[["7530ea64.384ca4"],[]]},{"id":"1c8d5b97.10df34","type":"inject","z":"c7c6d4d7.58fc3","name":"5 sec","props":[{"p":"topic","vt":"str"},{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"true","payloadType":"bool","x":170,"y":180,"wires":[["5860dff5.9728c8"]]},{"id":"afcec612.c1d05","type":"modbus-getter","z":"c7c6d4d7.58fc3","name":"200 ~ 299","showStatusActivities":true,"showErrors":false,"logIOActivities":false,"unitid":"1","dataType":"HoldingRegister","adr":"200","quantity":"100","server":"","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":true,"keepMsgProperties":true,"x":290,"y":420,"wires":[["cf904603.e7106"],[]]},{"id":"9f1d845b.532d8","type":"debug","z":"c7c6d4d7.58fc3","name":"Modbus Read","active":true,"tosidebar":true,"console":true,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":810,"y":480,"wires":[]},{"id":"d711d966.b30618","type":"debug","z":"c7c6d4d7.58fc3","name":"error","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":610,"y":160,"wires":[]},{"id":"50f4d5f8.e58de4","type":"function","z":"c7c6d4d7.58fc3","name":"error","func":"let t = new Date()\nt = (('00' + t.getHours()).slice(-2) + ':' + ('00' + t.getMinutes()).slice(-2) + ':' + ('00' + t.getSeconds()).slice(-2) + ' / ' + ('00' + (t.getMonth() + 1)).slice(-2) + '.' + ('00' + t.getDate()).slice(-2) + '.' + t.getFullYear());\n\nif (msg.payload == \"\" || msg.hasOwnProperty('error')) {\n    \nnode.status({fill:\"red\",shape:\"ring\",text:`Last disconnected ${t}`});\nreturn [msg, null] // output error on first output\n}\n\nelse {\n    \nmsg.data1 = msg.payload\nreturn [null, msg];  // output valid msg on second output\n}","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":450,"y":180,"wires":[["d711d966.b30618"],["7e92c260.6f4cc4"]]},{"id":"7530ea64.384ca4","type":"function","z":"c7c6d4d7.58fc3","name":"error","func":"let t = new Date()\nt = (('00' + t.getHours()).slice(-2) + ':' + ('00' + t.getMinutes()).slice(-2) + ':' + ('00' + t.getSeconds()).slice(-2) + ' / ' + ('00' + (t.getMonth() + 1)).slice(-2) + '.' + ('00' + t.getDate()).slice(-2) + '.' + t.getFullYear());\n\nif (msg.payload == \"\" || msg.hasOwnProperty('error')) {\n    \nnode.status({fill:\"red\",shape:\"ring\",text:`Last disconnected ${t}`});\nreturn [msg, null] // output error on first output\n}\n\nelse {\n    \nmsg.data2 = msg.payload\nreturn [null, msg];  // output valid msg on second output\n}","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":450,"y":300,"wires":[["9fe4b0a3.20daf"],["afcec612.c1d05"]]},{"id":"9fe4b0a3.20daf","type":"debug","z":"c7c6d4d7.58fc3","name":"error","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":610,"y":280,"wires":[]},{"id":"cf904603.e7106","type":"function","z":"c7c6d4d7.58fc3","name":"error","func":"let t = new Date()\nt = (('00' + t.getHours()).slice(-2) + ':' + ('00' + t.getMinutes()).slice(-2) + ':' + ('00' + t.getSeconds()).slice(-2) + ' / ' + ('00' + (t.getMonth() + 1)).slice(-2) + '.' + ('00' + t.getDate()).slice(-2) + '.' + t.getFullYear());\n\nif (msg.payload == \"\" || msg.hasOwnProperty('error')) {\n    \nnode.status({fill:\"red\",shape:\"ring\",text:`Last disconnected ${t}`});\nreturn [msg, null] // output error on first output\n}\n\nelse {\n    \nmsg.data3 = msg.payload\nreturn [null, msg];  // output valid msg on second output\n}","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":450,"y":420,"wires":[["5abf456b.7e6a14"],["f0992446.7f13d8"]]},{"id":"5abf456b.7e6a14","type":"debug","z":"c7c6d4d7.58fc3","name":"error","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":610,"y":400,"wires":[]},{"id":"f0992446.7f13d8","type":"function","z":"c7c6d4d7.58fc3","name":"format msg","func":"msg.payload = [...msg.data1, ...msg.data2, ...msg.data3]\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":630,"y":480,"wires":[["9f1d845b.532d8"]]},{"id":"2979eedb.de0842","type":"modbus-client","name":"","clienttype":"tcp","bufferCommands":true,"stateLogEnabled":false,"queueLogEnabled":false,"tcpHost":"172.16.10.71","tcpPort":"502","tcpType":"DEFAULT","serialPort":"/dev/ttyUSB","serialType":"RTU-BUFFERD","serialBaudrate":"9600","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"100","unit_id":1,"commandDelay":1,"clientTimeout":1000,"reconnectOnTimeout":true,"reconnectTimeout":2000,"parallelUnitIdsAllowed":true}]

Im not sure if the above helps but in my case i found it easier than using Status nodes because of the flexibility the Function node gives you to also modify the error msg to your needs.

Hello,
thanks for sharing.
My idea was to transfer the message to the dashboard, and beside the status to get to display the error message too.
I'm not sure how to extract the error message display by the debugging node.
.
Screenshot_2021-03-28 Node-RED Dashboard(2)
Screenshot_2021-03-28 Node-RED Dashboard(1)

I didn't study your exchange of code regarding the dashboard part (i got lost in the code) :sweat_smile:
but you could use Flow Context in each of the "error" functions to set the status for each of your devices

let t = new Date()
t = (('00' + t.getHours()).slice(-2) + ':' + ('00' + t.getMinutes()).slice(-2) + ':' + ('00' + t.getSeconds()).slice(-2) + ' / ' + ('00' + (t.getMonth() + 1)).slice(-2) + '.' + ('00' + t.getDate()).slice(-2) + '.' + t.getFullYear());

if (msg.payload == "" || msg.hasOwnProperty('error')) {

node.status({fill:"red",shape:"ring",text:`Last disconnected ${t}`});
flow.set('Status.Device1', {"Status": "Failed", "lastDisconnected": t, "error": msg.error })  // set Status context failed
return [msg, null] // output error on first output
}

else {

msg.data1 = msg.payload
flow.set('Status.Device1', {"Status": "Active", "lastDisconnected": t, "error": ""})  // // set Status context ok
return [null, msg];  // output valid msg on second output
}

and later in the "generate alarm table" function read again the Flow context flow.get('Status') and incorporate the status in the table .. somehow.

Hi Steve,

Can you please help me with the correct syntax to extract the "S/N..." for the lookup list.
Thanks.

var lookup = {
  1: "Door Sensor", "S/N: 012456",
  2: "Window Sensor",  "S/N: 315352"
}

I have another question regarding deleting parts of the database.

I create database with this topic message:

CREATE TABLE 'alarmlog' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 'Time' TEXT, 'Channel' TEXT, 'Alarm' TEXT, 'Status' TEXT, 'epoch' INTEGER, 'timestamp' INTEGER DEFAULT CURRENT_TIMESTAMP)

to display the last 24hours alarms I use this topic message:

let d = new Date();
let epoch = d.getTime();
let fromdate = 0;
let enddate = 0;

    fromdate = epoch - 1000*60*60*24*1;
    topic = "SELECT * FROM alarmlog WHERE epoch > "+fromdate;

msg.topic = topic+ " ORDER BY timestamp DESC";
return msg;

I want to delete all records older then 7 days, I try to use this topic but does work.

var d = new Date();
var epoch = d.getTime();
let fromdate = 0;

// today - 7 days (1000*60*60*24*7)
   fromdate = epoch - 1000*60*60*24*7;

topic = "DELETE FROM alarmlog WHERE epoch < "+fromdate;
msg.topic = topic+ " ORDER BY timestamp DESC LIMIT 10";
return msg;

Thanks for your help

Depends what you are trying to achieve.

if you want to get multiple values from a lookup you need the looked up item to be either an array or object...

Lookup an object...

var lookup = {
  1: {"name": "Door Sensor", "SN": "012456"},
  2: {"name": "Window Sensor",  "SN": "315352"}
}

Then you would use it like this...

var item = lookup["1"];
var details = "The name is " + item.name + " and serial No. is " + item.SN;


Lookup an array...

var lookup = {
  1: ["Door Sensor", "S/N: 012456"],
  2: ["Window Sensor",  "S/N: 315352"]
}

Then you would use it like this...

var item = lookup["1"];
var details = "The name is " + item[0] + " and serial No. is " + item[1];

Not really a node-red question but I feel generous :wink:

Untested (off the top of my head)...

var d = new Date();
d.setDate(d.getDate()-7);
var epoch7Daysago = d.valueOf();
msg.topic = "DELETE FROM alarmlog WHERE epoch < " + epoch7Daysago;
return msg;

If it doesnt work, show me a sample of data you have in alarmlog table

Thanks for your help,

I will test your code and get back with the results.
Here is a sample of my database:

By the way, I find an awesome database reader:

This is way good things happens to good people :grinning:
Much appreciate yout time and help.

Hi all .. regarding the deletion of records older than 7 days ...
since you also have the timestamp column you could also try

msg.topic = `SELECT * FROM alarmlog WHERE datetime(timestamp, 'localtime') < datetime('now', 'localtime', '-7 days')`;

SQLite datetime Function

This way you can avoid the additional javascript to calculate the epoh.

ps1. in above query i used SELECT just to see if it gives you the right result and you can then replace it with DELETE

ps2. you mentioned above that you used ... 'timestamp' INTEGER DEFAULT CURRENT_TIMESTAMP ...
to create the table.
How you insert a datetime in a column that is defined as INTEGER. it doesnt give an error ? What does DB Browser say about the type of that column ?

Hello,

Here is the code I use to insert data from a function node, no errors, I don't insert the timestamp, I presume the SQLite fills the column with the timestamp when the insert occur.

node.send( [null, { topic: ("INSERT INTO alarmlog (Time,Channel,Alarm,Status,epoch) " +
    "VALUES ('"+row.Time+"','"+row.ChannelNo+"','"+row.Alarmlog+"','"+row.Statuslog+"',"+epoch+")")}] );

Thanks.

Hello again,

This code works fine, thanks.

I try it with 'minutes' instead of 'days' and works great too.
This is a much easy way to sort database and no bother to store epoch.

I try to use DELETE command but nothing happens, no errors in node-red, however in DB-Browser I'm getting this error:

Execution finished with errors.
Result: attempt to write a readonly database
At line 1:
DELETE FROM alarmlog WHERE datetime(timestamp, 'localtime') < datetime('now', 'localtime', '-1 days')

SELECT command works fine:
SELECT * FROM alarmlog WHERE datetime(timestamp, 'localtime') < datetime('now', 'localtime', '-1 days')

How can I give write permissions to my db?
Thanks for your help,

I dont think its a db persmission issue because the Insert commands are working .. at least in node-red.

Possibly because DB Browser and Node-red had the database file open at the same time ?
try to close one or the other and try the DELETE command again

Hello,
I try to use DELETE command with database been used individually only by Node-Red or DB Browser,
but same results.
Any guidance how to fix this?
Thanks.

Hi .. sorry but there gonna be more questions than answers.

  1. just to clarify .. the Insert sql commands work and just the Delete query command doesnt work ?
  2. What Operating System are you using for the Node-red server ?
  3. Are you using Docker ?
  4. In the configuration of the Sqlite nodes (check all instances if there are many) ..
    have you set the Mode to be Read-Write-Create ?
    image
  5. Can you share the flow to check how exactly you implemented the delete command ?

Hello,

First let me answer your questions:

  1. INSERT, SELECT - works properly just DELETE command doesn't work
  2. Raspberry Pi OS Lite
  3. no Docker
  4. All instances are Read-Write-Create

Here is a simplify flow including just the SQlite nodes, I add the insert node as my original flow gets the insert from Modbus.
Interesting is that in this flow the DELETE commands works :see_no_evil:.
I have to check why in my original flow same DELETE syntax doesn't perform (no errors).

thanks for your help.

[{"id":"6603ad1a.d86384","type":"sqlite","z":"9d6ab3c7.985c","mydb":"10fc33a3.4b18bc","sqlquery":"msg.topic","sql":"","name":"test Log","x":480,"y":1380,"wires":[["41292ce7.18f4dc"]]},{"id":"6d41f9d.fdf1288","type":"inject","z":"9d6ab3c7.985c","name":"create database","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"CREATE TABLE 'alarmlog' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 'Time' TEXT, 'Channel' TEXT, 'Alarm' TEXT, 'Status' TEXT, 'epoch' INTEGER, 'timestamp' INTEGER DEFAULT CURRENT_TIMESTAMP)","payload":"","payloadType":"date","x":300,"y":1380,"wires":[["6603ad1a.d86384"]]},{"id":"aedde1d9.afd5c","type":"ui_button","z":"9d6ab3c7.985c","name":"","group":"2787f0f5.7d5fa8","order":5,"width":3,"height":1,"passthru":true,"label":"Last 5 min Alarms ","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"date","topic":"SELECT * FROM alarmlog WHERE datetime(timestamp, 'localtime') > datetime('now', 'localtime', '-5 minutes')  ORDER BY timestamp DESC","topicType":"str","x":750,"y":1620,"wires":[["d34ad534.c1a18"]]},{"id":"d34ad534.c1a18","type":"delay","z":"9d6ab3c7.985c","name":"","pauseType":"delay","timeout":"500","timeoutUnits":"milliseconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":950,"y":1620,"wires":[["6ab078f3.02beb"]]},{"id":"bc54c55.612c9b8","type":"ui_template","z":"9d6ab3c7.985c","group":"2787f0f5.7d5fa8","name":"Alarm Log2","order":2,"width":12,"height":4,"format":"\n<style>\ntable\n{\n    font-size: 13px;\n    color: white;\n    width: 100%;\n    border: 1px color #000;   \n}\n.main\n{\n    border: 1px color #000; \n    width: 100%;\n    height:100px;\n}\n\n    .mytable2 td, .mytable2 th { width:21%;background:darkred; }\n    .mytable2 td + td, .mytable2 th + th { width:11%; }\n    .mytable2 td + td + td, .mytable2 th + th + th { width:52%; }\n    .mytable2 td + td + td + td, .mytable2 th + th + th + td { width:16%; }\n</style>\n<!--\n<div style='background-color: red; width:100%; color:white'></div>\n<div ng-bind-html=\"msg.payload\"></div>\n-->\n\n<div ng-bind-html=\"msg.payload\" height=\"300\" style=\"height: 250px;\"></div>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":1510,"y":1560,"wires":[[]]},{"id":"6ab078f3.02beb","type":"sqlite","z":"9d6ab3c7.985c","mydb":"10fc33a3.4b18bc","sqlquery":"msg.topic","sql":"","name":"test Log","x":1160,"y":1560,"wires":[["b1df61a.aff072","2118aa.0795d756"]]},{"id":"b1df61a.aff072","type":"template","z":"9d6ab3c7.985c","name":"Format","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"\n<table>\n \n    {{#payload}}\n        <tr class=\"mytable2\">\n            <td><center>{{Time}}</td>\n            <td><center>{{Channel}}</td>\n            <td><center>{{Alarm}}</td>\n            <td><center>{{Status}}</td>\n        </tr>\n    {{/payload}}\n</table>\n","output":"str","x":1340,"y":1560,"wires":[["bc54c55.612c9b8"]]},{"id":"1bdfe7e2.998e38","type":"ui_template","z":"9d6ab3c7.985c","group":"2787f0f5.7d5fa8","name":"Alarm Log menu2","order":1,"width":0,"height":0,"format":"\n<style>\ntable\n{\n    font-size: 204px;\n    color: white;\n    width: 100%;\n    border: 1px color #000;\n    margin: 10px 0px 0px 0px;\n\n}\n.main\n{\n    border: 1px color #000; \n    width: 100%;\n\n}\n\n    .mytable td, .mytable th { width:21%;}\n    .mytable td + td, .mytable th + th { width:11%; }\n    .mytable td + td + td, .mytable th + th + th { width:52%; }\n    .mytable td + td + td + td, .mytable th + th + th + td { width:16%;}\n\n</style>\n<!--\n<div style='background-color: red; width:100%; color:white'></div>\n<div ng-bind-html=\"msg.payload\"></div>\n-->\n\n<div ng-bind-html=\"msg.payload\" height=\"0\" style=\"height: 0px;\"></div>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":1530,"y":1520,"wires":[[]]},{"id":"2118aa.0795d756","type":"template","z":"9d6ab3c7.985c","name":"Format","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<table class=\"mytable\" border=\"1\">\n\n    <tr>\n\n        <th>Time</th>\n        <th>Channel</th>\n        <th>Alarm Message</th>\n        <th>Status</th>\n\n    </tr>\n</table>\n\n\n","output":"str","x":1340,"y":1520,"wires":[["1bdfe7e2.998e38"]]},{"id":"ea2ec7ff.4837a8","type":"inject","z":"9d6ab3c7.985c","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":"","topic":"","payload":"","payloadType":"date","x":570,"y":1560,"wires":[["8f2d453e.1844c8"]]},{"id":"8f2d453e.1844c8","type":"delay","z":"9d6ab3c7.985c","name":"","pauseType":"delay","timeout":"500","timeoutUnits":"milliseconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":750,"y":1560,"wires":[["a3ff11c0.9e5e38"]]},{"id":"a3ff11c0.9e5e38","type":"change","z":"9d6ab3c7.985c","name":"Last 10 events","rules":[{"t":"set","p":"topic","pt":"msg","to":"SELECT * FROM alarmlog ORDER BY timestamp DESC LIMIT 10","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":920,"y":1560,"wires":[["6ab078f3.02beb"]]},{"id":"e08024b0.813de","type":"sqlite","z":"9d6ab3c7.985c","mydb":"10fc33a3.4b18bc","sqlquery":"msg.topic","sql":"","name":"test Log","x":620,"y":1500,"wires":[["8f2d453e.1844c8"]]},{"id":"f1e3fb3d.2c0b48","type":"function","z":"9d6ab3c7.985c","name":"","func":"var t = new Date();\nvar epoch = t.getTime();\nTime = (('00' + t.getHours()).slice(-2) + ':' + ('00' + t.getMinutes()).slice(-2) + ':' + ('00' + t.getSeconds()).slice(-2) + ' ' + ('00' + (t.getMonth() + 1)).slice(-2) + '-' + ('00' + t.getDate()).slice(-2) + '-' + (t.getYear()-100));\n\nChannelNo = \"00001\"\nAlarmlog = \"This is a test message!\";\nStatuslog = \"ALARM\"\n\nmsg.topic = \"INSERT INTO alarmlog (Time,Channel,Alarm,Status,epoch) \" + \"VALUES ('\"+Time+\"','\"+ChannelNo+\"','\"+Alarmlog+\"','\"+Statuslog+\"',\"+epoch+\")\";\nmsg.payload = {Time, ChannelNo, Alarmlog, Statuslog};\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":460,"y":1500,"wires":[["e08024b0.813de"]]},{"id":"3bf0e153.dccc2e","type":"inject","z":"9d6ab3c7.985c","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"","payloadType":"date","x":280,"y":1440,"wires":[["f1e3fb3d.2c0b48"]]},{"id":"41292ce7.18f4dc","type":"debug","z":"9d6ab3c7.985c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":650,"y":1380,"wires":[]},{"id":"ef19e047.46f3d8","type":"change","z":"9d6ab3c7.985c","name":"All events","rules":[{"t":"set","p":"topic","pt":"msg","to":"SELECT * FROM alarmlog ORDER BY timestamp DESC","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":960,"y":1680,"wires":[["6ab078f3.02beb"]]},{"id":"6af0717b.ea8668","type":"delay","z":"9d6ab3c7.985c","name":"","pauseType":"delay","timeout":"500","timeoutUnits":"milliseconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":790,"y":1680,"wires":[["ef19e047.46f3d8"]]},{"id":"c1f4d8be.ec9cb","type":"ui_button","z":"9d6ab3c7.985c","name":"","group":"2787f0f5.7d5fa8","order":4,"width":3,"height":1,"passthru":true,"label":"All Alarms","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"date","topic":"","topicType":"str","x":620,"y":1680,"wires":[["6af0717b.ea8668"]]},{"id":"a9a4a1dc.198688","type":"ui_button","z":"9d6ab3c7.985c","name":"","group":"2787f0f5.7d5fa8","order":3,"width":3,"height":1,"passthru":true,"label":"Insert record","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"date","topic":"","topicType":"str","x":270,"y":1500,"wires":[["f1e3fb3d.2c0b48"]]},{"id":"c790c843.20d448","type":"ui_button","z":"9d6ab3c7.985c","name":"","group":"2787f0f5.7d5fa8","order":6,"width":3,"height":1,"passthru":false,"label":"Delete dB > 1 min","tooltip":"","color":"","bgcolor":"orange","icon":"","payload":"","payloadType":"date","topic":"DELETE FROM alarmlog WHERE datetime(timestamp, 'localtime') < datetime('now', 'localtime', '-1 minutes')","topicType":"str","x":930,"y":1740,"wires":[["6ab078f3.02beb"]]},{"id":"10fc33a3.4b18bc","type":"sqlitedb","db":"test.db","mode":"RWC"},{"id":"2787f0f5.7d5fa8","type":"ui_group","name":"Alarms Log","tab":"f3e9af7a.6f0da","order":2,"disp":true,"width":12,"collapse":false},{"id":"f3e9af7a.6f0da","type":"ui_tab","name":"Test","icon":"dashboard","order":4,"disabled":false,"hidden":false}]

Hi .. i imported your flow and the Delete works for me too with the example you shared :wink:
Strange why you have this problem in the original flow .. add complete message debug nodes after the Delete command and see in msg.topic if there are any spelling mistakes in the sql and if all the single quotes ' are in place.