# How to create an Modbus-TCP alarm status dashboard

**URL:** <https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041>\
**Category:** Industrial\
**Created:** [22 March 2021 19:59 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041 "2021-03-22T19:59:23Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [27 March 2021 23:11 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/21 "2021-03-27T23:11:04Z")

</div>

> [@Steve-Mcl](#):
>
> i really dont have the energy to debug it

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

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](https://control.com/forums/threads/maximum-amount-of-holding-registers-per-request.9904/#post-86251)

---

<div class="post-metadata">

**Author:** ![lucibuz](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lucibuz/32/39131_2.png) [@lucibuz](https://discourse.nodered.org/u/lucibuz)\
**Post date:** [28 March 2021 18:46 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/22 "2021-03-28T18:46:04Z")

</div>

Based on your suggestions, here is the flow:

 ![Screenshot_2021-03-28 Node-RED 172 16 10 226](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/d/0d7fb88792187b307763ab46363494174248f527.png)

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

from

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

```

to

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

```

thanks for your help

```auto
[{"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}]

```

---

<div class="post-metadata">

**Author:** ![lucibuz](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lucibuz/32/39131_2.png) [@lucibuz](https://discourse.nodered.org/u/lucibuz)\
**Post date:** [28 March 2021 18:54 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/23 "2021-03-28T18:54:31Z")

</div>

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)](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/b/2b119e080669799a7bd5b3734edc040cac75943e.png)

![Screenshot_2021-03-28 Node-RED 172 16 10 226(2)](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/4/646327dc88535df4134fc6a3844d68cade57ccd8.png)

Thanks.

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [28 March 2021 20:32 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/24 "2021-03-28T20:32:19Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/2/d2f2a66f87a02a7cf42948073af2b4f32a71c7bd.png)

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 )

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/a/1a3a323337a61a3c60eb522632fb4632b2f6b402.png)

Example flow :

```auto
[{"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.

---

<div class="post-metadata">

**Author:** ![lucibuz](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lucibuz/32/39131_2.png) [@lucibuz](https://discourse.nodered.org/u/lucibuz)\
**Post date:** [28 March 2021 20:54 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/25 "2021-03-28T20:54:53Z")

</div>

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)](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/a/8a9017839dda284ac66e3edbcd3a0e622059f1db.png)  
 ![Screenshot_2021-03-28 Node-RED Dashboard(1)](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/a/ba2b5ccb31ea8534abb10ab296a20251c4dc40a4.png)

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [28 March 2021 21:30 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/26 "2021-03-28T21:30:03Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![lucibuz](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lucibuz/32/39131_2.png) [@lucibuz](https://discourse.nodered.org/u/lucibuz)\
**Post date:** [7 April 2021 04:22 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/27 "2021-04-07T04:22:10Z")

</div>

Hi Steve,

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

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

```

---

<div class="post-metadata">

**Author:** ![lucibuz](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lucibuz/32/39131_2.png) [@lucibuz](https://discourse.nodered.org/u/lucibuz)\
**Post date:** [7 April 2021 04:38 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/28 "2021-04-07T04:38:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 April 2021 06:55 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/29 "2021-04-07T06:55:22Z")

</div>

> [@lucibuz](#):
>
> Can you please help me with the correct syntax to extract the "S/N..." for the lookup list.  
> Thanks.
> 
> ```auto
> var lookup = {
> 1: "Door Sensor", "S/N: 012456",
> 2: "Window Sensor", "S/N: 315352"
> }
> 
> ```

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...

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

```

Then you would use it like this...

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

```

  
  

### Lookup an array...

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

```

Then you would use it like this...

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

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 April 2021 07:02 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/30 "2021-04-07T07:02:21Z")

</div>

> [@lucibuz](#):
>
> I want to delete all records older then 7 days, I try to use this topic but does work.

Not really a node-red question but I feel generous 😉

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

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

---

<div class="post-metadata">

**Author:** ![lucibuz](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lucibuz/32/39131_2.png) [@lucibuz](https://discourse.nodered.org/u/lucibuz)\
**Post date:** [7 April 2021 08:15 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/31 "2021-04-07T08:15:24Z")

</div>

Thanks for your help,

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

 ![database](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/2/9220e8233789d7ec0da4c4a97d80faf73238ed16.jpeg)

By the way, I find an awesome database reader:

> **[GitHub - sqlitebrowser/sqlitebrowser: Official home of the DB Browser for...](https://github.com/sqlitebrowser/sqlitebrowser)**
>
> Official home of the DB Browser for SQLite (DB4S) project. Previously known as "SQLite Database Browser" and "Database Browser for SQLite". Website at: - GitHub - sqlitebrowser...

> [@Steve-Mcl](#):
>
> Not really a node-red question but I feel generous 😉

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

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [7 April 2021 20:27 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/32 "2021-04-07T20:27:22Z")

</div>

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](https://www.sqlitetutorial.net/sqlite-date-functions/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 ?

---

<div class="post-metadata">

**Author:** ![lucibuz](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lucibuz/32/39131_2.png) [@lucibuz](https://discourse.nodered.org/u/lucibuz)\
**Post date:** [8 April 2021 07:22 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/33 "2021-04-08T07:22:04Z")

</div>

Hello,

> [@UnborN](#):
>
> How you insert a datetime in a column that is defined as INTEGER. it doesnt give an error ?

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+")")}] );

```

 ![database2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/8/58d9580755af482142d9695398080e9c30f902d7.jpeg)

Thanks.

---

<div class="post-metadata">

**Author:** ![lucibuz](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lucibuz/32/39131_2.png) [@lucibuz](https://discourse.nodered.org/u/lucibuz)\
**Post date:** [8 April 2021 08:43 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/34 "2021-04-08T08:43:07Z")

</div>

Hello again,

This code works fine, thanks.

> [@UnborN](#):
>
> `msg.topic = `SELECT \* FROM alarmlog WHERE datetime(timestamp, 'localtime') \< datetime('now', 'localtime', '-7 days')`;`

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,

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [8 April 2021 18:30 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/35 "2021-04-08T18:30:47Z")

</div>

> [@lucibuz](#):
>
> How can I give write permissions to my db?

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

> [@lucibuz](#):
>
> attempt to write a readonly database

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

---

<div class="post-metadata">

**Author:** ![lucibuz](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lucibuz/32/39131_2.png) [@lucibuz](https://discourse.nodered.org/u/lucibuz)\
**Post date:** [9 April 2021 18:17 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/36 "2021-04-09T18:17:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [9 April 2021 20:11 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/37 "2021-04-09T20:11:08Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/4/946c7fe10d79989c9d5a67a8cc1c61f851d12015.png)
5. Can you share the flow to check how exactly you implemented the delete command ?

---

<div class="post-metadata">

**Author:** ![lucibuz](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lucibuz/32/39131_2.png) [@lucibuz](https://discourse.nodered.org/u/lucibuz)\
**Post date:** [10 April 2021 14:30 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/38 "2021-04-10T14:30:47Z")

</div>

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 🙈.  
I have to check why in my original flow same DELETE syntax doesn't perform (no errors).

thanks for your help.

```auto
[{"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}]

```

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [10 April 2021 16:01 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/39 "2021-04-10T16:01:12Z")

</div>

Hi .. i imported your flow and the Delete works for me too with the example you shared 😉  
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.

---

<div class="post-metadata">

**Author:** ![lucibuz](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lucibuz/32/39131_2.png) [@lucibuz](https://discourse.nodered.org/u/lucibuz)\
**Post date:** [13 April 2021 11:25 UTC](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041/40 "2021-04-13T11:25:01Z")

</div>

Hi Steve,

How can I skip from reading some of the array elements, using your code example?  
for example: to skip when i = 3, i =28 , i = 59.

```auto
var previous = context.get("previous") || [];
var current = [...msg.payload];
var table = context.get("table") || [];
for (var i = 0; i < msg.payload.length; i++) {
    var channel = ("" + (i + 1)).padStart(5,"0");
    var sensorNo = (i + 1);
    var sensorName = lookup[sensorNo] || ("Unknown " + sensorNo);
    //has value changed?
    if (previous[i] !== current[i]) {
        var row = table.find(e => e.Channel == channel);

        if (!row) {
            if (current[i] === 0)
                continue; //dont add this one (its 0)
            
            row = {
                "Time":t,
                "Channel": channel,
                "Alarm": "",                
                "Status": "",
                "Alarmlog": "",                
                "Statuslog": ""                
            }            
            table.push(row);
        } else {
            //there is a row in the table...
            if (current[i] === 0) {
                ///remove it because its now 0
                table = table.filter(e => e.Channel !== channel);
                continue;
            }
        }

        row.Time = t;//update the time
        if (current[i] === 2) {
            row.Status = "<div style='background-color: red; width:100%; color:white'>" + "ALARM" + "</div>";
            row.Alarm = "<div style='background-color: red; width:100%; color:white'>" + sensorName + "</div>";
            row.Statuslog = "ALARM !";
            row.Alarmlog = sensorName ;            
        } else if (current[i] === 1027) {
            row.Status = "<div style='background-color: yellow; width:100%; color:red'>" + "ALARM" + "</div>";
            row.Alarm = "<div style='background-color: red; width:100%; color:white'>" + sensorName + "</div>";
            row.Statuslog = "ALARM";
            row.Alarmlog = sensorName ;            
        } else if (current[i] === 1028) {
            row.Status = "<div style='background-color: yellow; width:100%; color:red'>" + "ALARM" + "</div>";
            row.Alarm = "<div style='background-color: red; width:100%; color:white'>" + sensorName + "</div>";
            row.Statuslog = "ALARM";
            row.Alarmlog = sensorName ;
        } else if (current[i] === 1029) {
            row.Status = "<div style='background-color: yellow; width:100%; color:red'>" + "ALARM" + "</div>";
            row.Alarm = "<div style='background-color: red; width:100%; color:white'>" + sensorName + "</div>";
            row.Statuslog = "ALARM";
            row.Alarmlog = sensorName ;
        } else if (current[i] === 1088) {
            row.Status = "<div style='background-color: yellow; width:100%; color:red'>" + "SENSOR FAIL" + "</div>";
            row.Alarm = "<div style='background-color: red; width:100%; color:white'>" + sensorName + "</div>";
            row.Statuslog = "SENSOR FAIL";
            row.Alarmlog = sensorName ;
        }
                //TODO: 
        // You can create the SQL here and send it in the topic OR send an object with 
        // the clean alarm and notes info (i.e. no HTML) then build a SQL query in the next node 
        node.send( [null, { topic: ("INSERT INTO alarmlog (Time,Channel,Alarm,Status) " +
        "VALUES ('"+row.Time+"','"+row.Channel+"','"+row.Alarmlog+"','"+row.Statuslog+"')")}] ); //Send the SQL query out of output 2
    }
}

context.set("previous", current);
context.set("table", table);

msg.payload = table;
return [msg, null]; //Return table to function output 1;

```

Thanks.

[Previous page](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041.md?page=1)

[Next page](https://discourse.nodered.org/t/how-to-create-an-modbus-tcp-alarm-status-dashboard/43041.md?page=3)
