System diagnostic framework
This is an overview of my system diagnostic framework I used in node red to keep an eye of my connected devices and overall system health.
This framework lets you manage systems and statuses, store and report system messages and provide daily email updates on system health.
Take a look at a video which explains the features and working in detail: https://youtu.be/pgOOkukzeX0
[{"id":"74f191ff.db063","type":"tab","label":"Logging"},{"id":"c8983015.ec0bc","type":"inject","z":"74f191ff.db063","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"x":117,"y":260,"wires":[["39165f3e.711fd"]]},{"id":"39165f3e.711fd","type":"function","z":"74f191ff.db063","name":"SQL","func":"msg.topic = \"SELECT * FROM state ORDER BY sta_id\";\nreturn msg;","outputs":1,"noerr":0,"x":280,"y":260,"wires":[["836f4e1f.39fae"]]},{"id":"836f4e1f.39fae","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":444,"y":260,"wires":[["49766a28.143e14"]]},{"id":"49766a28.143e14","type":"function","z":"74f191ff.db063","name":"Process states","func":"global.set(\"Diag_State_Buffer\",msg.payload);\n\nvar output = [];\nfor (var i = 0; i < msg.payload.length; i++) {\n //output.push( \"[\"+msg.payload[i].id+\"] \"+msg.payload[i].caption);\n obj = {};\n \n obj [\"[\"+msg.payload[i].sta_id+\"] \"+msg.payload[i].sta_title]=msg.payload[i].sta_id;\n output.push(obj);\n}\nmsg.options = output;\nreturn msg;","outputs":1,"noerr":0,"x":644,"y":261,"wires":[["bc53d497.598f18"]]},{"id":"bc53d497.598f18","type":"ui_dropdown","z":"74f191ff.db063","name":"","label":"Select","group":"92a841c5.36f1e","order":1,"width":0,"height":0,"passthru":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":830,"y":261,"wires":[["ac7035e9.97dad8"]]},{"id":"6cdcda53.8a9064","type":"ui_text_input","z":"74f191ff.db063","name":"","label":"Caption","group":"92a841c5.36f1e","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"","x":847,"y":379,"wires":[["fdddf5bd.664d88"]]},{"id":"27f97b34.451ad4","type":"ui_colour_picker","z":"74f191ff.db063","name":"","label":"Color","group":"92a841c5.36f1e","format":"hex","outformat":"string","showSwatch":true,"showPicker":false,"showValue":false,"showAlpha":false,"order":4,"width":0,"height":0,"passthru":false,"topic":"","x":846,"y":423,"wires":[["5eb8dd59.2c0b94"]]},{"id":"ac7035e9.97dad8","type":"function","z":"74f191ff.db063","name":"SQL","func":"global.set(\"diag_state_selected\",msg.payload);\nmsg.topic = \"SELECT * FROM state WHERE sta_id=\" + msg.payload;\nreturn msg;","outputs":1,"noerr":0,"x":119,"y":375,"wires":[["723f23e2.608f2c"]]},{"id":"723f23e2.608f2c","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":283,"y":375,"wires":[["99b35c16.4c647"]]},{"id":"99b35c16.4c647","type":"switch","z":"74f191ff.db063","name":"If no data","property":"payload.length","propertyType":"msg","rules":[{"t":"gt","v":"0","vt":"num"}],"checkall":"true","outputs":1,"x":449,"y":375,"wires":[["afc3ee0d.2f819","24c19da5.3efd32","5539f0fc.3e109"]]},{"id":"afc3ee0d.2f819","type":"change","z":"74f191ff.db063","name":"Format data","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0].sta_title","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":649,"y":379,"wires":[["6cdcda53.8a9064"]]},{"id":"24c19da5.3efd32","type":"change","z":"74f191ff.db063","name":"Format data","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0].sta_color","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":647,"y":423,"wires":[["27f97b34.451ad4"]]},{"id":"fcb48d10.f0216","type":"ui_numeric","z":"74f191ff.db063","name":"","label":"ID","group":"92a841c5.36f1e","order":2,"width":0,"height":0,"passthru":false,"topic":"","format":"{{value}}","min":0,"max":"100","step":1,"x":840,"y":342,"wires":[["dc18d35c.a1d71"]]},{"id":"5539f0fc.3e109","type":"change","z":"74f191ff.db063","name":"Format data","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0].sta_id","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":654,"y":342,"wires":[["fcb48d10.f0216"]]},{"id":"8c23d072.1edf1","type":"ui_button","z":"74f191ff.db063","name":"","group":"92a841c5.36f1e","order":0,"width":0,"height":0,"label":"Create New","color":"","bgcolor":"","icon":"add","payload":"","payloadType":"str","topic":"","x":108,"y":189,"wires":[["7590a57f.ba8fdc"]]},{"id":"7590a57f.ba8fdc","type":"function","z":"74f191ff.db063","name":"SQL","func":"msg.topic = \"INSERT INTO state (sta_id,sta_caption,sta_color) VALUES (101,'New state','ffffff')\";\nglobal.set(\"diag_state_selected\",undefined);\nreturn msg;","outputs":1,"noerr":0,"x":293,"y":189,"wires":[["16dc1e7.36147e2"]]},{"id":"16dc1e7.36147e2","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":457,"y":189,"wires":[["39165f3e.711fd","213aa4a7.38482c"]]},{"id":"dc18d35c.a1d71","type":"function","z":"74f191ff.db063","name":"Store value","func":"global.set(\"diag_state_id\",msg.payload);\nreturn msg;","outputs":1,"noerr":0,"x":1026,"y":341,"wires":[[]]},{"id":"fdddf5bd.664d88","type":"function","z":"74f191ff.db063","name":"Store value","func":"global.set(\"diag_state_caption\",msg.payload);\nreturn msg;","outputs":1,"noerr":0,"x":1028,"y":379,"wires":[[]]},{"id":"5eb8dd59.2c0b94","type":"function","z":"74f191ff.db063","name":"Store value","func":"global.set(\"diag_state_color\",msg.payload);\nreturn msg;","outputs":1,"noerr":0,"x":1031,"y":419,"wires":[[]]},{"id":"e0762304.b6c82","type":"ui_button","z":"74f191ff.db063","name":"","group":"92a841c5.36f1e","order":0,"width":0,"height":0,"label":"Update selected","color":"","bgcolor":"#FF9000","icon":"mode_edit","payload":"","payloadType":"str","topic":"","x":116,"y":145,"wires":[["c13ab780.77dcd8"]]},{"id":"c13ab780.77dcd8","type":"function","z":"74f191ff.db063","name":"SQL","func":"if (global.get(\"diag_state_selected\")!==undefined) {\n msg.topic = \"UPDATE state SET \";\n if (global.get(\"diag_state_id\")!==undefined) {\n msg.topic = msg.topic+ \"sta_id=\"+global.get(\"diag_state_id\")+\", \";\n }\n if (global.get(\"diag_state_caption\")!==undefined) {\n msg.topic = msg.topic+ \"sta_name='\"+global.get(\"diag_state_caption\")+\"', \";\n }\n if (global.get(\"diag_state_color\")!==undefined) {\n msg.topic = msg.topic+ \"sta_color='\"+global.get(\"diag_state_color\")+\"', \";\n }\n msg.topic = msg.topic.substring(0,msg.topic.length-2);\n msg.topic = msg.topic+ \" WHERE sta_id=\"+global.get(\"diag_state_selected\");\n global.set(\"diag_state_selected\",undefined);\n global.set(\"diag_state_id\",undefined);\n global.set(\"diag_state_caption\",undefined);\n global.set(\"diag_state_color\",undefined);\n} else {\n msg.topic = \"\";\n}\nreturn msg;","outputs":1,"noerr":0,"x":291,"y":145,"wires":[["b79b354c.3632a8"]]},{"id":"b79b354c.3632a8","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":459,"y":143,"wires":[["39165f3e.711fd","9ee87714.0979c8"]]},{"id":"dda7ba54.f5f6c8","type":"ui_button","z":"74f191ff.db063","name":"","group":"92a841c5.36f1e","order":0,"width":0,"height":0,"label":"Delete selected","color":"","bgcolor":"#ff5555","icon":"delete","payload":"","payloadType":"str","topic":"","x":117,"y":97,"wires":[["51af1faf.d8e47"]]},{"id":"51af1faf.d8e47","type":"function","z":"74f191ff.db063","name":"SQL","func":"if (global.get(\"diag_state_selected\")!==undefined) {\n msg.topic = \"DELETE FROM state WHERE sta_id=\"+global.get(\"diag_state_selected\");\n global.set(\"diag_state_selected\",undefined);\n global.set(\"diag_state_id\",undefined);\n global.set(\"diag_state_caption\",undefined);\n global.set(\"diag_state_color\",undefined);\n} else {\n msg.topic = \"\";\n}\nreturn msg;","outputs":1,"noerr":0,"x":292,"y":97,"wires":[["5131fdbf.96d484"]]},{"id":"5131fdbf.96d484","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":458,"y":98,"wires":[["39165f3e.711fd","721ebeb3.173f8"]]},{"id":"86b8c984.49d4f8","type":"comment","z":"74f191ff.db063","name":"Maintenance of the State table","info":"","x":165,"y":50,"wires":[]},{"id":"8fafecd1.2e4fe","type":"ui_toast","z":"74f191ff.db063","position":"top right","displayTime":"3","outputs":0,"ok":"OK","cancel":"","topic":"","name":"","x":969,"y":143,"wires":[]},{"id":"721ebeb3.173f8","type":"change","z":"74f191ff.db063","name":"Toast msg","rules":[{"t":"set","p":"topic","pt":"msg","to":"Selected entry has been deleted","tot":"str"},{"t":"delete","p":"payload","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":693.5,"y":97,"wires":[["8fafecd1.2e4fe"]]},{"id":"9ee87714.0979c8","type":"change","z":"74f191ff.db063","name":"Toast msg","rules":[{"t":"set","p":"topic","pt":"msg","to":"Selected entry has been updated","tot":"str"},{"t":"delete","p":"payload","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":694,"y":147,"wires":[["8fafecd1.2e4fe"]]},{"id":"213aa4a7.38482c","type":"change","z":"74f191ff.db063","name":"Toast msg","rules":[{"t":"set","p":"topic","pt":"msg","to":"New entry has been created","tot":"str"},{"t":"delete","p":"payload","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":694,"y":192,"wires":[["8fafecd1.2e4fe"]]},{"id":"dc9854b1.88f1a8","type":"inject","z":"74f191ff.db063","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"x":143,"y":689,"wires":[["7085358a.2eb0cc"]]},{"id":"7085358a.2eb0cc","type":"function","z":"74f191ff.db063","name":"SQL","func":"msg.topic = \"SELECT * FROM system ORDER BY sys_id\";\nreturn msg;","outputs":1,"noerr":0,"x":306,"y":689,"wires":[["a8b2fe47.49a9f"]]},{"id":"a8b2fe47.49a9f","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":470,"y":689,"wires":[["bdbf9310.6e77","f6e11140.f0dd6"]]},{"id":"bdbf9310.6e77","type":"function","z":"74f191ff.db063","name":"Process systems","func":"global.set(\"Diag_System_Buffer\",msg.payload);\n\nvar output = [];\nfor (var i = 0; i < msg.payload.length; i++) {\n //output.push( \"[\"+msg.payload[i].id+\"] \"+msg.payload[i].caption);\n obj = {};\n \n obj [\"[\"+msg.payload[i].sys_id+\"] \"+msg.payload[i].sys_name]=msg.payload[i].sys_id;\n output.push(obj);\n}\nmsg.options = output;\nreturn msg;","outputs":1,"noerr":0,"x":680,"y":690,"wires":[["f991b12c.23db6"]]},{"id":"f991b12c.23db6","type":"ui_dropdown","z":"74f191ff.db063","name":"","label":"Select","group":"89937c4.fcd738","order":1,"width":0,"height":0,"passthru":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":856,"y":690,"wires":[["38a32071.ea1fb"]]},{"id":"a7b6d72a.58a298","type":"ui_text_input","z":"74f191ff.db063","name":"","label":"Name","group":"89937c4.fcd738","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"","x":863,"y":808,"wires":[["b83f7e93.01e07"]]},{"id":"38a32071.ea1fb","type":"function","z":"74f191ff.db063","name":"SQL","func":"global.set(\"diag_system_selected\",msg.payload);\nmsg.topic = \"SELECT * FROM system WHERE sys_id=\" + msg.payload;\nreturn msg;","outputs":1,"noerr":0,"x":145,"y":804,"wires":[["6cb39c3a.aa0004"]]},{"id":"6cb39c3a.aa0004","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":309,"y":804,"wires":[["e68742d3.a662c"]]},{"id":"e68742d3.a662c","type":"switch","z":"74f191ff.db063","name":"If no data","property":"payload.length","propertyType":"msg","rules":[{"t":"gt","v":"0","vt":"num"}],"checkall":"true","outputs":1,"x":475,"y":804,"wires":[["86bf8c59.7fdde","d2ea2de2.6e5ba"]]},{"id":"86bf8c59.7fdde","type":"change","z":"74f191ff.db063","name":"Format data","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0].sys_name","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":675,"y":808,"wires":[["a7b6d72a.58a298"]]},{"id":"874d4299.e632","type":"ui_numeric","z":"74f191ff.db063","name":"","label":"ID","group":"89937c4.fcd738","order":2,"width":0,"height":0,"passthru":false,"topic":"","format":"{{value}}","min":0,"max":"100","step":1,"x":866,"y":771,"wires":[["2302ae26.a33f52"]]},{"id":"d2ea2de2.6e5ba","type":"change","z":"74f191ff.db063","name":"Format data","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0].sys_id","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":771,"wires":[["874d4299.e632"]]},{"id":"ac956cc6.16c88","type":"ui_button","z":"74f191ff.db063","name":"","group":"89937c4.fcd738","order":0,"width":0,"height":0,"label":"Create New","color":"","bgcolor":"","icon":"add","payload":"","payloadType":"str","topic":"","x":134,"y":618,"wires":[["6104fd37.bb4f34"]]},{"id":"6104fd37.bb4f34","type":"function","z":"74f191ff.db063","name":"SQL","func":"msg.topic = \"INSERT INTO system (sys_id,sys_name,sys_state) VALUES (101,'New system',0)\";\nglobal.set(\"diag_system_selected\",undefined);\nreturn msg;","outputs":1,"noerr":0,"x":319,"y":618,"wires":[["df4fb7a0.a60fe8"]]},{"id":"df4fb7a0.a60fe8","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":483,"y":618,"wires":[["7085358a.2eb0cc","bb24bc98.4f35d"]]},{"id":"2302ae26.a33f52","type":"function","z":"74f191ff.db063","name":"Store value","func":"global.set(\"diag_system_id\",msg.payload);\nreturn msg;","outputs":1,"noerr":0,"x":1052,"y":770,"wires":[[]]},{"id":"b83f7e93.01e07","type":"function","z":"74f191ff.db063","name":"Store value","func":"global.set(\"diag_system_name\",msg.payload);\nreturn msg;","outputs":1,"noerr":0,"x":1054,"y":808,"wires":[[]]},{"id":"2e9b67f1.e03668","type":"ui_button","z":"74f191ff.db063","name":"","group":"89937c4.fcd738","order":0,"width":0,"height":0,"label":"Update selected","color":"","bgcolor":"#FF9000","icon":"mode_edit","payload":"","payloadType":"str","topic":"","x":142,"y":574,"wires":[["41dbf258.93d12c"]]},{"id":"41dbf258.93d12c","type":"function","z":"74f191ff.db063","name":"SQL","func":"if (global.get(\"diag_system_selected\")!==undefined) {\n msg.topic = \"UPDATE system SET \";\n if (global.get(\"diag_system_id\")!==undefined) {\n msg.topic = msg.topic+ \"sys_id=\"+global.get(\"diag_system_id\")+\", \";\n }\n if (global.get(\"diag_system_name\")!==undefined) {\n msg.topic = msg.topic+ \"sys_name='\"+global.get(\"diag_system_name\")+\"', \";\n }\n msg.topic = msg.topic.substring(0,msg.topic.length-2);\n msg.topic = msg.topic+ \" WHERE sys_id=\"+global.get(\"diag_system_selected\");\n global.set(\"diag_system_selected\",undefined);\n global.set(\"diag_system_id\",undefined);\n global.set(\"diag_system_name\",undefined);\n} else {\n msg.topic = \"\";\n}\nreturn msg;","outputs":1,"noerr":0,"x":317,"y":574,"wires":[["9471b2fd.f122f"]]},{"id":"9471b2fd.f122f","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":485,"y":572,"wires":[["7085358a.2eb0cc","ca7ca159.279b9"]]},{"id":"f0c9ccc6.83f64","type":"ui_button","z":"74f191ff.db063","name":"","group":"89937c4.fcd738","order":0,"width":0,"height":0,"label":"Delete selected","color":"","bgcolor":"#ff5555","icon":"delete","payload":"","payloadType":"str","topic":"","x":143,"y":526,"wires":[["230f94b.f2e9d6c"]]},{"id":"230f94b.f2e9d6c","type":"function","z":"74f191ff.db063","name":"SQL","func":"if (global.get(\"diag_system_selected\")!==undefined) {\n msg.topic = \"DELETE FROM system WHERE sys_id=\"+global.get(\"diag_system_selected\");\n global.set(\"diag_system_selected\",undefined);\n global.set(\"diag_system_id\",undefined);\n global.set(\"diag_system_name\",undefined);\n} else {\n msg.topic = \"\";\n}\nreturn msg;","outputs":1,"noerr":0,"x":318,"y":526,"wires":[["d1da4488.a82038"]]},{"id":"d1da4488.a82038","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":484,"y":527,"wires":[["7085358a.2eb0cc","9aa79075.af131"]]},{"id":"40b947e2.6a38f8","type":"comment","z":"74f191ff.db063","name":"Maintenance of the System table","info":"","x":191,"y":479,"wires":[]},{"id":"1ec8499a.bade76","type":"ui_toast","z":"74f191ff.db063","position":"top right","displayTime":"3","outputs":0,"ok":"OK","cancel":"","topic":"","name":"","x":995,"y":572,"wires":[]},{"id":"9aa79075.af131","type":"change","z":"74f191ff.db063","name":"Toast msg","rules":[{"t":"set","p":"topic","pt":"msg","to":"Selected entry has been deleted","tot":"str"},{"t":"delete","p":"payload","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":719.5,"y":526,"wires":[["1ec8499a.bade76"]]},{"id":"ca7ca159.279b9","type":"change","z":"74f191ff.db063","name":"Toast msg","rules":[{"t":"set","p":"topic","pt":"msg","to":"Selected entry has been updated","tot":"str"},{"t":"delete","p":"payload","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":720,"y":576,"wires":[["1ec8499a.bade76"]]},{"id":"bb24bc98.4f35d","type":"change","z":"74f191ff.db063","name":"Toast msg","rules":[{"t":"set","p":"topic","pt":"msg","to":"New entry has been created","tot":"str"},{"t":"delete","p":"payload","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":720,"y":621,"wires":[["1ec8499a.bade76"]]},{"id":"16deb1aa.fe8f7e","type":"comment","z":"74f191ff.db063","name":"Receiving and processing messages","info":"","x":205,"y":912,"wires":[]},{"id":"13e089a7.73cb46","type":"link in","z":"74f191ff.db063","name":"Diagnostic_Update","links":["772d158d.384c0c","1856177c.692aa9","e592d5a5.26bde8","b73c8744.8ec588","59df56a.e8d5aa8","6bc27c2.26eb584","b269471e.f03798","149d2576.aa372b","4f24cc12.704334","85f0d77.da83928","78fed854.1b6aa8","eaf2075b.f08f88","9371bd5d.293c2","8c6c56bb.efe678","2e75adec.578e52","d9ab022c.d40aa","7692ea3d.040324","5c63f003.554","89cc7882.52fb68","ee08c280.18881","31989b49.404cf4","1711c87b.c95a78","858824a8.955768","1ceba7.f7399459","8d86bc97.858bd","c1d8ef52.f0ff9"],"x":101,"y":1018,"wires":[["7a2a0c21.9d9444","8a5e3701.adbfd8","4887060b.233e78","a200b955.f56308"]]},{"id":"2dc1be5b.1a3482","type":"function","z":"74f191ff.db063","name":"Diagnostic input message structure","func":"msg.payload = \"This updates the system status as well\";\nmsg.system = 1; // System id, use 1 for Dummy\nmsg.state = 70; // specify if the message is to change system status\nmsg.severity = 0; // 0: information, 1: warning, 2: error\n//msg.email = true; // if separate email should be sent\n//msg.emailtext = \"\"; this a long text which goes into the email\nreturn msg;","outputs":1,"noerr":0,"x":400,"y":959,"wires":[["772d158d.384c0c"]]},{"id":"46a746d0.64f828","type":"inject","z":"74f191ff.db063","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":117,"y":960,"wires":[["2dc1be5b.1a3482"]]},{"id":"772d158d.384c0c","type":"link out","z":"74f191ff.db063","name":"","links":["13e089a7.73cb46"],"x":627,"y":959,"wires":[]},{"id":"7a2a0c21.9d9444","type":"function","z":"74f191ff.db063","name":"Insert message record","func":"var d = new Date();\nvar epoch = d.getTime();\n\nif (msg.state>0) {\n msg.topic = \"INSERT INTO message (msg_system,msg_severity,msg_text,msg_epoch,msg_newstate) VALUES (\"+msg.system+\",\"+msg.severity+\",'\"+msg.payload+\"',\"+epoch+\",\"+msg.state+\")\";\n} else {\n msg.topic = \"INSERT INTO message (msg_system,msg_severity,msg_text,msg_epoch) VALUES (\"+msg.system+\",\"+msg.severity+\",'\"+msg.payload+\"',\"+epoch+\")\";\n}\n\nreturn msg;","outputs":1,"noerr":0,"x":369,"y":1021,"wires":[["83d660f8.e2a54"]]},{"id":"83d660f8.e2a54","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":977,"y":1022,"wires":[["f4817aa0.927cf8"]]},{"id":"8a5e3701.adbfd8","type":"switch","z":"74f191ff.db063","name":"Check status change","property":"state","propertyType":"msg","rules":[{"t":"nnull"}],"checkall":"true","outputs":1,"x":371,"y":1077,"wires":[["c89daa88.250688","40fbcf44.23282"]]},{"id":"c89daa88.250688","type":"function","z":"74f191ff.db063","name":"Status update","func":"msg.topic = \"UPDATE system SET sys_state=\"+msg.state+\" WHERE sys_id=\"+msg.system;\nreturn msg;","outputs":1,"noerr":0,"x":633,"y":1077,"wires":[["83d660f8.e2a54"]]},{"id":"4887060b.233e78","type":"switch","z":"74f191ff.db063","name":"Check email","property":"email","propertyType":"msg","rules":[{"t":"true"}],"checkall":"true","outputs":1,"x":339,"y":1127,"wires":[["a5f4d0f.485a13"]]},{"id":"e2331a1e.23aa28","type":"e-mail","z":"74f191ff.db063","server":"smtp.gmail.com","port":"465","secure":true,"name":"[email protected]","dname":"Email notification","x":935.8888702392578,"y":1121.3332824707031,"wires":[]},{"id":"a5f4d0f.485a13","type":"function","z":"74f191ff.db063","name":"Email content","func":"msg.topic=msg.payload;\nmsg.payload = msg.emailtext;\nreturn msg;","outputs":1,"noerr":0,"x":583,"y":1127,"wires":[["e2331a1e.23aa28"]]},{"id":"13c64144.b78baf","type":"comment","z":"74f191ff.db063","name":"System status display","info":"","x":171,"y":1243,"wires":[]},{"id":"3e3a22e6.222eae","type":"template","z":"74f191ff.db063","name":"Formatting","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<table>\n {{#payload}}\n <tr>\n <td style=\"background-color: {{sta_color}};\">{{sys_name}}</td>\n <td style=\"background-color: {{sta_color}};\">{{sta_title}}</td>\n </tr>\n {{/payload}}\n</table>\n","x":712,"y":1326,"wires":[["85a02253.5bef3","9091c9ad.d83748"]]},{"id":"85a02253.5bef3","type":"ui_template","z":"74f191ff.db063","group":"548bd5b.ace5b2c","name":"Status list","order":0,"width":0,"height":0,"format":"<div ng-bind-html=\"msg.payload\" style=\"height:100%;\"></div>","storeOutMessages":true,"fwdInMessages":true,"x":918,"y":1324,"wires":[[]]},{"id":"f4817aa0.927cf8","type":"function","z":"74f191ff.db063","name":"SQL","func":"msg.topic = \"SELECT * FROM system, state WHERE sys_state = sta_id ORDER BY sys_id\";\nreturn msg;","outputs":1,"noerr":0,"x":299,"y":1325,"wires":[["9047b44e.0677e8"]]},{"id":"9047b44e.0677e8","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":472,"y":1326,"wires":[["3e3a22e6.222eae","9091c9ad.d83748"]]},{"id":"461bc8ca.78b398","type":"inject","z":"74f191ff.db063","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"x":132,"y":1325,"wires":[["f4817aa0.927cf8"]]},{"id":"9091c9ad.d83748","type":"debug","z":"74f191ff.db063","name":"","active":false,"console":"false","complete":"false","x":924,"y":1393,"wires":[]},{"id":"7f22a7d5.dfbdd8","type":"comment","z":"74f191ff.db063","name":"Message Log","info":"","x":128,"y":1483,"wires":[]},{"id":"a1ff7899.091818","type":"template","z":"74f191ff.db063","name":"Formatting","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<table>\n <tr><th>Severity</th><th>Timestamp</th><th>System</th><th>Message</th></tr>\n {{#payload}}\n <tr class=\"\">\n <td>{{msg_severity}}</td>\n <td>{{msg_timestamp}}</td>\n <td>{{sys_name}}</td>\n <td>{{msg_text}}</td>\n </tr>\n {{/payload}}\n</table>\n","x":727,"y":1878,"wires":[["e55af17b.66c31"]]},{"id":"e55af17b.66c31","type":"ui_template","z":"74f191ff.db063","group":"726cdf63.1118c","name":"Log output","order":1,"width":0,"height":0,"format":"<div ng-bind-html=\"msg.payload\" style=\"height:400;\"></div>","storeOutMessages":true,"fwdInMessages":true,"x":948,"y":1878,"wires":[[]]},{"id":"3ec5e397.54829c","type":"function","z":"74f191ff.db063","name":"SQL","func":"context.set(msg.topic,msg.payload);\n\nvar d = new Date();\nvar epoch = d.getTime();\nvar fromdate = 0;\nvar enddate = 0;\n\nmsg.topic = \"SELECT * FROM message, system WHERE msg_system = sys_id \";\n\nif (context.get(\"msg_severity\")!==undefined) {\n if (context.get(\"msg_severity\")!==\"*\") {\n msg.topic = msg.topic + \" AND msg_severity = \"+context.get(\"msg_severity\");\n }\n}\n\nif (context.get(\"msg_system\")!==undefined) {\n if (context.get(\"msg_system\")!==\"*\") {\n msg.topic = msg.topic + \" AND msg_system = \"+context.get(\"msg_system\");\n }\n}\n\nif (context.get(\"msg_newstate\")===1) {\n msg.topic = msg.topic + \" AND msg_newstate IS NOT NULL \";\n}\n\nif (context.get(\"msg_time\")!==undefined) {\n switch (context.get(\"msg_time\")) {\n case 0:\n fromdate = epoch - 1000*60*60*24;\n msg.topic = msg.topic + \" AND msg_epoch > \"+fromdate;\n break;\n case 1:\n fromdate = epoch - 1000*60*60*24*7;\n msg.topic = msg.topic + \" AND msg_epoch > \"+fromdate;\n break;\n case 2:\n fromdate = epoch - 1000*60*60*24*30;\n msg.topic = msg.topic + \" AND msg_epoch > \"+fromdate;\n break;\n }\n}\n\n// msg.topic = msg.topic.substring(0,msg.topic.length-2);\nmsg.topic = msg.topic+ \" ORDER BY msg_id\";\nreturn msg;","outputs":1,"noerr":0,"x":314,"y":1877,"wires":[["86a6da56.9f5448"]]},{"id":"86a6da56.9f5448","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":502,"y":1877,"wires":[["a1ff7899.091818"]]},{"id":"a36bb94d.aea428","type":"inject","z":"74f191ff.db063","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":147,"y":1877,"wires":[["3ec5e397.54829c"]]},{"id":"eccda258.9b457","type":"ui_button","z":"74f191ff.db063","name":"","group":"726cdf63.1118c","order":2,"width":"3","height":"1","label":"Refresh","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"msg_refresh","x":550,"y":1587,"wires":[["3ec5e397.54829c"]]},{"id":"fb2cfcfb.fdd5","type":"ui_dropdown","z":"74f191ff.db063","name":"Severity","label":"","group":"726cdf63.1118c","order":3,"width":"4","height":"1","passthru":true,"options":[{"label":"All severity","value":"*","type":"str"},{"label":"Information only","value":0,"type":"num"},{"label":"Warnings only","value":1,"type":"num"},{"label":"Errors only","value":2,"type":"num"}],"payload":"","topic":"msg_severity","x":549,"y":1633,"wires":[["3ec5e397.54829c"]]},{"id":"b05c44b1.7c4f38","type":"ui_dropdown","z":"74f191ff.db063","name":"Time filter","label":"","group":"726cdf63.1118c","order":4,"width":"4","height":"1","passthru":true,"options":[{"label":"Last 24 hrs","value":0,"type":"num"},{"label":"Last 7 days","value":1,"type":"num"},{"label":"Last 30 days","value":2,"type":"num"},{"label":"All","value":3,"type":"num"}],"payload":"","topic":"msg_time","x":551,"y":1680,"wires":[["3ec5e397.54829c"]]},{"id":"97e3977d.14ef08","type":"inject","z":"74f191ff.db063","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"x":127,"y":1625,"wires":[["4fe5188b.963bf8","b9de53f5.f51d7","468b7bff.2dffe4"]]},{"id":"4fe5188b.963bf8","type":"change","z":"74f191ff.db063","name":"Initial value","rules":[{"t":"set","p":"payload","pt":"msg","to":"*","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":357,"y":1633,"wires":[["fb2cfcfb.fdd5","40669e88.03a85"]]},{"id":"b9de53f5.f51d7","type":"change","z":"74f191ff.db063","name":"Initial value","rules":[{"t":"set","p":"payload","pt":"msg","to":"0","tot":"num"}],"action":"","property":"","from":"","to":"","reg":false,"x":358,"y":1679,"wires":[["b05c44b1.7c4f38"]]},{"id":"a200b955.f56308","type":"ui_toast","z":"74f191ff.db063","position":"top right","displayTime":"3","outputs":0,"ok":"OK","cancel":"","topic":"","name":"","x":355.5,"y":1181,"wires":[]},{"id":"eb6b97de.1c8378","type":"ui_dropdown","z":"74f191ff.db063","name":"New State","label":"","group":"726cdf63.1118c","order":5,"width":"4","height":"1","passthru":true,"options":[{"label":"All messages","value":0,"type":"num"},{"label":"Only new status","value":1,"type":"num"}],"payload":"","topic":"msg_newstate","x":561,"y":1726,"wires":[["3ec5e397.54829c"]]},{"id":"468b7bff.2dffe4","type":"change","z":"74f191ff.db063","name":"Initial value","rules":[{"t":"set","p":"payload","pt":"msg","to":"0","tot":"num"}],"action":"","property":"","from":"","to":"","reg":false,"x":362,"y":1728,"wires":[["eb6b97de.1c8378"]]},{"id":"f6e11140.f0dd6","type":"link out","z":"74f191ff.db063","name":"System List Updated","links":["6cdc50b.f1341b"],"x":905,"y":645,"wires":[]},{"id":"7aba9e57.e4507","type":"ui_dropdown","z":"74f191ff.db063","name":"System","label":"","group":"726cdf63.1118c","order":6,"width":"5","height":"1","passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"msg_system","x":548,"y":1536,"wires":[["3ec5e397.54829c"]]},{"id":"6cdc50b.f1341b","type":"link in","z":"74f191ff.db063","name":"","links":["f6e11140.f0dd6"],"x":197,"y":1537,"wires":[["146340f0.90227f"]]},{"id":"146340f0.90227f","type":"function","z":"74f191ff.db063","name":"Process systems","func":"var output = [];\noutput.push({\"All systems\":\"*\"});\nfor (var i = 0; i < msg.payload.length; i++) {\n obj = {};\n obj [\"[\"+msg.payload[i].sys_id+\"] \"+msg.payload[i].sys_name]=msg.payload[i].sys_id;\n output.push(obj);\n}\nmsg.options = output;\nreturn msg;","outputs":1,"noerr":0,"x":357,"y":1536,"wires":[["7aba9e57.e4507"]]},{"id":"40669e88.03a85","type":"delay","z":"74f191ff.db063","name":"","pauseType":"delay","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":354.5,"y":1586,"wires":[["7aba9e57.e4507"]]},{"id":"b1b17241.f39f8","type":"comment","z":"74f191ff.db063","name":"Message Archiving","info":"","x":145,"y":1949,"wires":[]},{"id":"582f2b2f.dee284","type":"inject","z":"74f191ff.db063","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":139,"y":2006,"wires":[["2eea1304.7377ac"]]},{"id":"a0f88b0f.150a08","type":"link in","z":"74f191ff.db063","name":"Diag email notification in","links":["3bdde187.1895fe","94858698.7f1fb8"],"x":710.6666666666666,"y":1199.111111111111,"wires":[["e2331a1e.23aa28"]]},{"id":"eefd813.2e2018","type":"comment","z":"74f191ff.db063","name":"Diagnostic Daily Digest","info":"","x":139.3333282470703,"y":2101.777587890625,"wires":[]},{"id":"eeb5ffdf.7e682","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":502,"y":2006,"wires":[[]]},{"id":"2eea1304.7377ac","type":"function","z":"74f191ff.db063","name":"SQL","func":"var d = new Date();\nvar epoch = d.getTime();\n\n// today - 30 days\nvar fromdate = epoch - 1000*60*60*24*30;\n\nmsg.topic = \"DELETE * FROM message WHERE msg_epoch < \"+fromdate;\n\nreturn msg;","outputs":1,"noerr":0,"x":306,"y":2006,"wires":[["eeb5ffdf.7e682"]]},{"id":"f28f61ce.b118c","type":"template","z":"74f191ff.db063","name":"Formatting","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<table>\n <tr><th>System</th><th>Status</th></tr>\n {{#payload}}\n <tr>\n <td>{{sys_name}}</td>\n <td style=\"background-color: {{sta_color}};\">{{sta_title}}</td>\n </tr>\n {{/payload}}\n</table>\n","x":746.8888549804688,"y":2161.888345718384,"wires":[["db68f52d.0fa048"]]},{"id":"9cc86ec4.62c03","type":"function","z":"74f191ff.db063","name":"System State Summary","func":"msg.topic = \"SELECT * FROM system, state WHERE sys_state = sta_id ORDER BY sys_id\";\nreturn msg;","outputs":1,"noerr":0,"x":368.77777099609375,"y":2162.888589859009,"wires":[["4588e139.259ee"]]},{"id":"4588e139.259ee","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":560.7777709960938,"y":2161.888589859009,"wires":[["f28f61ce.b118c","b2a0dc5a.a0fe"]]},{"id":"8804fc3c.5cc4c","type":"inject","z":"74f191ff.db063","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"00 06 * * *","once":false,"x":140.44444274902344,"y":2162.888589859009,"wires":[["9cc86ec4.62c03","c3d3b6ea.ad6648","76fa9a8b.9de9d4","d0b9df8d.ca433","220ccd4a.b5d062","746b9820.3de158","f4ff462.56b3db8"]]},{"id":"c6efe4ca.14f608","type":"join","z":"74f191ff.db063","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"<br/>","timeout":"5","count":"8","x":1288.222183227539,"y":2155.5280361175537,"wires":[["d85ae838.da2a98"]]},{"id":"723d5d7c.430594","type":"template","z":"74f191ff.db063","name":"Formatting","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<table width=\"100%\">\n <tr><td class=\"solidline\"><b>Severity</b></td><td class=\"solidline\"><b>Timestamp</b></td><td class=\"solidline\"><b>System</b></td><td class=\"solidline\"><b>Message</b></td></tr>\n {{#payload}}\n <tr style=\"border-bottom: 1px dotted #e0e0e0;\">\n <td class=\"dottedline\">{{msg_severity}}</td>\n <td class=\"dottedline\">{{msg_timestamp}}</td>\n <td class=\"dottedline\">{{sys_name}}</td>\n <td class=\"dottedline\">{{msg_text}}</td>\n </tr>\n {{/payload}}\n</table>\n","x":735.9999923706055,"y":2231.7775650024414,"wires":[["b0e7b264.00868"]]},{"id":"41938839.e7a568","type":"function","z":"74f191ff.db063","name":"Messages","func":"var d = new Date();\nvar epoch = d.getTime();\nvar fromdate = 0;\nfromdate = epoch - 1000*60*60*24;\n\nmsg.topic = \"SELECT * FROM message, system WHERE msg_system = sys_id AND msg_epoch > \"+fromdate;\nmsg.topic = msg.topic+ \" ORDER BY msg_id\";\n\n//msg.complete = true;\n\nreturn msg;","outputs":1,"noerr":0,"x":337.5555419921875,"y":2232.888589859009,"wires":[["d1125ffc.2dabf"]]},{"id":"d1125ffc.2dabf","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":544.3333129882812,"y":2231.888589859009,"wires":[["723d5d7c.430594"]]},{"id":"c3d3b6ea.ad6648","type":"delay","z":"74f191ff.db063","name":"","pauseType":"delay","timeout":"100","timeoutUnits":"milliseconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":134.7777862548828,"y":2232.888833999634,"wires":[["41938839.e7a568"]]},{"id":"9a1aac91.cfe77","type":"comment","z":"74f191ff.db063","name":"System Status Stat Reset","info":"","x":157.13888549804688,"y":2650.749917984009,"wires":[]},{"id":"677c2138.cbd33","type":"inject","z":"74f191ff.db063","name":"","topic":"reset","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"x":158.24999237060547,"y":2716.305337905884,"wires":[["611ef36d.2ac47c"]]},{"id":"16105f6.a6452a1","type":"function","z":"74f191ff.db063","name":"Reset log","func":"global.set(\"Diag_System_Buffer\",msg.payload);\n\nvar d = new Date();\nvar current = d.getTime();\nvar output = [];\n\nif (msg.payload!==undefined) {\n for (var i = 0; i < msg.payload.length; i++) {\n output = [];\n output.push( { state: msg.payload[i].sys_state, epoch: current } );\n global.set(msg.payload[i].sys_name.replace(\" \",\"_\")+\"_statestat\",output);\n }\n}\n\nreturn msg;","outputs":1,"noerr":0,"x":650.6944274902344,"y":2717.194253921509,"wires":[["46bca2fb.6c849c"]]},{"id":"46bca2fb.6c849c","type":"debug","z":"74f191ff.db063","name":"","active":false,"console":"false","complete":"false","x":821.8054809570312,"y":2717.305582046509,"wires":[]},{"id":"40fbcf44.23282","type":"function","z":"74f191ff.db063","name":"Update state stat","func":"var systembuf = global.get(\"Diag_System_Buffer\");\nvar d = new Date();\nvar current = d.getTime();\nvar sys_name = \"\";\n\n// find the system name\nfor (var i=0; i<systembuf.length; i++) {\n if (systembuf[i].sys_id===msg.system) {\n sys_name = systembuf[i].sys_name;\n }\n}\n\nvar statestat = global.get(sys_name.replace(\" \",\"_\")+\"_statestat\");\nif (statestat===undefined) {\n statestat = [];\n}\n\nstatestat.push({state: msg.state, epoch: current});\nglobal.set(sys_name.replace(\" \",\"_\")+\"_statestat\",statestat);\n\nreturn msg;","outputs":1,"noerr":0,"x":643,"y":1042,"wires":[[]]},{"id":"b2a0dc5a.a0fe","type":"function","z":"74f191ff.db063","name":"Generate state stat","func":"var statebuf = global.get(\"Diag_State_Buffer\");\nvar d = new Date();\nvar current = d.getTime();\nvar sys_name = \"\";\nvar html = \"<table width='100%'>\";\n\nfor (var i=0; i<msg.payload.length; i++) {\n html = html + \"<tr><td nowrap>\" + msg.payload[i].sys_name + \"</td><td width='100%'><table height='100%' width='100%'><tr>\";\n var statestat = global.get(msg.payload[i].sys_name.replace(\" \",\"_\")+\"_statestat\");\n var width = 0;\n for (var j=0; j<statestat.length; j++) {\n if (j===statestat.length-1) {\n // this is the last item in the list\n width=Math.floor((current-statestat[j].epoch)/(current-statestat[0].epoch)*100+0.999999999);\n } else {\n // this is not the last item in the list\n width=Math.floor((statestat[j+1].epoch-statestat[j].epoch)/(current-statestat[0].epoch)*100+0.999999999);\n }\n \n // find the color for the state\n var mycolor = \"\";\n for (var k=0; k<statebuf.length; k++) {\n if (statebuf[k].sta_id===statestat[j].state) {\n mycolor = statebuf[k].sta_color;\n }\n }\n \n html = html + \"<td style='background-color:\" + mycolor + \"; width:\" + width + \"%'> </td>\";\n }\n html = html + \"</tr></table></td></tr>\\n\";\n}\nhtml = html + \"</table>\\n\";\n\nmsg.payload = html;\n\nreturn msg;","outputs":1,"noerr":0,"x":512,"y":2298.999917984009,"wires":[["30e5d608.43859a","611ef36d.2ac47c"]]},{"id":"611ef36d.2ac47c","type":"function","z":"74f191ff.db063","name":"SQL","func":"msg.topic = \"SELECT * FROM system ORDER BY sys_id\";\nreturn msg;","outputs":1,"noerr":0,"x":339.25,"y":2716.749917984009,"wires":[["ccbf44e6.f92d38"]]},{"id":"ccbf44e6.f92d38","type":"sqlite","z":"74f191ff.db063","mydb":"3187c06b.70165","name":"Diag DB","x":492.25,"y":2716.749917984009,"wires":[["16105f6.a6452a1"]]},{"id":"1d26f0ff.9d092f","type":"function","z":"74f191ff.db063","name":"Dump state stat","func":"var systembuf = global.get(\"Diag_System_Buffer\");\nvar d = new Date();\nvar current = d.getTime();\nvar sys_name = \"\";\nvar output = [];\nvar statestat = [];\n\n// find the system name\nfor (var i=0; i<systembuf.length; i++) {\n sys_name = systembuf[i].sys_name;\n //var statestat = \"none\";\n statestat = global.get(sys_name.replace(\" \",\"_\")+\"_statestat\");\n if (statestat===undefined) {\n statestat=[];\n }\n output.push({system: sys_name, stat: statestat});\n}\n\nmsg.payload = output;\n\nreturn msg;","outputs":1,"noerr":0,"x":374.2500305175781,"y":2845.035888671875,"wires":[["5fcddac6.ecbff4"]]},{"id":"5fcddac6.ecbff4","type":"debug","z":"74f191ff.db063","name":"","active":true,"console":"false","complete":"false","x":634.1071510314941,"y":2846.4644412994385,"wires":[]},{"id":"87a24b73.09a008","type":"inject","z":"74f191ff.db063","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":154.1071510314942,"y":2843.6072984422954,"wires":[["1d26f0ff.9d092f"]]},{"id":"fb512652.713ef8","type":"comment","z":"74f191ff.db063","name":"Debugging","info":"","x":89.82142639160156,"y":2792.1787109375,"wires":[]},{"id":"d85ae838.da2a98","type":"template","z":"74f191ff.db063","name":"Email body","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<html>\n <body style=\"margin:0; padding:0;\" bgcolor=\"#f2f2f2\" leftmargin=\"0\" t=\nopmargin=\"0\" marginwidth=\"0\" marginheight=\"0\">\n<style>\n body {\n margin: 0;\n padding: 0;\n -ms-text-size-adjust: 100%;\n -webkit-text-size-adjust: 100%;\n font-family: 'Roboto', Helvetica, Arial, sans-serif;\n }\n\n table {\n border-spacing: 0;\n }\n\n table td {\n border-collapse: collapse;\n }\n\n .ReadMsgBody {\n width: 100%;\n background-color: #ebebeb;\n }\n \n .divider {\n border-bottom: 1px solid #e2066e;\n }\n \n td.solidline {\n border-bottom: 1px solid #e0e0e0;\n }\n \n td.dottedline {\n border-bottom: 1px dotted #e0e0e0;\n } \n\n</style> \n\n<table border=\"0\" width=\"100%\" height=\"100%\" cellpadding=\"0\" cellsp=\nacing=\"0\" bgcolor=\"#ffffff\" style=\"border: 20px solid #f2f2f2;\">\n <tr><td valign=\"top\" width=\"20\"></td><td valign=\"top\">\n \n <p style=\"text-align: right;\"><span style=\"font-size:150%;\">{{{payload.title}}}</span><br/>\n <span style=\"font-size:80%;\">{{{payload.date}}}</span></p>\n <table bgcolor=\"#f3f3f3\" cellspacing=\"4\" align=\"center\"><tr><td bgcolor=\"#ffffff\" width=\"120\">{{{payload.solar1}}}</td><td bgcolor=\"#ffffff\" width=\"120\">{{{payload.solar2}}}</td><td bgcolor=\"#ffffff\" width=\"120\">{{{payload.solar3}}}</td></tr></table>\n </td><td valign=\"top\" width=\"20\"></td></tr>\n <tr><td style=\"border-bottom: 1px solid #e2066e;\" colspan=\"3\"></td></tr>\n <tr><td valign=\"top\" width=\"20\"></td><td valign=\"top\">\n {{{payload.state_summary}}}\n </td><td valign=\"top\" width=\"20\"></td></tr>\n <tr><td style=\"border-bottom: 1px solid #e2066e;\" colspan=\"3\"></td></tr>\n <tr><td valign=\"top\" width=\"20\"></td><td valign=\"top\">\n {{{payload.state_stat}}}\n </td><td valign=\"top\" width=\"20\"></td></tr>\n <tr><td style=\"border-bottom: 1px solid #e2066e;\" colspan=\"3\"></td></tr>\n <tr><td valign=\"top\" width=\"20\"></td><td valign=\"top\">\n {{{payload.messages}}}\n </td><td valign=\"top\" width=\"20\"></td></tr>\n \n</table>\n </body>\n</html>","x":1461.000057220459,"y":2155.527823448181,"wires":[["38af8579.1035da"]]},{"id":"38af8579.1035da","type":"change","z":"74f191ff.db063","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"Daily Diagnostic Report","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1653.222324371338,"y":2155.5279865264893,"wires":[["94858698.7f1fb8"]]},{"id":"94858698.7f1fb8","type":"link out","z":"74f191ff.db063","name":"","links":["a0f88b0f.150a08"],"x":1785.4446334838867,"y":2155.528067588806,"wires":[]},{"id":"db68f52d.0fa048","type":"change","z":"74f191ff.db063","name":"Set topic","rules":[{"t":"set","p":"topic","pt":"msg","to":"state_summary","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":918.2222290039062,"y":2163.2220611572266,"wires":[["c6efe4ca.14f608"]]},{"id":"b0e7b264.00868","type":"change","z":"74f191ff.db063","name":"Set topic","rules":[{"t":"set","p":"topic","pt":"msg","to":"messages","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":904.4444732666016,"y":2231.4444522857666,"wires":[["c6efe4ca.14f608"]]},{"id":"30e5d608.43859a","type":"change","z":"74f191ff.db063","name":"Set topic","rules":[{"t":"set","p":"topic","pt":"msg","to":"state_stat","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":902.6666259765625,"y":2296.222085952759,"wires":[["c6efe4ca.14f608"]]},{"id":"76fa9a8b.9de9d4","type":"function","z":"74f191ff.db063","name":"Run date","func":"// Update the status with current timestamp\nvar now = new Date();\nvar yyyy = now.getFullYear();\nvar mm = now.getMonth() < 9 ? \"0\" + (now.getMonth() + 1) : (now.getMonth() + 1); // getMonth() is zero-based\nvar dd = now.getDate() < 10 ? \"0\" + now.getDate() : now.getDate();\nvar hh = now.getHours() < 10 ? \"0\" + now.getHours() : now.getHours();\nvar mmm = now.getMinutes() < 10 ? \"0\" + now.getMinutes() : now.getMinutes();\nvar ss = now.getSeconds() < 10 ? \"0\" + now.getSeconds() : now.getSeconds();\nmsg.payload = dd + \".\" + mm + \".\" + yyyy + \".\"; \n \nreturn msg;","outputs":1,"noerr":0,"x":336,"y":2356.499917984009,"wires":[["e1ab5f3b.62322"]]},{"id":"e1ab5f3b.62322","type":"change","z":"74f191ff.db063","name":"Set topic","rules":[{"t":"set","p":"topic","pt":"msg","to":"date","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":903.5,"y":2355.249917984009,"wires":[["c6efe4ca.14f608"]]},{"id":"c06f73af.5aceb","type":"change","z":"74f191ff.db063","name":"Set topic","rules":[{"t":"set","p":"topic","pt":"msg","to":"title","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":904.75,"y":2405.249917984009,"wires":[["c6efe4ca.14f608"]]},{"id":"d0b9df8d.ca433","type":"change","z":"74f191ff.db063","name":"Report title","rules":[{"t":"set","p":"payload","pt":"msg","to":"Node Red Diagnistic Daily Report","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":344.75,"y":2403.999917984009,"wires":[["c06f73af.5aceb"]]},{"id":"220ccd4a.b5d062","type":"function","z":"74f191ff.db063","name":"SQL","func":"var p_30d = 1000*60*60*24*30 ; //30 Days\nvar p_7d = 1000*60*60*24*7 ; //7 Days\nvar p_1d = 1000*60*60*24 ; // 1 Day\nvar d = new Date();\nvar current = d.getTime();\nvar today0h = d.setHours(0,0,0,0);\nvar day = d.getDay();\nvar monday0h = today0h - (day + (day === 0 ? -6:1)) * p_1d;\nvar fromdate = 0;\nvar enddate = 0;\nvar sql = [];\n\nfromdate = today0h - p_30d;\nenddate = today0h;\nsql.push({ topic: \"SELECT sum(value) AS value FROM sensor_aggr WHERE device='growatt' AND sensor='today' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n\nfromdate = fromdate - p_30d;\nenddate = enddate - p_30d;\nsql.push({ topic: \"SELECT sum(value) AS value FROM sensor_aggr WHERE device='growatt' AND sensor='today' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n\n// set the completed flag for the join node later\nsql[sql.length-1].complete=true;\n\nreturn [ sql ];","outputs":1,"noerr":0,"x":326.50000381469727,"y":2457.4999647140503,"wires":[["8a033b1d.695708"]]},{"id":"8a033b1d.695708","type":"sqlite","z":"74f191ff.db063","mydb":"1c25415d.b8427f","name":"DB","x":471.50000381469727,"y":2456.4999647140503,"wires":[["c7af6f32.8e423"]]},{"id":"c7af6f32.8e423","type":"join","z":"74f191ff.db063","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","timeout":"","count":"","x":604.5000038146973,"y":2456.4999647140503,"wires":[["927fb22f.af554"]]},{"id":"927fb22f.af554","type":"function","z":"74f191ff.db063","name":"Prep Data","func":"// Output format for the ui_template node\n// msg.current: current values displayed in large numbers\n// msg.unit: unit value shown under the current value\n// msg.trend = up|down: displays the up/down trend arrow\n// msg.reference: reference/past value displayed in smaller numbers\n// msg.title: KPI title shown on the top\n\nmsg.title = \"Solar generation in last 30 days\";\nmsg.current = Math.floor(msg.payload[0][0].value/1000);\nmsg.unit = \"kWh\";\nmsg.reference = Math.floor(msg.payload[1][0].value/1000);\nif (msg.payload[0][0].value>msg.payload[1][0].value) {\n msg.trend = \"⇧\";\n msg.trendcolor = \"green\";\n} else {\n msg.trend = \"⇩\";\n msg.trendcolor = \"red\";\n}\n\nreturn msg;","outputs":1,"noerr":0,"x":766.5000038146973,"y":2456.4999647140503,"wires":[["8e07a6e3.fb2008"]]},{"id":"746b9820.3de158","type":"function","z":"74f191ff.db063","name":"SQL","func":"var p_30d = 1000*60*60*24*30 ; //30 Days\nvar p_7d = 1000*60*60*24*7 ; //7 Days\nvar p_1d = 1000*60*60*24 ; // 1 Day\nvar d = new Date();\nvar current = d.getTime();\nvar today0h = d.setHours(0,0,0,0);\nvar day = d.getDay();\nvar monday0h = today0h - (day + (day === 0 ? -6:1)) * p_1d;\nvar fromdate = 0;\nvar enddate = 0;\nvar sql = [];\n\nfromdate = today0h - p_7d;\nenddate = today0h;\nsql.push({ topic: \"SELECT sum(value) AS value FROM sensor_aggr WHERE device='growatt' AND sensor='today' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n\nfromdate = fromdate - p_7d;\nenddate = enddate - p_7d;\nsql.push({ topic: \"SELECT sum(value) AS value FROM sensor_aggr WHERE device='growatt' AND sensor='today' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n\n// set the completed flag for the join node later\nsql[sql.length-1].complete=true;\n\nreturn [ sql ];","outputs":1,"noerr":0,"x":324.50000381469727,"y":2509.4999647140503,"wires":[["48b52cb8.2a9964"]]},{"id":"48b52cb8.2a9964","type":"sqlite","z":"74f191ff.db063","mydb":"1c25415d.b8427f","name":"DB","x":469.50000381469727,"y":2508.4999647140503,"wires":[["f74bd0c3.46a56"]]},{"id":"f74bd0c3.46a56","type":"join","z":"74f191ff.db063","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","timeout":"","count":"","x":602.5000038146973,"y":2508.4999647140503,"wires":[["90a578bf.47dc38"]]},{"id":"90a578bf.47dc38","type":"function","z":"74f191ff.db063","name":"Prep Data","func":"// Output format for the ui_template node\n// msg.current: current values displayed in large numbers\n// msg.unit: unit value shown under the current value\n// msg.trend = up|down: displays the up/down trend arrow\n// msg.reference: reference/past value displayed in smaller numbers\n// msg.title: KPI title shown on the top\n\nmsg.title = \"Solar generation in last 7 days\";\nmsg.current = parseFloat(msg.payload[0][0].value/1000).toFixed(1);\nmsg.unit = \"kWh\";\nmsg.reference = Math.abs(Math.floor((msg.payload[0][0].value-msg.payload[1][0].value)/msg.payload[0][0].value*100)) + \"%\";\nif (msg.payload[0][0].value>msg.payload[1][0].value) {\n msg.trend = \"⇧\";\n msg.trendcolor = \"green\";\n} else {\n msg.trend = \"⇩\";\n msg.trendcolor = \"red\";\n}\n\nreturn msg;","outputs":1,"noerr":0,"x":764.5000038146973,"y":2508.4999647140503,"wires":[["bd52e835.8a3098"]]},{"id":"f4ff462.56b3db8","type":"function","z":"74f191ff.db063","name":"SQL","func":"var p_30d = 1000*60*60*24*30 ; //30 Days\nvar p_7d = 1000*60*60*24*7 ; //7 Days\nvar p_1d = 1000*60*60*24 ; // 1 Day\nvar d = new Date();\nvar current = d.getTime();\nvar today0h = d.setHours(0,0,0,0);\nvar day = d.getDay();\nvar monday0h = today0h - (day + (day === 0 ? -6:1)) * p_1d;\nvar fromdate = 0;\nvar enddate = 0;\nvar sql = [];\n\nfromdate = today0h-p_1d;\nmsg.topic= \"SELECT * FROM sensor_aggr WHERE device='growatt' AND sensor='today' AND epoch = \" + fromdate + \";\";\n\nreturn msg;","outputs":1,"noerr":0,"x":329.50000381469727,"y":2564.4999647140503,"wires":[["9e9f57bb.9465f8"]]},{"id":"9e9f57bb.9465f8","type":"sqlite","z":"74f191ff.db063","mydb":"1c25415d.b8427f","name":"DB","x":474.50000381469727,"y":2563.4999647140503,"wires":[["7e49b792.d66968"]]},{"id":"7e49b792.d66968","type":"function","z":"74f191ff.db063","name":"Prep Data","func":"// Output format for the ui_template node\n// msg.current: current values displayed in large numbers\n// msg.unit: unit value shown under the current value\n// msg.trend = up|down: displays the up/down trend arrow\n// msg.reference: reference/past value displayed in smaller numbers\n// msg.title: KPI title shown on the top\n\nmsg.title = \"Solar generation yesterday vs. max\";\nmsg.current = parseFloat(msg.payload[0].value/1000).toFixed(2);\nmsg.unit = \"kWh\";\nvar width = Math.abs(Math.floor(msg.payload[0].value/20000*100));\nmsg.reference = width.toString() + \"%\";\nmsg.reference2 = (100-width).toString() + \"%\";\n\nreturn msg;","outputs":1,"noerr":0,"x":634.5000038146973,"y":2563.4999647140503,"wires":[["d6bb8e9c.53853"]]},{"id":"8e07a6e3.fb2008","type":"template","z":"74f191ff.db063","name":"Formatting","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"\n <table width=\"100%\" height=\"100%\">\n <tr>\n <td colspan=\"2\">{{title}}</td>\n </tr>\n <tr>\n <td rowspan=\"2\" style=\"text-align: center;\"><span style=\"font-size: 300%; font-weight: bold;\">{{current}}</span><br/>{{unit}}</td>\n <td style=\"text-align: center; font-size: 300%; font-weight: bold; color:{{trendcolor}}; background-color:white;\">{{{trend}}}</td>\n </tr>\n <tr>\n <td style=\"text-align: center; font-style: italic;\">{{reference}}</td>\n </tr>\n </table>\n","x":938.5000152587891,"y":2456.4999656677246,"wires":[["81496500.6b5a58"]]},{"id":"bd52e835.8a3098","type":"template","z":"74f191ff.db063","name":"Formatting","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"\n <table width=\"100%\" height=\"100%\">\n <tr>\n <td colspan=\"2\">{{title}}</td>\n </tr>\n <tr>\n <td rowspan=\"2\" style=\"text-align: center;\"><span style=\"font-size: 300%; font-weight: bold;\">{{current}}</span><br/>{{unit}}</td>\n <td style=\"text-align: center; font-size: 300%; font-weight: bold; color:{{trendcolor}}; background-color:white;\">{{{trend}}}</td>\n </tr>\n <tr>\n <td style=\"text-align: center; font-style: italic;\">{{reference}}</td>\n </tr>\n </table>\n","x":936.0000152587891,"y":2508.9999656677246,"wires":[["74b9bc33.9a4d14"]]},{"id":"d6bb8e9c.53853","type":"template","z":"74f191ff.db063","name":"Formatting","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"\n <table width=\"100%\" height=\"100%\" style=\"border-collapse: collapse;\">\n <tr>\n <td colspan=\"2\">{{title}}</td>\n </tr>\n <tr>\n <td rowspan=\"2\" style=\"text-align: center;\"><span style=\"font-size: 300%; font-weight: bold;\">{{current}}</span><br/>{{unit}}</td>\n <td style=\"text-align: center; font-size: 300%; font-weight: bold; color:{{trendcolor}}; background-color:white;\"> </td>\n </tr>\n <tr>\n <td style=\"text-align: center; font-style: italic;\">{{reference}}</td>\n </tr>\n <tr style=\"background-color: lightgrey;\"><td colspan=\"2\">\n <table width=\"100%\" height=\"100%\" style=\"border-collapse: collapse;\"><tr>\n <td width={{reference}} style=\"border-bottom: 6px solid blue;\"></td><td width={{reference2}}></td>\n </tr></table></td>\n </tr>\n </table>\n","x":807.2500152587891,"y":2562.7499656677246,"wires":[["1048d690.434449"]]},{"id":"81496500.6b5a58","type":"change","z":"74f191ff.db063","name":"Set topic","rules":[{"t":"set","p":"topic","pt":"msg","to":"solar1","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1104.75,"y":2457.749917984009,"wires":[["c6efe4ca.14f608"]]},{"id":"74b9bc33.9a4d14","type":"change","z":"74f191ff.db063","name":"Set topic","rules":[{"t":"set","p":"topic","pt":"msg","to":"solar2","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1102.25,"y":2511.499917984009,"wires":[["c6efe4ca.14f608"]]},{"id":"1048d690.434449","type":"change","z":"74f191ff.db063","name":"Set topic","rules":[{"t":"set","p":"topic","pt":"msg","to":"solar3","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1103.500015258789,"y":2562.7499656677246,"wires":[["c6efe4ca.14f608"]]},{"id":"3187c06b.70165","type":"sqlitedb","z":"","db":"/home/pi/sqlite/diagnostic"},{"id":"92a841c5.36f1e","type":"ui_group","z":"","name":"State Administration","tab":"8e9ce132.4d12b","disp":true,"width":"6"},{"id":"89937c4.fcd738","type":"ui_group","z":"","name":"System Administration","tab":"8e9ce132.4d12b","order":3,"disp":true,"width":"6"},{"id":"548bd5b.ace5b2c","type":"ui_group","z":"","name":"System Status","tab":"8e9ce132.4d12b","disp":true,"width":"6"},{"id":"726cdf63.1118c","type":"ui_group","z":"","name":"System Messages","tab":"8e9ce132.4d12b","disp":true,"width":"24"},{"id":"1c25415d.b8427f","type":"sqlitedb","z":"","db":"/home/pi/sqlite/nodered"},{"id":"8e9ce132.4d12b","type":"ui_tab","z":"","name":"Logs","icon":"message","order":3}]
