Hello,
I manage to create the SQLite database and to inset the triggered alarms into the database log as suggested above.
Here is the code I adapted up to my knowledge:
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;
and the flow:
[{"id":"80183847.7650d8","type":"modbus-read","z":"74d8f714.ab8378","name":"","topic":"","showStatusActivities":true,"logIOActivities":false,"showErrors":true,"unitid":"","dataType":"HoldingRegister","adr":"0","quantity":"115","rate":"5","rateUnit":"s","delayOnStart":false,"startDelayTime":"","server":"c6527b9f.17c448","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"x":250,"y":1460,"wires":[["c37bbf47.cf40d8","b50640ea.4fca38","115b2abd.6fd2fd"],[]]},{"id":"115b2abd.6fd2fd","type":"function","z":"74d8f714.ab8378","name":"generate alarm table","func":"\nvar 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\nvar lookup = {\n 1: \"NO COMM. ON MAIN NETWORK\",\n 2: \"NO COMM. ON BACKUP NETWORK\",\n 3: \"MAIN SERVER OUT OF ORDER\",\n 4: \"BACKUP SERVER OUT OF ORDER\",\n 5: \"NO COMM. MAIN SERVER ON MAIN NETWORK\",\n 6: \"NO COMM. MAIN SERVER ON BACKUP NETWORK\",\n 7: \"NO COMM. BACKUP SERVER ON MAIN NETWORK\",\n 8: \"NO COMM. BACKUP SERVER ON BACKUP NETWORK\",\n 9: \"MAXIMUM DISKSPACE USED. LOGGING STOPPED\",\n 10: \"ALARMLOG: FILE CAN NOT BE WRITTEN\",\n 11: \"MTUS PORT: RD4 COM PORT FAILURE\",\n 12: \"MTUS PORT: RD4 NO COMMUNICATION\",\n 13: \"MTUS PORT: RD4 ERROR IN COMMUNICATION\",\n 14: \"MTUS STBD: RD5 COM PORT FAILURE\",\n 15: \"MTUS STBD: RD5 NO COMMUNICATION\",\n 16: \"MTUS STBD: RD5 ERROR IN COMMUNICATION\",\n 17: \"X101 NOT PRESENT\",\n 18: \"X102 NOT PRESENT\",\n 19: \"X103 NOT PRESENT\",\n 20: \"X104 NOT PRESENT\",\n 21: \"X109 NOT PRESENT\",\n 22: \"X110 NOT PRESENT\",\n 23: \"X111 NOT PRESENT\",\n 24: \"X117 NOT PRESENT\",\n 25: \"X118 NOT PRESENT\",\n 26: \"X119 NOT PRESENT\",\n 27: \"X125 NOT PRESENT\",\n 28: \"X126 NOT PRESENT\",\n 29: \"X127 NOT PRESENT\",\n 30: \"X128 NOT PRESENT\",\n 31: \"XP101 MAIN LINK NOT PRESENT\",\n 32: \"XP109 MAIN LINK NOT PRESENT\",\n 33: \"XP117 MAIN LINK NOT PRESENT\",\n 34: \"XP125 MAIN LINK NOT PRESENT\",\n 35: \"XP101 BACKUP LINK NOT PRESENT\",\n 36: \"XP109 BACKUP LINK NOT PRESENT\",\n 37: \"XP117 BACKUP LINK NOT PRESENT\",\n 38: \"XP125 BACKUP LINK NOT PRESENT\",\n 39: \"X101 INVALID BOARD TYPE\",\n 40: \"X102 INVALID BOARD TYPE\",\n 41: \"X103 INVALID BOARD TYPE\",\n 42: \"X104 INVALID BOARD TYPE\",\n 43: \"X109 INVALID BOARD TYPE\",\n 44: \"X110 INVALID BOARD TYPE\",\n 45: \"X111 INVALID BOARD TYPE\",\n 46: \"X117 INVALID BOARD TYPE\",\n 47: \"X118 INVALID BOARD TYPE\",\n 48: \"X119 INVALID BOARD TYPE\",\n 49: \"X125 INVALID BOARD TYPE\",\n 50: \"X126 INVALID BOARD TYPE\",\n 51: \"X127 INVALID BOARD TYPE\",\n 52: \"X128 INVALID BOARD TYPE\",\n 53: \"MBS_TCP: RD18 TCP/IP Socket Error\",\n 54: \"MBS_TCP: RD18 No Communication\",\n 55: \"MAIN SERVER IO SERVER NOT FOUND\",\n 56: \"BACKUP SERVER IO SERVER NOT FOUND\",\n 57: \"FB1 MAIN LINK NO COMMUNICATION\",\n 58: \"FB1 BACKUP LINK NO COMMUNICATION\",\n 59: \"FB2 MAIN LINK NO COMMUNICATION\",\n 60: \"X201 NOT PRESENT\",\n 61: \"X202 NOT PRESENT\",\n 62: \"X203 NOT PRESENT\",\n 63: \"X204 NOT PRESENT\",\n 64: \"X209 NOT PRESENT\",\n 65: \"X210 NOT PRESENT\",\n 66: \"X211 NOT PRESENT\",\n 67: \"X217 NOT PRESENT\",\n 68: \"X218 NOT PRESENT\",\n 69: \"X219 NOT PRESENT\",\n 70: \"X220 NOT PRESENT\",\n 71: \"FB2 BACKUP LINK NO COMMUNICATION\",\n 72: \"XP201 MAIN LINK NOT PRESENT\",\n 73: \"XP209 MAIN LINK NOT PRESENT\",\n 74: \"XP217 MAIN LINK NOT PRESENT\",\n 75: \"XP201 BACKUP LINK NOT PRESENT\",\n 76: \"XP209 BACKUP LINK NOT PRESENT\",\n 77: \"XP217 BACKUP LINK NOT PRESENT\",\n 78: \"X202 INVALID BOARD TYPE\",\n 79: \"X203 INVALID BOARD TYPE\",\n 80: \"X204 INVALID BOARD TYPE\",\n 81: \"X210 INVALID BOARD TYPE\",\n 82: \"X211 INVALID BOARD TYPE\",\n 83: \"X212 INVALID BOARD TYPE\",\n 84: \"X218 INVALID BOARD TYPE\",\n 85: \"X219 INVALID BOARD TYPE\",\n 86: \"X220 INVALID BOARD TYPE\",\n 87: \"X301 NOT PRESENT\",\n 88: \"X302 NOT PRESENT\",\n 89: \"X303 NOT PRESENT\",\n 90: \"X304 NOT PRESENT\",\n 91: \"X309 NOT PRESENT\",\n 92: \"XP301 MAIN LINK NOT PRESENT\",\n 93: \"XP309 MAIN LINK NOT PRESENT\",\n 94: \"XP301 BACKUP LINK NOT PRESENT\",\n 95: \"FB3 MAIN LINK NO COMMUNICATION\",\n 96: \"FB3 BACKUP LINK NO COMMUNICATION\",\n 97: \"FB3 PROC301 IEC-1131 CONFIG NOT LOADED\",\n 98: \"FB3 PROC301 IEC-1131 PROGRAM NOT RUNNING\",\n 99: \"NMEA_WIND: RD6 COM PORT ERROR\",\n 100: \"NMEA_WIND: RD6 NO COMMUNICATION\",\n 101: \"FB3 PROC309 IEC-1131 CONFIG NOT LOADED\",\n 102: \"FB3 PROC309 IEC-1131 PROGRAM NOT RUNNING\",\n 103: \"X301 INVALID BOARD TYPE\",\n 104: \"X302 INVALID BOARD TYPE\",\n 105: \"X303 INVALID BOARD TYPE\",\n 106: \"X304 INVALID BOARD TYPE\",\n 107: \"X309 INVALID BOARD TYPE\",\n 108: \"FB4 MAIN LINK NO COMMUNICATION\",\n 109: \"FB4 BACKUP LINK NO COMMUNICATION\", \n 110: \"FB4 LOP MESS NOT PRESENT\",\n 111: \"FB4 LOP ENGINEER NOT PRESENT\",\n 112: \"FB4 LOP 1ST MATE NOT PRESENT\",\n 113: \"FB4 LOP ALARMPANEL ENGINEROOM\",\n 114: \"ALARM PANEL E.R. POWER FAIL\",\n 115: \"ALARM PANEL E.R. EARTH FAULT\",\n 116: \"WATERTIGHT DOORS POWER FAILURE\",\n 117: \"WATERTIGHT DOORS BATTERY LOW\",\n 118: \"BILGE FORE PEAK LEVEL HIGH\",\n 119: \"BILGE BOW THRUSTER ROOM LEVEL HIGH\",\n 120: \"BILGE COFFERDAM LEVEL HIGH\"\n}\n\n\n\nvar previous = context.get(\"previous\") || [];\nvar current = [...msg.payload];\nvar table = context.get(\"table\") || [];\nfor (var i = 0; i < msg.payload.length; i++) {\n var channel = (\"\" + (i + 1)).padStart(5,\"0\");\n var sensorNo = (i + 1);\n var sensorName = lookup[sensorNo] || (\"Unknown \" + sensorNo);\n //has value changed?\n if (previous[i] !== current[i]) {\n var row = table.find(e => e.Channel == channel);\n\n if (!row) {\n if (current[i] === 0)\n continue; //dont add this one (its 0)\n \n row = {\n \"Time\":t,\n \"Channel\": channel,\n \"Alarm\": \"\", \n \"Status\": \"\",\n \"Alarmlog\": \"\", \n \"Statuslog\": \"\" \n } \n table.push(row);\n } else {\n //there is a row in the table...\n if (current[i] === 0) {\n ///remove it because its now 0\n table = table.filter(e => e.Channel !== channel);\n continue;\n }\n }\n\n row.Time = t;//update the time\n if (current[i] === 2) {\n row.Status = \"<div style='background-color: red; width:100%; color:white'>\" + \"ALARM\" + \"</div>\";\n row.Alarm = \"<div style='background-color: red; width:100%; color:white'>\" + sensorName + \"</div>\";\n row.Statuslog = \"ALARM !\";\n row.Alarmlog = sensorName ; \n } else if (current[i] === 1027) {\n row.Status = \"<div style='background-color: yellow; width:100%; color:red'>\" + \"ALARM\" + \"</div>\";\n row.Alarm = \"<div style='background-color: red; width:100%; color:white'>\" + sensorName + \"</div>\";\n row.Statuslog = \"ALARM\";\n row.Alarmlog = sensorName ; \n } else if (current[i] === 1028) {\n row.Status = \"<div style='background-color: yellow; width:100%; color:red'>\" + \"ALARM\" + \"</div>\";\n row.Alarm = \"<div style='background-color: red; width:100%; color:white'>\" + sensorName + \"</div>\";\n row.Statuslog = \"ALARM\";\n row.Alarmlog = sensorName ;\n } else if (current[i] === 1029) {\n row.Status = \"<div style='background-color: yellow; width:100%; color:red'>\" + \"ALARM\" + \"</div>\";\n row.Alarm = \"<div style='background-color: red; width:100%; color:white'>\" + sensorName + \"</div>\";\n row.Statuslog = \"ALARM\";\n row.Alarmlog = sensorName ;\n } else if (current[i] === 1088) {\n row.Status = \"<div style='background-color: yellow; width:100%; color:red'>\" + \"SENSOR FAIL\" + \"</div>\";\n row.Alarm = \"<div style='background-color: red; width:100%; color:white'>\" + sensorName + \"</div>\";\n row.Statuslog = \"SENSOR FAIL\";\n row.Alarmlog = sensorName ;\n }\n //TODO: \n // You can create the SQL here and send it in the topic OR send an object with \n // the clean alarm and notes info (i.e. no HTML) then build a SQL query in the next node \n node.send( [null, { topic: (\"INSERT INTO alarmlog (Time,Channel,Alarm,Status) \" +\n \"VALUES ('\"+row.Time+\"','\"+row.Channel+\"','\"+row.Alarmlog+\"','\"+row.Statuslog+\"')\")}] ); //Send the SQL query out of output 2\n }\n}\n\ncontext.set(\"previous\", current);\ncontext.set(\"table\", table);\n\nmsg.payload = table;\nreturn [msg, null]; //Return table to function output 1;","outputs":2,"noerr":0,"initialize":"","finalize":"","x":460,"y":1460,"wires":[["1caa8632.9ffda2","e36f3a9f.a38dd8"],["33eac830.bf165","25ea02b9.be8eee"]]},{"id":"e36f3a9f.a38dd8","type":"ui_table","z":"74d8f714.ab8378","group":"81dd3718.b97888","name":"Alarm List","order":1,"width":13,"height":8,"columns":[{"field":"Time","title":"<center>Time</>","width":"23%","align":"center","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"Channel","title":"<center>Channel</>","width":"10%","align":"center","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"Alarm","title":"<center>Alarm Message</>","width":"52%","align":"center","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"Status","title":"<center>Status</>","width":"15%","align":"center","formatter":"html","formatterParams":{"target":"_blank"}}],"outputs":0,"cts":false,"x":680,"y":1440,"wires":[]},{"id":"33eac830.bf165","type":"sqlite","z":"74d8f714.ab8378","mydb":"27e5ab9a.4c829c","sqlquery":"msg.topic","sql":"","name":"Alarm Log","x":690,"y":1480,"wires":[["a37080a.2ef5","b91eacf3.d6a2b8"]]},{"id":"84f739ae.91b518","type":"ui_button","z":"74d8f714.ab8378","name":"","group":"783af07a.d61d58","order":2,"width":3,"height":1,"passthru":true,"label":"View Alarm Log","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"date","topic":"","topicType":"str","x":500,"y":1600,"wires":[["25ea02b9.be8eee"]]},{"id":"c17826fc.b4e2","type":"inject","z":"74d8f714.ab8378","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":520,"y":1640,"wires":[["25ea02b9.be8eee"]]},{"id":"25ea02b9.be8eee","type":"delay","z":"74d8f714.ab8378","name":"","pauseType":"delay","timeout":"500","timeoutUnits":"milliseconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":690,"y":1620,"wires":[["83bb0a3b.56c4d8"]]},{"id":"83bb0a3b.56c4d8","type":"change","z":"74d8f714.ab8378","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":860,"y":1620,"wires":[["927b5032.7c45e8"]]},{"id":"927b5032.7c45e8","type":"sqlite","z":"74d8f714.ab8378","mydb":"27e5ab9a.4c829c","sqlquery":"msg.topic","sql":"","name":"Alarm Log","x":1030,"y":1620,"wires":[["6c2561ae.43481","761d8c23.1ca984"]]},{"id":"761d8c23.1ca984","type":"template","z":"74d8f714.ab8378","name":"Format","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<table>\n \n <tr><th> Time </th><th> Channel </th><th> Alarm Message </th><th> Status </th><th>\n {{#payload}}\n <tr class=\"\">\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":1200,"y":1620,"wires":[["1cf08fea.b4b38"]]},{"id":"1cf08fea.b4b38","type":"ui_template","z":"74d8f714.ab8378","group":"783af07a.d61d58","name":"Alarm Log","order":4,"width":13,"height":8,"format":"<div ng-bind-html=\"msg.payload\" height=\"600\" style=\"height: 600px;\"></div>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":1350,"y":1620,"wires":[[]]},{"id":"27d0e60a.18dc62","type":"inject","z":"74d8f714.ab8378","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, 'timestamp' INTEGER DEFAULT CURRENT_TIMESTAMP)","payload":"","payloadType":"date","x":260,"y":1760,"wires":[["a896741a.d61798"]]},{"id":"a896741a.d61798","type":"sqlite","z":"74d8f714.ab8378","mydb":"27e5ab9a.4c829c","sqlquery":"msg.topic","sql":"","name":"Alarm Log","x":430,"y":1760,"wires":[["741330e7.ba891"]]},{"id":"741330e7.ba891","type":"debug","z":"74d8f714.ab8378","name":"","active":false,"tosidebar":true,"console":true,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":600,"y":1760,"wires":[]},{"id":"c6527b9f.17c448","type":"modbus-client","name":"","clienttype":"tcp","bufferCommands":false,"stateLogEnabled":false,"queueLogEnabled":false,"tcpHost":"192.168.1.120","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},{"id":"81dd3718.b97888","type":"ui_group","name":"Active Alarms","tab":"4e11db25.239e94","order":1,"disp":true,"width":13,"collapse":true},{"id":"27e5ab9a.4c829c","type":"sqlitedb","db":"nodered.db","mode":"RWC"},{"id":"783af07a.d61d58","type":"ui_group","name":"Alarm Log","tab":"4e11db25.239e94","order":4,"disp":false,"width":"13","collapse":false},{"id":"4e11db25.239e94","type":"ui_tab","name":"Alarms Monitiring","icon":"dashboard","order":1,"disabled":false,"hidden":false}]
There are few things that I want to improve:
- when the modebus connection is lost the Alarm List table is still showing the last alarms, better way is to create a dashboard element showing the connection state and to clear the table
- the Alarm log HTML view, maybe to add some button to have a filter the log entries by 24hours, 1 week, 1 month.
Thanks for all your help.