Hello - I've been playing around with Node Red and wanted to get feedback from more experienced people! I'm new to Hubitat and Node Red so please be gentle 
Background: I was having a hard time trying to follow the Hubitat native logs as they were getting spammed by entries from Zooz ZEN25 Double Plug and the Aeotec Multisensor6. The logs would not go back more than a day and thus were not very useful. I leveraged examples that I saw on found here and other places.
Basic Logic: Listen for events published by the MakerAPI, filter out events that I don't want to log (e.g. illuminance, temperature, power etc.), build the SQL statement and create an entry in a MySQL database. I created a bunch of virtual switches that get turned on/off by the automations and I use those the naming convention of "Virtual..." to log an automation event (I could not find a way to capture when an app executed, hence this workaround).
Script for filtering events:
//Create an array of events to be filtered out
var filterEvents = ['power','current','voltage','energy','illuminance','temperature','lastActivity','humidity','energyDuration']; // build the array of events to filter
if(filterEvents.indexOf(msg.payload.name)!== -1){ // check if published event needs to be filtered
msg = null //null should stop the flow
}
return msg;
Script for creating the SQL statement;
//Assign value to temp to build query
//
var q_source ="'"+ "events"+"',"; //default source is events
if(msg.payload.displayName.indexOf('Virtual')!== -1){ // check if this is an automation (requires virtual device with name "Virual...")
q_source = "'"+ "automation"+"',"
}
var q_displayName = msg.payload['displayName'] === undefined? "null,":"'"+msg.payload['displayName']+"',";
var q_name = "'"+msg.payload['name']+ "',";
var q_value = "'"+msg.payload['value']+ "',";
var q_unit = "'"+msg.payload['unit'] + "',";
var q_deviceId =msg.payload['deviceId']===undefined||msg.payload['deviceId']===null?"null,": "'"+msg.payload['deviceId']+"',";
var q_hubId =msg.payload['hubId']===undefined||msg.payload['hubId']===null?"null,": "'"+msg.payload['hubId']+"',";
var q_locationId =msg.payload['locationId']===undefined?"null,": "'"+msg.payload['locationId']+"',";
var q_installedAppId =msg.payload['installedAppId']===undefined?"null,":"'"+msg.payload['installedAppId']+"',";
var q_descriptionText =msg.payload['descriptionText']===undefined?"null,":"'"+msg.payload['descriptionText']+"'";
//Build the query
//
var query = "INSERT INTO events (source, name, displayName, value, unit, deviceId, hubId, locationId, installedAppId, descriptionText)";
query+= " VALUES ("+q_source+q_name+q_displayName+q_value+q_unit+q_deviceId+q_hubId+q_locationId+q_installedAppId+q_descriptionText+")";
msg.topic = query;
return msg;
Actual Database Entries:
Questions/feedback requests:
-
Areas for improvement? I haven't built in any error handling as yet, so any suggestions on how to handle that would be great
-
Is returning "null" if I want to stop the flow (filter script) an acceptable way? I tried to use a switch with no connector on one output, but I wasn't sure if that was the right way either.
-
Is there any downside to creating virtual switches? I don't expect to have a ton of devices (as yet!) - just need to move two door locks (Kwikset Z-Wave) and a garage door (GoControl) over, so will end up with about 25 or so actual devices and about 15-20 virtual devices.
Thanks for your help.