A couple of people asked if I could document my process for using Node-RED to store and display HE logs, so here goes:
Overall design
Log data to DB : Node-RED WebSocket node subscribes to the data published by the logsocket connection on HE and stores the data into a mySQL database. If the log type is anything other than "debug", "info" or "warn", the flow sends out an SMS/email via Gmail using the email node.
Display log data : Node-RED native dashboard ui_table is used to display the data. The data can be filtered by date, device or message type (info, warn, error...)
Periodic data purge : Log info is stored for the past 4 weeks. Once a week, any log data > 4 weeks old is exported to a text file and deleted from the database.
Misc maintenance : If Hubitat is restarted and Node-RED is not, the websocket node shows that it is connected to HE but is not actually receiving any data. To work around this issue, a flow periodically queries the database for the latest entry and if it is > 30 minutes, it will automatically restart the flow. The restart (redeploy) of Node-RED is also triggered by the systemStart event from HE.
Create the database table:
create table IF NOT EXISTS logs (row_id int auto_increment primary key, name varchar(1000) null, msg varchar(8000) null, id int, time datetime, type varchar(15), level varchar (50));
Write logs to database
WebSocket node config:
Function Node - Log SQL
//Fix for SQL insert errors on HPM name
if(msg.id == 133){
msg.name = 'Hubitat Package Manager'
}
var sqlString = 'INSERT INTO LOGS (NAME,MSG,ID,TIME,TYPE, LEVEL)VALUE ('
var quote = "'"
var comma = ","
sqlString +=quote+msg.name+quote+comma
sqlString +=quote+msg.msg+quote+comma
sqlString +=msg.id+comma
sqlString +=quote+msg.time+quote+comma
sqlString +=quote+msg.type+quote+comma
sqlString +=quote+msg.level+quote+")"
msg.topic = sqlString
return msg;
NOTE: Hubitat Package Manager log data can contain html tags that cause SQL errors. This happens when there is an update available. I had to put in special handling for this based on device id.
Filter error messages and format for email
The complete flow:
[{"id":"65a51cc8.270a7c","type":"group","z":"7b994e31.f24a6","style":{"stroke":"#999999","stroke-opacity":"1","fill":"none","fill-opacity":"1","label":true,"label-position":"nw","color":"#a4a4a4"},"nodes":["36966e59.07b402","57aaa540.2a5b84","c0a6c5c4.10901","7a12636a.8b7024","6ab45c5f.4f89d4","9fdfa8df.0fde48","97cb985e.2d933","8c34dafb.a6673"],"x":78.33329772949219,"y":2022.999755859375,"w":1025.3333740234375,"h":240.666748046875},{"id":"9af4e78a.36d3a8","type":"subflow","name":"Email/SMS Sublow","info":"","category":"","in":[{"x":64,"y":71,"wires":[{"id":"959cdf48.a1d5e"},{"id":"1b30b239.c60986"}]}],"out":[{"x":687.9999694824219,"y":137.9999771118164,"wires":[{"id":"df9128d8.bd35c8","port":0}]}],"env":[],"color":"#DDAA99","status":{"x":297.99998474121094,"y":198.99999237060547,"wires":[{"id":"6190969c.a1bb28","port":0}]}},{"id":"959cdf48.a1d5e","type":"e-mail","z":"9af4e78a.36d3a8","server":"smtp.gmail.com","port":"465","secure":true,"tls":true,"name":"","dname":"Send Email/SMS","x":246,"y":72,"wires":[]},{"id":"6190969c.a1bb28","type":"status","z":"9af4e78a.36d3a8","name":"Update Status","scope":null,"x":165.33331298828125,"y":199.00000762939453,"wires":[[]]},{"id":"2ddd3654.97432a","type":"comment","z":"9af4e78a.36d3a8","name":"Change Log","info":"2021-03-16: Changed sending email to gupta.notifications@gmail.com (new account)\n2021-05-28: Added logging of Notification Event","x":114.33332824707031,"y":291.33333587646484,"wires":[]},{"id":"df9128d8.bd35c8","type":"change","z":"9af4e78a.36d3a8","name":"Create Notification Event","rules":[{"t":"set","p":"log.displayName","pt":"msg","to":"'Notification - '&log.displayName","tot":"jsonata"},{"t":"set","p":"log.descriptionText","pt":"msg","to":"payload","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"log","tot":"msg"},{"t":"delete","p":"log","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":512.3332824707031,"y":137.00000762939453,"wires":[[]]},{"id":"1b30b239.c60986","type":"switch","z":"9af4e78a.36d3a8","name":"Check log attribute","property":"log","propertyType":"msg","rules":[{"t":"nnull"}],"checkall":"true","repair":false,"outputs":1,"x":249.33334350585938,"y":136.99999237060547,"wires":[["df9128d8.bd35c8"]]},{"id":"36966e59.07b402","type":"websocket in","z":"7b994e31.f24a6","g":"65a51cc8.270a7c","name":"Hubitat Log Socket","server":"96044230.8127a","client":"","x":197.6666259765625,"y":2119.333251953125,"wires":[["57aaa540.2a5b84","9fdfa8df.0fde48"]]},{"id":"57aaa540.2a5b84","type":"function","z":"7b994e31.f24a6","g":"65a51cc8.270a7c","name":"Log SQL","func":"//Fix for SQL insert errors on HPM name\nif(msg.id == 133){\n msg.name = 'Hubitat Package Manager'\n}\n\nvar sqlString = 'INSERT INTO LOGS (NAME,MSG,ID,TIME,TYPE, LEVEL)VALUE ('\nvar quote = \"'\"\nvar comma = \",\"\nsqlString +=quote+msg.name+quote+comma\nsqlString +=quote+msg.msg+quote+comma\nsqlString +=msg.id+comma\nsqlString +=quote+msg.time+quote+comma\nsqlString +=quote+msg.type+quote+comma\nsqlString +=quote+msg.level+quote+\")\"\n\n//sqlString = sqlString+quote+msg.name+quote+comma+quote+msg.msg+quote+\",\"+msg.id+\",\"+msg.time+\",\"+msg.type+\",\"+msg.level+\")\"\nmsg.topic = sqlString\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":431.99993896484375,"y":2119.333381652832,"wires":[["c0a6c5c4.10901"]]},{"id":"c0a6c5c4.10901","type":"mysql","z":"7b994e31.f24a6","g":"65a51cc8.270a7c","mydb":"2d73f9f7.c404d6","name":"Create Log DB Entry","x":972.6666564941406,"y":2119.66650390625,"wires":[[]]},{"id":"7a12636a.8b7024","type":"comment","z":"7b994e31.f24a6","g":"65a51cc8.270a7c","name":"Hubitat Log","info":"2021-05-30: Created","x":174.3332977294922,"y":2063.999755859375,"wires":[]},{"id":"6ab45c5f.4f89d4","type":"comment","z":"7b994e31.f24a6","g":"65a51cc8.270a7c","name":"Change Log","info":"2021-06-02: Removed filter node as I think it was filtering out actual data\n2021-06-10: added notification if level <> info or debug\n2021-06-11: added filter for \"warn\" because of turning off debug logging for leak sensors\n2021-06-12: Removed HE login process as that did not re-establish the websocket connection. Replaced with re-deploying NR flows\n2021-06-13: Added fix for HPM name field creating SQL insert errors\n2021-06-24: Reactivated restart of NR on HE restart (fix for websocket disconnect)\n2021-08-01: Removed restarting flows as it was moved to Notifications tab\n2021-09-13: Removed filter for \"warn\" notifications to debug zigbee firmware update error messages\n2021-09-14: Added back \"warn\" filter - too many messages","x":181.6666259765625,"y":2222.66650390625,"wires":[]},{"id":"9fdfa8df.0fde48","type":"switch","z":"7b994e31.f24a6","g":"65a51cc8.270a7c","name":"Not Info, Debug or Warn","property":"level in [\"info\",\"debug\"]","propertyType":"jsonata","rules":[{"t":"false"}],"checkall":"true","repair":false,"outputs":1,"x":482.33331298828125,"y":2181,"wires":[["97cb985e.2d933"]]},{"id":"97cb985e.2d933","type":"change","z":"7b994e31.f24a6","g":"65a51cc8.270a7c","name":"Format for SMS/Email","rules":[{"t":"set","p":"message","pt":"msg","to":"msg","tot":"msg"},{"t":"delete","p":"msg","pt":"msg"},{"t":"set","p":"topic","pt":"msg","to":"\"Log: \"&level&\" - \"&name","tot":"jsonata"},{"t":"set","p":"payload","pt":"msg","to":"\"Date/Time: \"&time&\"\\n\"&\"Message: \"&message","tot":"jsonata"},{"t":"set","p":"to","pt":"msg","to":"$globalContext(\"rakeshSMS\")&\",\"&$globalContext(\"rakeshEmail\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":729.3333435058594,"y":2182,"wires":[["8c34dafb.a6673"]]},{"id":"8c34dafb.a6673","type":"subflow:9af4e78a.36d3a8","z":"7b994e31.f24a6","g":"65a51cc8.270a7c","name":"","env":[],"x":977.6666717529297,"y":2180.6666259765625,"wires":[[]]},{"id":"96044230.8127a","type":"websocket-listener","path":"ws://192.168.1.113/logsocket","wholemsg":"true"},{"id":"2d73f9f7.c404d6","type":"MySQLdatabase","name":"Hubitat_Event_Prod","host":"192.168.1.86","port":"3306","db":"hubitat_logging","tz":"","charset":""}]
Check for dropped websocket connection and HE restart event:
The complete flow:
[{"id":"ed35d08.60c2eb","type":"group","z":"f8301745.84255","style":{"stroke":"#999999","stroke-opacity":"1","fill":"none","fill-opacity":"1","label":true,"label-position":"nw","color":"#a4a4a4"},"nodes":["efddebd2.0446","5c0e9b18.5ed764","7b0ae38f.f3d324","9a75adc0.43aa","5b26a2ec.1ae9bc","6276c97d.4a232","e03a33be.98ae3","3f2d1764.947a7","bd7872ea.ff2788","47c54dac.f491e4","45ea655a.87abf4","e7d89981.94d008","766ea13b.52fee8","c00b82c.f11e5","cca233d5.227588","5cd01c6e.ed7d7c","5115ddc.cd0c424","484e7fed.492e2"],"x":140.61911010742188,"y":1943.8572692871094,"w":1374.0001525878906,"h":459.9999084472656},{"id":"9af4e78a.36d3a8","type":"subflow","name":"Email/SMS Sublow","info":"","category":"","in":[{"x":64,"y":71,"wires":[{"id":"959cdf48.a1d5e"},{"id":"1b30b239.c60986"}]}],"out":[{"x":687.9999694824219,"y":137.9999771118164,"wires":[{"id":"df9128d8.bd35c8","port":0}]}],"env":[],"color":"#DDAA99","status":{"x":297.99998474121094,"y":198.99999237060547,"wires":[{"id":"6190969c.a1bb28","port":0}]}},{"id":"959cdf48.a1d5e","type":"e-mail","z":"9af4e78a.36d3a8","server":"smtp.gmail.com","port":"465","secure":true,"tls":true,"name":"","dname":"Send Email/SMS","x":246,"y":72,"wires":[]},{"id":"6190969c.a1bb28","type":"status","z":"9af4e78a.36d3a8","name":"Update Status","scope":null,"x":165.33331298828125,"y":199.00000762939453,"wires":[[]]},{"id":"2ddd3654.97432a","type":"comment","z":"9af4e78a.36d3a8","name":"Change Log","info":"2021-03-16: Changed sending email to gupta.notifications@gmail.com (new account)\n2021-05-28: Added logging of Notification Event","x":114.33332824707031,"y":291.33333587646484,"wires":[]},{"id":"df9128d8.bd35c8","type":"change","z":"9af4e78a.36d3a8","name":"Create Notification Event","rules":[{"t":"set","p":"log.displayName","pt":"msg","to":"'Notification - '&log.displayName","tot":"jsonata"},{"t":"set","p":"log.descriptionText","pt":"msg","to":"payload","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"log","tot":"msg"},{"t":"delete","p":"log","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":512.3332824707031,"y":137.00000762939453,"wires":[[]]},{"id":"1b30b239.c60986","type":"switch","z":"9af4e78a.36d3a8","name":"Check log attribute","property":"log","propertyType":"msg","rules":[{"t":"nnull"}],"checkall":"true","repair":false,"outputs":1,"x":249.33334350585938,"y":136.99999237060547,"wires":[["df9128d8.bd35c8"]]},{"id":"efddebd2.0446","type":"cronplus","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Every 30 mins","outputField":"payload","timeZone":"","persistDynamic":false,"commandResponseMsgOutput":"output1","outputs":1,"options":[{"name":"schedule1","topic":"schedule1","payloadType":"date","payload":"","expressionType":"cron","expression":"0 5,35 * * * *","location":"","offset":"0","solarType":"all","solarEvents":"sunrise,sunset"}],"x":266.6191101074219,"y":2042.1904296875,"wires":[["5c0e9b18.5ed764"]]},{"id":"5c0e9b18.5ed764","type":"function","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Fetch max Logs time","func":"// Get current time and when last log enry was received\n\nvar d='select UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) as current, unix_timestamp(Max(time)) as latest from logs' \n//var d = 'select * from logs'\nmsg.topic = d\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":491.61910247802734,"y":2042.5239562988281,"wires":[["7b0ae38f.f3d324"]]},{"id":"7b0ae38f.f3d324","type":"mysql","z":"f8301745.84255","g":"ed35d08.60c2eb","mydb":"2d73f9f7.c404d6","name":"","x":740.6190490722656,"y":2042.857177734375,"wires":[["9a75adc0.43aa"]]},{"id":"9a75adc0.43aa","type":"change","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Verify last transmission","rules":[{"t":"set","p":"currentTime","pt":"msg","to":"payload[0].current","tot":"jsonata"},{"t":"set","p":"lastData","pt":"msg","to":"payload[0].latest","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":994.9524230957031,"y":2043.857177734375,"wires":[["5b26a2ec.1ae9bc"]]},{"id":"5b26a2ec.1ae9bc","type":"function","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Calc time since last log entry","func":"//Copy attributes to variables\n\nvar currentTime = msg.currentTime*1000;\nvar lastData = msg.lastData*1000;\n\n//node.warn(\"currentTime=\"+currentTime);\n//node.warn(\"lastRestarrt=\"+lastRestart);\n\n\n\n//Conversion factors\nvar millisInDays = 86400000;\nvar millisInHrs =3600000;\nvar millisInMins = 60000;\nvar millisInSecs = 1000;\n\n//Text for days, hours, minutes and seconds\nvar daysText ='d';\nvar hrsText ='h';\nvar minText ='m';\nvar secText ='s';\n\n//Calc differene in milliseconds\nvar ut = (currentTime) - (lastData);\n\n//Calc minutes sicne last data received\nvar totalTimeInMins = ut/millisInMins\n//node.warn(\"ut=\"+ut);\n\nvar utBalance = ut;\n\n//Calculate days, hous, months and seconds\nvar days = Math.trunc((utBalance/(millisInDays)));\nutBalance = utBalance - (days*millisInDays);\nvar utBalanceD = utBalance;\n\nvar hrs = Math.trunc(utBalance/(millisInHrs));\nutBalance = utBalance - (hrs*millisInHrs);\nvar utBalanceH = utBalance;\n\nvar min = Math.trunc(utBalance/(millisInMins));\nutBalance = utBalance - (min*millisInMins);\nvar utBalanceM = utBalance;\n\nvar sec = Math.trunc(utBalance/(millisInSecs));\n\n\nmsg.days = days;\nmsg.hrs = hrs;\nmsg.min = min;\nmsg.sec = sec;\nmsg.totalTimeInMins = totalTimeInMins;\n\nif (days !=1) {\n daysText = 'days';\n } else {\n daysText = 'day';\n}\n\nif (hrs !=1) {\n hrsText = 'hours';\n } else {\n hrsText = 'hour';\n}\n\nif (min !=1) {\n minText = 'minutes';\n } else {\n minText = 'minute';\n}\n\nif (sec !=1) {\n secText = 'seconds';\n } else {\n secText = 'second';\n}\n\nmsg.payload = null;\nmsg.payload = days+\" \"+daysText+\", \"+hrs+\" \"+hrsText+\", \"+min+\" \"+minText+\", \"+sec+\" \"+secText;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":324.95263671875,"y":2144.857177734375,"wires":[["6276c97d.4a232"]]},{"id":"6276c97d.4a232","type":"switch","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Check mins since last data","property":"totalTimeInMins","propertyType":"msg","rules":[{"t":"gt","v":"90","vt":"num"}],"checkall":"false","repair":false,"outputs":1,"x":623.9525909423828,"y":2144.857666015625,"wires":[["e03a33be.98ae3"]]},{"id":"e03a33be.98ae3","type":"change","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Create SMS/Email data","rules":[{"t":"delete","p":"currentTime","pt":"msg"},{"t":"delete","p":"lastData","pt":"msg"},{"t":"delete","p":"days","pt":"msg"},{"t":"delete","p":"hrs","pt":"msg"},{"t":"delete","p":"min","pt":"msg"},{"t":"delete","p":"sec","pt":"msg"},{"t":"delete","p":"totalTimeInMins","pt":"msg"},{"t":"set","p":"topic","pt":"msg","to":"Warning - logsocket data not flowing","tot":"str"},{"t":"set","p":"payload","pt":"msg","to":"\"Last log entry was received \"&payload&\" ago. Restart Node-RED flows.\"","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":888.2859497070312,"y":2144.5246963500977,"wires":[["3f2d1764.947a7","bd7872ea.ff2788"]]},{"id":"3f2d1764.947a7","type":"change","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Add SMS/Email Params","rules":[{"t":"set","p":"to","pt":"msg","to":"$globalContext(\"rakeshSMS\")&\",\"&$globalContext(\"rakeshEmail\")","tot":"jsonata"},{"t":"set","p":"topic","pt":"msg","to":"\"Warning - \"&$globalContext(\"wsAlertCount\")&\" :Logsocket data not received\"","tot":"jsonata"},{"t":"set","p":"payload","pt":"msg","to":"\"Last log entry was received \"&payload&\" ago.\"","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1146.9525146484375,"y":2145.190589904785,"wires":[["47c54dac.f491e4"]]},{"id":"bd7872ea.ff2788","type":"function","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Node-RED http header","func":"msg.url = 'http://localhost:1880/flows';\nmsg.method = 'POST';\nmsg.headers = {};\nmsg.headers['content-type'] = \"application/json; charset=utf-8\";\nmsg.headers['Node-RED-Deployment-Type'] = \"reload\";\nmsg.headers['Node-RED-API-Version'] = \"v2\";\nmsg.payload = {\n \"flows\": [\n {\n \"type\": \"tab\",\n \"id\": \"7b994e31.f24a6\",\n \"label\": \"Hubitat Events DB\"\n }\n ]\n};\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":886.2862396240234,"y":2224.52481842041,"wires":[["766ea13b.52fee8"]]},{"id":"47c54dac.f491e4","type":"subflow:9af4e78a.36d3a8","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"","x":1388.6192626953125,"y":2144.857093811035,"wires":[[]]},{"id":"45ea655a.87abf4","type":"inject","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Manual restart NR Flows","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":576.6192016601562,"y":2222.85733795166,"wires":[["bd7872ea.ff2788"]]},{"id":"e7d89981.94d008","type":"switch","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Scheduled Reboot?","property":"scheduledReboot","propertyType":"global","rules":[{"t":"false"},{"t":"null"},{"t":"true"}],"checkall":"true","repair":false,"outputs":3,"x":526.619140625,"y":2290.857177734375,"wires":[["bd7872ea.ff2788"],["bd7872ea.ff2788"],["cca233d5.227588"]]},{"id":"766ea13b.52fee8","type":"http request","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Reestart Node-RED flows","method":"use","ret":"txt","paytoqs":"ignore","url":"","tls":"","persist":false,"proxy":"","authType":"","x":1147.2866668701172,"y":2224.525062561035,"wires":[["5cd01c6e.ed7d7c"]]},{"id":"c00b82c.f11e5","type":"link in","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Restart NR Flow - In","links":["900f7db3.900b9"],"x":284.9524230957031,"y":2290.5239181518555,"wires":[["e7d89981.94d008"]],"l":true},{"id":"cca233d5.227588","type":"change","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Set Scheduled Reboot GV = False","rules":[{"t":"set","p":"scheduledReboot","pt":"global","to":"false","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":846.6191101074219,"y":2302.857177734375,"wires":[[]]},{"id":"5cd01c6e.ed7d7c","type":"change","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Delete Payload","rules":[{"t":"delete","p":"payload","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1376.2864227294922,"y":2224.858070373535,"wires":[[]]},{"id":"5115ddc.cd0c424","type":"comment","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Verify logsocket connection is working","info":"","x":341.6191101074219,"y":1984.8572692871094,"wires":[]},{"id":"484e7fed.492e2","type":"comment","z":"f8301745.84255","g":"ed35d08.60c2eb","name":"Change Log","info":"2021-06-04: Created. Currently auto restart is disabled\n2021-07-26: Enabled autorestart as websocket does not connect on unexpected Hub full restart. Also removed count global variable so flows restart if last websocket entry was > 60 mins ago.\n2021-07-26: Changed time interval to 90 minutes as log messages slow down at night\n2021-07-26: Added restarting flows if system restart <> scheduled\n2021-07-31: Linked to HE Restart Notification flow","x":252.61911010742188,"y":2362.857177734375,"wires":[]},{"id":"2d73f9f7.c404d6","type":"MySQLdatabase","name":"Hubitat_Event_Prod","host":"192.168.1.86","port":"3306","db":"hubitat_logging","tz":"","charset":""}]
There is some stuff in this flow that is specific to my situation (eg: global variable for scheduled reboot) but the rest of the logic is pretty straightforward.
Export log data to text and delete from DB
The complete flow:
[{"id":"bb185947.db5538","type":"group","z":"934f1005.6e02","style":{"stroke":"#999999","stroke-opacity":"1","fill":"none","fill-opacity":"1","label":true,"label-position":"nw","color":"#a4a4a4"},"nodes":["c0a6de16.b5c3d","e9e479b2.d1c26"],"x":38.666656494140625,"y":2486.666664123535,"w":2384.3333435058594,"h":648.3335800170898},{"id":"c0a6de16.b5c3d","type":"group","z":"934f1005.6e02","g":"bb185947.db5538","style":{"stroke":"#999999","stroke-opacity":"1","fill":"none","fill-opacity":"1","label":true,"label-position":"nw","color":"#a4a4a4"},"nodes":["42572637.38d988","8a75bea4.2bd5c8","220c4427.14aac4","eab0f86a.c3fff8"],"x":67.66665649414062,"y":2972.33349609375,"w":841.9999389648438,"h":136.666748046875},{"id":"9af4e78a.36d3a8","type":"subflow","name":"Email/SMS Sublow","info":"","category":"","in":[{"x":64,"y":71,"wires":[{"id":"959cdf48.a1d5e"},{"id":"1b30b239.c60986"}]}],"out":[{"x":687.9999694824219,"y":137.9999771118164,"wires":[{"id":"df9128d8.bd35c8","port":0}]}],"env":[],"color":"#DDAA99","status":{"x":297.99998474121094,"y":198.99999237060547,"wires":[{"id":"6190969c.a1bb28","port":0}]}},{"id":"959cdf48.a1d5e","type":"e-mail","z":"9af4e78a.36d3a8","server":"smtp.gmail.com","port":"465","secure":true,"tls":true,"name":"","dname":"Send Email/SMS","x":246,"y":72,"wires":[]},{"id":"6190969c.a1bb28","type":"status","z":"9af4e78a.36d3a8","name":"Update Status","scope":null,"x":165.33331298828125,"y":199.00000762939453,"wires":[[]]},{"id":"2ddd3654.97432a","type":"comment","z":"9af4e78a.36d3a8","name":"Change Log","info":"2021-03-16: Changed sending email to gupta.notifications@gmail.com (new account)\n2021-05-28: Added logging of Notification Event","x":114.33332824707031,"y":291.33333587646484,"wires":[]},{"id":"df9128d8.bd35c8","type":"change","z":"9af4e78a.36d3a8","name":"Create Notification Event","rules":[{"t":"set","p":"log.displayName","pt":"msg","to":"'Notification - '&log.displayName","tot":"jsonata"},{"t":"set","p":"log.descriptionText","pt":"msg","to":"payload","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"log","tot":"msg"},{"t":"delete","p":"log","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":512.3332824707031,"y":137.00000762939453,"wires":[[]]},{"id":"1b30b239.c60986","type":"switch","z":"9af4e78a.36d3a8","name":"Check log attribute","property":"log","propertyType":"msg","rules":[{"t":"nnull"}],"checkall":"true","repair":false,"outputs":1,"x":249.33334350585938,"y":136.99999237060547,"wires":[["df9128d8.bd35c8"]]},{"id":"42572637.38d988","type":"catch","z":"934f1005.6e02","g":"c0a6de16.b5c3d","name":"HB Export Log Error","scope":["3a370967.021eb6","f1b5d110.f7216","a1a3d10a.321a7","55eda512.15332c","6e3faed6.28c038","552265e5.5b111c","39d8ad87.6af632"],"uncaught":false,"x":193.66665649414062,"y":3066.33349609375,"wires":[["220c4427.14aac4"]]},{"id":"8a75bea4.2bd5c8","type":"comment","z":"934f1005.6e02","g":"c0a6de16.b5c3d","name":"Exception Handing - Export Logs","info":"","x":233.66665649414062,"y":3013.33349609375,"wires":[]},{"id":"220c4427.14aac4","type":"function","z":"934f1005.6e02","g":"c0a6de16.b5c3d","name":"Set Error Notification Params","func":"//Set error message\n\nvar message = \"An error occurred at Name: \"+msg.error.source.name+ \", Node Type: \"+msg.error.source.type +\" , Message: \"+msg.error.message\nvar topic = \"Log archive exception\";\n\n// Added for email/sms\n\nvar to = global.get(\"rakeshSMS\")+\",\"+global.get(\"rakeshEmail\");\n\n/* Not needed for email (only used for Pushover)\nvar priority = 0 //values are -2,-1,0,1,2\nvar device = \"\" //Blank device = All\nvar url = \"\" // url to be included in message\nvar url_title = \"\" // title of URL\nvar sound = \"\" // notifcation sound\nvar attachmentPath = \"\" //file path if attachment is included\n*/\n\n//Inputs for Notification/Email Subflow\nmsg.payload = message;\nmsg.topic = topic;\nmsg.to = to;\n\n/* Not needed for email (only for Pushover)\nmsg.device = device;\nmsg.priority = priority;\nmsg.url = url;\nmsg.url_title = url_title\nmsg.attachment = attachmentPath;\nmsg.sound = sound;\n*/\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":505.6666259765625,"y":3067.000244140625,"wires":[["eab0f86a.c3fff8"]]},{"id":"eab0f86a.c3fff8","type":"subflow:9af4e78a.36d3a8","z":"934f1005.6e02","g":"c0a6de16.b5c3d","name":"","x":783.6665954589844,"y":3068.000244140625,"wires":[[]]},{"id":"e9e479b2.d1c26","type":"group","z":"934f1005.6e02","g":"bb185947.db5538","style":{"stroke":"#999999","stroke-opacity":"1","fill":"none","fill-opacity":"1","label":true,"label-position":"nw","color":"#a4a4a4"},"nodes":["719afa0f.dd3ee4","a17430b5.9da9e8","faa60f3f.2fd638","f1e5bc5a.73f098","de988ab5.70b808","552265e5.5b111c","62e86954.30b21","e044e5aa.ccf9","7b739ef5.766258","a1a3d10a.321a7","e0332290.521f28","2025b474.5c5fb4","cbc29a8d.fa9778","377b7098.6fead8","a3b50d57.f2209","55eda512.15332c","92abf1aa.ac5508","6e3faed6.28c038","b453fdd8.6e2188","265287cc.eb6cb","ed539233.d43e08","12e96aa9.fd603d","39d8ad87.6af632"],"x":64.66665649414062,"y":2512.666664123535,"w":2332.3333435058594,"h":423.33333587646484},{"id":"719afa0f.dd3ee4","type":"inject","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"12:15 AM Saturday","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"15 00 * * 6","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":210.66665649414062,"y":2602.0001220703125,"wires":[["a17430b5.9da9e8"]]},{"id":"a17430b5.9da9e8","type":"change","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Set File Path Parts","rules":[{"t":"set","p":"filePath","pt":"msg","to":"/home/pi/Documents/Hubitat_Logs/","tot":"str"},{"t":"set","p":"fileSuffix","pt":"msg","to":" - Hubitat_Logs.csv","tot":"str"},{"t":"set","p":"ftpPath","pt":"msg","to":"/rakesh/Home Automation Backups/Hubitat Logs/","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":424.99993896484375,"y":2601.66650390625,"wires":[["f1e5bc5a.73f098"]]},{"id":"faa60f3f.2fd638","type":"function","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Set Full File Path","func":"//Log file name\nmsg.filename = msg.filePath+msg.dateTo+msg.fileSuffix;\n// FTP path+name for backup to NAS\nmsg.ftpFilename = msg.ftpPath+msg.dateTo+msg.fileSuffix;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":812.9998779296875,"y":2601.66650390625,"wires":[["de988ab5.70b808"]]},{"id":"f1e5bc5a.73f098","type":"moment","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Get dateTo","topic":"","input":"","inputType":"date","inTz":"America/Chicago","adjAmount":"29","adjType":"days","adjDir":"subtract","format":"YYYY-MM-DD","locale":"en-US","output":"dateTo","outputType":"msg","outTz":"America/Chicago","x":616.9998779296875,"y":2601,"wires":[["faa60f3f.2fd638"]]},{"id":"de988ab5.70b808","type":"function","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Fetch data to be archived","func":"//SQL to select data\n\n//var d='select * from LOGS where date(time)>=\"'+msg.dateFrom+'\" and date(time)<= \"'+msg.dateTo+'\" order by row_id desc'\nvar d='select row_id, name, msg, id, date_format(time,\"%Y-%m-%d %H:%i:%s\") as \"dateTime\", type, level from LOGS where date(time)<=\"'+msg.dateTo+ '\" order by row_id asc'\nmsg.topic = d\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1065.9999084472656,"y":2602,"wires":[["552265e5.5b111c"]]},{"id":"552265e5.5b111c","type":"mysql","z":"934f1005.6e02","g":"e9e479b2.d1c26","mydb":"2d73f9f7.c404d6","name":"HE Logs Export DB","x":1318.6666259765625,"y":2602,"wires":[["62e86954.30b21"]]},{"id":"62e86954.30b21","type":"switch","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Record count > 0","property":"$count(payload)","propertyType":"jsonata","rules":[{"t":"gt","v":"0","vt":"str"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":270.66668701171875,"y":2681,"wires":[["e044e5aa.ccf9"],["7b739ef5.766258"]]},{"id":"e044e5aa.ccf9","type":"csv","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"","sep":",","hdrin":"","hdrout":"all","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":true,"include_null_values":true,"x":481.66668701171875,"y":2676,"wires":[["a1a3d10a.321a7"]]},{"id":"7b739ef5.766258","type":"change","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"No records selected","rules":[{"t":"set","p":"payload","pt":"msg","to":"\"Hubitat log export through \"&dateTo&\" - no data selected\"","tot":"jsonata"},{"t":"set","p":"topic","pt":"msg","to":"\"Hubitat Log Export -\"&dateTo","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":548.6666870117188,"y":2840.33349609375,"wires":[["e0332290.521f28"]]},{"id":"a1a3d10a.321a7","type":"file","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Save Logs CSV","filename":"","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":642.9999694824219,"y":2675.66650390625,"wires":[["2025b474.5c5fb4"]]},{"id":"e0332290.521f28","type":"change","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Add SMS/Email Params","rules":[{"t":"set","p":"to","pt":"msg","to":"$globalContext(\"rakeshSMS\")&\",\"&$globalContext(\"rakeshEmail\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1775.666748046875,"y":2840,"wires":[["a3b50d57.f2209"]]},{"id":"2025b474.5c5fb4","type":"change","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Set FTP File Paths","rules":[{"t":"set","p":"localFilename","pt":"msg","to":"filename","tot":"msg"},{"t":"set","p":"filename","pt":"msg","to":"ftpFilename","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":860.9998474121094,"y":2675.666748046875,"wires":[["55eda512.15332c"]]},{"id":"cbc29a8d.fa9778","type":"change","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Backup Failed","rules":[{"t":"set","p":"payload","pt":"msg","to":"\"Hubitat log export through \"&dateTo&\" failed\"","tot":"jsonata"},{"t":"set","p":"topic","pt":"msg","to":"\"Hubitat Log Export -\"&dateTo","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1518.9999389648438,"y":2760.666748046875,"wires":[["e0332290.521f28"]]},{"id":"377b7098.6fead8","type":"change","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Backup Success","rules":[{"t":"set","p":"payload","pt":"msg","to":"\"Hubitat log export through \"&dateTo&\" was successful\"","tot":"jsonata"},{"t":"set","p":"topic","pt":"msg","to":"\"Hubitat Log Export -\"&dateTo","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1960.9998779296875,"y":2671.666748046875,"wires":[["e0332290.521f28","12e96aa9.fd603d"]]},{"id":"a3b50d57.f2209","type":"subflow:9af4e78a.36d3a8","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"","env":[],"x":2024.3335571289062,"y":2840,"wires":[[]]},{"id":"55eda512.15332c","type":"ftp in","z":"934f1005.6e02","g":"e9e479b2.d1c26","ftp":"3164f7d8.d5f6c8","operation":"put","filename":"","localFilename":"","name":"FTP to Logs to NAS","x":1076.9998474121094,"y":2675.666748046875,"wires":[["92abf1aa.ac5508"]]},{"id":"92abf1aa.ac5508","type":"switch","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"FTP Success?","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"Put operation successful.","vt":"str"},{"t":"else"}],"checkall":"false","repair":false,"outputs":2,"x":1311,"y":2677.666748046875,"wires":[["b453fdd8.6e2188"],["cbc29a8d.fa9778"]]},{"id":"6e3faed6.28c038","type":"file","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Delete Local Logs","filename":"","appendNewline":true,"createDir":false,"overwriteFile":"delete","encoding":"none","x":1728.9999389648438,"y":2672.666748046875,"wires":[["377b7098.6fead8"]]},{"id":"b453fdd8.6e2188","type":"change","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Set Local File Path","rules":[{"t":"set","p":"filename","pt":"msg","to":"localFilename","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1516.9999389648438,"y":2671.666748046875,"wires":[["6e3faed6.28c038"]]},{"id":"265287cc.eb6cb","type":"comment","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Export logs > 4 weeks old","info":"","x":257.3333282470703,"y":2553.666664123535,"wires":[]},{"id":"ed539233.d43e08","type":"comment","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Change Log","info":"2021-06-04: Created\n2021-08-01: Added Error Handling process","x":215.66665649414062,"y":2895,"wires":[]},{"id":"12e96aa9.fd603d","type":"function","z":"934f1005.6e02","g":"e9e479b2.d1c26","name":"Delete archived records SQL","func":"//Build delete SQL\n\nvar d='delete from LOGS where date(time)<=\"'+msg.dateTo+ '\"'\nmsg.topic = d\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":2010.3333435058594,"y":2602.333335876465,"wires":[["39d8ad87.6af632"]]},{"id":"39d8ad87.6af632","type":"mysql","z":"934f1005.6e02","g":"e9e479b2.d1c26","mydb":"2d73f9f7.c404d6","name":"HE Delete Old Logs","x":2271,"y":2602.33349609375,"wires":[[]]},{"id":"2d73f9f7.c404d6","type":"MySQLdatabase","name":"Hubitat_Event_Prod","host":"192.168.1.86","port":"3306","db":"hubitat_logging","tz":"","charset":""},{"id":"3164f7d8.d5f6c8","type":"ftp","host":"192.168.1.110","port":"","secureOptions":"","user":"rakesh","connTimeout":"","pasvTimeout":"","keepalive":""}]
The ftp portion of the flow again is specific to my step up but can be easily deleted or adapted.
Display log data
The actual dashboard:
The date selection - this uses the date picker UI node
The device selection - this is built dynamically based on data selected
The error type selection - also built dynamically based on data selected
The complete flow:
[{"id":"47a878b3.bbadf","type":"group","z":"5e2453b7.c3e99c","style":{"stroke":"#999999","stroke-opacity":"1","fill":"none","fill-opacity":"1","label":true,"label-position":"nw","color":"#a4a4a4"},"nodes":["f72fda03.2de5e8","262f0a39.b3498e","734a613c.863588","e2c2c5c8.f71c58","cf7b050.4d0c6f8","183be8fb.e1077f","d780163e.6f942","fd3340d1.c2f3e","73810a4b.ce4adc","6138b8eb.678098","51b659a6.25eb7","409b949f.b8285c","99426c7.9c0959","acf79464.491cb8","755b9f2e.cddaf8","647ecd26.1ac3e4","c5fe6d2a.058538","7dfd18ff.3b32f","feb92447.41da88","40d223d.4d96edc","21978045.f330e","8fabaceb.332bb","2842b927.514efe","81d69fea.97f42","6696c56e.19cc6c","d294caea.d30598","cf661a1.510a8e8","8563b363.a007a8","67e2353e.fa45c4","2832223f.248756","c22872da.7238a","fac6cd5a.423ae8","86278a6f.65fcb8","f0f0c31.a558d4","10cca7da.08a46","3a17bbd1.01d664","30bff4f6.0c89cc","ae52cec9.32a718","ec16f5af.71061","4ddee6a4.d301e","22c9476d.5201c","b58b6ffc.18b78"],"x":47.666595458984375,"y":28.000015258789062,"w":3136.0013732910156,"h":660.6666526794434},{"id":"f72fda03.2de5e8","type":"link in","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Dashboard Startup - Logs In","links":["130b08c0.946dff"],"x":193.66659545898438,"y":297.66666412353516,"wires":[["262f0a39.b3498e","734a613c.863588"]],"l":true},{"id":"262f0a39.b3498e","type":"function","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Current Date","func":"msg.payload = new Date()\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":228.6666259765625,"y":366.33338165283203,"wires":[["e2c2c5c8.f71c58"]]},{"id":"734a613c.863588","type":"change","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Set Log Labels","rules":[{"t":"set","p":"topic","pt":"msg","to":"<font size=\"5\">Hubitat Logs Information</font>","tot":"str"},{"t":"set","p":"labelColor","pt":"msg","to":"#FFFFFF","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":219.66665649414062,"y":234.99999237060547,"wires":[["cf7b050.4d0c6f8"]]},{"id":"e2c2c5c8.f71c58","type":"ui_date_picker","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"","label":"Select Date","group":"a4f9c1dc.c51478","order":1,"width":"7","height":"1","passthru":true,"topic":"","topicType":"str","x":400.6666259765625,"y":366.33338165283203,"wires":[["183be8fb.e1077f"]],"inputLabels":["Today"]},{"id":"cf7b050.4d0c6f8","type":"ui_text","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","group":"2b1869e6.fc6116","order":2,"width":"26","height":"1","name":"Logs Header","label":"<font color ={{msg.labelColor}}>{{msg.topic}}</font>","format":"","layout":"row-spread","x":414.66668701171875,"y":234.99999237060547,"wires":[]},{"id":"183be8fb.e1077f","type":"moment","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"","topic":"","input":"","inputType":"msg","inTz":"ETC/GMT","adjAmount":0,"adjType":"days","adjDir":"add","format":"YYYY-MM-DD","locale":"en_US","output":"logDate","outputType":"flow","outTz":"America/Chicago","x":598.6665649414062,"y":366.33338165283203,"wires":[["d780163e.6f942","fd3340d1.c2f3e","73810a4b.ce4adc"]]},{"id":"d780163e.6f942","type":"function","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Fetch Logs SQL","func":"//Get Flow variable\n\nvar logDate =flow.get(\"logDate\")\n\nvar d1='select date_format(time,\"%Y-%m-%d %H:%i:%s\") as \"Date\",date_format(time,\"%Y-%m-%d %H:%i:%s\") as \"Time\",type as \"Type\",name as \"Device\", msg as \"Detail\", level as \"Level\" from LOGS where date(time) = \"'\nvar d2 = '\" order by row_id desc'\n\nvar d = d1+logDate+d2\n\nmsg.topic = d\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":867.6666259765625,"y":184.33343505859375,"wires":[["6138b8eb.678098"]]},{"id":"fd3340d1.c2f3e","type":"function","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Fetch Level SQL","func":"var logDate =flow.get(\"logDate\")\n\nvar d1 = 'select distinct level as \"value\", level as \"label\" from LOGS where date(time) = \"';\nvar d2 = '\" order by level'\n\nvar d = d1+logDate+d2\n\nmsg.topic = d\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":866.6666259765625,"y":603.000150680542,"wires":[["51b659a6.25eb7"]]},{"id":"73810a4b.ce4adc","type":"function","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Fetch Device SQL","func":"\nvar logDate =flow.get(\"logDate\")\n\nvar d1 = 'select distinct name as \"value\", name as \"label\" from LOGS where date(time) = \"';\nvar d2 = '\" order by name'\n\nvar d = d1+logDate+d2\nmsg.topic = d\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":876.333251953125,"y":304.00003814697266,"wires":[["409b949f.b8285c"]]},{"id":"6138b8eb.678098","type":"mysql","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","mydb":"2d73f9f7.c404d6","name":"","x":1096.6666259765625,"y":184.33338928222656,"wires":[["99426c7.9c0959","acf79464.491cb8"]]},{"id":"51b659a6.25eb7","type":"mysql","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","mydb":"2d73f9f7.c404d6","name":"","x":1111.6666870117188,"y":603.0001811981201,"wires":[["755b9f2e.cddaf8"]]},{"id":"409b949f.b8285c","type":"mysql","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","mydb":"2d73f9f7.c404d6","name":"","x":1093.333251953125,"y":304.00004959106445,"wires":[["647ecd26.1ac3e4"]]},{"id":"99426c7.9c0959","type":"change","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Count of log entries","rules":[{"t":"set","p":"logCount","pt":"msg","to":"$count(payload)","tot":"jsonata"},{"t":"set","p":"selectCount","pt":"msg","to":"$count(payload)","tot":"jsonata"},{"t":"set","p":"dataNoFilter","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1330,"y":126.33334350585938,"wires":[["c5fe6d2a.058538","7dfd18ff.3b32f"]]},{"id":"acf79464.491cb8","type":"ui_table","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","group":"9aa62032.6ecb9","name":"Logs Table","order":5,"width":"26","height":"16","columns":[],"outputs":0,"cts":false,"x":3087.66796875,"y":186.33348083496094,"wires":[],"inputLabels":["Events"]},{"id":"755b9f2e.cddaf8","type":"switch","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Selection = 1?","property":"$count(payload)","propertyType":"jsonata","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"gt","v":"1","vt":"num"}],"checkall":"true","repair":false,"outputs":2,"x":1343.0001220703125,"y":602.6669292449951,"wires":[["8fabaceb.332bb"],["2842b927.514efe"]]},{"id":"647ecd26.1ac3e4","type":"switch","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Selection = 1?","property":"$count(payload)","propertyType":"jsonata","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"gt","v":"1","vt":"num"}],"checkall":"true","repair":false,"outputs":2,"x":1311.3335266113281,"y":303.66686248779297,"wires":[["81d69fea.97f42"],["6696c56e.19cc6c"]]},{"id":"c5fe6d2a.058538","type":"ui_text","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","group":"a4f9c1dc.c51478","order":4,"width":"3","height":"1","name":"Total Count","label":"Total Events:","format":"{{msg.logCount}}","layout":"row-left","x":1536.666748046875,"y":95.33350372314453,"wires":[]},{"id":"7dfd18ff.3b32f","type":"link out","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Total Count - Out","links":["4ddee6a4.d301e"],"x":1553.3333740234375,"y":146.6666488647461,"wires":[],"l":true},{"id":"feb92447.41da88","type":"change","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"ui_control","rules":[{"t":"set","p":"ui_control","pt":"msg","to":"{\"tabulator\":{\"layout\":\"fitDataTable\",\"columns\":[{\"title\":\"Row\",\"field\":\"row_id\",\"formatter\":\"rownum\",\"width\":\"10%\"},{\"formatter\":\"datetime\",\"formatterParams\":{\"inputFormat\":\"YYYY-MM-DD HH:mm:ss\",\"outputFormat\":\"YYYY-MM-DD\",\"invalidPlaceholder\":\"(invalid date)\"},\"title\":\"Date\",\"field\":\"Date\",\"width\":\"10%\"},{\"formatter\":\"datetime\",\"formatterParams\":{\"inputFormat\":\"YYYY-MM-DD HH:mm:ss\",\"outputFormat\":\"hh:mm:ss A\",\"invalidPlaceholder\":\"(invalid time)\"},\"title\":\"Time\",\"field\":\"Time\",\"width\":\"10%\"},{\"title\":\"Level\",\"field\":\"Level\",\"width\":\"10%\"},{\"title\":\"Device\",\"field\":\"Device\",\"width\":\"20%\"},{\"title\":\"Detail\",\"field\":\"Detail\",\"width\":\"30%\"},{\"title\":\"Type\",\"field\":\"Type\",\"width\":\"10%\"}]}}","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":2882.66748046875,"y":106.33350372314453,"wires":[["acf79464.491cb8"]]},{"id":"40d223d.4d96edc","type":"mysql","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","mydb":"2d73f9f7.c404d6","name":"","x":2979.0001220703125,"y":538.6667098999023,"wires":[["acf79464.491cb8","8563b363.a007a8"]]},{"id":"21978045.f330e","type":"change","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Restore Original Data","rules":[{"t":"set","p":"payload","pt":"msg","to":"dataNoFilter","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":2477.33349609375,"y":295.6667251586914,"wires":[["acf79464.491cb8","8563b363.a007a8"]]},{"id":"8fabaceb.332bb","type":"change","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Set Dropdown Selection = 1","rules":[{"t":"set","p":"options[0]","pt":"msg","to":"payload.value","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1571.3338012695312,"y":553.3335239887238,"wires":[["c22872da.7238a"]]},{"id":"2842b927.514efe","type":"change","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Set Dropdown Selection > 1","rules":[{"t":"set","p":"options","pt":"msg","to":"payload.value","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1573.0001831054688,"y":642.0001802444458,"wires":[["c22872da.7238a"]]},{"id":"81d69fea.97f42","type":"change","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Set Dropdown Selection = 1","rules":[{"t":"set","p":"options[0]","pt":"msg","to":"payload.value","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1560,"y":266.33350372314453,"wires":[["fac6cd5a.423ae8"]]},{"id":"6696c56e.19cc6c","type":"change","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Set Dropdown Selection > 1","rules":[{"t":"set","p":"options","pt":"msg","to":"payload.value","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1559.6666259765625,"y":327.0000686645508,"wires":[["fac6cd5a.423ae8"]]},{"id":"d294caea.d30598","type":"link in","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Dashboard Startup Logs - In","links":["130b08c0.946dff"],"x":2635,"y":106.3333511352539,"wires":[["feb92447.41da88"]],"l":true},{"id":"cf661a1.510a8e8","type":"function","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Log SQL","func":"//Create SQL query depending on which drop down (Device or Level) is selected\n\nvar selectField = ''\nvar logDate =flow.get(\"logDate\") //NOTE: Date is stored from the date picker\n\nif (msg.topic =='Device Selection'){\n selectField = 'name';\n} else \nif (msg.topic == 'Level Selection'){\n selectField = 'level'\n}\n\n\n//Build the SQL statement\n\nvar d1='select date_format(time,\"%Y-%m-%d %H:%i:%s\") as \"Date\",date_format(time,\"%Y-%m-%d %H:%i:%s\") as \"Time\",type as \"Type\",name as \"Device\", msg as \"Detail\", level as \"Level\" from LOGS where date(time) = \"'\nvar d2 = '\" and '+selectField+' in (\"'+msg.criteria+'\") '\n\nvar d = d1+logDate+d2\n\nmsg.topic = d\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":2756.3336181640625,"y":538.0000839233398,"wires":[["40d223d.4d96edc"]]},{"id":"8563b363.a007a8","type":"change","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Selected Count","rules":[{"t":"set","p":"selectCount","pt":"msg","to":"$count(payload)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":2730,"y":596.9999809265137,"wires":[["f0f0c31.a558d4"]]},{"id":"67e2353e.fa45c4","type":"switch","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Data Selected?","property":"$count(payload)","propertyType":"jsonata","rules":[{"t":"gt","v":"0","vt":"num"},{"t":"eq","v":"0","vt":"num"}],"checkall":"true","repair":false,"outputs":2,"x":1988.33349609375,"y":289.33350372314453,"wires":[["10cca7da.08a46"],["21978045.f330e"]]},{"id":"2832223f.248756","type":"switch","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Data Selected?","property":"$count(payload)","propertyType":"jsonata","rules":[{"t":"gt","v":"0","vt":"num"},{"t":"eq","v":"0","vt":"num"}],"checkall":"true","repair":false,"outputs":2,"x":2108.33349609375,"y":592.3335800170898,"wires":[["3a17bbd1.01d664"],["21978045.f330e"]]},{"id":"c22872da.7238a","type":"ui_dropdown","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Log Level","label":"Log Level:","tooltip":"","place":"All Levels","group":"a4f9c1dc.c51478","order":3,"width":"5","height":"1","passthru":false,"multiple":true,"options":[],"payload":"","topic":"Level Selection","topicType":"str","x":1807.666748046875,"y":594.0002136230469,"wires":[["2832223f.248756","ae52cec9.32a718"]]},{"id":"fac6cd5a.423ae8","type":"ui_dropdown","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Devices","label":"Device(s):","tooltip":"","place":"All Devices","group":"a4f9c1dc.c51478","order":3,"width":"5","height":"1","passthru":false,"multiple":true,"options":[],"payload":"","topic":"Device Selection","topicType":"str","x":1787.6666259765625,"y":289.0000686645508,"wires":[["67e2353e.fa45c4","30bff4f6.0c89cc"]]},{"id":"86278a6f.65fcb8","type":"change","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Array2String","rules":[{"t":"set","p":"criteria","pt":"msg","to":"$join(payload,'\",\"')","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":2937.3336181640625,"y":472.0001449584961,"wires":[["cf661a1.510a8e8"]]},{"id":"f0f0c31.a558d4","type":"ui_text","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","group":"a4f9c1dc.c51478","order":4,"width":"3","height":"1","name":"Select Count","label":"Selected:","format":"{{msg.selectCount}}","layout":"row-left","x":2968.666748046875,"y":647.6666679382324,"wires":[]},{"id":"10cca7da.08a46","type":"link out","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Restore Data 1 - Out","links":["ec16f5af.71061"],"x":2195.333251953125,"y":247.00003814697266,"wires":[],"l":true},{"id":"3a17bbd1.01d664","type":"link out","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Restore Data 2 - Out","links":["ec16f5af.71061"],"x":2107,"y":521.3333969116211,"wires":[],"l":true},{"id":"30bff4f6.0c89cc","type":"change","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Clear Level Selection","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1815.666748046875,"y":485.0000457763672,"wires":[["c22872da.7238a"]]},{"id":"ae52cec9.32a718","type":"change","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Clear Device Selection","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1814.666748046875,"y":379.6666717529297,"wires":[["fac6cd5a.423ae8"]]},{"id":"ec16f5af.71061","type":"link in","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Restore Data - In","links":["10cca7da.08a46","3a17bbd1.01d664"],"x":2733.3333740234375,"y":471.3333969116211,"wires":[["86278a6f.65fcb8"]],"l":true},{"id":"4ddee6a4.d301e","type":"link in","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Total Count - In","links":["7dfd18ff.3b32f"],"x":2742.333251953125,"y":647.0000419616699,"wires":[["f0f0c31.a558d4"]],"l":true},{"id":"22c9476d.5201c","type":"comment","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Logs Tab","info":"","x":147.33334350585938,"y":69.00001525878906,"wires":[]},{"id":"b58b6ffc.18b78","type":"comment","z":"5e2453b7.c3e99c","g":"47a878b3.bbadf","name":"Change Log","info":"2021-05-31: Created. Filter commands not working - data was getting wiped when changing tabs\n2021-06-01: Replaced filter commands with SQL updates. Optimized and reorganized flow\n2021-06-03: Removed SQL call to get total log count and replaced with getting size of array with JSONATA ($count(payload))\n2021-06-03: Added showing count of filtered log entries","x":153.66665649414062,"y":612.6667175292969,"wires":[]},{"id":"a4f9c1dc.c51478","type":"ui_group","name":"Logs For:","tab":"c0ac087.290f478","order":2,"disp":true,"width":"26","collapse":false},{"id":"2b1869e6.fc6116","type":"ui_group","name":"Hub Log Information","tab":"c0ac087.290f478","order":1,"disp":false,"width":"26","collapse":false},{"id":"2d73f9f7.c404d6","type":"MySQLdatabase","name":"Hubitat_Event_Prod","host":"192.168.1.86","port":"3306","db":"hubitat_logging","tz":"","charset":""},{"id":"9aa62032.6ecb9","type":"ui_group","name":"Logs Table","tab":"c0ac087.290f478","order":3,"disp":true,"width":"26","collapse":false},{"id":"c0ac087.290f478","type":"ui_tab","name":"Logs","icon":"fa-cogs","order":2,"disabled":false,"hidden":false}]
Hope this helps. Feel free to ping with questions if you need.
